Bug #40460 Falcon Repeatable Read transactions can be affected by destructive DDL
Submitted: 31 Oct 2008 16:36 Modified: 9 Mar 2010 20:12
Reporter: Kevin Lewis Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Falcon storage engine Severity:S3 (Non-critical)
Version:6.0 OS:Any
Assigned to: John Embretsen CPU Architecture:Any
Tags: F_ISOLATION, server

[31 Oct 2008 16:36] Kevin Lewis
Description:
A repeatable read transaction should continue to see the same data throughout its life.  But in Falcon currently, it can see the affects of destructive DDL statement immediately.  These statements include drop table, truncate, and alter table drop index.  These statements are non-transactional by intention and should remain so.  Falcon does not keep track of what files have previously been read by a transaction, only what has been written.

How to repeat:
=== CLIENT #1 ===
mysql> CREATE TABLE t1 (a INT) engine falcon;
Query OK, 0 rows affected (0.17 sec)

mysql> INSERT INTO t1 VALUES (1), (2), (3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t1;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

=== CLIENT #2 ===
mysql> TRUNCATE t1;
Query OK, 3 rows affected (0.08 sec)

=== CLIENT #1 ===
mysql> SELECT * FROM t1;
Empty set (0.00 sec)

Suggested fix:
In order to prevent this, Falcon should keep a bitmap in each transaction that tracks which tables it has touched.  Whenever a new table is touched, it should get a shared lock on a new SyncObject for that table called syncDestructiveDDL.  Then in order for a destructive DDL to continue, it must get an exclusive lock on this for the table involved.  This will make it wait for any existing transaction that has touched that table to end before it continues.  Newer repeatable read transactions will not starve the DDL because the SyncObject class will make new shared locks wait behind waiting exclusive locks.
[31 Oct 2008 16:39] Kevin Lewis
This is similar to bug#28006.  That test could be expanded to include the truncate, drop table, and drop index commands.  This bug is only different in that the solution is not to retain transaction isolation during DDL, but to delay the DDL.
[31 Oct 2008 17:09] MySQL Verification Team
Thank you for the bug report. Verified as described.
[10 Nov 2008 18:50] Kevin Lewis
Assign to Kevin Lewis.  This bug should be fixed by WL#4284.  We will keep it open until that can be tested.
[12 May 2009 21:35] Hakan Küçükyılmaz
If t1 has a PRIMARY KEY, then the TRUNCATE is blocked with

[23:34] root@test>TRUNCATE t1;
ERROR 1015 (HY000): Can't lock file (errno: 212)

If we test this scenario with a table without PK and any indexes, then the TRUNCATE is not blocked.
[20 May 2009 16:06] Kevin Lewis
This needs to be tested again after Bug#44836 is fixed by the Server team.  Hakan did not want to close the bug because of that inconsistency.

Here are the results of Hakan's testing;

               Falcon       InnoDB

DROP TABLE     blocked      blocked
TRUNCATE       ERROR 1015   works without block
DROP COLUMN    ERROR 1015   blocked
DROP INDEX     ERROR 1015   blocked
ADD COLUMN     ERROR 1015   blocked
ADD INDEX      ERROR 1015   blocked

Legend:  ERROR 1015 (HY000): Can't lock file (errno: 212)

DROP TABLE:  DROP TABLE t1;
TRUNCATE:    TRUNCATE t1;
DROP COLUMN: ALTER TABLE t1 DROP COLUMN b;
DROP INDEX:  ALTER TABLE t1 DROP PRIMARY KEY;
ADD COLUMN:  ALTER TABLE t1 ADD COLUMN c varchar(5);
ADD INDEX:   ALTER TABLE t1 ADD INDEX (b);

Details:
DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a int PRIMARY KEY, b varchar(5)) Engine Falcon;
-- In another session with Falcon
CREATE TABLE t1 (a int PRIMARY KEY, b varchar(5)) Engine InnoDB;

INSERT INTO t1 VALUES (1, 'aaaaa'), (2, 'bbbbb'), (3, 'ccccc');

-- Connection 1
SET @@autocommit = 0;
SELECT * FROM t1;

-- Connection 2
SET @@autocommit = 0;
-- Theses DML operations were tested in different sessions
DROP TABLE t1;
TRUNCATE t1;
ALTER TABLE t1 DROP COLUMN b;
ALTER TABLE t1 DROP PRIMARY KEY;
ALTER TABLE t1 ADD COLUMN c varchar(5);
ALTER TABLE t1 ADD INDEX (b);

-- CONNECTION 1
SELECT * FROM t1;
SELECT * FROM t1;
COMMIT;
SELECT * FROM t1;

> For Falcon strange things are happening. In my last email I got
> > ERROR 1015 (HY000): Can't lock file (errno: 212)
> > 
> > This time I get:
> > [22:33] root@test>SELECT * FROM t1;
> > ERROR 1213 (40001): Deadlock found when trying to get lock; try
> > restarting transaction
> > 
> > This means, that I have to do some further testing ...

The strange behavior is now documented as
http://bugs.mysql.com/bug.php?id=44836 DDL locking is not consistent
[4 Feb 2010 11:26] John Embretsen
Bug#44836 is now closed as Duplicate/Fixed in mysql-next-4284 branch, to be merged into next-mr.

No Falcon branch (old 6.0 server) is anywhere near compatibility with this branch, so I cannot test the fix against Falcon at this time.
[9 Mar 2010 20:12] Konstantin Osipov
We're fixing TRUNCATE for all engines in 5.5 (Bug#42643).
No Falcon-specific fix will be necessary.