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 |
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 transaction
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?
