question about InnoDB deadlock in MySQL?

I found this kind of interesting problem in MySQL InnoDB engine, could anyone explain why the engine always claim it's a deadlock.

First, I created a table with a single row, single column:

   CREATE TABLE `SeqNum` (`current_seq_num` bigint(30) NOT NULL default '0',
                           PRIMARY KEY  (`current_seq_num`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
   Query OK, 0 rows affected (0.03 sec)

   mysql> insert into SeqNum values (5);
   Query OK, 1 row affected (0.00 sec)

Now, I have two MySQL connector threads, In thread1:

    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)

    mysql> select `current_seq_num` into @curr_seq FROM SeqNum FOR UPDATE;
    Query OK, 1 row affected (0.00 sec)

Now, in thread2, I did the exactly same:

    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)

    mysql> select `current_seq_num` into @curr_seq FROM SeqNum FOR UPDATE;

before the default innodb_lock_wait_timeout, the thread2 just wait for thread1 to release its exclusive lock on the table, and it's normal.

However, in thread1, if I input the following update query:

     mysql> update SeqNum set `current_seq_num` = 8;
     ERROR 1213 (40001): Deadlock found when trying to get lock; 
     try restarting transac开发者_开发知识库tion

Now, thread2 get the select query finished because thread1 quits.

In addition, in thread1, if I input the update query with a where clause, it can be executed very well:

     mysql> update SeqNum set `current_seq_num` = 8 where `current_seq_num` =5
     Query OK, 1 row affected (0.00 sec)

Could anyone explain this?

Why do you even want to execute this SQL instead of

REPLACE INTO SeqNum VALUES (NULL); 
SELECT last_insert_id();

"SELECT ... FOR UPDATE" places an INTENTION EXCLUSIVE (IX) lock on the SeqNum table, and places an EXCLUSIVE (X) lock on all rows matching the SELECT criteria.

The state of the locks can be seen using the Innodb lock monitor. This is enabled by creating a specially named table:

create table innodb_lock_monitor( i int not null ) engine = innodb;

The state of the locks is then displayed whenever the following command is issued:

show engine innodb status \G

When the first thread executes "Select ... for update", the following locks are placed (I have one row with value 5 in the table) :

MySQL thread id 42, query id 338 localhost root
TABLE LOCK table `test`.`SeqNum` trx id 0 1284 lock mode IX
RECORD LOCKS space id 0 page no 51 n bits 72 index `PRIMARY` of table `test`.`SeqNum` trx id 0 1284 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 8000000000000005; asc         ;; 1: len 6; hex 000000000503; asc       ;; 2: len 7; hex 800000002d0110; asc     -  ;;

Here is the IX lock on the table, and two X locks - one on the gap (supremum) after the only row, and the other on the actual data row.

When the "select ... for update" is executed in the second thread, the following locks are added:

TABLE LOCK table `test`.`SeqNum` trx id 0 1285 lock mode IX
RECORD LOCKS space id 0 page no 51 n bits 72 index `PRIMARY` of table `test`.`SeqNum` trx id 0 1285 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 8000000000000005; asc         ;; 1: len 6; hex 000000000503; asc       ;; 2: len 7; hex 800000002d0110; asc     -  ;;

This is a new IX lock on the table, plus a "X waiting" lock on the only data row.

The original thread can run an update against the whole table, or against the only data row, with no deadlock.

This is running "5.1.37-1ubuntu5.1", with REPEATABLE-READ.

See:

MySQL Manual - 13.6.8.1. InnoDB Lock Modes