1

I am getting a deadlock between these 2 queries from 2 separate stored procedures:

1.

UPDATE ord
SET
    [Num] = @Num,
    [Ref] = @Re,
    [Date] = @Date
FROM [Order] ord
INNER JOIN [Orders_Open] oo ON oo.Id = ord.ID

2.

    UPDATE oo
    SET oo.Name = i.Name,
    oo.Email = i.Email,
    oo.PostCode = i.PostCode,
    FROM Item_Order i 
    INNER JOIN [Orders_Open] oo ON oo.ID = i.ID

What is the best way to tackle preventing a deadlock from happening? I have looked into using holdlock to prevent a deadlock before it happens:

e.g.

    UPDATE oo WITH (HOLDLOCK)
    SET oo.Name = i.Name,
    oo.Email = i.Email,
    oo.PostCode = i.PostCode,
    FROM Item_Order i 
    INNER JOIN [Orders_Open] oo ON oo.ID = i.ID

Would this work? Or can anyone else give advice on what else i could try?

Thanks

Ryan Gadsdon
  • 2,272
  • 4
  • 31
  • 56
  • 1
    Do you have any previous operations on the same transaction in each SP? – EzLo Feb 18 '19 at 14:21
  • More than 1 update firing at the same time, hitting the same source? – jimmy8ball Feb 18 '19 at 14:43
  • @EzLo nope, just a select statement. The transactions are also implicit. Would it resolve any issues by making them explicit? – Ryan Gadsdon Feb 18 '19 at 15:14
  • I'm pretty sure the issue is that when the UPDATES are firing, update 1 is joining in your oo table which is also being updated - have you tried simply adding a nolock to the UPDATE 1 script that follows INNER JOIN [Orders_Open] oo WITH (NOLOCK) ? – jimmy8ball Feb 18 '19 at 15:17
  • @RyanGadsdon, if you mean autocommit (default behavior where each statement is an individual transaction), I wouldn't expect an explict transaction to make a different. Implicit transactions start a transaction when updates are performed but do not commit the transaction automatically. – Dan Guzman Feb 18 '19 at 15:18
  • How do `[Order]` from first query and `Item_Order` from second are related? – Alex Yu Feb 18 '19 at 15:29
  • @AlexYu do you mean what relation do they have? – Ryan Gadsdon Feb 18 '19 at 15:32
  • `[Order]` and `Item_Order`: a) different tables and totally unrelated, b) different tables with relation thru constraint(s), c) one of them is view of another, d) synonyms, e) name of one of them is mistyped – Alex Yu Feb 18 '19 at 15:36
  • @AlexYu b) different tables with relation thru constraint(s) – Ryan Gadsdon Feb 18 '19 at 15:38
  • @jimmy8ball what if NOLOCK isnt an option because of the potential for dirty reads? – Ryan Gadsdon Feb 18 '19 at 15:42
  • Do you have indexes on the foreign keys? If not, the join need to scan more rows when reading which rows to update – Daniel Stackenland Feb 18 '19 at 15:49

2 Answers2

0

You can,t update same record simultaneously because data integrity must be kept in every transaction.

data integrity refers to the accuracy and consistency of data stored in a database, data warehouse, data mart or other construct.

You can use one transaction block for both of query to prevent deadlock.

Milad
  • 117
  • 1
  • 8
0

Best way is to tune both for performance, so its less likely they will both will be running at the same moment.

RichardBSmith
  • 11
  • 1
  • 3