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