List:Internals« Previous MessageNext Message »
From:Elena Stepanova Date:September 11 2012 11:12pm
Subject:Re: Metadata locking and non-transactional tables
View as plain text  
On 12.09.2012 2:09, Sveta Smirnova wrote:
> 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 
> particularly in the last paragraph.
> More details are in Davi's comments at 
> Sveta.

Hi Sveta,

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...
>> INSERT INTO t1 VALUES (1),(2);
>> # And then there came a connection in no-autocommit mode...
>> connect con1,localhost,root,,;
>> # 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;
>> # And it hung for 1 year (which happens to be default 
>> lock_wait_timeout)...
>> connection con1;
>> 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

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