Debian Codegraph @

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

raw
1 --echo #
2 --echo # Bug #23755664 DEADLOCK WITH 3 CONCURRENT DELETES BY UNIQUE KEY
3 --echo #
4
5 --source include/have_innodb.inc
6 --source include/have_debug.inc
7 --source include/have_debug_sync.inc
8 --source include/count_sessions.inc
9
10 --connection default
11 # There are various scenarious in which a transaction already holds "half"
12 # of a record lock (for example, a lock on the record but not on the gap)
13 # and wishes to "upgrade it" to a full lock (i.e. on both gap and record).
14 # This is often a cause for a deadlock, if there is another transaction
15 # which is already waiting for the lock being blocked by us:
16 # 1. our granted lock for one half
17 # 2. her waiting lock for the same half
18 # 3. our waiting lock for the whole
19
20 #
21 # SCENARIO 1
22 #
23 # In this scenario, three different threads try to delete the same row,
24 # identified by a secondary index key.
25 # This kind of operation (besides LOCK_IX on a table) requires
26 # an LOCK_REC_NOT_GAP|LOCK_REC|LOCK_X lock on a secondary index
27 # 1. `deleter` is the first to get the required lock
28 # 2. `holder` enqueues a waiting lock
29 # 3. `waiter` enqueues right after `holder`
30 # 4. `deleter` commits, releasing the lock, and granting it to `holder`
31 # 5. `holder` now observes that the row was deleted, so it needs to
32 # "seal the gap", by obtaining a LOCK_X|LOCK_REC, but..
33 # 6. this causes a deadlock between `holder` and `waiter`
34 #
35 # This scenario does not fail if MDEV-10962 is not fixed because of MDEV-30225
36 # fix, as the 'holder' does not "seal the gap" after 'deleter' was committed,
37 # because it was initially sealed, as row_search_mvcc() requests next-key lock
38 # after MDEV-30225 fix in the case when it requested not-gap lock before the
39 # fix.
40 #
41 # But let the scenario be in the tests, because it can fail if MDEV-30225
42 # related code is changed
43
44 CREATE TABLE `t`(
45 `id` INT,
46 `a` INT DEFAULT NULL,
47 PRIMARY KEY(`id`),
48 UNIQUE KEY `u`(`a`)
49 ) ENGINE=InnoDB;
50
51 INSERT INTO t (`id`,`a`) VALUES
52 (1,1),
53 (2,9999),
54 (3,10000);
55
56 --connect(deleter,localhost,root,,)
57 --connect(holder,localhost,root,,)
58 --connect(waiter,localhost,root,,)
59
60
61 --connection deleter
62 SET DEBUG_SYNC =
63 'lock_sec_rec_read_check_and_lock_has_locked
64 SIGNAL deleter_has_locked
65 WAIT_FOR waiter_has_locked';
66 --send DELETE FROM t WHERE a = 9999
67
68 --connection holder
69 SET DEBUG_SYNC=
70 'now WAIT_FOR deleter_has_locked';
71 SET DEBUG_SYNC=
72 'lock_sec_rec_read_check_and_lock_has_locked SIGNAL holder_has_locked';
73 --send DELETE FROM t WHERE a = 9999
74
75 --connection waiter
76 SET DEBUG_SYNC=
77 'now WAIT_FOR holder_has_locked';
78 SET DEBUG_SYNC=
79 'lock_sec_rec_read_check_and_lock_has_locked SIGNAL waiter_has_locked';
80 --send DELETE FROM t WHERE a = 9999
81
82 --connection deleter
83 --reap
84
85 --connection holder
86 --reap
87
88 --connection waiter
89 --reap
90
91 --connection default
92
93 --disconnect deleter
94 --disconnect holder
95 --disconnect waiter
96
97 DROP TABLE `t`;
98 SET DEBUG_SYNC='reset';
99
100 # SCENARIO 2
101 #
102 # Here, we form a situation in which con1 has LOCK_REC_NOT_GAP on rows 1 and 2
103 # con2 waits for lock on row 1, and then con1 wants to upgrade the lock on row 1,
104 # which might cause a deadlock, unless con1 properly notices that even though the
105 # lock on row 1 can not be upgraded, a separate LOCK_GAP can be obtaied easily.
106
107 CREATE TABLE `t`(
108 `id` INT NOT NULL PRIMARY KEY
109 ) ENGINE=InnoDB;
110
111 INSERT INTO t (`id`) VALUES (1), (2);
112
113 --connect(holder,localhost,root,,)
114 --connect(waiter,localhost,root,,)
115
116 --connection holder
117 BEGIN;
118 SELECT id FROM t WHERE id=1 FOR UPDATE;
119 SELECT id FROM t WHERE id=2 FOR UPDATE;
120
121 --connection waiter
122 SET DEBUG_SYNC=
123 'lock_wait_before_suspend SIGNAL waiter_will_wait';
124 --send SELECT id FROM t WHERE id = 1 FOR UPDATE
125
126 --connection holder
127 SET DEBUG_SYNC=
128 'now WAIT_FOR waiter_will_wait';
129 SELECT * FROM t FOR UPDATE;
130 COMMIT;
131
132 --connection waiter
133 --reap
134
135 --connection default
136
137 --disconnect holder
138 --disconnect waiter
139
140 DROP TABLE `t`;
141 SET DEBUG_SYNC='reset';
142
143 --source include/wait_until_count_sessions.inc