List:Internals« Previous MessageNext Message »
From:Joerg Bruehe Date:March 19 2009 1:48pm
Subject:Re: Bug#989/WL#4284 "Transactional DDL locking" and prepared statements
View as plain text  
Hi Peter, Konstantin, all!


Peter Gulutzan wrote:
> 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.
>>
>> [[...]]
>>
>> 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.

Just for clarity:
This is not a lock on the data, but rather one on the schema - right?

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

FYI:
Adabas D aka SAP-DB aka MaxDB would also invalidate a prepared statement
when the schema for any of the referenced objects changes (this includes
adding an index, for example).
When then the application asks the server to execute that prepared
statement, it would receive an error code "Parse again".
The runtime environment layer (application side) would re-send the
statement text and have it parsed, and then call for its execution.

This was handled implicitly, without the application code taking action;
the only visible effect would be the increased runtime.
Of course, if the DDL did some incompatible change (say, a rename or
drop of a referenced column), then the parsing would fail, and the
application would receive the appropriate error code.

With Adabas D, a prepared statement remained valid after the end of the
transaction, throughout the session (connection), so a schema lock of
the referenced objects would not solve the issue - the next access using
that prepared statement could be in any following transaction.


Regards,
Jörg

-- 
Joerg Bruehe,  MySQL Build Team,
               Joerg.Bruehe@stripped
Sun Microsystems GmbH,   Sonnenallee 1,   D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering     Muenchen: HRB161028

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