While troubleshooting deadlocks for a customer, I came around an interesting situation involving InnoDB gap locks. For a non-INSERT write operation where the WHERE clause does not match any row, I expected there should’ve been no locks to be held by the transaction, but I was wrong. Let’s take a look at this table and and example UPDATE.
mysql> SHOW CREATE TABLE preferences \G
*************************** 1. row ***************************
Table: preferences
Create Table: CREATE TABLE `preferences` (
`numericId` int(10) 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 …[Read more]