Showing entries 1 to 1
Displaying posts with tag: gap lock (reset)
One more InnoDB gap lock to avoid

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`)
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 …
[Read more]
Showing entries 1 to 1