On 09/12/2012 12:12 AM, Elena wrote:
> [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
> 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.
I think this behavior described in the MySQL user reference manual at
particularly in the last paragraph.
More details are in Davi's comments at
> 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.
Sveta Smirnova, Principal Technical Support Engineer, Russia
MySQL @ Oracle, Moscow, Russia, www.oracle.com/mysql
Don't forget to register for MySQL Connect Conference (Sept. 29-30, San
Francisco) where MySQL engineers, support staff, users, and customers
will share roadmap, directions, and deployment best practices.