Bug #44836 | DDL locking is not consistent | ||
---|---|---|---|
Submitted: | 12 May 2009 21:38 | Modified: | 4 Feb 2010 10:32 |
Reporter: | Hakan Küçükyılmaz | Email Updates: | |
Status: | Duplicate | Impact on me: | |
Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
Version: | mysql-6.0-falcon | OS: | Any |
Assigned to: | Assigned Account | CPU Architecture: | Any |
[12 May 2009 21:38]
Hakan Küçükyılmaz
[12 May 2009 21:56]
Hakan Küçükyılmaz
Happens in mysql-6.0-falcon tree. Most likely also in mysql-6.0 tree.
[13 May 2009 7:25]
Sveta Smirnova
Thank you for the report. Verified as described using mysql-6.0-falcon tree.
[13 May 2009 7:30]
Sveta Smirnova
With mysql-6.0 results almost as described, although ----<Q>---- -- Connection 2 SET @@autocommit = 0; -- Falcon get's ERROR 1015 (HY000): Can't lock file (errno: 212) -- InnoDB it blocks here ALTER TABLE t1 DROP COLUMN b; ----</Q>---- doesn't block in case of InnoDB
[12 Jan 2010 18:29]
Konstantin Osipov
Hello Jon Olav, this should be resolved by the patch that implements the simple deadlock detection heuristics. Please investigate, and let's discuss on IRC. It should be closed as a duplicate of Bug#46273 if it's no longer repeatable.
[13 Jan 2010 13:20]
Jon Olav Hauglid
I've looked into this bug using the current version of the mysql-next-4284 tree. The reason why the two tests appear inconsistent, is SET autocommit = 0. Doing COMMIT with autocommit off, does not turn autocommit on again. This means that the second test starts with autocommit off. At the time when ALTER TABLE is issued in the first test, the active transaction in connection 1 consists of: SELECT * FROM t1; At the time when ALTER TABLE is issued in the second test, the active transaction in connection 1 consists of: INSERT INTO t1 VALUES (1, 'aaaaa'), (2, 'bbbbb'), (3, 'ccccc'); SELECT * FROM t1; This has consequences for the table locks held by InnoDB which changes where ALTER TABLE blocks. In the first test, ALTER TABLE blocks after setting table_share->version to 0 during upgrade to exclusive metadata lock. This makes SELECT unable to use the table_share. It will back off and report ER_LOCK_DEADLOCK. In the second test, ALTER TABLE blocks earlier - during copy to tmp table (due to InnoDB). At this point table_share->version has not been reset and the metadata lock has not been upgraded. This makes it possible for SELECT to execute. Replacing SET AUTOCOMMIT = 0 with START TRANSACTION or forcing COMMIT after the INSERT, makes the two tests consistent. The reported deadlock is very likely a duplicate of Bug#46272. I will verify this once a patch for Bug#46272 is ready.