MySQL Lists are EOL. Please join:

List:Internals« Previous MessageNext Message »
From:Peter Gulutzan Date:March 18 2009 11:17pm
Subject:Re: Bug#989/WL#4284 "Transactional DDL locking" and prepared statements
View as plain text  
Hi Konstantin,

Konstantin Osipov wrote:
> Hello,
> Peter, there are two questions to you below.
> After implementation of WL#4284 in 6.0, if one uses a table in a
> transaction, it restricts other connections' ability to perform
> DDL on the table until the transaction is finished.
> One effect of it is that if one does:
> -- in one connection,
> other connections won't be able to FLUSH, ALTER, or DROP t1 until
> the transaction in the first connection commits.
> Since we can see both advantages and disadvantages of this
> behaviour, for now we decided to not take any action.
> How do you think it should work to be user-friendly?
> One can always say COMMIT or turn autocommit on if this behaviour
> is not desirable.
> Peter, is there a standard prescription for this case?
> How do other vendors behave?
> Note, that the lock is kept till the end of the transaction
> regardless of the table storage engine. I.e. even if it is a
> MyISAM table, we treat it equally, and won't let concurrent
> connections drop it.
> Your help and opinions are much appreciated,

So PREPARE locks all objects referenced in the statement being prepared,

The standard allows PREPARE but says nothing about "locks".

IBM says that PREPARE is invalidated by DDL, so EXECUTE will cause a re-prepare.

Oracle has a variety of "prepare" methods depending whether you're coming
from JDBC, PL/SQL, or static SQL. Generally I guess we could say that the
important thing is: does the statement get into the shared pool, and get cached?
I regard that as automatic preparing. Some relevant-seeming quotations are:
"Avoid performing DDL operations on high-usage segments during peak hours.
Performing DDL on such segments often results in the dependent SQL being
invalidated and hence reparsed on a later execution."
"The DDL will invalidate the [cached] statement [if it affects objects
referenced by the cached statement]
In other words: DDL invalidates prepared or cached statements.

Since both IBM and Oracle prefer to invalidate prepared statements,
rather than be blocked by them, I consider MySQL's method unusual.


Peter Gulutzan
Database Group / MySQL
Sun Microsystems of Canada Inc.
Edmonton, AB, Canada
Bug#989/WL#4284 "Transactional DDL locking" and prepared statementsKonstantin Osipov17 Mar
  • Re: Bug#989/WL#4284 "Transactional DDL locking" and prepared statementsPeter Gulutzan19 Mar
    • Re: Bug#989/WL#4284 "Transactional DDL locking" and prepared statementsJoerg Bruehe19 Mar