Bug #50821 Deadlock between LOCK TABLES and ALTER TABLE

This was a deadlock between ALTER TABLE and another DML statement
(or LOCK TABLES ... READ). ALTER TABLE would wait trying to upgrade
its lock to MDL_EXCLUSIVE and the DML statement would wait trying
to acquire a TL_READ_NO_INSERT table level lock.

This could happen if one connection first acquired a MDL_SHARED_READ
lock on a table. In another connection ALTER TABLE is then started.
ALTER TABLE eventually blocks trying to upgrade to MDL_EXCLUSIVE,
but while holding a TL_WRITE_ALLOW_READ table level lock.

If the first connection then tries to acquire TL_READ_NO_INSERT,
it will block and we have a deadlock since neither connection can
proceed.

This patch fixes the problem by allowing TL_READ_NO_INSERT 
locks to be granted if another connection holds TL_WRITE_ALLOW_READ
on the same table. This will allow the DML statement to proceed
such that it eventually can release its MDL lock which in turn
makes ALTER TABLE able to proceed.

Note that TL_READ_NO_INSERT was already partially compatible with
TL_WRITE_ALLOW_READ as the latter would be granted if the former
lock was held. This patch just makes the opposite true as well.

Also note that since ALTER TABLE takes an upgradable MDL lock,
there will be no starvation of ALTER TABLE statements by
statements acquiring TL_READ or TL_READ_NO_INSERT.

Test case added to lock_sync.test.

1 files changed, 2 insertions, 3 deletions

diff --git a/mysys/thr_lock.c b/mysys/thr_lock.c
index 6d62476ecf3..901620d3a93 100644
--- a/mysys/thr_lock.c
+++ b/mysys/thr_lock.c
@@ -549,7 +549,7 @@ thr_lock(THR_LOCK_DATA *data, THR_LOCK_OWNER *owner,
            Request
           /-------
          H|++++  WRITE_ALLOW_WRITE
-         e|+++-  WRITE_ALLOW_READ
+         e|++++  WRITE_ALLOW_READ
          l|+++-  WRITE_CONCURRENT_INSERT
          d|++++  WRITE_DELAYED
            ||||
@@ -572,8 +572,7 @@ thr_lock(THR_LOCK_DATA *data, THR_LOCK_OWNER *owner,
       if (thr_lock_owner_equal(data->owner, lock->write.data->owner) ||
 	  (lock->write.data->type <= TL_WRITE_DELAYED &&
 	   (((int) lock_type <= (int) TL_READ_HIGH_PRIORITY) ||
-	    (lock->write.data->type != TL_WRITE_CONCURRENT_INSERT &&
-	     lock->write.data->type != TL_WRITE_ALLOW_READ))))
+	    (lock->write.data->type != TL_WRITE_CONCURRENT_INSERT))))
       {						/* Already got a write lock */
 	(*lock->read.last)=data;		/* Add to running FIFO */
 	data->prev=lock->read.last;