Skip to content

Commit b07ec61

Browse files
Dmitry LenevDmitry Lenev
Dmitry Lenev
authored and
Dmitry Lenev
committed
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.
1 parent 894b948 commit b07ec61

21 files changed

Lines changed: 257 additions & 142 deletions

‎mysql-test/include/handler.inc‎

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1087,8 +1087,7 @@ connection con1;
10871087
connection default;
10881088
--echo #
10891089
--echo # Demonstrate that HANDLER locks and transaction locks
1090-
--echo # reside in the same context, and we don't back-off
1091-
--echo # when have transaction or handler locks.
1090+
--echo # reside in the same context.
10921091
--echo #
10931092
create table t1 (a int, key a (a));
10941093
insert into t1 (a) values (1), (2), (3), (4), (5);
@@ -1109,9 +1108,9 @@ let $wait_condition=select count(*)=1 from information_schema.processlist
11091108
--source include/wait_condition.inc
11101109
--echo # --> connection default
11111110
connection default;
1111+
--echo # We back-off on hitting deadlock condition.
11121112
--error ER_LOCK_DEADLOCK
11131113
handler t0 open;
1114-
--error ER_LOCK_DEADLOCK
11151114
select * from t0;
11161115
handler t1 open;
11171116
commit;

‎mysql-test/r/handler_innodb.result‎

Lines changed: 8 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1104,8 +1104,7 @@ handler t1 close;
11041104
# --> connection default
11051105
#
11061106
# Demonstrate that HANDLER locks and transaction locks
1107-
# reside in the same context, and we don't back-off
1108-
# when have transaction or handler locks.
1107+
# reside in the same context.
11091108
#
11101109
create table t1 (a int, key a (a));
11111110
insert into t1 (a) values (1), (2), (3), (4), (5);
@@ -1125,10 +1124,16 @@ rename table t0 to t3, t1 to t0, t3 to t1;
11251124
# --> connection con1
11261125
# Waiting for 'rename table ...' to get blocked...
11271126
# --> connection default
1127+
# We back-off on hitting deadlock condition.
11281128
handler t0 open;
11291129
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
11301130
select * from t0;
1131-
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
1131+
a
1132+
1
1133+
2
1134+
3
1135+
4
1136+
5
11321137
handler t1 open;
11331138
commit;
11341139
handler t1 close;

‎mysql-test/r/handler_myisam.result‎

Lines changed: 8 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1100,8 +1100,7 @@ handler t1 close;
11001100
# --> connection default
11011101
#
11021102
# Demonstrate that HANDLER locks and transaction locks
1103-
# reside in the same context, and we don't back-off
1104-
# when have transaction or handler locks.
1103+
# reside in the same context.
11051104
#
11061105
create table t1 (a int, key a (a));
11071106
insert into t1 (a) values (1), (2), (3), (4), (5);
@@ -1121,10 +1120,16 @@ rename table t0 to t3, t1 to t0, t3 to t1;
11211120
# --> connection con1
11221121
# Waiting for 'rename table ...' to get blocked...
11231122
# --> connection default
1123+
# We back-off on hitting deadlock condition.
11241124
handler t0 open;
11251125
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
11261126
select * from t0;
1127-
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
1127+
a
1128+
1
1129+
2
1130+
3
1131+
4
1132+
5
11281133
handler t1 open;
11291134
commit;
11301135
handler t1 close;

‎mysql-test/r/mdl_sync.result‎

