MySQL Bugs: #40460: Falcon Repeatable Read transactions can be affected by destructive DDL

Bug #40460	Falcon Repeatable Read transactions can be affected by destructive DDL
Submitted:	31 Oct 2008 17:36	Modified:	20 May 18:06
Reporter:	Kevin Lewis
Status:	QA testing
Category:	Server: Falcon	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Any
Assigned to:	John H. Embretsen	Target Version:	6.0-beta
Tags:	server, F_ISOLATION
Triage:	Triaged: D2 (Serious)

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.

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.

Thank you for the bug report. Verified as described.

Assign to Kevin Lewis.  This bug should be fixed by WL#4284.  We will keep it open until
that can be tested.

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.

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