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?