Lines changed: 5 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -1843,15 +1843,11 @@ rename table t2 to t0, t1 to t2, t0 to t1;;
18431843
# for 'deadlock_con1' which holds shared metadata lock on 't2'.
18441844
#
18451845
# The below statement should not wait as doing so will cause deadlock.
1846-
# Instead it should fail and emit ER_LOCK_DEADLOCK statement.
1846+
# Instead it should fail and emit ER_LOCK_DEADLOCK statement and
1847+
# transaction should be rolled back.
18471848
select * from t1;
18481849
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
18491850
#
1850-
# Let us check that failure of the above statement has not released
1851-
# metadata lock on table 't1', i.e. that RENAME TABLE is still blocked.
1852-
# Commit transaction to unblock RENAME TABLE.
1853-
commit;
1854-
#
18551851
# Switching to connection 'default'.
18561852
# Reap RENAME TABLE.
18571853
#
@@ -1888,16 +1884,10 @@ unlock tables;
18881884
# Switching to connection 'deadlock_con1'.
18891885
# Since the latest RENAME TABLE entered in deadlock with SELECT
18901886
# statement the latter should be aborted and emit ER_LOCK_DEADLOCK
1891-
# error.
1887+
# error and transaction should be rolled back.
18921888
# Reap SELECT * FROM t1.
18931889
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
18941890
#
1895-
# Again let us check that failure of the SELECT statement has not
1896-
# released metadata lock on table 't2', i.e. that the latest RENAME
1897-
# is blocked.
1898-
# Commit transaction to unblock this RENAME TABLE.
1899-
commit;
1900-
#
19011891
# Switching to connection 'deadlock_con2'.
19021892
# Reap RENAME TABLE ... .
19031893
#
@@ -1931,14 +1921,10 @@ alter table t1 add column j int, rename to t2;;
19311921
# metadata lock on 't2' and starts waiting for connection
19321922
# 'deadlock_con1' which holds shared lock on 't1'.
19331923
# The below statement should not wait as it will cause deadlock.
1934-
# An appropriate error should be reported instead.
1924+
# An appropriate error should be reported instead and transaction
1925+
# should be rolled back.
19351926
select * from t2;
19361927
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
1937-
# Again let us check that failure of the above statement has not
1938-
# released all metadata locks in connection 'deadlock_con1' and
1939-
# so ALTER TABLE ... RENAME is still blocked.
1940-
# Commit transaction to unblock ALTER TABLE ... RENAME.
1941-
commit;
19421928
#
19431929
# Switching to connection 'default'.
19441930
# Reap ALTER TABLE ... RENAME.
@@ -2426,12 +2412,6 @@ set debug_sync='mdl_acquire_lock_wait SIGNAL alter_go';
24262412
update t1 set c3=c3+1 where c2 = 3;
24272413
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
24282414
#
2429-
# Let us check that failure of the above statement has not released
2430-
# metadata lock on table 't1', i.e. that ALTER TABLE is still blocked.
2431-
# Unblock ALTER TABLE by commiting transaction and thus releasing
2432-
# metadata lock on 't1'.
2433-
commit;
2434-
#
24352415
# Switching to connection 'con46273'.
24362416
# Reap ALTER TABLE.
24372417
#

‎mysql-test/t/mdl_sync.test‎

Lines changed: 5 additions & 51 deletions
Original file line numberDiff line numberDiff line change
@@ -2619,21 +2619,11 @@ let $wait_condition=
26192619
--source include/wait_condition.inc
26202620
--echo #
26212621
--echo # The below statement should not wait as doing so will cause deadlock.
2622-
--echo # Instead it should fail and emit ER_LOCK_DEADLOCK statement.
2622+
--echo # Instead it should fail and emit ER_LOCK_DEADLOCK statement and
2623+
--echo # transaction should be rolled back.
26232624
--error ER_LOCK_DEADLOCK
26242625
select * from t1;
26252626

2626-
--echo #
2627-
--echo # Let us check that failure of the above statement has not released
2628-
--echo # metadata lock on table 't1', i.e. that RENAME TABLE is still blocked.
2629-
let $wait_condition=
2630-
select count(*) = 1 from information_schema.processlist
2631-
where state = "Waiting for table metadata lock" and
2632-
info = "rename table t2 to t0, t1 to t2, t0 to t1";
2633-
--source include/wait_condition.inc
2634-
--echo # Commit transaction to unblock RENAME TABLE.
2635-
commit;
2636-
26372627
--echo #
26382628
--echo # Switching to connection 'default'.
26392629
connection default;
@@ -2696,23 +2686,11 @@ unlock tables;
26962686
connection deadlock_con1;
26972687
--echo # Since the latest RENAME TABLE entered in deadlock with SELECT
26982688
--echo # statement the latter should be aborted and emit ER_LOCK_DEADLOCK
2699-
--echo # error.
2689+
--echo # error and transaction should be rolled back.
27002690
--echo # Reap SELECT * FROM t1.
27012691
--error ER_LOCK_DEADLOCK
27022692
--reap
27032693

2704-
--echo #
2705-
--echo # Again let us check that failure of the SELECT statement has not
2706-
--echo # released metadata lock on table 't2', i.e. that the latest RENAME
2707-
--echo # is blocked.
2708-
let $wait_condition=
2709-
select count(*) = 1 from information_schema.processlist
2710-
where state = "Waiting for table metadata lock" and
2711-
info = "rename table t1 to t0, t2 to t1, t0 to t2";
2712-
--source include/wait_condition.inc
2713-
--echo # Commit transaction to unblock this RENAME TABLE.
2714-
commit;
2715-
27162694
--echo #
27172695
--echo # Switching to connection 'deadlock_con2'.
27182696
connection deadlock_con2;
@@ -2761,22 +2739,11 @@ let $wait_condition=
27612739
--source include/wait_condition.inc
27622740

