List:Commits« Previous MessageNext Message »
From:marko.makela Date:November 10 2011 11:31am
Subject:bzr push into mysql-5.1 branch (marko.makela:3638 to 3639) Bug#7975
Bug#11759688
View as plain text  
 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#11759688marko.makela11 Nov