Bug #22350 InnoDB: Select ... for update creates a deadlock without primary key

• Submitted: 14 Sep 2006 14:45
• Modified: 14 Sep 2006 21:02
• Reporter: Jeff C
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.0.24a
• OS: Linux (RHEL)
• Assigned to: Heikki Tuuri
• CPU Architecture: Any

[14 Sep 2006 14:45] Jeff C

Description:
InnoDB issue:  Errors out on a deadlock when primary key not specified.

I know that InnoDB will create it's own primary key if I don't specify one.  Therefore I was testing out "select ... for update" and noticed deadlocks.  

I realized that you MUST define your own primary key or deadlocks will happen... InnoDB will not use it's internal primary key.

How to repeat:
THIS WORKS PROPERLY
(A) = session a
(B) = session b

(A) create table t (id int not null primary key) engine=InnoDB;
(A) insert into t values (1);
(A) begin;
(A) select * from t for update;
(B) begin;
(B) select * from t for update;
(A) update t set id = 2 where id = 1;
(A) commit;

-- Note: (A) does not deadlock

THIS DOES NOT WORK PROPERLY (Either docs need updated or innodb needs to use internal primary key)

(A) create table t (id int) engine=InnoDB;
(A) insert into t values(1);
(A) begin;
(A) select * from t for update;
(B) begin;
(B) select * from t for update;
(A) update t set id = 2 where id = 1;   

-- Note: (A) DEADLOCKS here...

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Suggested fix:
Either update docs to reflect that the primary key must be defined, or fix InnoDB to use it's own internal key.

[14 Sep 2006 15:36] Heikki Tuuri

Jeff,

this is already addressed in the manual:

http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html

"
Add well-chosen indexes to your tables. Then your queries need to scan fewer index records and consequently set fewer locks. Use EXPLAIN SELECT to determine which indexes the MySQL server regards as the most appropriate for your queries. 
"

Regards,

Heikki

[14 Sep 2006 15:40] Valeriy Kravchuk

I was not able to repeat the behaviour described with 5.0.26-BK on Linux, by the way. Even without explicit PRIMARY KEY declared, I've got exectly the same results. Session B just waited for COMNMIT in session A, but session A was able to execute UPDATE. So, no deadlocks.

[14 Sep 2006 21:02] Jeff C

Ok, well in 5.0.24a it deadlocked.  I just want to prove that it was in fact happening.  

(root@localhost) [test]> select version();
+-----------------+
| version()       |
+-----------------+
| 5.0.24a-max-log | 
+-----------------+
1 row in set (0.00 sec)

(root@localhost) [test]> create table t (id int) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [test]> insert into t values (1);
Query OK, 1 row affected (0.00 sec)

(root@localhost) [test]> select * from t;
+------+
| id   |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)

(root@localhost) [test]> begin;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [test]> select * from t for update;
+------+
| id   |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)

************* Session 2 ******************

(root@localhost) [test]> begin;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [test]> select * from t for update;
-- Blocks

******************** Session 1 again **************

(root@localhost) [test]> update t set id = 2 where id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

****************** Session 2 ********************

(root@localhost) [test]> select * from t for update;
+------+
| id   |
+------+
|    1 | 
+------+
1 row in set (9.33 sec)