27632741
--echo # The below statement should not wait as it will cause deadlock.
2764-
--echo # An appropriate error should be reported instead.
2742+
--echo # An appropriate error should be reported instead and transaction
2743+
--echo # should be rolled back.
27652744
--error ER_LOCK_DEADLOCK
27662745
select * from t2;
27672746

2768-
--echo # Again let us check that failure of the above statement has not
2769-
--echo # released all metadata locks in connection 'deadlock_con1' and
2770-
--echo # so ALTER TABLE ... RENAME is still blocked.
2771-
let $wait_condition=
2772-
select count(*) = 1 from information_schema.processlist
2773-
where state = "Waiting for table metadata lock" and
2774-
info = "alter table t1 add column j int, rename to t2";
2775-
--source include/wait_condition.inc
2776-
2777-
--echo # Commit transaction to unblock ALTER TABLE ... RENAME.
2778-
commit;
2779-
27802747
--echo #
27812748
--echo # Switching to connection 'default'.
27822749
connection default;
@@ -3577,19 +3544,6 @@ set debug_sync='mdl_acquire_lock_wait SIGNAL alter_go';
35773544
--error ER_LOCK_DEADLOCK
35783545
update t1 set c3=c3+1 where c2 = 3;
35793546

3580-
--echo #
3581-
--echo # Let us check that failure of the above statement has not released
3582-
--echo # metadata lock on table 't1', i.e. that ALTER TABLE is still blocked.
3583-
let $wait_condition=
3584-
select count(*) = 1 from information_schema.processlist
3585-
where state = "Waiting for table metadata lock" and
3586-
info = "alter table t1 add column e int, rename to t2";
3587-
--source include/wait_condition.inc
3588-
3589-
--echo # Unblock ALTER TABLE by commiting transaction and thus releasing
3590-
--echo # metadata lock on 't1'.
3591-
commit;
3592-
35933547
--echo #
35943548
--echo # Switching to connection 'con46273'.
35953549
connection con46273;

‎sql/ha_ndbcluster_binlog.cc‎

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
/*
2-
Copyright (c) 2006, 2011, Oracle and/or its affiliates. All rights reserved.
2+
Copyright (c) 2006, 2013, Oracle and/or its affiliates. All rights reserved.
33
44
This program is free software; you can redistribute it and/or modify
55
it under the terms of the GNU General Public License as published by
@@ -2422,6 +2422,12 @@ int ndb_add_ndb_binlog_index(THD *thd, void *_row)
24222422
thd->is_error() ? trans_rollback_stmt(thd) : trans_commit_stmt(thd);
24232423
thd->stmt_da->can_overwrite_status= FALSE;
24242424
close_thread_tables(thd);
2425+
/*
2426+
There should be no need for rolling back transaction due to deadlock
2427+
(since ndb_binlog_index is non transactional).
2428+
*/
2429+
DBUG_ASSERT(! thd->transaction_rollback_request);
2430+
24252431
thd->mdl_context.release_transactional_locks();
24262432
ndb_binlog_index= 0;
24272433
thd->variables.option_bits= saved_options;

‎sql/handler.cc‎

Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1480,10 +1480,16 @@ int ha_rollback_trans(THD *thd, bool all)
14801480
}
14811481
trans->ha_list= 0;
14821482
trans->no_2pc=0;
1483-
if (is_real_trans && thd->transaction_rollback_request &&
1484-
thd->transaction.xid_state.xa_state != XA_NOTR)
1485-
thd->transaction.xid_state.rm_error= thd->stmt_da->sql_errno();
14861483
}
1484+
1485+
/*
1486+
Thanks to possibility of MDL deadlock rollback request can come even if
1487+
transaction hasn't been started in any transactional storage engine.
1488+
*/
1489+
if (is_real_trans && thd->transaction_rollback_request &&
1490+
thd->transaction.xid_state.xa_state != XA_NOTR)
1491+
thd->transaction.xid_state.rm_error= thd->stmt_da->sql_errno();
1492+
14871493
/* Always cleanup. Even if nht==0. There may be savepoints. */
14881494
if (is_real_trans)
14891495
thd->transaction.cleanup();

