3639 Marko Mäkelä 2011-11-10
Bug#11759688 52020: InnoDB can still deadlock on just INSERT...ON DUPLICATE KEY
a.k.a. Bug#7975 deadlock without any locking, simple select and update
Bug#7975 was reintroduced when the storage engine API was made
pluggable in MySQL 5.1. Instead of looking at thd->lex directly, we
rely on handler::extra(). But, we were looking at the wrong extra()
flag, and we were ignoring the TRX_DUP_REPLACE flag in places where we
should obey it.
innodb_replace.test: Add tests for hopefully all affected statement
types, so that bug should never ever resurface. This kind of tests
should have been added when fixing Bug#7975 in MySQL 5.0.3 in the
first place.
rb:806 approved by Sunny Bains
added:
mysql-test/suite/innodb/r/innodb_replace.result
mysql-test/suite/innodb/t/innodb_replace.test
mysql-test/suite/innodb_plugin/r/innodb_replace.result
mysql-test/suite/innodb_plugin/t/innodb_replace.test
modified:
sql/sql_insert.cc
storage/innobase/handler/ha_innodb.cc
storage/innobase/row/row0ins.c
storage/innodb_plugin/ChangeLog
storage/innodb_plugin/handler/ha_innodb.cc
storage/innodb_plugin/row/row0ins.c
3638 Marko Mäkelä 2011-11-08
Bug#13358468 ASSERTION FAILURE IN BTR_PCUR_GET_BLOCK
btr_pcur_restore_position_func(): When the cursor was positioned at
the tree infimum or supremum, initialize pos_state and latch_mode. The
assertion failed, because pos_state was BTR_PCUR_WAS_POSITIONED. In
the test failure of WL#5874, the purge thread attempted to restore the
cursor position on the infimum record (the clustered index was empty).
btr_pcur_detach(), btr_pcur_is_detached(): Unused functions, remove.
rb:804 approved by Inaam Rana
modified:
storage/innodb_plugin/ChangeLog
storage/innodb_plugin/btr/btr0pcur.c
storage/innodb_plugin/include/btr0pcur.h
storage/innodb_plugin/include/btr0pcur.ic
=== added file 'mysql-test/suite/innodb/r/innodb_replace.result'
--- a/mysql-test/suite/innodb/r/innodb_replace.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/innodb/r/innodb_replace.result revid:marko.makela@stripped02nfjbi2an
@@ -0,0 +1,77 @@
+#
+#Bug#11759688 52020: InnoDB can still deadlock
+#on just INSERT...ON DUPLICATE KEY
+#a.k.a. Bug#7975 deadlock without any locking, simple select and update
+#
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(3,1);
+BEGIN;
+SET DEBUG_SYNC='write_row_noreplace SIGNAL insert1 WAIT_FOR select1';
+INSERT INTO t1 VALUES(3,2);
+SET DEBUG_SYNC='now WAIT_FOR insert1';
+SELECT * FROM t1 LOCK IN SHARE MODE;
+a b
+3 1
+SELECT * FROM t1 FOR UPDATE;
+SET DEBUG_SYNC='now SIGNAL select1';
+ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
+INSERT INTO t1 VALUES(3,3) ON DUPLICATE KEY UPDATE b=b+10;
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+COMMIT;
+SET DEBUG_SYNC='write_row_replace SIGNAL insert2 WAIT_FOR select2';
+REPLACE INTO t1 VALUES(3,4);
+SET DEBUG_SYNC='now WAIT_FOR insert2';
+SELECT * FROM t1;
+a b
+3 11
+SELECT * FROM t1 LOCK IN SHARE MODE;
+SET DEBUG_SYNC='now SIGNAL select2';
+SET DEBUG_SYNC='write_row_replace SIGNAL insert3 WAIT_FOR select3';
+INSERT INTO t1 VALUES(3,5) ON DUPLICATE KEY UPDATE b=b+20;
+a b
+3 4
+SET DEBUG_SYNC='now WAIT_FOR insert3';
+SELECT b FROM t1 LOCK IN SHARE MODE;
+SET DEBUG_SYNC='now SIGNAL select3';
+b
+24
+SET DEBUG_SYNC='write_row_noreplace SIGNAL insert4 WAIT_FOR select4';
+LOAD DATA INFILE '../../std_data/loaddata5.dat' INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
+SET DEBUG_SYNC='now WAIT_FOR insert4';
+SELECT b FROM t1 WHERE a=3 LOCK IN SHARE MODE;
+b
+24
+SELECT b FROM t1 WHERE a=3 FOR UPDATE;
+SET DEBUG_SYNC='now SIGNAL select4';
+b
+24
+ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
+SET DEBUG_SYNC='write_row_noreplace SIGNAL insert5 WAIT_FOR select5';
+LOAD DATA INFILE '../../std_data/loaddata5.dat' IGNORE INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
+SET DEBUG_SYNC='now WAIT_FOR insert5';
+SELECT * FROM t1;
+a b
+3 24
+SELECT * FROM t1 WHERE a=3 LOCK IN SHARE MODE;
+a b
+3 24
+SELECT * FROM t1 WHERE a=3 FOR UPDATE;
+SET DEBUG_SYNC='now SIGNAL select5';
+a b
+3 24
+SET DEBUG_SYNC='write_row_replace SIGNAL insert6 WAIT_FOR select6';
+LOAD DATA INFILE '../../std_data/loaddata5.dat' REPLACE INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
+SET DEBUG_SYNC='now WAIT_FOR insert6';
+SELECT * FROM t1;
+a b
+1 2
+3 24
+5 6
+SELECT a,b FROM t1 LOCK IN SHARE MODE;
+SET DEBUG_SYNC='now SIGNAL select6';
+a b
+1 2
+3 4
+5 6
+SET DEBUG_SYNC='RESET';
+DROP TABLE t1;
=== added file 'mysql-test/suite/innodb/t/innodb_replace.test'
--- a/mysql-test/suite/innodb/t/innodb_replace.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/innodb/t/innodb_replace.test revid:marko.makela@strippedbi2an
@@ -0,0 +1,186 @@
+--source include/have_innodb.inc
+--source include/have_debug_sync.inc
+
+--echo #
+--echo #Bug#11759688 52020: InnoDB can still deadlock
+--echo #on just INSERT...ON DUPLICATE KEY
+--echo #a.k.a. Bug#7975 deadlock without any locking, simple select and update
+--echo #
+
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES(3,1);
+
+connect (con1,localhost,root,,);
+connect (con2,localhost,root,,);
+connection con1;
+
+BEGIN;
+# normal INSERT of a duplicate should only S-lock the existing record (3,1)
+SET DEBUG_SYNC='write_row_noreplace SIGNAL insert1 WAIT_FOR select1';
+--send
+INSERT INTO t1 VALUES(3,2);
+
+connection default;
+SET DEBUG_SYNC='now WAIT_FOR insert1';
+# this should S-lock (3,1); no conflict
+SELECT * FROM t1 LOCK IN SHARE MODE;
+# this should X-lock (3,1), conflicting with con1
+--send
+SELECT * FROM t1 FOR UPDATE;
+
+connection con2;
+# Check that the above SELECT is blocked
+let $wait_condition=
+ select count(*) = 1 from information_schema.processlist
+ where state = 'Sending data' and
+ info = 'SELECT * FROM t1 FOR UPDATE';
+--source include/wait_condition.inc
+SET DEBUG_SYNC='now SIGNAL select1';
+
+connection con1;
+--error ER_DUP_ENTRY
+reap;
+# We are still holding an S-lock on (3,1) after the failed INSERT.
+# The following will upgrade it to an X-lock, causing a deadlock.
+# InnoDB should resolve the deadlock by aborting the blocked SELECT.
+INSERT INTO t1 VALUES(3,3) ON DUPLICATE KEY UPDATE b=b+10;
+
+connection default;
+--error ER_LOCK_DEADLOCK
+reap;
+connection con1;
+COMMIT;
+
+SET DEBUG_SYNC='write_row_replace SIGNAL insert2 WAIT_FOR select2';
+--send
+REPLACE INTO t1 VALUES(3,4);
+
+connection default;
+SET DEBUG_SYNC='now WAIT_FOR insert2';
+SELECT * FROM t1;
+--send
+SELECT * FROM t1 LOCK IN SHARE MODE;
+
+connection con2;
+# Check that the above SELECT is blocked because of X lock.
+let $wait_condition=
+ select count(*) = 1 from information_schema.processlist
+ where state = 'Sending data' and
+ info = 'SELECT * FROM t1 LOCK IN SHARE MODE';
+--source include/wait_condition.inc
+SET DEBUG_SYNC='now SIGNAL select2';
+
+connection con1;
+reap;
+
+SET DEBUG_SYNC='write_row_replace SIGNAL insert3 WAIT_FOR select3';
+--send
+INSERT INTO t1 VALUES(3,5) ON DUPLICATE KEY UPDATE b=b+20;
+
+connection default;
+reap;
+SET DEBUG_SYNC='now WAIT_FOR insert3';
+--send
+SELECT b FROM t1 LOCK IN SHARE MODE;
+
+connection con2;
+# Check that the above SELECT is blocked because of X lock.
+let $wait_condition=
+ select count(*) = 1 from information_schema.processlist
+ where state = 'Sending data' and
+ info = 'SELECT b FROM t1 LOCK IN SHARE MODE';
+--source include/wait_condition.inc
+SET DEBUG_SYNC='now SIGNAL select3';
+
+connection default;
+reap;
+
+connection con1;
+reap;
+SET DEBUG_SYNC='write_row_noreplace SIGNAL insert4 WAIT_FOR select4';
+--send
+LOAD DATA INFILE '../../std_data/loaddata5.dat' INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
+
+connection default;
+SET DEBUG_SYNC='now WAIT_FOR insert4';
+# this should S-lock (3,1); no conflict
+SELECT b FROM t1 WHERE a=3 LOCK IN SHARE MODE;
+# this should X-lock (3,1), conflicting with con1
+--send
+SELECT b FROM t1 WHERE a=3 FOR UPDATE;
+
+connection con2;
+# Check that the above SELECT is blocked
+let $wait_condition=
+ select count(*) = 1 from information_schema.processlist
+ where state = 'statistics' and
+ info = 'SELECT b FROM t1 WHERE a=3 FOR UPDATE';
+--source include/wait_condition.inc
+SET DEBUG_SYNC='now SIGNAL select4';
+
+connection default;
+reap;
+
+connection con1;
+--error ER_DUP_ENTRY
+reap;
+SET DEBUG_SYNC='write_row_noreplace SIGNAL insert5 WAIT_FOR select5';
+--send
+LOAD DATA INFILE '../../std_data/loaddata5.dat' IGNORE INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
+
+connection default;
+SET DEBUG_SYNC='now WAIT_FOR insert5';
+SELECT * FROM t1;
+# this should S-lock; no conflict
+SELECT * FROM t1 WHERE a=3 LOCK IN SHARE MODE;
+# this should X-lock, conflicting with the S-lock of the IGNORE in con1
+--send
+SELECT * FROM t1 WHERE a=3 FOR UPDATE;
+
+connection con2;
+# Check that the above SELECT is blocked
+let $wait_condition=
+ select count(*) = 1 from information_schema.processlist
+ where state = 'statistics' and
+ info = 'SELECT * FROM t1 WHERE a=3 FOR UPDATE';
+--source include/wait_condition.inc
+SET DEBUG_SYNC='now SIGNAL select5';
+
+connection con1;
+reap;
+connection default;
+reap;
+
+connection con1;
+SET DEBUG_SYNC='write_row_replace SIGNAL insert6 WAIT_FOR select6';
+--send
+LOAD DATA INFILE '../../std_data/loaddata5.dat' REPLACE INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
+
+connection default;
+SET DEBUG_SYNC='now WAIT_FOR insert6';
+SELECT * FROM t1;
+# this should conflict with the X-lock acquired by the REPLACE
+--send
+SELECT a,b FROM t1 LOCK IN SHARE MODE;
+
+connection con2;
+# Check that the above SELECT is blocked
+let $wait_condition=
+ select count(*) = 1 from information_schema.processlist
+ where state = 'Sending data' and
+ info = 'SELECT a,b FROM t1 LOCK IN SHARE MODE';
+--source include/wait_condition.inc
+SET DEBUG_SYNC='now SIGNAL select6';
+
+connection con1;
+reap;
+connection default;
+reap;
+
+disconnect con1;
+disconnect con2;
+
+connection default;
+SET DEBUG_SYNC='RESET';
+DROP TABLE t1;
=== added file 'mysql-test/suite/innodb_plugin/r/innodb_replace.result'
--- a/mysql-test/suite/innodb_plugin/r/innodb_replace.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/innodb_plugin/r/innodb_replace.result revid:marko.makela@stripped20111110104931-w8xtuv02nfjbi2an
@@ -0,0 +1,77 @@
+#
+#Bug#11759688 52020: InnoDB can still deadlock
+#on just INSERT...ON DUPLICATE KEY
+#a.k.a. Bug#7975 deadlock without any locking, simple select and update
+#
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(3,1);
+BEGIN;
+SET DEBUG_SYNC='write_row_noreplace SIGNAL insert1 WAIT_FOR select1';
+INSERT INTO t1 VALUES(3,2);
+SET DEBUG_SYNC='now WAIT_FOR insert1';
+SELECT * FROM t1 LOCK IN SHARE MODE;
+a b
+3 1
+SELECT * FROM t1 FOR UPDATE;
+SET DEBUG_SYNC='now SIGNAL select1';
+ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
+INSERT INTO t1 VALUES(3,3) ON DUPLICATE KEY UPDATE b=b+10;
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+COMMIT;
+SET DEBUG_SYNC='write_row_replace SIGNAL insert2 WAIT_FOR select2';
+REPLACE INTO t1 VALUES(3,4);
+SET DEBUG_SYNC='now WAIT_FOR insert2';
+SELECT * FROM t1;
+a b
+3 11
+SELECT * FROM t1 LOCK IN SHARE MODE;
+SET DEBUG_SYNC='now SIGNAL select2';
+SET DEBUG_SYNC='write_row_replace SIGNAL insert3 WAIT_FOR select3';
+INSERT INTO t1 VALUES(3,5) ON DUPLICATE KEY UPDATE b=b+20;
+a b
+3 4
+SET DEBUG_SYNC='now WAIT_FOR insert3';
+SELECT b FROM t1 LOCK IN SHARE MODE;
+SET DEBUG_SYNC='now SIGNAL select3';
+b
+24
+SET DEBUG_SYNC='write_row_noreplace SIGNAL insert4 WAIT_FOR select4';
+LOAD DATA INFILE '../../std_data/loaddata5.dat' INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
+SET DEBUG_SYNC='now WAIT_FOR insert4';
+SELECT b FROM t1 WHERE a=3 LOCK IN SHARE MODE;
+b
+24
+SELECT b FROM t1 WHERE a=3 FOR UPDATE;
+SET DEBUG_SYNC='now SIGNAL select4';
+b
+24
+ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
+SET DEBUG_SYNC='write_row_noreplace SIGNAL insert5 WAIT_FOR select5';
+LOAD DATA INFILE '../../std_data/loaddata5.dat' IGNORE INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
+SET DEBUG_SYNC='now WAIT_FOR insert5';
+SELECT * FROM t1;
+a b
+3 24
+SELECT * FROM t1 WHERE a=3 LOCK IN SHARE MODE;
+a b
+3 24
+SELECT * FROM t1 WHERE a=3 FOR UPDATE;
+SET DEBUG_SYNC='now SIGNAL select5';
+a b
+3 24
+SET DEBUG_SYNC='write_row_replace SIGNAL insert6 WAIT_FOR select6';
+LOAD DATA INFILE '../../std_data/loaddata5.dat' REPLACE INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
+SET DEBUG_SYNC='now WAIT_FOR insert6';
+SELECT * FROM t1;
+a b
+1 2
+3 24
+5 6
+SELECT a,b FROM t1 LOCK IN SHARE MODE;
+SET DEBUG_SYNC='now SIGNAL select6';
+a b
+1 2
+3 4
+5 6
+SET DEBUG_SYNC='RESET';
+DROP TABLE t1;
=== added file 'mysql-test/suite/innodb_plugin/t/innodb_replace.test'
--- a/mysql-test/suite/innodb_plugin/t/innodb_replace.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/innodb_plugin/t/innodb_replace.test revid:marko.makela@stripped
@@ -0,0 +1,186 @@
+--source include/have_innodb_plugin.inc
+--source include/have_debug_sync.inc
+
+--echo #
+--echo #Bug#11759688 52020: InnoDB can still deadlock
+--echo #on just INSERT...ON DUPLICATE KEY
+--echo #a.k.a. Bug#7975 deadlock without any locking, simple select and update
+--echo #
+
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES(3,1);
+
+connect (con1,localhost,root,,);
+connect (con2,localhost,root,,);
+connection con1;
+
+BEGIN;
+# normal INSERT of a duplicate should only S-lock the existing record (3,1)
+SET DEBUG_SYNC='write_row_noreplace SIGNAL insert1 WAIT_FOR select1';
+--send
+INSERT INTO t1 VALUES(3,2);
+
+connection default;
+SET DEBUG_SYNC='now WAIT_FOR insert1';
+# this should S-lock (3,1); no conflict
+SELECT * FROM t1 LOCK IN SHARE MODE;
+# this should X-lock (3,1), conflicting with con1
+--send
+SELECT * FROM t1 FOR UPDATE;
+
+connection con2;
+# Check that the above SELECT is blocked
+let $wait_condition=
+ select count(*) = 1 from information_schema.processlist
+ where state = 'Sending data' and
+ info = 'SELECT * FROM t1 FOR UPDATE';
+--source include/wait_condition.inc
+SET DEBUG_SYNC='now SIGNAL select1';
+
+connection con1;
+--error ER_DUP_ENTRY
+reap;
+# We are still holding an S-lock on (3,1) after the failed INSERT.
+# The following will upgrade it to an X-lock, causing a deadlock.
+# InnoDB should resolve the deadlock by aborting the blocked SELECT.
+INSERT INTO t1 VALUES(3,3) ON DUPLICATE KEY UPDATE b=b+10;
+
+connection default;
+--error ER_LOCK_DEADLOCK
+reap;
+connection con1;
+COMMIT;
+
+SET DEBUG_SYNC='write_row_replace SIGNAL insert2 WAIT_FOR select2';
+--send
+REPLACE INTO t1 VALUES(3,4);
+
+connection default;
+SET DEBUG_SYNC='now WAIT_FOR insert2';
+SELECT * FROM t1;
+--send
+SELECT * FROM t1 LOCK IN SHARE MODE;
+
+connection con2;
+# Check that the above SELECT is blocked because of X lock.
+let $wait_condition=
+ select count(*) = 1 from information_schema.processlist
+ where state = 'Sending data' and
+ info = 'SELECT * FROM t1 LOCK IN SHARE MODE';
+--source include/wait_condition.inc
+SET DEBUG_SYNC='now SIGNAL select2';
+
+connection con1;
+reap;
+
+SET DEBUG_SYNC='write_row_replace SIGNAL insert3 WAIT_FOR select3';
+--send
+INSERT INTO t1 VALUES(3,5) ON DUPLICATE KEY UPDATE b=b+20;
+
+connection default;
+reap;
+SET DEBUG_SYNC='now WAIT_FOR insert3';
+--send
+SELECT b FROM t1 LOCK IN SHARE MODE;
+
+connection con2;
+# Check that the above SELECT is blocked because of X lock.
+let $wait_condition=
+ select count(*) = 1 from information_schema.processlist
+ where state = 'Sending data' and
+ info = 'SELECT b FROM t1 LOCK IN SHARE MODE';
+--source include/wait_condition.inc
+SET DEBUG_SYNC='now SIGNAL select3';
+
+connection default;
+reap;
+
+connection con1;
+reap;
+SET DEBUG_SYNC='write_row_noreplace SIGNAL insert4 WAIT_FOR select4';
+--send
+LOAD DATA INFILE '../../std_data/loaddata5.dat' INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
+
+connection default;
+SET DEBUG_SYNC='now WAIT_FOR insert4';
+# this should S-lock (3,1); no conflict
+SELECT b FROM t1 WHERE a=3 LOCK IN SHARE MODE;
+# this should X-lock (3,1), conflicting with con1
+--send
+SELECT b FROM t1 WHERE a=3 FOR UPDATE;
+
+connection con2;
+# Check that the above SELECT is blocked
+let $wait_condition=
+ select count(*) = 1 from information_schema.processlist
+ where state = 'statistics' and
+ info = 'SELECT b FROM t1 WHERE a=3 FOR UPDATE';
+--source include/wait_condition.inc
+SET DEBUG_SYNC='now SIGNAL select4';
+
+connection default;
+reap;
+
+connection con1;
+--error ER_DUP_ENTRY
+reap;
+SET DEBUG_SYNC='write_row_noreplace SIGNAL insert5 WAIT_FOR select5';
+--send
+LOAD DATA INFILE '../../std_data/loaddata5.dat' IGNORE INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
+
+connection default;
+SET DEBUG_SYNC='now WAIT_FOR insert5';
+SELECT * FROM t1;
+# this should S-lock; no conflict
+SELECT * FROM t1 WHERE a=3 LOCK IN SHARE MODE;
+# this should X-lock, conflicting with the S-lock of the IGNORE in con1
+--send
+SELECT * FROM t1 WHERE a=3 FOR UPDATE;
+
+connection con2;
+# Check that the above SELECT is blocked
+let $wait_condition=
+ select count(*) = 1 from information_schema.processlist
+ where state = 'statistics' and
+ info = 'SELECT * FROM t1 WHERE a=3 FOR UPDATE';
+--source include/wait_condition.inc
+SET DEBUG_SYNC='now SIGNAL select5';
+
+connection con1;
+reap;
+connection default;
+reap;
+
+connection con1;
+SET DEBUG_SYNC='write_row_replace SIGNAL insert6 WAIT_FOR select6';
+--send
+LOAD DATA INFILE '../../std_data/loaddata5.dat' REPLACE INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
+
+connection default;
+SET DEBUG_SYNC='now WAIT_FOR insert6';
+SELECT * FROM t1;
+# this should conflict with the X-lock acquired by the REPLACE
+--send
+SELECT a,b FROM t1 LOCK IN SHARE MODE;
+
+connection con2;
+# Check that the above SELECT is blocked
+let $wait_condition=
+ select count(*) = 1 from information_schema.processlist
+ where state = 'Sending data' and
+ info = 'SELECT a,b FROM t1 LOCK IN SHARE MODE';
+--source include/wait_condition.inc
+SET DEBUG_SYNC='now SIGNAL select6';
+
+connection con1;
+reap;
+connection default;
+reap;
+
+disconnect con1;
+disconnect con2;
+
+connection default;
+SET DEBUG_SYNC='RESET';
+DROP TABLE t1;
=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc revid:marko.makela@oracle.com-20111108121522-hyfqxk533a9bnnkf
+++ b/sql/sql_insert.cc revid:marko.makela@strippednfjbi2an
@@ -63,6 +63,8 @@
#include "slave.h"
#include "rpl_mi.h"
+#include "debug_sync.h"
+
#ifndef EMBEDDED_LIBRARY
static bool delayed_get_table(THD *thd, TABLE_LIST *table_list);
static int write_delayed(THD *thd, TABLE *table, enum_duplicates duplic,
@@ -1411,6 +1413,8 @@ int write_record(THD *thd, TABLE *table,
error= HA_ERR_FOUND_DUPP_KEY; /* Database can't find key */
goto err;
}
+ DEBUG_SYNC(thd, "write_row_replace");
+
/* Read all columns for the row we are going to replace */
table->use_all_columns();
/*
@@ -1604,6 +1608,7 @@ int write_record(THD *thd, TABLE *table,
}
else if ((error=table->file->ha_write_row(table->record[0])))
{
+ DEBUG_SYNC(thd, "write_row_noreplace");
if (!info->ignore ||
table->file->is_fatal_error(error, HA_CHECK_DUP))
goto err;
=== modified file 'storage/innobase/handler/ha_innodb.cc'
--- a/storage/innobase/handler/ha_innodb.cc revid:marko.makela@stripped
+++ b/storage/innobase/handler/ha_innodb.cc revid:marko.makela@stripped
@@ -4087,8 +4087,7 @@ no_commit:
switch (sql_command) {
case SQLCOM_LOAD:
- if ((trx->duplicates
- & (TRX_DUP_IGNORE | TRX_DUP_REPLACE))) {
+ if (trx->duplicates) {
goto set_max_autoinc;
}
@@ -4364,8 +4363,7 @@ ha_innobase::update_row(
&& table->next_number_field
&& new_row == table->record[0]
&& thd_sql_command(user_thd) == SQLCOM_INSERT
- && (trx->duplicates & (TRX_DUP_IGNORE | TRX_DUP_REPLACE))
- == TRX_DUP_IGNORE) {
+ && trx->duplicates) {
ulonglong auto_inc;
ulonglong col_max_value;
@@ -7099,6 +7097,7 @@ ha_innobase::extra(
break;
case HA_EXTRA_RESET_STATE:
reset_template(prebuilt);
+ thd_to_trx(ha_thd())->duplicates = 0;
break;
case HA_EXTRA_NO_KEYREAD:
prebuilt->read_just_key = 0;
@@ -7116,19 +7115,18 @@ ha_innobase::extra(
parameters below. We must not invoke update_thd()
either, because the calling threads may change.
CAREFUL HERE, OR MEMORY CORRUPTION MAY OCCUR! */
- case HA_EXTRA_IGNORE_DUP_KEY:
+ case HA_EXTRA_INSERT_WITH_UPDATE:
thd_to_trx(ha_thd())->duplicates |= TRX_DUP_IGNORE;
break;
+ case HA_EXTRA_NO_IGNORE_DUP_KEY:
+ thd_to_trx(ha_thd())->duplicates &= ~TRX_DUP_IGNORE;
+ break;
case HA_EXTRA_WRITE_CAN_REPLACE:
thd_to_trx(ha_thd())->duplicates |= TRX_DUP_REPLACE;
break;
case HA_EXTRA_WRITE_CANNOT_REPLACE:
thd_to_trx(ha_thd())->duplicates &= ~TRX_DUP_REPLACE;
break;
- case HA_EXTRA_NO_IGNORE_DUP_KEY:
- thd_to_trx(ha_thd())->duplicates &=
- ~(TRX_DUP_IGNORE | TRX_DUP_REPLACE);
- break;
default:/* Do nothing */
;
}
=== modified file 'storage/innobase/row/row0ins.c'
--- a/storage/innobase/row/row0ins.c revid:marko.makela@stripped
+++ b/storage/innobase/row/row0ins.c revid:marko.makela@stripped-20111110104931-w8xtuv02nfjbi2an
@@ -1674,7 +1674,7 @@ row_ins_scan_sec_index_for_duplicate(
btr_pcur_open(index, entry, PAGE_CUR_GE, BTR_SEARCH_LEAF, &pcur, &mtr);
- allow_duplicates = thr_get_trx(thr)->duplicates & TRX_DUP_IGNORE;
+ allow_duplicates = thr_get_trx(thr)->duplicates;
/* Scan index records and check if there is a duplicate */
@@ -1814,7 +1814,7 @@ row_ins_duplicate_error_in_clust(
sure that in roll-forward we get the same duplicate
errors as in original execution */
- if (trx->duplicates & TRX_DUP_IGNORE) {
+ if (trx->duplicates) {
/* If the SQL-query will update or replace
duplicate key we will take X-lock for
@@ -1856,7 +1856,7 @@ row_ins_duplicate_error_in_clust(
offsets = rec_get_offsets(rec, cursor->index, offsets,
ULINT_UNDEFINED, &heap);
- if (trx->duplicates & TRX_DUP_IGNORE) {
+ if (trx->duplicates) {
/* If the SQL-query will update or replace
duplicate key we will take X-lock for
=== modified file 'storage/innodb_plugin/ChangeLog'
--- a/storage/innodb_plugin/ChangeLog revid:marko.makela@stripped
+++ b/storage/innodb_plugin/ChangeLog revid:marko.makela@oracle.com-20111110104931-w8xtuv02nfjbi2an
@@ -1,3 +1,10 @@
+2011-11-10 The InnoDB Team
+
+ * handler/ha_innodb.cc, row/row0ins.c, innodb_replace.test:
+ Fix Bug#11759688 52020: InnoDB can still deadlock
+ on just INSERT...ON DUPLICATE KEY a.k.a. the reintroduction of
+ Bug#7975 deadlock without any locking, simple select and update
+
2011-11-08 The InnoDB Team
* btr/btr0pcur.c, include/btr0pcur.h, include/btr0pcur.ic:
=== modified file 'storage/innodb_plugin/handler/ha_innodb.cc'
--- a/storage/innodb_plugin/handler/ha_innodb.cc revid:marko.makela@stripped-20111108121522-hyfqxk533a9bnnkf
+++ b/storage/innodb_plugin/handler/ha_innodb.cc revid:marko.makela@stripped111110104931-w8xtuv02nfjbi2an
@@ -4793,8 +4793,7 @@ no_commit:
switch (sql_command) {
case SQLCOM_LOAD:
- if ((trx->duplicates
- & (TRX_DUP_IGNORE | TRX_DUP_REPLACE))) {
+ if (trx->duplicates) {
goto set_max_autoinc;
}
@@ -5069,8 +5068,7 @@ ha_innobase::update_row(
&& table->next_number_field
&& new_row == table->record[0]
&& thd_sql_command(user_thd) == SQLCOM_INSERT
- && (trx->duplicates & (TRX_DUP_IGNORE | TRX_DUP_REPLACE))
- == TRX_DUP_IGNORE) {
+ && trx->duplicates) {
ulonglong auto_inc;
ulonglong col_max_value;
@@ -8423,6 +8421,7 @@ ha_innobase::extra(
break;
case HA_EXTRA_RESET_STATE:
reset_template(prebuilt);
+ thd_to_trx(ha_thd())->duplicates = 0;
break;
case HA_EXTRA_NO_KEYREAD:
prebuilt->read_just_key = 0;
@@ -8440,19 +8439,18 @@ ha_innobase::extra(
parameters below. We must not invoke update_thd()
either, because the calling threads may change.
CAREFUL HERE, OR MEMORY CORRUPTION MAY OCCUR! */
- case HA_EXTRA_IGNORE_DUP_KEY:
+ case HA_EXTRA_INSERT_WITH_UPDATE:
thd_to_trx(ha_thd())->duplicates |= TRX_DUP_IGNORE;
break;
+ case HA_EXTRA_NO_IGNORE_DUP_KEY:
+ thd_to_trx(ha_thd())->duplicates &= ~TRX_DUP_IGNORE;
+ break;
case HA_EXTRA_WRITE_CAN_REPLACE:
thd_to_trx(ha_thd())->duplicates |= TRX_DUP_REPLACE;
break;
case HA_EXTRA_WRITE_CANNOT_REPLACE:
thd_to_trx(ha_thd())->duplicates &= ~TRX_DUP_REPLACE;
break;
- case HA_EXTRA_NO_IGNORE_DUP_KEY:
- thd_to_trx(ha_thd())->duplicates &=
- ~(TRX_DUP_IGNORE | TRX_DUP_REPLACE);
- break;
default:/* Do nothing */
;
}
=== modified file 'storage/innodb_plugin/row/row0ins.c'
--- a/storage/innodb_plugin/row/row0ins.c revid:marko.makela@oracle.com-20111108121522-hyfqxk533a9bnnkf
+++ b/storage/innodb_plugin/row/row0ins.c revid:marko.makela@stripped20111110104931-w8xtuv02nfjbi2an
@@ -1662,7 +1662,7 @@ row_ins_scan_sec_index_for_duplicate(
ulint n_fields_cmp;
btr_pcur_t pcur;
ulint err = DB_SUCCESS;
- unsigned allow_duplicates;
+ ulint allow_duplicates;
mtr_t mtr;
mem_heap_t* heap = NULL;
ulint offsets_[REC_OFFS_NORMAL_SIZE];
@@ -1693,7 +1693,7 @@ row_ins_scan_sec_index_for_duplicate(
btr_pcur_open(index, entry, PAGE_CUR_GE, BTR_SEARCH_LEAF, &pcur, &mtr);
- allow_duplicates = thr_get_trx(thr)->duplicates & TRX_DUP_IGNORE;
+ allow_duplicates = thr_get_trx(thr)->duplicates;
/* Scan index records and check if there is a duplicate */
@@ -1827,7 +1827,7 @@ row_ins_duplicate_error_in_clust(
sure that in roll-forward we get the same duplicate
errors as in original execution */
- if (trx->duplicates & TRX_DUP_IGNORE) {
+ if (trx->duplicates) {
/* If the SQL-query will update or replace
duplicate key we will take X-lock for
@@ -1871,7 +1871,7 @@ row_ins_duplicate_error_in_clust(
offsets = rec_get_offsets(rec, cursor->index, offsets,
ULINT_UNDEFINED, &heap);
- if (trx->duplicates & TRX_DUP_IGNORE) {
+ if (trx->duplicates) {
/* If the SQL-query will update or replace
duplicate key we will take X-lock for
No bundle (reason: useless for push emails).| Thread |
|---|
| • bzr push into mysql-5.1 branch (marko.makela:3638 to 3639) Bug#7975Bug#11759688 | marko.makela | 11 Nov |
