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

Question

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 fixed 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.

Scharf

are you sure all records are locked? InnoDB may lock neighborhood records (when you lock 18 - 17 and 19 as well), but should not lock records that are far awat in the btree - so try this with greater distance in the ids? Also, what is your isolation level? – Czardom

@Darhazer. Thanks. Changing the isolation level to READ COMMITTED solved the problem. Can you explain what was happening? Was MySQL gap locking my invoice_expenses table? Why didn't the same happen with invoice_components? Either way, if you post an answer I'll accept it. – Wisnicki

I have no answer why this is happening in REPEATABLE READ, so I'd rather put a bounty on the question, than to post incomplete answer. Is those the only queries run in the transactions? Is the second transaction started after performing the third query in the first transaction, or they are concurrent? I'll add a bounty after the 2 days required by SO – Czardom

I'll add everything as additional info for the question. Thanks for help. – Wisnicki

Jut wondering. What happens if you set isolation level to SERIALIZABLE ? – Engrail

I suspect it has to do with the REPEATABLE READ: "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." – Engrail

... behaviour, and the READ COMMITED behaviour: "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." – Engrail

(the above are from: dev.mysql.com/doc/refman/5.1/en/set-transaction.html ) – Engrail

@Miloš Rašić can you add result from "EXPLAIN" to check @ypercube theory? As well as check if the result is the same in SERIALIZABLE? @ypercube post it as an answer, so it can be rewarded. – Czardom

What I'd like to test (I don't have time now) is if this thing happens with not sequential ids, like 18 and 20, or 18 and 99. If it still happens, then my guess that it has to do with "gap locks and next-key locks" is probably wrong. – Engrail

@MilošRašić did you fiind your answer about why this occur? – Mccollum

@MilošRašić and can you also show me your table indexes? – Mccollum

Answer 1

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

Adultery

My innodb_locks_unsafe_for_binlog is OFF. I have the same results when locking rows for id 18 and trying to select for update rows for id 21. There is no id 20. – Wisnicki

@Miloš: And if you set innodb_locks_unsafe_for_binlog to ON? Does it still happen? – Engrail

We can't tell yet if this is causing the problem, but your answer is usefull for me, and is the only one that provides some insights, so I'm granting you the bounty. I hope OP will continue testing and we will find the root cause. – Czardom

Answer 2

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

Roland

Answer 3

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.

Ciera

Sorry, Rush, you either didn't read my question in detail or didn't understand it very well. I'm keeping the first transaction open on purpose to test the concurrency. The problem is that it is locking rows which it is not selecting in the table invoice_expenses. This is on a development server so there are no other users, connections or transactions. Already using SHOW ENGINE INNODB STATUS. – Wisnicki