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

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

mysql server version 5.1.41 innodb plugin enabled. have next 3 tables invoices: invoices, invoice_components , invoice_expenses. table invoices has invoice_id primary key. both invoice_components , invoice_expenses linked table invoices invoice_id non-unique foreign_key (each invoice can have more 1 component , more 1 expense). both tables have btree index foreign key.

i have next transactions:

transaction 1

start transaction; select * invoices invoice_id = 18 update; select * invoice_components invoice = 18 update; select * invoice_expenses invoice = 18 update;

everything works ok first transaction , rows selected , locked.

transaction 2

start transaction; select * invoices invoice_id = 19 update; select * invoice_components invoice = 19 update; select * invoice_expenses invoice = 19 update;

the sec transaction returns error 1205 (hy000): lock wait timeout exceeded; seek restarting transaction 3rd query.

the same happens when seek select ... update other invoices , components , expenses. seems first transaction has locked rows in invoice_expenses table. ideas why happening?

additional info

transaction 2 starts after 3rd query of transaction 1. there no other users, connections or transactions on server.

the problem occurs in default repeatable read transaction isolation level. fixed changing read committed level. solution still doesn't explain why problem occurring invoice_expenses , not invoice_components.

i suspect has gap locks , next-key locks , differences in behaviour of repeatable read :

the excerpts mysql docs: set transaction syntax

for locking reads (select update or lock in share mode), update, , delete statements, locking depends on whether statement uses unique index unique search condition, or range-type search condition. unique index unique search condition, innodb locks index record found, not gap before it. other search conditions, innodb locks index range scanned, using gap locks or next-key (gap plus index-record) locks block insertions other sessions gaps covered range.

and read committed :

note: in mysql 5.1, if read committed isolation level used or innodb_locks_unsafe_for_binlog scheme variable enabled, there no innodb gap locking except foreign-key constraint checking , duplicate-key checking. also, record locks nonmatching rows released after mysql has evaluated condition.

perhaps op can tell status of innodb_locks_unsafe_for_binlog system variable , if same locking occurs when variable's setting changed.

also, if same locking happens not sequential ids, 18 , 20, or 18 , 99

mysql transactions innodb isolation-level locks