MySQL Bugs: #24144: strange transactional behaviour: DROP TABLE drops also locks

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:	Assigned Account	CPU Architecture:	Any

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

test script

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

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

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

Thank you for a bug report/documentation request.

Looks like a duplicate of
Bug#12347 InnoDB: DROP TABLE ignores locks

Duplicate of Bug#989.