Debian Codegraph @

mysql-test/suite/perfschema/t/data_lock_waits_join.test

raw
1 --source include/have_debug_sync.inc
2
3 --echo Bug #28733170
4 --echo PERFORMANCE_SCHEMA.DATA_LOCKS DOES NOT HANDLE JOINS CORRECTLY
5
6 CREATE TABLE t1(
7 id INT PRIMARY KEY
8 ) Engine=InnoDB;
9
10 INSERT INTO t1 VALUES (1);
11 # Create a situation in which a transaction holds more than one lock on same record.
12 BEGIN;
13 SELECT * FROM t1 WHERE id=1 FOR SHARE;
14 SELECT * FROM t1 WHERE id=1 FOR UPDATE;
15
16 # Now let another transaction be blocked by the first one
17 --connect (C1, localhost, root,,)
18 BEGIN;
19 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL C1_will_wait';
20 --send SELECT * FROM t1 WHERE id=1 FOR UPDATE
21
22
23 --connect (C2, localhost, root,,)
24 SET DEBUG_SYNC = 'now WAIT_FOR C1_will_wait';
25
26 # Store all blocking_engine_lock_id-s in t2
27
28 CREATE TABLE t2 (
29 id INT PRIMARY KEY AUTO_INCREMENT,
30 lock_id VARCHAR(200) NOT NULL
31 ) Engine=InnoDB;
32
33 INSERT INTO t2 (lock_id)
34 SELECT blocking_engine_lock_id
35 FROM performance_schema.data_lock_waits;
36
37 # We expect the number of rows in the JOIN
38 # to be equal to the number of rows in t2.
39 # The bug was causing this JOIN to trigger debug assertion
40 # failure (or return single row only if one only fixed the
41 # assertion failure).
42
43 SELECT COUNT(*) FROM t2;
44
45 SELECT COUNT(t2.id)
46 FROM t2
47 JOIN performance_schema.data_lock_waits dlw
48 ON(dlw.blocking_engine_lock_id = t2.lock_id)
49 ORDER BY t2.id;
50
51 # Store two copies of the requesting_engine_lock_id in t3
52
53 CREATE TABLE t3 (
54 id INT PRIMARY KEY AUTO_INCREMENT,
55 lock_id VARCHAR(200) NOT NULL
56 ) Engine=InnoDB;
57
58 INSERT INTO t3 (lock_id)
59 SELECT requesting_engine_lock_id
60 FROM performance_schema.data_lock_waits
61 LIMIT 1;
62 INSERT INTO t3 (lock_id)
63 SELECT requesting_engine_lock_id
64 FROM performance_schema.data_lock_waits
65 LIMIT 1;
66
67 # We expect each of two rows in t3 to match at least one row
68 # so the result should be 2. The bug caused assertion failure
69 # in debug mode (or just 1 row to be found if one fixed only
70 # assertion failure).
71 SELECT COUNT(DISTINCT t3.id)
72 FROM t3
73 JOIN performance_schema.data_lock_waits dlw
74 ON(dlw.requesting_engine_lock_id = t3.lock_id)
75 ORDER BY t3.id;
76
77 # Finally try if JOINing data_lock_waits with data_locks works,
78 # which means that each of edges in data_lock_waits should be
79 # successfully joined on both ends to nodes in data_locks.
80 # The bug caused this to find only one row instead of two.
81 SELECT COUNT(*) FROM performance_schema.data_lock_waits;
82 SELECT COUNT(*)
83 FROM performance_schema.data_lock_waits
84 JOIN performance_schema.data_locks r
85 ON(r.engine_lock_id = requesting_engine_lock_id)
86 JOIN performance_schema.data_locks b
87 ON(b.engine_lock_id = blocking_engine_lock_id);
88
89 DROP TABLE t2;
90 DROP TABLE t3;
91
92 --connection default
93 ROLLBACK;
94
95 --disconnect C1
96 --disconnect C2
97
98 DROP TABLE t1;