Question about InnoDB deadlock in MySQL?

Question

I found this interesting problem in MySQL InnoDB, can someone explain why the engine always claims to be a dead end.

First, I created a table with one row, one 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 threads of MySQL connectors, 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 same:

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

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

Until innodb_lock_wait_timeout defaults, thread2 just waits until thread1 exits its exclusive lock on the table, and that's fine.

However, in thread1, if I enter the following update request:

     mysql> update SeqNum set `current_seq_num` = 8;
     ERROR 1213 (40001): Deadlock found when trying to get lock; 
     try restarting transaction

Now thread2 will receive a select request because thread1 quits.

Also, in thread1, if I enter an update request 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)

Can anyone explain this?

Answer 1

SQL

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

Answer 2

"SELECT... FOR UPDATE" INTENTION EXCLUSIVE (IX) SeqNum EXCLUSIVE (X) , SELECT.

Innodb. , :

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

, :

show engine innodb status \G

"... ", ( 5 ):

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     -  ;;

IX X - ( ) , .

"select... for update", :

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     -  ;;

IX , "X " .

.

"5.1.37-1ubuntu5.1", REPEATABLE-READ.

:

MySQL - 13.6.8.1. InnoDB