From: Ann W. Harrison
Date: November 5 2008 4:41pm
Subject: Re: Interaction of DDL and DML
List-Archive: http://lists.mysql.com/falcon/146
Message-Id: <4911CCB5.8010308@mysql.com>
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset=ISO-8859-1
Content-Transfer-Encoding: 7BIT

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

I agree that the server should never roll back transactions unless
it crashes.  In this case, a request for the shared DDL lock on
a table that has a pending request for an exclusive DDL lock should
cause an error that the transaction can handle.

On the other hand, I'd be happy to have even the solution that
includes an abort, if it can happen for 6.0.

Under WL#4284, transactions that have already got the DDL lock -
meaning that they have already started reading the affected table -
will continue and the DDL operation will wait until they finish.
> 
>  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."

That sounds harsh, but blocking the DDL operation as long
as some transaction holds a shared DDL lock on the table
is not acceptable either.  That shared lock could be held
indefinitely by a series of overlapping transactions.

> 
> The correct solution should not cause any transaction to abort due to a 
> DDL statement.   

The correct solution is complicated.  Different operations and
different ways of implementing those operations should be handled
differently.

For example, there is no need to take an exclusive DDL lock for
an on-line add index or add column statement, but the same
operations done off-line do need the DDL lock.

And your point:

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

That is true in general, but may not be true for the binlog and
replication, particularly when statement-based.

The RENAME DDL statement can affect multiple tables, so the
exclusive and shared locks can deadlock.  Should that be a
special case? In the general case after getting the "shared
lock unavailable" error, the transaction can retry its
request with some hope for success, but in this case, it's
never going to get that shared lock, but must finish (either
way) to release its locks.

A correct implementation might queue requests for a shared
DDL lock behind the request for an exclusive lock.  But
why wait - even briefly - if the table is going to be dropped
or renamed?  So in that case, an immediate error is appropriate.

What about truncate?  Immediate error?  Read the empty table
eventually?

Drop, rename, or alter column?  Wait and hope it's not
referenced?


> But it does make sense do this in the server, since this 
> behavior should be server wide.
> 

Absolutely.  And a working if less than perfect solution in
6.0 will be better than a perfect solution in five more years.

Cheers,

Ann