InnoDB gap lock problem in Mysql _mysql

When troubleshooting a deadlock problem for a customer, I encountered an interesting case involving a InnoDB clearance lock. I expect that the transaction should not have a lock for a WHERE clause does not match any of the rows, but I was wrong. Let's take a look at this table and the example update.

Mysql> Show CREATE TABLE preferences \g
*************************** 1 row ***************************
    Table:preferences
Create table:create Table ' preferences ' (
 ' numericid ' int (a) unsigned not NULL,
 ' Receivenotifications ' tinyint (1) Default NULL,
 PRIMARY KEY (' numericid ')
) engine=innodb default charset= Latin1
1 row in Set (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows Affected (0.00 sec)
mysql> SELECT COUNT (*) from preferences;
+----------+
| COUNT (*) |
+----------+
|    0 |
+----------+
1 row in Set (0.01 sec)
mysql> UPDATE Preferences set receivenotifications= ' 1 ' WHERE numericid = ' 2 ';
Query OK, 0 rows affected (0.01 sec)
rows matched:0 changed:0 warnings:0

InnoDB status Display This update holds an X lock on the primary index record:

---TRANSACTION 4a18101, ACTIVE sec
2 lock struct (s), heap size 376, 1 row lock (s)
MySQL thread ID 3, OS thread Handle 0x7ff2200cd700, query ID localhost msandbox
trx Read view would not? Trx with ID >= 4a18102, sees < 4a18102
Table LOCK table ' test '. ' Preferences ' TRX ID 4a18101 LOCK mode IX record
LOCKS Space ID 31766 page No 3 n BITS-index ' PRIMARY ' of table ' test '. ' Preferences ' TRX ID 4a18101 lock_mode X

That's why, Heikki in his bug report, it makes sense, I know it's hard to fix, but with a little disgust I want it to be differentiated. To complete this article, let me prove the deadlock situation mentioned above, the following MYSQL1 is the first session, MYSQL2 is another, the order of the query is as follows:

Mysql1> BEGIN;
Query OK, 0 rows Affected (0.00 sec)
mysql1> UPDATE Preferences SET receivenotifications= ' 1 ' WHERE numericid = ' 1 ';
Query OK, 0 rows Affected (0.00 sec)
Rows matched:0 changed:0 warnings:0
mysql2> BEGIN;
Query OK, 0 rows Affected (0.00 sec)
mysql2> UPDATE Preferences SET receivenotifications= ' 1 ' WHERE numericid = ' 2 ';
Query OK, 0 rows Affected (0.00 sec)
Rows matched:0 changed:0 warnings:0
mysql1> INSERT into preferences (NUMERICID, receivenotifications) VALUES (' 1 ', ' 1 '); --This one goes into the LOCK wait
mysql2> inserts into preferences (NUMERICID, receivenotifications) VALUES (' 2 ', ' 1 ') );
ERROR 1213 (40001): Deadlock found when trying to get lock; Try restarting transaction

Now you see how easy it is to cause deadlocks, so be sure to avoid this--if the insert part from the transaction causes the inserted write operation to not match any rows, do not do so, use replace into or use read-committed transaction isolation.