Debian Codegraph @

mysql-test/suite/innodb/t/mdev-14846.test

raw
1 --source include/have_innodb.inc
2 --source include/count_sessions.inc
3 --source include/have_debug_sync.inc
4
5 --source include/innodb_stable_estimates.inc
6
7 --disable_query_log
8 call mtr.add_suppression("InnoDB: Transaction was aborted due to ");
9 --enable_query_log
10
11 CREATE TABLE t1 (
12 pk INT,
13 f1 VARCHAR(10) NOT NULL,
14 f2 VARCHAR(10) NULL,
15 f3 INT UNSIGNED NULL,
16 KEY (f1),
17 PRIMARY KEY (pk)
18 ) ENGINE=InnoDB;
19
20 CREATE OR REPLACE ALGORITHM=MERGE VIEW v4 AS SELECT * FROM t1;
21 INSERT INTO t1 VALUES (1,'k','g',6),(2,'y','r',0),(3,'t','q',1),(4,'a','r',NULL),(5,'z','t',NULL);
22
23 CREATE TABLE t2 (f VARCHAR(10) NULL) ENGINE=InnoDB;
24 INSERT INTO t2 VALUES (NULL),('g'),('e'),('g');
25
26 CREATE TABLE t3 (
27 f1 VARCHAR(10) NOT NULL,
28 f2 VARCHAR(10) NULL,
29 f3 INT UNSIGNED NULL
30 ) ENGINE=InnoDB;
31
32 INSERT INTO t3 VALUES ('k','n',9),('y','b',8),('m','w',6);
33
34 CREATE TABLE t4 (f INT NULL) ENGINE=InnoDB;
35 INSERT INTO t4 VALUES (8),(9);
36 UPDATE t1 SET t1.pk = -109 WHERE t1.f1 IN ( SELECT 'a' FROM t4 WHERE f >= 1 );
37 SET DEBUG_SYNC='now SIGNAL con1_dml';
38
39 --connect (con1,localhost,root,,test)
40 SET DEBUG_SYNC='now WAIT_FOR con1_dml';
41 begin;
42 SELECT * FROM t1 for update; # Holds x lock of all records in the table t1
43 SET DEBUG_SYNC='now SIGNAL default_dml';
44 SET DEBUG_SYNC='now SIGNAL con2_dml';
45
46 --connection default
47 SET DEBUG_SYNC='now WAIT_FOR default_dml';
48 --replace_column 9 #
49 explain UPDATE t3 AS alias1 LEFT JOIN t3 AS alias2 ON ( alias1.f1 <> alias1.f2 ) SET alias1.f3 = 59 WHERE ( EXISTS ( SELECT t1.f3 FROM t1 IGNORE INDEX (f1) WHERE t1.f1 = alias1.f1 ) ) OR alias2.f1 = 'h';
50 --send UPDATE t3 AS alias1 LEFT JOIN t3 AS alias2 ON ( alias1.f1 <> alias1.f2 ) SET alias1.f3 = 59 WHERE ( EXISTS ( SELECT t1.f3 FROM t1 IGNORE INDEX (f1) WHERE t1.f1 = alias1.f1 ) ) OR alias2.f1 = 'h'
51 # It holds the lock of all record in t3 and tries to acquire record lock for the table t1.
52
53 --connect (con2,localhost,root,,test)
54 set debug_sync='now WAIT_FOR con2_dml';
55 let $wait_condition=
56 select count(*) > 0 from information_schema.innodb_lock_waits;
57 --source include/wait_condition.inc
58 SET DEBUG_SYNC='now SIGNAL con1_dml2';
59 disconnect con2;
60
61 # Cleanup
62 --connection con1
63 SET DEBUG_SYNC='now WAIT_FOR con1_dml2';
64 --replace_column 9 #
65 explain UPDATE v4, t1 SET t1.pk = 76 WHERE t1.f2 IN ( SELECT t2.f FROM t2 INNER JOIN t3 );
66 UPDATE v4, t1 SET t1.pk = 76 WHERE t1.f2 IN ( SELECT t2.f FROM t2 INNER JOIN t3 );
67 # It holds the record lock on table t1 and tries to acquire record lock on t3.
68 # leads to deadlock (con1 trx is waiting for default trx and vice versa)
69
70 --connection default
71 --error ER_LOCK_DEADLOCK
72 --reap
73
74 connection con1;
75 COMMIT;
76 disconnect con1;
77
78 --connection default
79 DROP VIEW v4;
80 DROP TABLE t1, t2, t3, t4;
81 set debug_sync= reset;