Solution for inserting intent lock in MySQL

Question

I have a very simple table:

CREATE TABLE `d` ( `id` int(11) DEFAULT NULL, UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 

without records:

 select * from d; Empty set (0,01 sec) 

Then I try to open two transactions in different sessions:

Session number 1:

 begin; Query OK, 0 rows affected (0,00 sec) select * from d where id = 100 for update; Empty set (0,00 sec) 

Session number 2:

 begin; Query OK, 0 rows affected (0,00 sec) select * from d where id = 700 for update; Empty set (0,00 sec) 

Now I am trying to insert a new entry into Session No. 2 , and the session is “freezing”:

 insert into d values (700); 

And when I try to do the same (with a different id field) in session # 1 , it will work:

 insert into d values (100); --> ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction in Session #1 insert into d values (700); --> Query OK, 1 row affected (4,08 sec) in Session #2 

How can I fix a dead end? InnoDB Status:

 ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2017-07-06 15:59:25 0x70000350d000 *** (1) TRANSACTION: TRANSACTION 43567, ACTIVE 15 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 4, OS thread handle 123145358217216, query id 89 localhost root update insert into d values (700) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 126 page no 4 n bits 72 index id of table `trx`.`d` trx id 43567 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** (2) TRANSACTION: TRANSACTION 43568, ACTIVE 7 sec inserting mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 3, OS thread handle 123145357938688, query id 90 localhost root update insert into d values (100) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 126 page no 4 n bits 72 index id of table `trx`.`d` trx id 43568 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 126 page no 4 n bits 72 index id of table `trx`.`d` trx id 43568 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** WE ROLL BACK TRANSACTION (2) 

Answer 1

I suspect the deadlock is because InnoDB is conservative about spaces. Note that 100 and 700 are in the same foggy area of ​​untouched land. InnoDB cannot (or at least not) understand that “100” and “700” are different. InnoDB would like to label individual rows, but there are no rows with these identifiers in this table.

Transaction 2 is likely to go into timeout (see innodb_lock_wait_timeout ). When you pushed transaction 1 a second time to C # 2, still in need of a lock, InnoDB lost and gave up.

Bottom line: live with dead ends. When this happens, start with BEGIN . You have found yet another obscure case where an unnecessary dead end occurs.

In addition, I suspect that fixing the code for this case will slow down most other cases and lead to a series of errors that will require several releases to detect and fix.

Answer 2

Please note that starting with MySQL 8.0.1, the performance scheme reveals innodb data locks.

See https://dev.mysql.com/doc/refman/8.0/en/data-locks-table.html

See https://dev.mysql.com/doc/refman/8.0/en/data-lock-waits-table.html

In this example, after the first selection, only in lock session 1:

 mysql> select * from performance_schema.data_locks \G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 1808:76 ENGINE_TRANSACTION_ID: 1808 THREAD_ID: 35 EVENT_ID: 13081 OBJECT_SCHEMA: test OBJECT_NAME: d PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 139756088373592 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 1808:2:5:1 ENGINE_TRANSACTION_ID: 1808 THREAD_ID: 35 EVENT_ID: 13111 OBJECT_SCHEMA: test OBJECT_NAME: d PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: id OBJECT_INSTANCE_BEGIN: 139756088370552 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: supremum pseudo-record <--- HERE 2 rows in set (0.00 sec) 

This is not a solution to the impasse itself, but the visibility of the accepted locks is of great importance for understanding the problem.

Here both SELECT FOR UPDATE blocks the "suprenum" record, because identifiers 100 and 700 are larger than the largest identifier in the table (it is empty).

As soon as new entries appear (say, with ID = 500), both requests must be executed simultaneously, since another space in the identifiers will be blocked.