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)
- Session 1: Acquires Update lock on row 1 (Column2 = 1)
- Session 2: Acquires Update lock on row 2 (Column2 = 2)
- Session 1: Tries to acquire Update lock on row 2 but it fails as it's already held by Session 2. (Column2 = 2)
- Session 2: Tries to acquire Update lock on row 1 but it fails as it's already held by Session 1. (Column2 = 1)
- 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?