Debian Codegraph @

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

raw
1 --source include/have_debug_sync.inc
2 --source include/count_sessions.inc
3
4 --echo #################################################################
5 --echo # #
6 --echo # Bug # 27898384: InnoDB: Assertion failure: lock0lock.cc:4226: #
7 --echo # !(lock2->trx == trx && lock_get_mode(lock2) == lock_mode) #
8 --echo # #
9 --echo #################################################################
10
11 --echo ##############
12 --echo # SCENARIO 1 #
13 --echo ##############
14 # To reproduce the problem we need a transaction in READ COMMITED mode
15 # to scan a range of rows, some of which do not match a criteria, which
16 # causes mysql to ask InnoDB to release a lock previously granted for
17 # such row. Then lock_rec_unlock is used to remove such lock, and it will
18 # fail if it is not sure which lock should be removed.
19 # Such a situation might happen if there are two different locks held for
20 # the same row, same trx, and same (LOCK_X) mode, but they differ in type.
21 # For example one is an LOCK_INSERT_INTENTION|LOCK_GAP|LOCK_REC|LOCK_X
22 # obtained in a previous query, and the another is a
23 # LOCK_REC_NOT_GAP|LOCK_REC|LOCK_X granted during the current scan.
24 # The scenario which leads to such situation is:
25 # 1. con1 attempts to DELETE a non-existing row with id=123.
26 # 2. con2 starts INSERT with id=123
27 # but has to wait for con1, thus enqueues LOCK_INSERT_INTENTION
28 # 3. con1 performs ROLLBACK
29 # and thus con2 can continue, and has a granted II lock
30 # 4. con2 performs SELECT ... FOR UPDATE with a query range containing
31 # id=123, and a condition which does not match the row with id=123.
32 # Which means it has to unlock the row with id=123.
33
34 CREATE TABLE t1 (
35 id INT PRIMARY KEY,
36 val INT
37 ) Engine=InnoDB;
38 INSERT INTO t1 (id, val) VALUES (100,100),(200,200);
39
40 --connect (con1, localhost, root,,)
41 --connect (con2, localhost, root,,)
42
43 --connection con1
44 BEGIN;
45 DELETE FROM t1 WHERE id=123;
46
47 --connection con2
48 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
49 BEGIN;
50 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con1_will_wait';
51 --send INSERT INTO t1 (id, val) VALUES (123,123);
52
53 --connection con1
54 SET DEBUG_SYNC = 'now WAIT_FOR con1_will_wait';
55 ROLLBACK;
56
57 --connection con2
58 --reap
59 SELECT * FROM t1 WHERE 100<=id AND id<=200 AND val=100 FOR UPDATE;
60 ROLLBACK;
61
62 # Clean up:
63
64 --connection default
65 --disconnect con1
66 --disconnect con2
67 DROP TABLE t1;
68 SET DEBUG_SYNC = 'RESET';
69
70
71 --echo ##############
72 --echo # SCENARIO 2 #
73 --echo ##############
74
75 CREATE TABLE t1 (
76 `a` int(11) NOT NULL auto_increment,
77 `b` int(11) default NULL,
78 `c` int default 7,
79 PRIMARY KEY (`a`),
80 UNIQUE KEY `b` (`b`)
81 ) ENGINE=innodb;
82 INSERT INTO t1 (a,b,c) VALUES (1,1,1), (2,2,2), (3,3,3), (4,4,4);
83
84 --connect (con1, localhost, root,,)
85 --connect (con2, localhost, root,,)
86
87 --connection con1
88 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
89 BEGIN;
90 # next UPDATE will fail because of duplicate key b=2
91 # also it will create an S-lock on index entry b=2 to preserve this
92 --error ER_DUP_ENTRY
93 UPDATE t1 SET b=2 WHERE a=1;
94
95 # next SELECT ... FOR SHARE is cleverly constructed to:
96 # 1. perform a scan over index for column b
97 # 2. in particular it visits b=2
98 # 3. it temporarily S-locks visited entries
99 # 4. it does not need to create an S-lock for b=2, because it already
100 # has it
101 # 5. it does however S-lock the row (2,2,2) in primary key
102 # and sets new_rec_locks=2
103 # 6. only at the mysql layer we observe that c=1 does not hold,
104 # so we discard this row
105 # 7. we see that new_rec_locks=2 so we think we have to unlock both:
106 # secondary and primary entry
107 # 8. thus we remove the S-lock in index on column b!
108 SELECT a FROM t1 WHERE 2<=b AND b<=3 AND c=1 FOR SHARE;
109
110 --connection con2
111 BEGIN;
112 # this DELETE should block, because transaction from con1 believes that
113 # there is a row with b=2, which is the reason UPDATE has failed.
114 # However, as you can see this DELETE will be performed without
115 # problems, because con1 no longer holds an S-lock on b=2!
116 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con2_will_wait';
117 --send DELETE FROM t1 WHERE b=2
118
119 --connection con1
120 # If the code has a bug, then con2 will never enter a wait, and the next
121 # line will fail with a timeout.
122 SET DEBUG_SYNC = 'now WAIT_FOR con2_will_wait';
123 COMMIT;
124
125 --connection con2
126 --reap
127 COMMIT;
128
129 # Clean up:
130
131 --connection default
132 --disconnect con1
133 --disconnect con2
134
135 DROP TABLE t1;
136 SET DEBUG_SYNC = 'RESET';
137
138 --echo ########################
139 --echo # #
140 --echo # End of Bug #27898384 #
141 --echo # #
142 --echo ########################
143
144
145 --echo # Bug #31046834 ASSERTION FAILURE: TRX0TRX.CC:2663:TRX_ALLOWED_TWO_LATCHES THREAD 14024410520550
146 --echo # Bug #31047326 ASSERTION FAILURE: TRX0TRX.CC:2663:TRX_ALLOWED_2_LATCHES THREAD 139840853837568
147
148 CREATE TABLE t1 (
149 id INT PRIMARY KEY,
150 val INT
151 ) Engine=InnoDB;
152 INSERT INTO t1 (id, val) VALUES (1,1);
153
154 --connect (con1, localhost, root,,)
155 --connect (con2, localhost, root,,)
156
157 --connection con1
158 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
159 BEGIN;
160 SET DEBUG_SYNC = 'after_lock_clust_rec_read_check_and_lock SIGNAL con1_created_lock WAIT_FOR con2_will_wait';
161 --send SELECT * FROM t1 WHERE val=13 FOR UPDATE
162
163 --connection con2
164 SET DEBUG_SYNC = 'now WAIT_FOR con1_created_lock';
165 BEGIN;
166 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL con2_will_wait';
167 --send SELECT * FROM t1 WHERE id=1 FOR UPDATE
168
169 --connection con1
170 --reap
171 COMMIT;
172
173 --connection con2
174 --reap
175 COMMIT;
176
177
178
179 # Clean up:
180 --connection default
181 --disconnect con1
182 --disconnect con2
183 DROP TABLE t1;
184 SET DEBUG_SYNC = 'RESET';
185
186 --source include/wait_until_count_sessions.inc