List:Internals« Previous MessageNext Message »
From:Elena Date:September 11 2012 9:12pm
Subject:Metadata locking and non-transactional tables
View as plain text  
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
Thread
Metadata locking and non-transactional tablesElena11 Sep
  • Re: Metadata locking and non-transactional tablesSveta Smirnova11 Sep
    • Re: Metadata locking and non-transactional tablesElena Stepanova11 Sep
  • Re: Metadata locking and non-transactional tablesDmitry Lenev12 Sep