Bug #24144 strange transactional behaviour: DROP TABLE drops also locks
Submitted: 9 Nov 2006 14:20 Modified: 18 Jun 2008 10:45
Reporter: Matthias Leich Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5.1-BK OS:Linux (Linux)
Assigned to: Dmitry Lenev CPU Architecture:Any

[9 Nov 2006 14:20] Matthias Leich
Description:
Heavy modified subtestcase of innodb.test/mix2.inc (in 5.1)
-----------------------------------------------------------
Snip of my protocol:

SET STORAGE_ENGINE = InnoDB;
# Establish the connection b (user=root)
# Switch to connection default (user=root)
default:         DROP TABLE IF EXISTS t1;
default:         CREATE TABLE t1(a INT NOT NULL, b INT, PRIMARY KEY(a));
default:         INSERT INTO t1(a) VALUES (1);
default:         SET AUTOCOMMIT = 0;
default:         SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
# Switch to connection default (user=root)
default:         COMMIT;
default:         SELECT * FROM t1 WHERE a = 1;
default:         a      b
default:         1      NULL
# Switch to connection b (user=root)
b:  DELETE FROM t1;
b:  ERROR HY000: Lock wait timeout exceeded; try restarting transaction
         <--- This response is ok.
# Switch to connection default (user=root)
default:         SELECT * FROM t1;
default:         a      b
default:         1      NULL
         <--- Excellent, the "read" is repeatable.
default:         COMMIT;
 ---- now we run the same test with DROP TABLE instead of DELETE --
# Switch to connection default (user=root)
default:         COMMIT;
default:         SELECT * FROM t1 WHERE a = 1;
default:         a      b
default:         1      NULL
# Switch to connection b (user=root)
b:  DROP TABLE t1;
         <--- This is surprising -- I expected
              ERROR HY000: Lock wait timeout exceeded .. --
              but ok as long as connection default sees effects
              following it's ISOLATION LEVEL.
# Switch to connection default (user=root)
default:         SELECT * FROM t1;
default:         ERROR 42S02: Table 'test.t1' doesn't exist
         <--- No repatable read though we have
              TRANSACTION ISOLATION LEVEL SERIALIZABLE.
              It looks for me as if this is a capital violation
              of the SQL standard. I also could not find a page
              within the MySQL manual mentioning this behaviour.

I can replay this strange effect also with connection default
doing INSERT or UPDATE instead of the SELECT. The modification
is lost with the table. AFAIR this is a lost update which is
also not allowed in all ISOLATION LEVELS.

My environment:
- PC Intel Pentium M (x86-32Bit) with Linux(SuSE 10.1)
- MySQL compiled from source
     mysql-5.0 last ChangeSet@1.2290, 2006-10-24
     mysql-5.1 show the same strange behaviour
     BUILD/compile-pentium-debug-max

How to repeat:
Please use my attached testscript ml06.test
  copy it to mysql-test/t
  echo "Dummy" > r/ml06.result # Produce a dummy file with 
                               # expected results
  ./mysql-test-run.pl ml06
[9 Nov 2006 14:21] Matthias Leich
test script

Attachment: ml06.test (application/octet-stream, text), 1.32 KiB.

[9 Nov 2006 14:42] Heikki Tuuri
Matthias,

Monty decided in December 2000 that DROP TABLE should succeed even if someone else has locks on the table.

It is in the MySQL AB TODO to change that behavior. I am classifying this as a Server bug because the fix has to be done in the server. The interpreter should ask the engine if there are some other users' locks on the table.

Regards,

Heikki
[9 Nov 2006 15:38] Matthias Leich
Heikki,

thank you for your fast response.
I did not know that there was such a decision.
Even if the bug report will be classified as change request
we have here a clear documentation bug.

Regards,

Matthias
[9 Nov 2006 17:55] Valeriy Kravchuk
Thank you for a bug report/documentation request.
[4 Dec 2006 21:52] Peter Gulutzan
Looks like a duplicate of
Bug#12347 InnoDB: DROP TABLE ignores locks
[18 Jun 2008 10:45] Konstantin Osipov
Duplicate of Bug#989.