MySQL Forums :: Transactions :: Problem: A SELECT ... FOR UPDATE statement locking all rows in a table
Problem: A SELECT ... FOR UPDATE statement locking all rows in a table
Posted by: Miloš Rašić
Date: July 13, 2011 10:17AM
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:
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.
SELECT * FROM invoices WHERE invoice_id = 19 FOR UPDATE;
SELECT * FROM invoices 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?
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.