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?