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
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
|
--source include/have_debug_sync.inc
--source include/count_sessions.inc
--echo #################################################################
--echo # #
--echo # Bug # 27898384: InnoDB: Assertion failure: lock0lock.cc:4226: #
--echo # !(lock2->trx == trx && lock_get_mode(lock2) == lock_mode) #
--echo # #
--echo #################################################################
--echo ##############
--echo # SCENARIO 1 #
--echo ##############
# To reproduce the problem we need a transaction in READ COMMITED mode
# to scan a range of rows, some of which do not match a criteria, which
# causes mysql to ask InnoDB to release a lock previously granted for
# such row. Then lock_rec_unlock is used to remove such lock, and it will
# fail if it is not sure which lock should be removed.
# Such a situation might happen if there are two different locks held for
# the same row, same trx, and same (LOCK_X) mode, but they differ in type.
# For example one is an LOCK_INSERT_INTENTION|LOCK_GAP|LOCK_REC|LOCK_X
# obtained in a previous query, and the another is a
# LOCK_REC_NOT_GAP|LOCK_REC|LOCK_X granted during the current scan.
# The scenario which leads to such situation is:
# 1. con1 attempts to DELETE a non-existing row with id=123.
# 2. con2 starts INSERT with id=123
# but has to wait for con1, thus enqueues LOCK_INSERT_INTENTION
# 3. con1 performs ROLLBACK
# and thus con2 can continue, and has a granted II lock
# 4. con2 performs SELECT ... FOR UPDATE with a query range containing
# id=123, and a condition which does not match the row with id=123.
# Which means it has to unlock the row with id=123.
CREATE TABLE t1 (
id INT PRIMARY KEY,
val INT
) Engine=InnoDB;
INSERT INTO t1 (id, val) VALUES (100,100),(200,200);
--connect (con1, localhost, root,,)
--connect (con2, localhost, root,,)
--connection con1
BEGIN;
DELETE FROM t1 WHERE id=123;
--connection con2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con1_will_wait';
--send INSERT INTO t1 (id, val) VALUES (123,123);
--connection con1
SET DEBUG_SYNC = 'now WAIT_FOR con1_will_wait';
ROLLBACK;
--connection con2
--reap
SELECT * FROM t1 WHERE 100<=id AND id<=200 AND val=100 FOR UPDATE;
ROLLBACK;
# Clean up:
--connection default
--disconnect con1
--disconnect con2
DROP TABLE t1;
SET DEBUG_SYNC = 'RESET';
--echo ##############
--echo # SCENARIO 2 #
--echo ##############
CREATE TABLE t1 (
`a` int(11) NOT NULL auto_increment,
`b` int(11) default NULL,
`c` int default 7,
PRIMARY KEY (`a`),
UNIQUE KEY `b` (`b`)
) ENGINE=innodb;
INSERT INTO t1 (a,b,c) VALUES (1,1,1), (2,2,2), (3,3,3), (4,4,4);
--connect (con1, localhost, root,,)
--connect (con2, localhost, root,,)
--connection con1
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
# next UPDATE will fail because of duplicate key b=2
# also it will create an S-lock on index entry b=2 to preserve this
--error ER_DUP_ENTRY
UPDATE t1 SET b=2 WHERE a=1;
# next SELECT ... FOR SHARE is cleverly constructed to:
# 1. perform a scan over index for column b
# 2. in particular it visits b=2
# 3. it temporarily S-locks visited entries
# 4. it does not need to create an S-lock for b=2, because it already
# has it
# 5. it does however S-lock the row (2,2,2) in primary key
# and sets new_rec_locks=2
# 6. only at the mysql layer we observe that c=1 does not hold,
# so we discard this row
# 7. we see that new_rec_locks=2 so we think we have to unlock both:
# secondary and primary entry
# 8. thus we remove the S-lock in index on column b!
SELECT a FROM t1 WHERE 2<=b AND b<=3 AND c=1 FOR SHARE;
--connection con2
BEGIN;
# this DELETE should block, because transaction from con1 believes that
# there is a row with b=2, which is the reason UPDATE has failed.
# However, as you can see this DELETE will be performed without
# problems, because con1 no longer holds an S-lock on b=2!
SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con2_will_wait';
--send DELETE FROM t1 WHERE b=2
--connection con1
# If the code has a bug, then con2 will never enter a wait, and the next
# line will fail with a timeout.
SET DEBUG_SYNC = 'now WAIT_FOR con2_will_wait';
COMMIT;
--connection con2
--reap
COMMIT;
# Clean up:
--connection default
--disconnect con1
--disconnect con2
DROP TABLE t1;
SET DEBUG_SYNC = 'RESET';
--echo ########################
--echo # #
--echo # End of Bug #27898384 #
--echo # #
--echo ########################
--echo # Bug #31046834 ASSERTION FAILURE: TRX0TRX.CC:2663:TRX_ALLOWED_TWO_LATCHES THREAD 14024410520550
--echo # Bug #31047326 ASSERTION FAILURE: TRX0TRX.CC:2663:TRX_ALLOWED_2_LATCHES THREAD 139840853837568
CREATE TABLE t1 (
id INT PRIMARY KEY,
val INT
) Engine=InnoDB;
INSERT INTO t1 (id, val) VALUES (1,1);
--connect (con1, localhost, root,,)
--connect (con2, localhost, root,,)
--connection con1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SET DEBUG_SYNC = 'after_lock_clust_rec_read_check_and_lock SIGNAL con1_created_lock WAIT_FOR con2_will_wait';
--send SELECT * FROM t1 WHERE val=13 FOR UPDATE
--connection con2
SET DEBUG_SYNC = 'now WAIT_FOR con1_created_lock';
BEGIN;
SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con2_will_wait';
--send SELECT * FROM t1 WHERE id=1 FOR UPDATE
--connection con1
--reap
COMMIT;
--connection con2
--reap
COMMIT;
# Clean up:
--connection default
--disconnect con1
--disconnect con2
DROP TABLE t1;
SET DEBUG_SYNC = 'RESET';
--source include/wait_until_count_sessions.inc
|
