Bookmark and Share
Innodb DB gap lock and timeout
(Publish Date: 2011-2-11 4:05pm, Total Visits: 2630, Today: 1, This Week: 5, This Month: 6)

We hit a timeout issue on inserts/updates on an innodb table. We can only resume INSERTS/UPDATES by restarting apache or MySQL.  The inserts/updates were logged in the slow query log and time out on the value of "innodb_lock_wait_timeout=120".

A table was designed like the following:

CREATE TABLE `book` (
  `store_id` int(10) unsigned NOT NULL,
  `book_id` int(10) unsigned NOT NULL,
  `return_count` int(10) unsigned DEFAULT NULL,
  `creation_date` datetime DEFAULT NULL,
  `last_updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_updated_by` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`store_id`,`book_id`),
  KEY `last_updated_date` (`last_updated_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

There is another table store_book which has a similar primary key.

There are 20+ million rows in the "book" table and there are about 3000 new inserts and many new updates on this table every day. Sometimes, the front users or webmasters got "timeout" on the new inserts/updates. It needs to restart apache or mysql to resume the new inserts or updates.

1. by checking the lock status, found an insert hanging in the database with a gap lock on the Primary key.

ID       Type    Waiting  Wait   Active    Mode  DB    Table       Index    Ins Intent  Special      
1038018  TABLE         0  00:00  02:36:25  IX    test  book                  0               
1038018  TABLE         0  00:00  02:36:25  IX    test  store_book            0               
1038018  RECORD        0  00:00  02:36:25  S     test  book        PRIMARY   0   gap before rec

Killing the thread in MySQL released the locks and allowed new inserts/updates.

2. check the primary key, the left-most column of the primary key has very few distinct values (only 5), the key cardinality is very low.

mysql> select distinct(store_id) from book;

+---------+
| store_id |
+---------+
|       1 |
|      60 |
|     817 |
|     918 |
|     919 |
+---------+
5 rows in set (0.00 sec)

It is very easy to hit a gap lock or next-key lock when inserts/updates/deletes are on store_id=60 or 817 or 918.

3. To resolve this problem, rebuilding the index like the following (either ways should work):

1) change the order of (`store_id`,`book_id`) to (`book_id`,`store_id`)

The cardinality of "book_id" is very high and this change doesn't need developers to change codes.

2) add an auto-incremental ID column as the PK and make an unique key on (`book_id`,`store_id`).

Notes: Suggest to make the chagnes like 2) if it's easy for the developers to change their codes. Since in MySQL, the PK is a cluster key which is added to all second keys automatically. And MySQL5.1, innoDB plugin 1.0, MySQL5.5 which has innoDB plugin1.1 as the default innodb engine have new features on the auto_incremental lock and fast index creation on the second indexes. The composite primary key can't benefit from these new features.