Bug #48128 unexpected lock timeout while selecting from innodb table

• Submitted: 17 Oct 2009 5:18
• Modified: 17 Oct 2009 7:42
• Reporter: A Ramos
• Status: Not a Bug
• Category: MySQL Server: Locking
• Severity: S5 (Performance)
• Version: 5.0.67
• OS: Linux (Ubuntu build)
• CPU Architecture: Any
• Tags: innodb, locking

[17 Oct 2009 5:18] A Ramos

Description:
While innodb table is modified in one transaction(session), another session cannot read from that table, leading to a lock time out.

How to repeat:
In Session #1:
--------------
mysql> create table z1 (a int) engine=innodb;
Query OK, 0 rows affected (0.03 sec)

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

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

In Session #2:
--------------
mysql> create temporary table z2 as select * from z1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

[17 Oct 2009 7:42] Valeriy Kravchuk

This is clearly documented in the manual, http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html:

"If innodb_locks_unsafe_for_binlog  is enabled and the transaction isolation level is not SERIALIZABLE, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S. InnoDB has to set locks in the latter case: In roll-forward recovery from a backup, every SQL statement must be executed in exactly the same way it was done originally.

CREATE TABLE ... SELECT ... performs the SELECT with shared next-key locks or as a consistent read, as for INSERT ... SELECT. "

Check the value of innodb_locks_unsafe_for_binlog in your case.