Bug #67924 Row lock isn't released after evaluating WHERE condition in READ COMMITTED
| Submitted: | 17 Dec 2012 15:01 | Modified: | 17 Dec 2012 18:39 |
|---|---|---|---|
| Reporter: | Vitaly Litvak | Email Updates: | |
| Status: | In progress | Impact on me: | |
| Category: | MySQL Server: Locking | Severity: | S2 (Serious) |
| Version: | 5.1.59, 5.5.24 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | innodb, lock, read committed, row | ||
[17 Dec 2012 15:02]
Vitaly Litvak
Data for three tables, where issue always reproduces
Attachment: _20121212_1939.sql (text/x-sql), 52.92 KiB.
[17 Dec 2012 18:39]
Sveta Smirnova
Thank you for the report. Verified as described.
[17 Dec 2012 18:40]
Sveta Smirnova
test case for MTR
Attachment: bug67924.test (application/octet-stream, text), 52.17 KiB.
[28 May 1:12]
MySQL Admin
Posted by developer: Bug status updated to 'In progress'
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
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.
Description: Our application executes several concurrent DELETE statements with INNER JOINs on same table. In default REPEATABLE READ transaction mode it results in dead locks. So we've decided to move to the READ COMMITTED, which is as told by documentation avoids using of gap-locks by using of simple record-locks. Also it's told that locks on all non-matching rows are released right after WHERE condition is evaluated. I believe this means that in case no rows matched after statement completes all row-level locks on a table are released. In our case we have one small child table with couple of rows related with the parent table with large number of rows. The relation isn't defined as a foreign key, but the column used for relation has index. However, MySQL query optimizer decides not to use the index, but iterate through couple of child table rows and thus lock them. This lock never gets released before transaction is committed or rolled back. The application is written in java, uses official mysql JDBC driver v. 5.1.12, hibernate ORM to execute queries. The issue reproduces from the MySQL command-line tool. How to repeat: Attached is the simple SQL dump file for three tables, which are being joined in concurrent DELETE statements. To reproduce the problem the following queries must be ran in two separate transactions: * Transaction 1 set transaction isolation level read committed; set autocommit=0; begin; DELETE pw FROM proc_warnings pw INNER JOIN day_position dp ON dp.transaction_id = pw.transaction_id INNER JOIN ivehicle_days vd ON vd.id = dp.ivehicle_day_id WHERE vd.ivehicle_id=2 AND dp.dirty_data=1; * Transaction 2 set transaction isolation level read committed; set autocommit=0; begin; DELETE pw FROM proc_warnings pw INNER JOIN day_position dp ON dp.transaction_id = pw.transaction_id INNER JOIN ivehicle_days vd ON vd.id = dp.ivehicle_day_id WHERE vd.ivehicle_id=13 AND dp.dirty_data=1; Suggested fix: None. The only workaround is to increase the innodb_lock_wait_timeout parameter up to 500 seconds or so. Then keep fingers crossed.