Bug #52377 | MDL locks taken by failing statements not automatically removed | ||
---|---|---|---|
Submitted: | 25 Mar 2010 20:15 | Modified: | 24 Jun 2010 10:43 |
Reporter: | Matthias Leich | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Locking | Severity: | S4 (Feature request) |
Version: | 5.5.4-m3,5.6.99-m4 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | mdl |
[25 Mar 2010 20:15]
Matthias Leich
[25 Mar 2010 21:19]
Sveta Smirnova
Thank you for the report. According to http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html this is not a bug: A transaction that tries to access a row that is locked by another InnoDB transaction will hang for at most this many seconds before issuing the following error: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction When a lock wait timeout occurs, the current statement is not executed. The current transaction is not rolled back. (To have the entire transaction roll back, start the server with the --innodb_rollback_on_timeout option, available as of MySQL 5.1.15. See also Section 13.6.12, “InnoDB Error Handling”.)
[26 Mar 2010 14:35]
Matthias Leich
Hi Sveta, sorry, I have some problems to understand your answer but maybe my report was not clear enough. The effect I am focused on is the hanging DROP TABLE t1. We have here MySQL >= 5.5 which has metadata locking (MDL). A MDL lock held by some session prevents that another session could drop the object (table,...), change it's layout or similar. MDL should prevent a lot unfortunate effects known from MySQL <= 5,1. con1: BEGIN a transaction do something con2: BEGIN a transaction do something on table t1 which - collides with a lock held by con1 => Get error message after innodb_lock_wait_timeout or - violates a constraint => Get error message because of duplicate key or similar con1: ROLLBACK; DROP TABLE t1; ==> hangs "endless" till lock_wait_timeout is exceeded ERROR HY000: Lock wait timeout exceeded; try restarting transaction In case of MySQL 5.1 the "DROP TABLE t1" would have success. Experiments + discussion with Jon Olav point to the following reason: =============================================== The action of con2 on the table t1 causes that in an early phase of the statement processing a Metadata lock (MDL) on t1 is set. And this MDL lock does not get automatically removed when it finally turns out that the statement cannot have success. I have the impression that row-level locks taken by a failing statement are at least mostly removed at it's end. The DROP TABLE t1 of con1 fails because of the MDL lock held by con2. IMHO from the - SQL standard point of view the behaviour I met is not wrong - system throughput/performance point of view it would have an advantage if MDL locks caused by a failing statement get automatically removed at statement end but I am unsure if all consequences fit to what the SQL standard says about ISOLATION LEVELs etc. - user POV my scenario is probably rather rare So what to do with this bug report? I do not know. The behaviour I met - is intentional => no bug - is drastic different than in MySQL 5.1 but not documented within the 5.5 manual => documentation bug - could be probably improved but the overall win is rather low => change request ? Regards Matthias
[26 Mar 2010 17:38]
Sveta Smirnova
Matthias, thank you for the feedback. Your arguments sounds reasonable and I did mistake during verification: missed con2 doesn't really use table t1, just tries to use it, Setting bug to "Verified", so runtime team will decide how to properly handle this problem.
[15 Jun 2010 15:00]
Konstantin Osipov
The bug needs to be split into two. We indeed need to document the effect of transactional locks on failing statements. This effect is intentional, since in with replication logging enabled, failed statements are still written to the binary log, and metadata locks protect consistency of the binary log. We need to keep these locks for both, statement and row based replication, since DDL is always replicated as SQL. The second part of the bug is a feature/extention/performance request, to automatically remove metadata locks on failed statements if the binary log is off. In theory, we could remove metadata locks whenever we rollback a statement that only modifies transactional tables, and all effects of the statements are being rolled back as well.
[15 Jun 2010 15:01]
Konstantin Osipov
Matthias, could you please morph this report into two reports as described above and request their triage? You could perhaps reuse the bug id of this report for one of the issues.
[24 Jun 2010 9:30]
Konstantin Osipov
Waiting for feedback from the reporter.
[24 Jun 2010 10:37]
Matthias Leich
The missing documentation part of this bug report is now moved to Bug#54767 Please document the effect of metadata locks taken by failing statements
[24 Jun 2010 10:43]
Matthias Leich
After moving the missing documentation part of this bugs out the remaining stuff is a feature/extention/performance request.
[25 Jun 2010 14:37]
Paul DuBois
For documentation part (Bug#54767) Added to http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html: If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.
[8 Jul 2010 13:10]
Konstantin Osipov
Setting to to be fixed later -- has no significant performance impact.