Debian Codegraph @

mysql-test/suite/innodb/t/lock_collision_report.test

raw
1 # BUG#32586458 INNODB CLUSTER SLOWNESS SEEING SESSIONS WITH STATUS "WAITING
2 # FOR HANDLER TO COMMIT"
3 #
4 --source include/have_debug_sync.inc
5
6 # This scenario demonstrates that when there are N reasons for a lock wait,
7 # (connections rk_1,..,rk_N hold S lock on a row needed by rt_wait)
8 # which go away one by one (as rk_$k get rolled back), then InnoDB correctly
9 # reports one of the remaining reasons for wait.
10 # InnoDB either grants a requested lock or picks one of conflicting transactions
11 # as the current reason it has to wait. If there is more than one conflicting
12 # transaction, the choice is non-deterministic. Thus, this test uses a trick:
13 # it relies on the InnoDB deadlock checker's logic, which rolls back a
14 # transaction which is part of a deadlock cycle consisting of edges that are
15 # "current reason for wait".
16 # Therefore this MTR does not have to know in which order it should rollback the
17 # transactions rt_1,...,rt_N to follow the order of picking "current reason for
18 # wait". Instead this MTR simply arranges it so that there are N small deadlock
19 # cycles, k-th of them involving rt_k and rt_wait only:
20 #
21 # rt_wait <--x-locked-by----- 100 <---has-requested-s-lock---\
22 # | |
23 # | / | \
24 # | rt_1 ... rt_N
25 # | ^ ^ ^
26 # | \ | /
27 # | |
28 # \--has-requested-x-lock--> 20 --s-locked-----------------/
29 #
30 # The wait-for graph between transactions inspected by InnoDB's deadlock checker
31 # is a sparse subgraph of the above: only one of the paths from rt_wait to one
32 # rt_k is picked. It is the same edge which is reported to Server layer.
33 # As it is a part of a deadlock cycle, the victim will be chosen, cycle removed,
34 # and another edge will be picked and so on.
35 # We make sure rt_wait is never chosen as a victim, by making its TRX_WEIGHT
36 # high, by INSERTing several records.
37 # What remains is to count that the number of reported wait-for edges is N*2.
38
39 # Create a table
40 CREATE TABLE t (
41 id INT PRIMARY KEY
42 ) ENGINE=InnoDB;
43 INSERT INTO t VALUES (10), (20), (30), (40), (50);
44
45 # We want to be informed about any lock waits reported to Server layer
46 --let $debug_point = syncpoint_report_lock_collision
47 --source include/add_debug_point.inc
48
49 --let $n_blockers = 13
50
51 # Create N connections to the server.
52 # Using connections 1..N, start a transaction, acquire shared lock over a given
53 # record using `SELECT ... FOR SHARE`
54 --let $k = $n_blockers
55 while ($k != 0)
56 {
57 --connect (rt_$k,127.0.0.1,root,,test,,)
58 BEGIN;
59 SELECT id FROM t FORCE INDEX (PRIMARY) WHERE id = 20 FOR SHARE;
60 --dec $k
61 }
62
63
64 # Using connection N + 1, acquire exclusive lock over the same record
65 # using `SELECT ... FOR UPDATE`. This will cause the rt_wait to wait on a lock.
66 --connect (rt_wait,127.0.0.1,root,,test,,)
67 BEGIN;
68 # make it heavy by inserting a lot of records
69 INSERT INTO t VALUES (60), (70), (80), (90), (100), (110), (120);
70 --send SELECT * FROM t FORCE INDEX (PRIMARY) WHERE id = 20 FOR UPDATE
71
72
73 # Verify that the first reason for wait was reported
74 --connection default
75 SET DEBUG_SYNC = 'now WAIT_FOR reached_report_lock_collision';
76 SET DEBUG_SYNC = 'now SIGNAL continue_report_lock_collision';
77
78 # create N small deadlocks cycles between rt_$k and and rt_wait
79 while ($k != $n_blockers)
80 {
81 --inc $k
82 --connection rt_$k
83 --send SELECT * FROM t FORCE INDEX (PRIMARY) WHERE id=100 FOR SHARE
84 }
85
86 --connection default
87
88 --let expected_reports = `SELECT $n_blockers * 2 - 1`
89 while ($expected_reports != 0)
90 {
91 --echo # expect $expected_reports more edges to be reported
92 SET DEBUG_SYNC = 'now WAIT_FOR reached_report_lock_collision';
93 SET DEBUG_SYNC = 'now SIGNAL continue_report_lock_collision';
94 --dec $expected_reports
95 }
96 while ($k != 0)
97 {
98 --echo # excpecting that connection $k was rolled back as a deadlock victim
99 --connection rt_$k
100 --error ER_LOCK_DEADLOCK
101 --reap
102 --disconnect rt_$k
103 --dec $k
104 }
105
106 # Clean up
107 --connection rt_wait
108 --reap
109 ROLLBACK;
110 --connection default
111 --disconnect rt_wait
112 --source include/remove_debug_point.inc
113 DROP TABLE t;