MySQL Bugs: #69668: Transaction is not fully rolled back in case of InnoDB deadlock

Bug #69668	Transaction is not fully rolled back in case of InnoDB deadlock
Submitted:	4 Jul 2013 8:00	Modified:	6 Sep 2013 16:42
Reporter:	Dmitry Lenev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.5.33-bzr	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When InnoDB detects deadlock and rollbacks victim transaction metadata locks are not released for the transaction (unless @@autocommit=1). These locks are kept until explicit COMMIT/ROLLBACK or implicit commit in the same connection which can be caused by DDL or BEGIN statement. This makes transaction incompletely rolled back and can be confusing for users/problematic for some applications since MDL which are not released can block concurrent DDL on this table.

This bug was probably introduced along with introduction of MDL subsystem in server (i.e. before 5.5 has gone GA). It is likely that this issue was not reported earlier simply because most of applications will restart transaction after getting ER_LOCK_DEADLOCK error and BEGIN statement will do implicit commit that will release all locks previously held.

How to repeat:
# Test for 'mysqltest' tool that demonstrates the issue.
create table t1 (i int) engine=innodb;
create table t2 (j int) engine=innodb;
insert into t1 values (1);
insert into t2 values (1);
begin;
select * from t1 for update;
 
connect (con1root,localhost,root,,);
begin;
select * from t2 for update;
--send select * from t1 for update;
 
connection default;
--sleep 1
--error ER_LOCK_DEADLOCK
select * from t2 for update;
 
connection con1root;
--reap;
commit;
# The below DROP TABLE blocks due to metadata locks not released by
# connection 'default'.
drop table t1;

Suggested fix:
Release metadata locks when transaction is rolled back due to InnoDB deadlock.

Also: http://bugs.mysql.com/bug.php?id=52377

Noted in 5.5.34, 5.6.14, 5.7.3 changelogs.

InnoDB deadlock caused transaction rollback but did not release
metadata locks, blocking concurrent DDL on the transaction tables
until the connection that got the deadlock issued an explicit COMMIT
or ROLLBACK.

5.5$ bzr log -r 4439
------------------------------------------------------------
revno: 4439
committer: Dmitry Lenev <Dmitry.Lenev@oracle.com>
branch nick: mysql-5.5-17054007-2
timestamp: Tue 2013-08-20 13:12:34 +0400
message:
  Fix for bug#14188793 - "DEADLOCK CAUSED BY ALTER TABLE DOEN'T CLEAR
  STATUS OF ROLLBACKED TRANSACTION" and bug #17054007 - "TRANSACTION
  IS NOT FULLY ROLLED BACK IN CASE OF INNODB DEADLOCK".
  
  The problem in the first bug report was that although deadlock involving
  metadata locks was reported using the same error code and message as InnoDB
  deadlock it didn't rollback transaction like the latter. This caused
  confusion to users as in some cases after ER_LOCK_DEADLOCK transaction
  could have been restarted immediately and in some cases rollback was
  required.
  
  The problem in the second bug report was that although InnoDB deadlock
  caused transaction rollback in all storage engines it didn't cause release
  of metadata locks. So concurrent DDL on the tables used in transaction was
  blocked until implicit or explicit COMMIT or ROLLBACK was issued in the
  connection which got InnoDB deadlock.
  
  The former issue has stemmed from the fact that when support for detection
  and reporting metadata locks deadlocks was added we erroneously assumed
  that InnoDB doesn't rollback transaction on deadlock but only last statement
  (while this is what happens on InnoDB lock timeout actually) and so didn't
  implement rollback of transactions on MDL deadlocks.
  
  The latter issue was caused by the fact that rollback of transaction due
  to deadlock is carried out by setting THD::transaction_rollback_request
  flag at the point where deadlock is detected and performing rollback
  inside of trans_rollback_stmt() call when this flag is set. And
  trans_rollback_stmt() is not aware of MDL locks, so no MDL locks are
  released.
  
  This patch solves these two problems in the following way:
  
  - In case when MDL deadlock is detect transaction rollback is requested
    by setting THD::transaction_rollback_request flag.
  
  - Code performing rollback of transaction if THD::transaction_rollback_request
    is moved out from trans_rollback_stmt(). Now we handle rollback request
    on the same level as we call trans_rollback_stmt() and release statement/
    transaction MDL locks.