From 6e1291bb49ab291c461b2da768089e866004eed7 Mon Sep 17 00:00:00 2001 From: Alexey Kopytov Date: Thu, 16 Jul 2015 16:39:25 +0300 Subject: [PATCH] Bug #76927: Duplicate UK values in READ-COMMITTED (again) The problem was with lock_rec_inherit_to_gap() assuming that only shared record locks can be set by a consistency constraint and thus, only that lock type should be inherited in the gap mode when an index record is deleted. However, REPLACE, LOAD DATA REPLACE and INSERT ON DUPLICATE KEY UPDATE set exclusive record locks. When a record had been deleted by purge, all record locks acquired by in-progress concurrent statements of those types were incorrectly released by lock_rec_inherit_to_gap() which might lead to a UK constraint violation. Fixed by changing lock_rec_inherit_to_gap() to take trx_t::duplicates into account. In case it has a non-zero value (i.e. TRX_DUP_IGNORE or TRX_DUP_REPLACE), exclusive rather than shared locks are now inherited as gap locks. --- mysql-test/suite/innodb/r/bug76927.result | 39 ++++++++++++++ mysql-test/suite/innodb/t/bug76927.test | 90 +++++++++++++++++++++++++++++++ storage/innobase/lock/lock0lock.cc | 3 +- storage/innobase/row/row0ins.cc | 2 + 4 files changed, 133 insertions(+), 1 deletion(-) create mode 100644 mysql-test/suite/innodb/r/bug76927.result create mode 100644 mysql-test/suite/innodb/t/bug76927.test diff --git a/mysql-test/suite/innodb/r/bug76927.result b/mysql-test/suite/innodb/r/bug76927.result new file mode 100644 index 0000000..985d00c --- /dev/null +++ b/mysql-test/suite/innodb/r/bug76927.result @@ -0,0 +1,39 @@ +CREATE TABLE t1 ( +a INT NOT NULL, +b INT NOT NULL, +PRIMARY KEY(b), +UNIQUE KEY(a)) +ENGINE=INNODB; +SET @old_innodb_stats_auto_recalc = @@innodb_stats_auto_recalc; +SET GLOBAL innodb_stats_auto_recalc = OFF; +SET GLOBAL innodb_purge_stop_now = ON; +SET @old_tx_isolation = @@tx_isolation; +SET GLOBAL tx_isolation = 'READ-COMMITTED'; +SET @old_innodb_lock_wait_timeout = @@innodb_lock_wait_timeout; +SET GLOBAL innodb_lock_wait_timeout = 1; +INSERT INTO t1 VALUES (1,1),(2,2); +DELETE FROM t1; +SET debug_sync = 'row_ins_sec_index_entry_dup_locks_created SIGNAL con1_locks_done WAIT_FOR con1_go'; +SET debug_sync = 'ha_commit_trans_after_acquire_commit_lock SIGNAL con1_insert_done WAIT_FOR con1_finish'; +REPLACE INTO t1 VALUES (1,2); +SET debug_sync = 'now WAIT_FOR con1_locks_done'; +SET debug_sync = 'lock_wait_suspend_thread_enter SIGNAL con2_blocked WAIT_FOR con2_go'; +SET debug_sync = 'ha_commit_trans_after_acquire_commit_lock SIGNAL con2_insert_done WAIT_FOR con2_finish'; +SET debug_sync = 'ib_after_row_insert SIGNAL con2_insert_done'; +REPLACE INTO t1 VALUES(1,3); +SET debug_sync = 'now WAIT_FOR con2_blocked'; +SET GLOBAL innodb_purge_run_now=ON; +SET debug_sync = 'now SIGNAL con2_go WAIT_FOR con2_insert_done'; +SET debug_sync = 'now SIGNAL con1_go WAIT_FOR con1_insert_done'; +SET debug_sync = 'now SIGNAL con1_finish'; +SET debug_sync = 'now SIGNAL con2_finish'; +SELECT * FROM t1; +a b +1 2 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +SET GLOBAL innodb_stats_auto_recalc = @old_innodb_stats_auto_recalc; +SET GLOBAL tx_isolation = @old_tx_isolation; +SET GLOBAL innodb_lock_wait_timeout = @old_innodb_lock_wait_timeout; diff --git a/mysql-test/suite/innodb/t/bug76927.test b/mysql-test/suite/innodb/t/bug76927.test new file mode 100644 index 0000000..42e138a --- /dev/null +++ b/mysql-test/suite/innodb/t/bug76927.test @@ -0,0 +1,90 @@ +######################################################################## +# Bug #76927: Duplicate UK values in READ-COMMITTED (again) +######################################################################## + +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc + +CREATE TABLE t1 ( + a INT NOT NULL, + b INT NOT NULL, + PRIMARY KEY(b), + UNIQUE KEY(a)) +ENGINE=INNODB; + +SET @old_innodb_stats_auto_recalc = @@innodb_stats_auto_recalc; +SET GLOBAL innodb_stats_auto_recalc = OFF; + +# Block purge +SET GLOBAL innodb_purge_stop_now = ON; + +SET @old_tx_isolation = @@tx_isolation; +SET GLOBAL tx_isolation = 'READ-COMMITTED'; + +SET @old_innodb_lock_wait_timeout = @@innodb_lock_wait_timeout; +SET GLOBAL innodb_lock_wait_timeout = 1; + +--connect(con1,localhost,root,,) +--connect(con2,localhost,root,,) + +--connection con1 + +# Create and delete-mark an index record + +INSERT INTO t1 VALUES (1,1),(2,2); +DELETE FROM t1; + +SET debug_sync = 'row_ins_sec_index_entry_dup_locks_created SIGNAL con1_locks_done WAIT_FOR con1_go'; +SET debug_sync = 'ha_commit_trans_after_acquire_commit_lock SIGNAL con1_insert_done WAIT_FOR con1_finish'; +--send +REPLACE INTO t1 VALUES (1,2); + +--connection con2 + +SET debug_sync = 'now WAIT_FOR con1_locks_done'; + +SET debug_sync = 'lock_wait_suspend_thread_enter SIGNAL con2_blocked WAIT_FOR con2_go'; +SET debug_sync = 'ha_commit_trans_after_acquire_commit_lock SIGNAL con2_insert_done WAIT_FOR con2_finish'; +SET debug_sync = 'ib_after_row_insert SIGNAL con2_insert_done'; + +--send +REPLACE INTO t1 VALUES(1,3); + +--connection default +SET debug_sync = 'now WAIT_FOR con2_blocked'; + +SET GLOBAL innodb_purge_run_now=ON; + +# Wait for purge to delete the delete-marked record +let $wait_condition= + SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS + WHERE VARIABLE_NAME='INNODB_PURGE_TRX_ID_AGE' AND VARIABLE_VALUE=2; +--source include/wait_condition.inc + +SET debug_sync = 'now SIGNAL con2_go WAIT_FOR con2_insert_done'; +SET debug_sync = 'now SIGNAL con1_go WAIT_FOR con1_insert_done'; + +SET debug_sync = 'now SIGNAL con1_finish'; +SET debug_sync = 'now SIGNAL con2_finish'; + +--connection con1 +--reap + +--connection con2 +--error 0,ER_LOCK_WAIT_TIMEOUT +--reap + +--connection default + +--disconnect con1 +--disconnect con2 + +SELECT * FROM t1; +CHECK TABLE t1; + +DROP TABLE t1; + +SET GLOBAL innodb_stats_auto_recalc = @old_innodb_stats_auto_recalc; +SET GLOBAL tx_isolation = @old_tx_isolation; +SET GLOBAL innodb_lock_wait_timeout = @old_innodb_lock_wait_timeout; diff --git a/storage/innobase/lock/lock0lock.cc b/storage/innobase/lock/lock0lock.cc index 9d047c4..ece3757 100644 --- a/storage/innobase/lock/lock0lock.cc +++ b/storage/innobase/lock/lock0lock.cc @@ -2645,7 +2645,8 @@ lock_rec_inherit_to_gap( && !((srv_locks_unsafe_for_binlog || lock->trx->isolation_level <= TRX_ISO_READ_COMMITTED) - && lock_get_mode(lock) == LOCK_X)) { + && lock_get_mode(lock) == + (lock->trx->duplicates ? LOCK_S : LOCK_X))) { lock_rec_add_to_queue( LOCK_REC | LOCK_GAP | lock_get_mode(lock), diff --git a/storage/innobase/row/row0ins.cc b/storage/innobase/row/row0ins.cc index e31b447..f35d36b 100644 --- a/storage/innobase/row/row0ins.cc +++ b/storage/innobase/row/row0ins.cc @@ -2709,6 +2709,8 @@ row_ins_sec_index_entry_low( goto func_exit; } + DEBUG_SYNC_C("row_ins_sec_index_entry_dup_locks_created"); + /* We did not find a duplicate and we have now locked with s-locks the necessary records to prevent any insertion of a duplicate by another