Debian Codegraph @

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

raw
1 --source include/have_debug_sync.inc
2
3 --echo #
4 --echo # Bug #29882690 UNDETECTED DEADLOCK WITH GAP AND INSERT INTENTION LOCKS
5 --echo # IS POSSIBLE
6 --echo #
7
8 # Save the original settings, to be restored at the end of test
9 SET @innodb_lock_wait_timeout_saved = @@global.innodb_lock_wait_timeout;
10
11 # Make sure that transactions will not finish prematurely
12 SET @@global.innodb_lock_wait_timeout = 20;
13
14 --source ../include/prepare_show_locks.inc
15
16 # Scenario for a deadlock which can be undetected if we move granted gap lock
17 # in front of waiting insert intention lock.
18 # Initially we have (1,"a"),(3,"c"),(4,"d")
19 # 1. con1 obtains a GRANTED LOCK_GAP on gap before row id=3
20 # 2. con2 obtains a GRANTED LOCK_X on row id=4
21 # (exact type, and row is not very important)
22 # 3. con2 enqueues a waiting LOCK_INSERT_INTENTION into the gap before id=3
23 # (and is blocked by con1)
24 # 4. con3 obtains a GRANTED LOCK_GAP on gap before row id=3
25 # (gap locks don't wait for II)
26 # 5. con3 enqueues a waiting lock on same row as in step 2.
27 # (we have: con3 `waits-for` con2 `waits-for` con1 - no deadlock so far)
28 # 6. con1 resizes the row with id=3 ("c"->"cccccccccccccccc") causing locks
29 # to be moved from one `heap_no` to another
30 # 7. it depends on implementation of move opeartion, if con3's LOCK_GAP lands in
31 # front of con2's waiting II or behind
32 # 8. con1 commits
33 # 9. if con3's LOCK_GAP is behind con2's II, then
34 # con2's II becomes granted.
35 # con2 re-attempts to insert.
36 # con2 notices the con3's gap lock.
37 # con2 decides to wait.
38 # This creates a deadlock cycle, con2 has to wait-for con3, but con3 is
39 # already waiting for con2.
40 # if we perform deadlock detection from requesting thread then
41 # con2 notices the deadlock immediately,
42 # otherwise, if we perform deadlock detection from dedicated thread then
43 # we notice the deadlock after a while.
44 # In either case, deadlock is resolved.
45 # else:
46 # We have a deadlock cycle, as con2's II lock is behind con3's GAP lock,
47 # so con2 waits-for con3, and con3 waits for con2 already, but this cycle
48 # can go undetected unless we have a dedicated deadlock detector thread.
49 # When con1 lock is released, con2 sees a con3's GAP lock in front of it,
50 # thus con2's II is not granted, so it continues to be waiting.
51 # Both con2 and con3 wait (and finally someone timesout).
52
53
54
55
56 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, val VARCHAR(200));
57 INSERT INTO t1 (id,val) VALUES (1, "a"), (3, "c"), (4, "d");
58
59 # These SELECTS are just to identify transactions easily as con1,con2,con3 in
60 # show_locks() by checking which row is locked by which.
61 --connect (con1, localhost, root,,)
62 CALL register_connection("con1");
63 BEGIN;
64
65 --connect (con2, localhost, root,,)
66 CALL register_connection("con2");
67 BEGIN;
68
69 --connect (con3, localhost, root,,)
70 CALL register_connection("con3");
71 BEGIN;
72
73 --echo # 1. con1 obtains a GRANTED LOCK_GAP on gap before row id=3
74 --connection con1
75 SELECT * FROM t1 WHERE id=2 FOR UPDATE;
76
77 --connection default
78 CALL show_locks();
79
80 --echo # 2. con2 obtains a GRANTED LOCK_X on row id=4
81 --connection con2
82 SELECT * FROM t1 WHERE id=4 FOR UPDATE;
83
84 --connection default
85 CALL show_locks();
86
87 --echo # 3. con2 enqueues a waiting LOCK_INSERT_INTENTION into the gap
88 --echo # before id=3
89 --connection con2
90 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con2_will_wait';
91 --send INSERT INTO t1 (id, val) VALUES (2, "b")
92
93 --connection default
94 SET DEBUG_SYNC = 'now WAIT_FOR con2_will_wait';
95 CALL show_locks();
96
97 --echo # 4. con3 obtains a GRANTED LOCK_GAP on gap before row id=3
98 --connection con3
99 SELECT * FROM t1 WHERE id=2 FOR UPDATE;
100
101 --connection default
102 CALL show_locks();
103
104 --echo # 5. con3 enqueues a waiting lock on same row as in step 2.
105 --connection con3
106 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con3_will_wait';
107 --send SELECT * FROM t1 WHERE id=4 FOR UPDATE
108
109 --connection default
110 SET DEBUG_SYNC = 'now WAIT_FOR con3_will_wait';
111 CALL show_locks();
112
113 --echo # 6. con1 resizes the row with id=3 ("c"->"cccccccccccccccc") causing
114 --echo # locks to be moved
115 --connection con1
116 UPDATE t1 SET val="cccccccccccccccc" WHERE id=3;
117
118 --echo # 7. it depends on implementation of con3's LOCK_GAP lands is in front
119 --echo # of con2's waiting II or not
120 --echo # 8. con1 commits
121 COMMIT;
122
123 # At this point there should be no waiters at all, as the deadlock should be
124 # noticed and resolved - either when re-acquiring II lock, on in dedicated
125 # deadlock detector thread.
126 # Yet on a faulty implementation, we will see an unresolved deadlock:
127 # two threads, each with a lock WAITING for another transaction.
128 # Eventually transactions will timeout, and we will see ER_LOCK_WAIT_TIMEOUT
129 # as opposed to expected: success, or ER_LOCK_DEADLOCK.
130
131 --connection con2
132 --reap
133 ROLLBACK;
134
135 --connection con3
136 --error ER_LOCK_DEADLOCK
137 --reap
138 ROLLBACK;
139
140
141
142 --connection default
143 --disconnect con1
144 --disconnect con2
145 --disconnect con3
146
147 DROP TABLE t1;
148
149
150
151 --source ../include/cleanup_show_locks.inc
152
153 # Restore saved settings
154 SET @@global.innodb_lock_wait_timeout = @innodb_lock_wait_timeout_saved ;