Description:
Steps:
1. time1 - session 1 - INSERT into detail table with foreign key to master table
Result: shared lock to master table row
2. time2 - session 2 - INSERT into detail table with foreign key to master table
Result: shared lock to master table row
3. time3 - session 1 - trigger logic ON INSERT try UPDATE some data in master table (try get exclusive lock), but can't do it because session 2, hold shared lock on master table row - DEADLOCK
How to repeat:
Preparing:
CREATE TABLE documents (
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
, sum INT NOT NULL DEFAULT 0
)
;
CREATE TABLE document_positions (
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
, document_id INT UNSIGNED NOT NULL
, price INT
, quantity INT
, CONSTRAINT document_positions_documnent FOREIGN KEY (document_id) REFERENCES documents (id)
)
;
CREATE TRIGGER document_positions_a_i_trg AFTER INSERT
ON document_positions
FOR EACH ROW
BEGIN
-- some other logic
-- .....
SELECT SLEEP(30) INTO @dummy
;
-- some other logic
-- .....
UPDATE documents
SET sum = sum + IFNULL(NEW.price * NEW.quantity, 0)
WHERE id = NEW.document_id
;
END
;
INSERT documents VALUES ()
;
Session 1:
00:00:00> INSERT document_positions (document_id, price, quantity) VALUES (1, 10, 1)
00:00:34> 1 row affected in 34 s 153 ms
Session 2:
00:00:04> INSERT document_positions (document_id, price, quantity) VALUES (1, 20, 2)
00:00:34> [40001][1213] Deadlock found when trying to get lock; try restarting transaction
Session 3:
00:00:15> SELECT * FROM performance_schema.data_locks
+---------+------------------+----------+---------------------+---------+-------------+-----------+---------+
|THREAD_ID|OBJECT_NAME |INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE |LOCK_STATUS|LOCK_DATA|
+---------+------------------+----------+---------------------+---------+-------------+-----------+---------+
|1314 |documents |NULL |139785482092872 |TABLE |IS |GRANTED |NULL |
|1314 |document_positions|NULL |139785482092784 |TABLE |IX |GRANTED |NULL |
|1314 |documents |PRIMARY |139785482089872 |RECORD |S,REC_NOT_GAP|GRANTED |1 |
|1310 |documents |NULL |139785482086776 |TABLE |IS |GRANTED |NULL |
|1310 |document_positions|NULL |139785482086688 |TABLE |IX |GRANTED |NULL |
|1310 |documents |PRIMARY |139785482083696 |RECORD |S,REC_NOT_GAP|GRANTED |1 |
+---------+------------------+----------+---------------------+---------+-------------+-----------+---------+
Suggested fix:
Oracle Database in same situation make TX (exclusive row lock) lock on documents and document_positions tabled and deadlock not happening
Description: Steps: 1. time1 - session 1 - INSERT into detail table with foreign key to master table Result: shared lock to master table row 2. time2 - session 2 - INSERT into detail table with foreign key to master table Result: shared lock to master table row 3. time3 - session 1 - trigger logic ON INSERT try UPDATE some data in master table (try get exclusive lock), but can't do it because session 2, hold shared lock on master table row - DEADLOCK How to repeat: Preparing: CREATE TABLE documents ( id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT , sum INT NOT NULL DEFAULT 0 ) ; CREATE TABLE document_positions ( id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT , document_id INT UNSIGNED NOT NULL , price INT , quantity INT , CONSTRAINT document_positions_documnent FOREIGN KEY (document_id) REFERENCES documents (id) ) ; CREATE TRIGGER document_positions_a_i_trg AFTER INSERT ON document_positions FOR EACH ROW BEGIN -- some other logic -- ..... SELECT SLEEP(30) INTO @dummy ; -- some other logic -- ..... UPDATE documents SET sum = sum + IFNULL(NEW.price * NEW.quantity, 0) WHERE id = NEW.document_id ; END ; INSERT documents VALUES () ; Session 1: 00:00:00> INSERT document_positions (document_id, price, quantity) VALUES (1, 10, 1) 00:00:34> 1 row affected in 34 s 153 ms Session 2: 00:00:04> INSERT document_positions (document_id, price, quantity) VALUES (1, 20, 2) 00:00:34> [40001][1213] Deadlock found when trying to get lock; try restarting transaction Session 3: 00:00:15> SELECT * FROM performance_schema.data_locks +---------+------------------+----------+---------------------+---------+-------------+-----------+---------+ |THREAD_ID|OBJECT_NAME |INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE |LOCK_STATUS|LOCK_DATA| +---------+------------------+----------+---------------------+---------+-------------+-----------+---------+ |1314 |documents |NULL |139785482092872 |TABLE |IS |GRANTED |NULL | |1314 |document_positions|NULL |139785482092784 |TABLE |IX |GRANTED |NULL | |1314 |documents |PRIMARY |139785482089872 |RECORD |S,REC_NOT_GAP|GRANTED |1 | |1310 |documents |NULL |139785482086776 |TABLE |IS |GRANTED |NULL | |1310 |document_positions|NULL |139785482086688 |TABLE |IX |GRANTED |NULL | |1310 |documents |PRIMARY |139785482083696 |RECORD |S,REC_NOT_GAP|GRANTED |1 | +---------+------------------+----------+---------------------+---------+-------------+-----------+---------+ Suggested fix: Oracle Database in same situation make TX (exclusive row lock) lock on documents and document_positions tabled and deadlock not happening