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 --- .../suite/innodb_plugin/r/innodb_replace.result | 77 ++++++++++++++++++++++ 1 file changed, 77 insertions(+) create mode 100644 mysql-test/suite/innodb_plugin/r/innodb_replace.result (limited to 'mysql-test/suite/innodb_plugin/r/innodb_replace.result') 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; -- cgit v1.2.1