From a0a51251e49365cc0f256a2ebc90994dbe0d9a95 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Thu, 10 Nov 2011 12:49:31 +0200 Subject: 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 --- mysql-test/suite/innodb/r/innodb_replace.result | 77 +++++++++ mysql-test/suite/innodb/t/innodb_replace.test | 186 +++++++++++++++++++++ .../suite/innodb_plugin/r/innodb_replace.result | 77 +++++++++ .../suite/innodb_plugin/t/innodb_replace.test | 186 +++++++++++++++++++++ 4 files changed, 526 insertions(+) create mode 100644 mysql-test/suite/innodb/r/innodb_replace.result create mode 100644 mysql-test/suite/innodb/t/innodb_replace.test create mode 100644 mysql-test/suite/innodb_plugin/r/innodb_replace.result create mode 100644 mysql-test/suite/innodb_plugin/t/innodb_replace.test (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb/r/innodb_replace.result b/mysql-test/suite/innodb/r/innodb_replace.result new file mode 100644 index 00000000000..30009b8ddc9 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_replace.result @@ -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; diff --git a/mysql-test/suite/innodb/t/innodb_replace.test b/mysql-test/suite/innodb/t/innodb_replace.test new file mode 100644 index 00000000000..a35f423c85e --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_replace.test @@ -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; diff --git a/mysql-test/suite/innodb_plugin/r/innodb_replace.result b/mysql-test/suite/innodb_plugin/r/innodb_replace.result new file mode 100644 index 00000000000..30009b8ddc9 --- /dev/null +++ b/mysql-test/suite/innodb_plugin/r/innodb_replace.result @@ -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; diff --git a/mysql-test/suite/innodb_plugin/t/innodb_replace.test b/mysql-test/suite/innodb_plugin/t/innodb_replace.test new file mode 100644 index 00000000000..f052edd1d24 --- /dev/null +++ b/mysql-test/suite/innodb_plugin/t/innodb_replace.test @@ -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; -- cgit v1.2.1