MySQL: Error: Lock wait timeout exceeded; try restarting transaction

Problem Description

The following conflict is seen in Dbvisit Replicate when replicating to MySQL:

OE.INVENTORIES at transaction 0010.002.00000317 at SCN 6673008
SQL text (with replaced bind values): update OE."INVENTORIES"
set QUANTITY_ON_HAND = 95435594
where (1=1)
and QUANTITY_ON_HAND = 95435595
and PRODUCT_ID = 587
and WAREHOUSE_ID = 56

Error: Lock wait timeout exceeded; try restarting transaction

If we log into MySQL we can see the actual lock:

mysql> SHOW ENGINE INNODB STATUS;
...
...
---TRANSACTION 0 18614, ACTIVE 21383 sec, process no 22126, OS thread id 1096927552 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 14320
MySQL thread id 15, query id 2433 dbvlin702.dbvisit.co.nz 172.16.1.72 root Updating
update OE.INVENTORIES
set QUANTITY_ON_HAND = '95435594'
where (1=1)
and QUANTITY_ON_HAND = '95435595'
and PRODUCT_ID = '587'
and WAREHOUSE_ID = '56'
------- TRX HAS BEEN WAITING 193 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 192 n bits 376 index GEN\_CLUST\_INDEX of table OE/INVENTORIES trx id 0 18614 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 6; hex 000000000334; asc 4;; 1: len 6; hex 000000002735; asc '5;; 2: len 7; hex 80000001350110; asc 5 ;; 3: len 8; hex 8000000000000001; asc ;; 4: len 8; hex 800000000000005b; asc [;; 5: len 8; hex 800000000339a203; asc 9 ;;

It can also be seen in the MySQL process list:

mysql> show processlist;
+----+------+-------------------------------+--------+---------+-------+----------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info|
+----+------+-------------------------------+--------+---------+-------+----------+------------------------------------------------------------------------------------------------------+
| 11 | root | dbvlin702.dbvisit.co.nz:29078 | dbvrep | Sleep | 18564 | | NULL | 
| 12 | root | dbvlin702.dbvisit.co.nz:29080 | dbvrep | Sleep | 301 | | NULL | 
| 13 | root | dbvlin702.dbvisit.co.nz:29088 | dbvrep | Sleep | 42358 | | NULL | 
| 14 | root | dbvlin702.dbvisit.co.nz:29089 | dbvrep | Query | 301 
| Updating | update OE.INVENTORIES set QUANTITY_ON_HAND = '95435594' where (1=1) and QUANTITY_ON_HAND = '95435595 | 
| 16 | root | dbvlin702.dbvisit.co.nz:44135 | dbvrep | Sleep | 18564 | | NULL | 
| 18 | root | localhost | OE | Query | 0 | NULL | show processlist |
+----+------+-------------------------------+--------+---------+-------+----------+------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

Solution

The issue is due to an internally created index called GEN_CLUSTER_INDEX for InnoDB. This basically means there was NO explicit Primary Key on the InnoDB table and InnoDB was creating its own internal Primary Key. The underlying issue seems to be related to gap locks on indexes which is described here: http://www.mysqlperformanceblog.com/2012/03/27/innodbs-gap-locks/

This problem can be resolved by creating a primary key (PK) on the OE.INVENTORIES table:

If this does not work, then also set the transaction isolation to read committed (like Oracle). Edit /etc/my.cnf:

And restart mysql:

Arjen Visser August 26, 2013 17:48