On 27 Oct 2010, at 23:26, Weldon Whipple wrote:
> Thank you very much for your response! (I apologize for the slowness
> of my reply. I've been fighting a cold for several days.)
> See below:
> On Thu, Oct 21, 2010 at 11:15 PM, Dmitry Lenev <Dmitry.Lenev@stripped
> > wrote:
>> Hello Weldon!
>> * Weldon Whipple <weldon@stripped> [10/10/20 00:26]:
>>> Thanks for the great feedback. It got me looking at the capabilities
>>> of MySQL 5.5.
>>> It's POSSIBLE that the new syntax:
>>> FLUSH TABLES t1, t2, t3, ... , tn WITH READ LOCK
>>> is PRECISELY the functionality we need!
>>> Our (perl) migration script MIGHT (for example) do the following:
>>> Issue the following command (implemented by me ...):
>>> 1. START BINLOG FOR DATABASE <db>;
>>> (for each database a given user owns. [Hmmm... Maybe my
>>> allows multiple <db> arguments. I need to check.] Each database
>>> start binlogging to its own file. [We will configure--in my.cnf--the
>>> 1-database-per-file binlogs to be stored on a very fast solid-state
>>> Then the script creates a list of all tables in all the user's
>>> databases being migrated to another server and issues the command
>>> to MySQL 5.5):
>>> 2. FLUSH TABLES t1, t2, t3, ..., tn WITH READ LOCK;
>>> (where the tables t1, t2, ..., tn are the tables being migrated.)
>>> Then issue the following command (implemented by me ...):
>> AFAIU you also want to prohibit creation of tables in this database
>> after this point as otherwise you can't guarantee binlog stability.
>> Also what about non-table objects like stored routines - don't you
>> want to block their modification as well? The above statement won't
>> provide this as it locks only *existing* *tables*.
>> OTOH in my opinion it should be fairly easy to implement
>> that you need (i.e. blocking of all DDL and DML that changes data in
>> database) on the basis of FLUSH TABLES <table_list> WITH READ LOCK
>> implementation. You just need to take database-scope S metadata lock
>> in addition to locking and flushing all tables in database. Such lock
>> will block creation of new tables and any DDL on stored routines in
>> the database.
> Maybe I need to implement a new command (something like FLUSH DATABASE
> db WITH READ LOCK) that--conceptually, at least--read locks all the
> tables in the database (like FLUSH TABLES ... WITH READ LOCK) *as well
> as* blocks all DDL and DML that changes data in database.
> I'll look through the code and find where FLUSH TABLES ... WITH READ
> LOCK is implemented. If I can't figure out how to "take database-scope
> S metadata lock," I'll get back with you...
> (I suppose I'll need a corresponding UNLOCK DATABASES command as
> I've observed (and read) that the read locks are held as long as the
> client connection remains open, and that the UNLOCK applies to all the
> locks created during the same connection that created the read locks.
> I guess that means that our migration tool will need to maintain the
> connection with mysqld while it dumps (or scp's, in the case of MyISAM
> tables) the database snapshot. (That shouldn't necessarily be a
> problem--it's just something we need to be aware of. It makes sense
> for it to work that way.)
>>> BUG IDENTIFIED.
>> I have taken the liberty to report this issue as bug #57649
>> Thanks for pointing it out!
> Thanks for reporting it.
>>> I read that Larry Ellison stated (at a conference) that 5.5 would be
>>> out by year end. How likely is that? Our company generally waits for
>>> some point releases before changing our customer boxes to the next
>>> Should I try to back port those locks to 5.1 in the interim? ... or
>>> leave well enough alone?
>> I don't think it makes sense to backport metadata locks to 5.1 as
>> it is
>> likely to require back-porting of too much other changes from 5.5.
>> OTOH implementing what you need on top of infrastructure already
>> in 5.1 is likely to require significant efforts as well.
>> So I think it is better to base your work on 5.5 tree.
> I agree. There are quite a few major changes in 5.5. I haven't noticed
> meta-data-locks (MDL) before. The PSI additions to the mutex and
> conditions also look new. (I'm sure I'm missing lots of other
> (Regarding the PSI_mutex_key, PSI_rwlock_key and similar additions:
> I've tried to follow the patterns in 5.5 as I added my own mutexes.
> I'm assuming [hoping?] that they work when multiple instances of an
> object all refer to the same-named PSI_xxx-key ??? ...)
> (Is there any externally available documentation on MySQL's use of
There was a MySQL University session on it:
You should be able to use a single key with multiple instances of the
Software Development Senior Manager
MySQL Enterprise Tools @ Oracle Corp.