Split from WL#3726.
Implement transaction-long metadata locks. I.e. if a table is used in a
transaction, a metadata lock should be kept for this table until the transaction
is committed. This will prevent concurrent transactions from issuing a DDL
against this table. Necessary to fix BUG#989 If DROP TABLE while there's an
active transaction, wrong binlog order

• WL#3726: DDL locking for all metadata objects

In order to ensure transaction serializability, 

the server must not allow a concurrent connection to perform a DDL

statement on a table that is used in an uncompleted transaction.



Currently, for each table used in a statement, the server

acquires a shared metadata lock till the end of the statement.

When the statement is complete, the shared metadata lock is released.

A simple deadlock avoidance technique is employed to address the

problem of deadlocks in case of a lock conflict (see

http://forge.mysql.com/wiki/MySQL_Internals_Data_and_meta-data_locking for

details). 



In order to ensure serializability, the server must defer the

release of metadata locks from the end of a statement till the end

of the encompassing transaction.



For a statement that doesn't start a transaction, e.g. a statement

on a non-transactional table/tables, this brings no change.



For a connection that runs in autocommit mode, the change also has

no effect.



Since each DDL statement commits the transaction, if any, in the

beginning, the change has no effect on lock conflicts between

concurrent DDL statements.



The other class of conflicts is between DDL and DML statements.

When a connection doesn't use autocommit and utilizes

multi-statement transactions, a new scenario

of lock conflicts is introduced. A conflict  may happen between

the acquirer of a shared lock and a concurrent DDL, at the time

when the acquirer has executed several statements and possibly

modified or retrieved data.



Let's consider an example.



Transaction 1 modifies table t1, then table t2, and then commits.

The first statement of the transaction will acquire a shared

metadata lock on table t1, and it will be kept till COMMIT, in

order to ensure serializability.



At the moment when the second statement attempts to acquire a

shared metadata lock on t2, a concurrent ALTER or DROP statement

may have locked t2 exclusively.

The prescription of the current locking protocol is that the

acquirer of the shared lock backs off -- gives up all his current

locks and retries.

For a multiple-statement scenario that implies that the entire

multi-statement transaction has to be rolled back.

Indeed, imagine that the concurrent connection issued

RENAME t2 to t3, t1 to t2, t3 to t1, rather than a simple ALTER.



In that case it will have acquired an exclusive lock on t2 at the

moment it encounters that t1 is locked in shared mode, and the

owner is not about to give away its locks. 

Unless the DML transaction back off, a deadlock is unavoidable.



In future we may consider implementing a deadlock detection

algorithm that would allow us to detect the scenario described

above while allowing for other scenarios of lock conflicts.



One possible solution is given in Appendix A.



The scope of this task, however, is to simply abort the

transaction that attempts to acquire a shared lock.

Note, that the abortion only takes place if the transaction has

executed statements -- in other words, has acquired shared

metadata locks that ought to be released. If a lock conflict

happens in the first statement of the transaction, the locking

algorithm back off and retries as before.



If a user has set a savepoint, ROLLBACK TO SAVEPOINT should release

all metadata locks acquired after the savepoint has been set.



Apart from these two changes, the locking protocol should be left

intact.



Appendix A: a possible approach to deadlock detection

-----------------------------------------------------



If a shared/exclusive lock conflict occurs when attempting to acquire an

exclusive lock, a "pending exclusive" lock is placed instead.

This lock is "upgraded" to exclusive when all transactions 

that own the conflicting lock have committed.

Existence of a pending exclusive lock prevents all new transactions

from acquiring the lock: they back off. Existing transaction, still,

can proceed, as long as new locks that they attempt to acquire do

not lead to deadlocks.

This latter fact can be checked easily by maintaining the list of

waiters on a given lock, and checking if some transaction is

present twice in the list.

Extend the metadata locking subsystem to keep metadata locks till the

end of a transaction.



Changes to existing APIs:



 - none



Changed files and functions:



1) Introduce ha_in_transaction() that would return TRUE 

   if there is a started transaction in a given connection.

2) Store metadata locks in transaction memory root, rather than

   statement memory root.

3) Modify close_thread_tables() to call mdl_release_locks() only

   if we're outside the context of some transaction. Since

   close_thread_tables() is called after each statement,

   including COMMIT and ROLLBACK, that will ensure that metadata

   locks are released automatically whenever a transaction has

   either ended automatically, or was committed/aborted

   manually.

 

   BEGIN word is a no-op at MySQL. Operations on non-transactional

   engines do not start a transaction either.  Therefore, for 

   statements that use non-transactional tables outside the 

   context of some transaction, locks will be released at the end of each

   statement, just like before.



 3) Modify open_table() to raise rollback the transaction and

    return ER_LOCK_DEADLOCK error in case of a shared/exclusive

    lock conflict in a non-first statement of a transaction.



 4) Modify ha_savepoint() to remember, and ha_rollback_to_savepoint()

    to rollback to the remembered set of metadata locks.



Effect on LOCK TABLES TRANSACTIONAL



Effect on other statements



None



Bug reports:

BUG#989 If DROP TABLE while there's an active transaction, wrong binlog order

BUG#12347 InnoDB: DROP TABLE ignores locks

BUG#39945 "WITH CONSISTENT SNAPSHOT" does not isolate against table changes

(i.e. RENAME)

You must be logged in to tag this worklog