From: Sveta Smirnova
Date: September 11 2012 10:09pm
Subject: Re: Metadata locking and non-transactional tables
List-Archive: http://lists.mysql.com/internals/38581
Message-Id: <504FB6AE.9030407@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Hi Elena,

On 09/12/2012 12:12 AM, Elena wrote:
> 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.

I think this behavior described in the MySQL user reference manual at 
http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html, 
particularly in the last paragraph.

More details are in Davi's comments at 
http://bugs.mysql.com/bug.php?id=61391

Sveta.

>
>
> 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
>

-- 
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.