0

I got a deadlock when having more than one sessions doing the following:

  1. start transaction
  2. insert data to table A
  3. delete data from table A
  4. commit

at the same time

They may delete the same records or different

I could replicate it with a simplified code like below, the table is empty at the start

Session 1:

BEGIN TRANSACTION
DECLARE @component_id  numeric = 1
DECLARE @relative_component_id  numeric = 0

INSERT INTO component_lock(area_id, component_id, generic_id, operator_id, session_id, lock_date, lock_type) 
VALUES(1, @component_id, 1, 1, 1, '1/1/2018', 1)

WAITFOR DELAY '00:00:05'

DELETE FROM component_lock
WHERE area_id = 1
AND (component_id = @component_id OR component_id = @relative_component_id)
AND operator_id = 1 
AND generic_id = 1
AND session_id = 1

COMMIT

Session 2

BEGIN TRANSACTION
DECLARE @component_id  numeric = 2
DECLARE @relative_component_id  numeric = 0

INSERT INTO component_lock(area_id, component_id, generic_id, operator_id, session_id, lock_date, lock_type) 
VALUES(1, @component_id, 1, 1, 1, '1/1/2018', 1)

WAITFOR DELAY '00:00:05'

DELETE FROM component_lock
WHERE area_id = 1
AND (component_id = @component_id OR component_id = @relative_component_id)
AND operator_id = 1 
AND generic_id = 1
AND session_id = 1

COMMIT

The primary key is (area_id, component_id, generic_id)

The foreign key is (operator_id)

There are 2 indexes on component_id and operator_id (nonclustered)

Deadlock graph enter image description here

Actual execution plan of the delete query from session that was not chosen as the deadlock victim https://www.brentozar.com/pastetheplan/?id=ryyPArbi7

The deadlock does not happen if I remove the OR operator and change it to 2 delete queries

DELETE FROM component_lock
WHERE area_id = 1
AND (component_id = @component_id)
AND operator_id = 1 
AND generic_id = 1
AND session_id = 1

DELETE FROM component_lock
WHERE area_id = 1
AND (component_id = @relative_component_id)
AND operator_id = 1 
AND generic_id = 1
AND session_id = 1

Actual execution plan https://www.brentozar.com/pastetheplan/?id=Sy9ACrbsm

Can anyone please explain to me why the difference?

3
  • Indexes? Plans? Oct 12, 2018 at 2:14
  • @AaronBertrand I have added more details Oct 12, 2018 at 5:02
  • @MikeMinhLe, it's better to upload actual execution plan XML and add the link to your question rather than post images. For the deadlock, add the deadlock XML graph to your question.
    – Dan Guzman
    Oct 12, 2018 at 11:58

2 Answers 2

0

The issue is not about how many delete operations there are, it’s about how it finds the rows that are to be deleted.

If you look at the query plans you’ll almost certainly let see that your delete with OR is scanning the whole table. This will take out locks to make sure the rows don’t move around while it’s looking for ones to delete. Meanwhile, your inserts will take out locks, as the table and other indexes are maintained. Without the OR, you’re getting Seek operations, and only small parts of the table are locked.

10
  • Thanks Rob, that's what I thought at first, however checking the actual execution plan, it shows that Clustered Index Seek is used for both cases (with OR or without) Oct 12, 2018 at 6:04
  • @MikeMinhLe if there's only two rows, they'll both be on the same page. In this instance the whole table is equal to that one page (minus the metadata) Oct 12, 2018 at 9:17
  • If you look at the “Actual Number of Rows Read”, is there a discrepancy between the two types of plans?
    – Rob Farley
    Oct 12, 2018 at 9:52
  • HI Rob, I checked the actual rows using Plan Explorer and they are the same (=1) for both plans. In my test, @relative_component_id is 0, so it does not match any row in the table Oct 14, 2018 at 23:25
  • No, I mean the Actual Number of Rows Read, not the Actual Number of Rows.
    – Rob Farley
    Oct 15, 2018 at 0:26
-1

Replace your delete query with a select to select all primary key columns and then delete the rows using only primary key columns

SELECT component_id, generic_id, area_id 
INTO #temp
FROM component_lock
WHERE area_id = 1
AND (component_id = @component_id OR component_id = @relative_component_id)
AND operator_id = 1 
AND generic_id = 1
AND session_id = 1

DELETE FROM component_lock cl
JOIN #temp t ON t.area_id = cl.area_id and t.generic_id = cl.generic_id and t.component_id = cl.component_id

This will change the locking pattern and hopefully will resolve the deadlocks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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