Hello,
[The question was originally meant for Dmitry Lenev, but whoever else
can shed light on it, please do so, your input will be very much
appreciated]
Recently I was looking into some obscure deadlocks which turned out to
be test issues, and it reminded me about a trick in behavior of
non-transactional tables in a transactional context, introduced along
with metadata locking in 5.5. If I remember correctly, when I first
encountered it, I was told that it was by design; but I'm not 100% sure
it was indeed so.
Could you please take a look and confirm, and if possible, provide some
insights on why it happens, and whether there is a room for improvement,
should there be anybody willing to spend time on it.
The point is, as long as metadata locking is involved, even
non-transactional tables become, to some extent, transactional -- at
least the locks are held till the whole "transaction" is finished, not
just for the duration of the statement:
# Once upon a time, there was a non-transactional table...
CREATE TABLE t1 (i INT) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1),(2);
# And then there came a connection in no-autocommit mode...
connect con1,localhost,root,,;
SET AUTOCOMMIT=0;
# It did different things, and touched our table in the process...
UPDATE t1 SET i=3;
# And then another connection decided to alter the table...
connection default;
ALTER TABLE t1 ADD COLUMN f INT;
# And it hung for 1 year (which happens to be default lock_wait_timeout)...
connection con1;
SHOW PROCESSLIST;
Id User Host db Command Time State Info Progress
2 root localhost test Query 1 Waiting for table
metadata lock ALTER TABLE t1 ADD COLUMN f INT 0.000
3 root localhost test Query 0 NULL SHOW PROCESSLIST
0.000
# because the first connection didn't know it locked anything
# as it didn't touch transactional tables,
# and didn't even think of doing an explicit COMMIT.
Regards,
Elena
