1

Can we experience deadlock if run the same update query concurrently?

Let's assume there's a table with millions of records and we need to update several thousand records at one shot.

UPDATE TABLEX
SET Column1 = '1'
WHERE Column2 BETWEEN 1 AND 10000

I'm wondering if running this query concurrently would lead to a deadlock as each update query may acquire update locks on rows in a different order.

In other words, is the following a possible scenario. The scenario is that two concurrent sessions run the same query to update the same set of records.

(My assumption is that each UPDATE statement will scan rows in a different order)

  1. Session 1: Acquires Update lock on row 1 (Column2 = 1)
  2. Session 2: Acquires Update lock on row 2 (Column2 = 2)
  3. Session 1: Tries to acquire Update lock on row 2 but it fails as it's already held by Session 2. (Column2 = 2)
  4. Session 2: Tries to acquire Update lock on row 1 but it fails as it's already held by Session 1. (Column2 = 1)
  5. Deadlock is detected.

Here, my assumption is that each query may scan rows in a different order which implies locks on rows (resources) will be taken in different orders. Does this lead to a deadlock?

2
  • 1
    Please share the deadlock graph (upload it to gist.github.com) – MBuschi Feb 11 at 8:02
  • 3
    Is this a hypothetical question, or an actual deadlock you are running in to? If the latter, do you have any other statements happening inside the same transaction? – Colin 't Hart Feb 11 at 8:49
2

Here, my assumption is that each query may scan rows in a different order which implies locks on rows (resources) will be taken in different orders.

The scenario, as you've outlined it, is unlikely under normal circumstances. The exact same query being run concurrently will most likely use the same execution plan - which means they will both scan the index on TABLEX in the same order. This situation would most likely lead to blocking rather than deadlocks between the two identical queries.

This assumes you have an index on Column2 that can be used to seek to the right spot, and then scan the subset of rows that need to be updated.

If you don't have an index on Column2, you could end up with a parallel scan of the index / table, and thus your updates could be in an indeterminate order. Consider a plan like this:

screenshot of parallel update plan

The order of rows coming out of the clustered index scan will vary depending on how many cores are being used by the plan, and how busy each core is doing other work - as well as disk-related waits and things like that. Unless the "gather streams" is order-preserving, the rows will be updated in a sort of random order. This could lead to deadlocks as you've described.

In summary, the answer to your question depends on several factors, including the details of your table / indexes, isolation levels, server and database level settings, etc. Hopefully this gives you some idea of what's involved though.

8
  • Thanks Josh. Now, I understand better how it works. So, it's always better to prevent the db engine from scanning the whole db by using an index. – Ehsan Mirsaeedi Feb 11 at 14:56
  • Is there any book or article that describes this behavior in detail? – Ehsan Mirsaeedi Feb 11 at 15:03
  • @JoshDarnell great answer, the bit I can't quite get my head around is, if 2 update statements are running concurrently and they scan the rows in a different order, what are the 2 resources that would result in a deadlock rather than blocking? I would have assumed that one process would still gain a lock on the row first and block the other process? – Dale K Feb 11 at 22:31
  • @JoshDarnell no you understand my comment correctly. I can't get my head around how scanning in opposite orders can cause a deadlock - I'm more familiar with (and assumed this was classic) 2 table deadlocks e.g. dotnettutorials.net/lesson/sql-server-deadlock-examples. I would have assumed that scanning the table in different orders would just result in one process being blocked? – Dale K Feb 12 at 19:25
  • 1
    @DaleK No, this: process A needs to update rows 1, 2, and 3. Process B needs to update the same three rows, but it's doing them in the opposite order. Process A updates row 1, then row 2. In that moment, Process B jumps in and updates row 3. Now it needs to update row 2, which is locked by Process A. Neither can proceed now. Deadlock. – Josh Darnell Feb 13 at 3:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.