On 12.09.2012 2:09, Sveta Smirnova wrote:
> Hi Elena,
> 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
Thank you for the links. They confirm that I remembered correctly and it
was by design indeed.
One could argue that the manual there is somewhat controversial: it says
"the server achieves this by acquiring metadata locks on tables used
within a transaction and deferring release of those locks until the
transaction ends", and at the same time still defines transactions as
"atomic units of work that can be committed or rolled back."
and not like "something that lasts till a commit/rollback statement".
So, in the provided example the locks are held far beyond the defined
duration of the transaction.
But it wasn't my point, I'm totally resigned to the thought that it was
done this way on purpose and is not a bug. The goal of the post is to
acquire as much information as possible about technical reasoning behind
the chosen solution, the more low-level the better.
>> 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
>> 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.