‎sql/log_event.cc‎

Lines changed: 11 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5104,6 +5104,8 @@ int Load_log_event::do_apply_event(NET* net, Relay_log_info const *rli,
51045104
thd->stmt_da->can_overwrite_status= FALSE;
51055105
close_thread_tables(thd);
51065106
/*
5107+
- If transaction rollback was requested due to deadlock
5108+
perform it and release metadata locks.
51075109
- If inside a multi-statement transaction,
51085110
defer the release of metadata locks until the current
51095111
transaction is either committed or rolled back. This prevents
@@ -5113,7 +5115,12 @@ int Load_log_event::do_apply_event(NET* net, Relay_log_info const *rli,
51135115
- If in autocommit mode, or outside a transactional context,
51145116
automatically release metadata locks of the current statement.
51155117
*/
5116-
if (! thd->in_multi_stmt_transaction_mode())
5118+
if (thd->transaction_rollback_request)
5119+
{
5120+
trans_rollback_implicit(thd);
5121+
thd->mdl_context.release_transactional_locks();
5122+
}
5123+
else if (! thd->in_multi_stmt_transaction_mode())
51175124
thd->mdl_context.release_transactional_locks();
51185125
else
51195126
thd->mdl_context.release_statement_locks();
@@ -8197,7 +8204,10 @@ static int rows_event_stmt_cleanup(Relay_log_info const *rli, THD * thd)
81978204
Xid_log_event will come next which will, if some transactional engines
81988205
are involved, commit the transaction and flush the pending event to the
81998206
binlog.
8207+
If there was a deadlock the transaction should have been rolled back
8208+
already. So there should be no need to rollback the transaction.
82008209
*/
8210+
DBUG_ASSERT(! thd->transaction_rollback_request);
82018211
error|= (error ? trans_rollback_stmt(thd) : trans_commit_stmt(thd));
82028212

82038213
/*

‎sql/log_event_old.cc‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* Copyright (c) 2007, 2012, Oracle and/or its affiliates. All rights reserved.
1+
/* Copyright (c) 2007, 2013, Oracle and/or its affiliates. All rights reserved.
22
33
This program is free software; you can redistribute it and/or modify
44
it under the terms of the GNU General Public License as published by
@@ -1808,7 +1808,10 @@ int Old_rows_log_event::do_apply_event(Relay_log_info const *rli)
18081808
Xid_log_event will come next which will, if some transactional engines
18091809
are involved, commit the transaction and flush the pending event to the
18101810
binlog.
1811+
If there was a deadlock the transaction should have been rolled back
1812+
already. So there should be no need to rollback the transaction.
18111813
*/
1814+
DBUG_ASSERT(! thd->transaction_rollback_request);
18121815
if ((error= (binlog_error ? trans_rollback_stmt(thd) : trans_commit_stmt(thd))))
18131816
rli->report(ERROR_LEVEL, error,
18141817
"Error in %s event: commit of row events failed, "

‎sql/rpl_rli.cc‎

Lines changed: 9 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* Copyright (c) 2006, 2012, Oracle and/or its affiliates. All rights reserved.
1+
/* Copyright (c) 2006, 2013, Oracle and/or its affiliates. All rights reserved.
22
33
This program is free software; you can redistribute it and/or modify
44
it under the terms of the GNU General Public License as published by
@@ -1317,6 +1317,8 @@ void Relay_log_info::slave_close_thread_tables(THD *thd)
13171317

13181318
close_thread_tables(thd);
13191319
/*
1320+
- If transaction rollback was requested due to deadlock
1321+
perform it and release metadata locks.
13201322
- If inside a multi-statement transaction,
13211323
defer the release of metadata locks until the current
13221324
transaction is either committed or rolled back. This prevents
@@ -1326,7 +1328,12 @@ void Relay_log_info::slave_close_thread_tables(THD *thd)
13261328
- If in autocommit mode, or outside a transactional context,
13271329
automatically release metadata locks of the current statement.
13281330
*/
1329-
if (! thd->in_multi_stmt_transaction_mode())
1331+
if (thd->transaction_rollback_request)
1332+
{
1333+
trans_rollback_implicit(thd);
1334+
thd->mdl_context.release_transactional_locks();
1335+
}
1336+
else if (! thd->in_multi_stmt_transaction_mode())
13301337
thd->mdl_context.release_transactional_locks();
13311338
else
13321339
thd->mdl_context.release_statement_locks();

0 commit comments

Comments
 (0)