InnoDB SELECT ... FOR UPDATE statement locking all rows in a table

MySQL Server version 5.1.41 with InnoDB plugin enabled. I have the following three tables for invoices: invoices, invoice_components and invoice_expenses. Table invoices has invoice_id primary key. Both invoice_components and invoice_expenses are linked to table invoices with invoice_id as a non-unique foreign_key (each invoice can have more than one component and more than one expense). Both tables have a BTREE index for this foreign key.

I have the following transactions:

transaction 1

START TRANSACTION; 
SELECT * FROM invoices WHERE invoice_id = 18 FOR UPDATE; 
SELECT * FROM invoice_components WHERE invoice = 18 FOR UPDATE; 
SELECT * FROM invoice_expenses WHERE invoice = 18 FOR UPDATE; 

Everything works ok for the first transaction and the rows are selected and locked.

transaction 2

START TRANSACTION; 
SELECT * FROM invoices WHERE invoice_id = 19 FOR UPDATE; 
SELECT * FROM invoice_components WHERE invoice = 19 FOR UPDATE; 
SELECT * FROM invoice_expenses WHERE invoice = 19 FOR UPDATE; 

The second transaction returns ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction for the third query.

The same happens when I try to SELECT ... FOR UPDATE other invoices and their components and expenses. It seems the first transaction has locked all the rows in invoice_expenses table. Any ideas why this is happening?

Additional info

Transaction 2 starts after the third query of transaction 1. There are no other users, connections or transactions on the server.

The problem occurs in the default REPEATABLE READ transaction isolation level. It is fi开发者_如何学Goxed by changing to READ COMMITTED level. This is a solution but it still doesn't explain why the problem is occurring with invoice_expenses and not with invoice_components.

I suspect it has to do with gap locks and next-key locks and the differences in the behaviour of REPEATABLE READ :

The excerpts are from MySQL docs: SET TRANSACTION syntax

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range.

and READ COMMITTED :

Note: In MySQL 5.1, if the READ COMMITTED isolation level is used or the innodb_locks_unsafe_for_binlog system variable is enabled, there is no InnoDB gap locking except for foreign-key constraint checking and duplicate-key checking. Also, record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition.

Perhaps OP can tell us the status of innodb_locks_unsafe_for_binlog system variable and if the same locking occurs when this variable's setting is changed.

Also, if same locking happens with not sequential ids, like 18 and 20, or 18 and 99

"For index records the search encounters, SELECT ... FROM ... FOR UPDATE blocks other sessions from doing SELECT ... FROM ... LOCK IN SHARE MODE or from reading in certain transaction isolation levels. Consistent reads will ignore any locks set on the records that exist in the read view"

What are those certain locks which can be applied with select for update so that other sessions cannot read locked record?

You are using a transaction; autocommit does not disable transactions, it just makes them automatically commit at the end of the statements that do not have an explicit start transaction on them.

What is happening is, some other thread is holding a record lock on some record (you're updating every record in the table!) for too long, and your thread is being timed out.

You can see more details of the event by issuing a "SHOW ENGINE INNODB STATUS" after the event. Ideally do this on a quiet test-machine.