I got a deadlock when having more than one sessions doing the following:
- start transaction
- insert data to table A
- delete data from table A
- 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)
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?
Best Answer
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.