[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql-internals
Subject:    Re: Metadata locking and non-transactional tables
From:       Dmitry Lenev <Dmitry.Lenev () oracle ! com>
Date:       2012-09-12 5:57:00
Message-ID: 20120912055700.GE15265 () jubjub
[Download RAW message or body]

Hello Elena!

* Elena <subscribe@elenst.ru> [12/09/12 01:15]:
> 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:

Yes, this is by design and not a bug.

The metadata locking subsystem doesn't look at the type of table
which is used on transaction, i.e. doesn't differentiate
transactional and non-transactional tables.

Instead to determine start and end of transaction it relies on statements
issued (BEGIN/COMMIT/ROLLBACK, other statements, state of @@autocommit
variable).

The main reasoning behind this is that we wanted to keep code handling
metadata locks simple.

In theory we can probably release metadata locks after end of statement
which uses only non-transactional tables. But it quickly gets complicated
once you start taking into account transactions and statements which mix
transactional and non-transactional tables, and has to be tightly
coordinated with how binary logging works, and etc.

I don't think it is worthwhile to add all this extra complexity just
to handle more nicely rather unnatural case when one uses ONLY
non-transactional tables in @@autocommit=0 mode or with BEGIN/COMMIT/
ROLLBACK statements.

Best regards,
Dmitry

-- 
Dmitry Lenev, Software Developer
Oracle Development SPB/MySQL, www.mysql.com

Are you MySQL certified?  http://www.mysql.com/certification

-- 
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe:    http://lists.mysql.com/internals

[prev in list] [next in list] [prev in thread] [next in thread]