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:
None 
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
Description:
DDL locking is not consistent when tested on a fresh install compared to later operations.

How to repeat:
-- Connection 1 with a complete fresh installation
DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a int PRIMARY KEY, b varchar(5)) Engine Falcon;
-- In another session try with InnoDB
-- CREATE TABLE t1 (a int PRIMARY KEY, b varchar(5)) Engine InnoDB;
INSERT INTO t1 VALUES (1, 'aaaaa'), (2, 'bbbbb'), (3, 'ccccc');

SET @@autocommit = 0;
SELECT * FROM t1;

-- Connection 2
SET @@autocommit = 0;
-- this blocks
ALTER TABLE t1 DROP COLUMN b;

-- Connection 1
-- this happens with Falcon and InnoDB
-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
SELECT * FROM t1;

--
-- COMMIT on both connections and start the test again
-- Connection 1
DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a int PRIMARY KEY, b varchar(5)) Engine Falcon;
INSERT INTO t1 VALUES (1, 'aaaaa'), (2, 'bbbbb'), (3, 'ccccc');

SET @@autocommit = 0;
SELECT * FROM t1;

-- 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;

-- Connection 1
-- works as expected
SELECT * FROM t1;
COMMIT;

-- Connection 2
SET @@autocommit = 0;
-- works
ALTER TABLE t1 DROP COLUMN b;

-- Connection 1
-- works as expected
SELECT * FROM t1;
[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.
[4 Feb 2010 10:32] Jon Olav Hauglid
The deadlock reported in the test case is no longer repeatable
in mysql-next-4284 with the patch for Bug#46272.
Closing this bug as duplicate of Bug#46272.

For the seemingly inconsistent locking mentioned in the bug title,
see the above comment.