From: Kevin Lewis
Date: November 5 2008 2:43pm
Subject: Re: Interaction of DDL and DML
List-Archive: http://lists.mysql.com/falcon/144
Message-Id: <4911B12D.4030708@sun.com>
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset=ISO-8859-1
Content-Transfer-Encoding: 7BIT

 >> Ann wrote,
>>    Currently for both Falcon and InnoDB, a repeatable read
>> transaction's isolation is broken if a destructive alter table,
>> drop table, or truncate is executed by a concurrent transaction.

> James Day wrote:
> Please see this bug and it's related worklog to see whether the work 
> planned to fix it resolves the problem. If it does, please let Omer know.
> 
> http://bugs.mysql.com/bug.php?id=989
> 
> https://intranet.mysql.com/worklog/Server-BackLog/?tid=4284

Related Bugs;
http://bugs.mysql.com/bug.php?id=37303 (Innodb)
http://bugs.mysql.com/bug.php?id=28006 (Falcon)
http://bugs.mysql.com/bug.php?id=40460 (Falcon


Worklog #4284 is NOT a good solution to the problem.  It causes existing 
transactions to abort when a waiting DDL exists for a table.

It proposes that each transaction will get a shared 'DDL lock' on all 
tables that it accesses.  Then when a DDL comes in to get an exclusive 
lock, it will wait on existing shared locks to be released at the end of 
those transactions.  But all transactions that try to get a new shared 
lock once a DDL is waiting for an exclusive will automatically abort. 
It says that "The scope of this task is to simply abort the
transaction that attempts to acquire a shared lock."

This worklog introduces the idea of a "pending exclusive" lock which 
"prevents all new transactions from acquiring the lock".  But it does 
not plan to implement it.

The correct solution should not cause any transaction to abort due to a 
DDL statement.  But it does make sense do this in the server, since this 
behavior should be server wide.

Worklog #4284 also does not distinguish between repeatable-read and 
read-committed isolations. The current behavior is fine for 
read-committed transactions.