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:
> 
> BEGIN
> PREPARE stmt FROM "SELECT * FROM t1";
> 
> -- 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,
until COMMIT/ROLLBACK.

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

IBM says that PREPARE is invalidated by DDL, so EXECUTE will cause a re-prepare.
http://www.informatik.uni-bonn.de/~tb/Lehre/ws01/vRDBMS/db2s0/frame3.htm#sqls0631

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."
http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10752/memory.htm
"The DDL will invalidate the [cached] statement [if it affects objects
referenced by the cached statement]
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:801662252143
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 www.mysql.com
Sun Microsystems of Canada Inc.
Edmonton, AB, Canada
Thread
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