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