Backport of revno ## 2617.31.1, 2617.31.3, 2617.31.4, 2617.31.5,

2617.31.12, 2617.31.15, 2617.31.15, 2617.31.16, 2617.43.1
- initial changeset that introduced the fix for 
Bug#989 and follow up fixes for all test suite failures
introduced in the initial changeset. 
------------------------------------------------------------
revno: 2617.31.1
committer: Davi Arnaut <Davi.Arnaut@Sun.COM>
branch nick: 4284-6.0
timestamp: Fri 2009-03-06 19:17:00 -0300
message:
Bug#989: If DROP TABLE while there's an active transaction, wrong binlog order
WL#4284: Transactional DDL locking

Currently the MySQL server does not keep metadata locks on
schema objects for the duration of a transaction, thus failing
to guarantee the integrity of the schema objects being used
during the transaction and to protect then from concurrent
DDL operations. This also poses a problem for replication as
a DDL operation might be replicated even thought there are
active transactions using the object being modified.

The solution is to defer the release of metadata locks until
a active transaction is either committed or rolled back. This
prevents other statements from modifying the table for the
entire duration of the transaction. This provides commitment
ordering for guaranteeing serializability across multiple
transactions.

- Incompatible change:

If MySQL's metadata locking system encounters a lock conflict,
the usual schema is to use the try and back-off technique to
avoid deadlocks -- this schema consists in releasing all locks
and trying to acquire them all in one go.

But in a transactional context this algorithm can't be utilized
as its not possible to release locks acquired during the course
of the transaction without breaking the transaction commitments.
To avoid deadlocks in this case, the ER_LOCK_DEADLOCK will be
returned if a lock conflict is encountered during a transaction.

Let's consider an example:

A transaction has two statements that modify 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
utill COMMIT 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
might 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. This implies that
the entire multi-statement transaction has to be rolled back.

- Incompatible change:

FLUSH commands such as FLUSH PRIVILEGES and FLUSH TABLES WITH READ
LOCK won't cause locked tables to be implicitly unlocked anymore.

1 files changed, 3 insertions, 3 deletions

diff --git a/sql/mdl.cc b/sql/mdl.cc
index 1d591bb2244..eb8fcdb323e 100644
--- a/sql/mdl.cc
+++ b/sql/mdl.cc
@@ -491,7 +491,7 @@ void MDL_ticket::destroy(MDL_ticket *ticket)
   @sa THD::enter_cond()/exit_cond()/killed.
 
   @note We can't use THD::enter_cond()/exit_cond()/killed directly here
-        since this will make metadata subsystem dependant on THD class
+        since this will make metadata subsystem dependent on THD class
         and thus prevent us from writing unit tests for it. And usage of
         wrapper functions to access THD::killed/enter_cond()/exit_cond()
         will probably introduce too much overhead.
@@ -881,6 +881,7 @@ static bool notify_shared_lock(THD *thd, MDL_ticket *conflicting_ticket)
   if (conflicting_ticket->is_shared())
   {
     THD *conflicting_thd= conflicting_ticket->get_ctx()->get_thd();
+    DBUG_ASSERT(thd != conflicting_thd); /* Self-deadlock */
     woke= mysql_notify_thread_having_shared_lock(thd, conflicting_thd);
   }
   return woke;
@@ -1089,7 +1090,6 @@ MDL_ticket::upgrade_shared_lock_to_exclusive()
 
   old_msg= MDL_ENTER_COND(thd, mysys_var);
 
-
   /*
     Since we should have already acquired an intention exclusive
     global lock this call is only enforcing asserts.
@@ -1164,7 +1164,7 @@ MDL_ticket::upgrade_shared_lock_to_exclusive()
   @param conflict   [out] Indicates that conflicting lock exists
 
   @retval TRUE  Failure either conflicting lock exists or some error
-                occured (probably OOM).
+                occurred (probably OOM).
   @retval FALSE Success, lock was acquired.
 
   FIXME: Compared to lock_table_name_if_not_cached()