Debian Codegraph @

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

raw
1 --source include/have_innodb.inc
2 --source include/have_partition.inc
3
4 --disable_query_log
5 call mtr.add_suppression("InnoDB: Transaction was aborted due to ");
6 --enable_query_log
7
8 #
9 # Check and select innodb lock type
10 #
11
12 set global innodb_table_locks=1;
13
14 select @@innodb_table_locks;
15
16 #
17 # Testing of explicit table locks with enforced table locks
18 #
19
20 set @@innodb_table_locks=1;
21
22 connect (con1,localhost,root,,);
23 create table t1 (id integer, x integer) engine=INNODB;
24 insert into t1 values(0, 0);
25 set autocommit=0;
26 SELECT * from t1 where id = 0 FOR UPDATE;
27
28 connect (con2,localhost,root,,);
29 set autocommit=0;
30
31 # The following statement should hang because con1 is locking the page
32 --send
33 lock table t1 write;
34 --sleep 2
35
36 connection con1;
37 update t1 set x=1 where id = 0;
38 select * from t1;
39 commit;
40
41 connection con2;
42 reap;
43 update t1 set x=2 where id = 0;
44 commit;
45 unlock tables;
46
47 connection con1;
48 select * from t1;
49 commit;
50
51 drop table t1;
52
53 --echo #
54 --echo # Old lock method (where LOCK TABLE was ignored by InnoDB) no longer
55 --echo # works when LOCK TABLE ... WRITE is used due to fix for bugs #46272
56 --echo # "MySQL 5.4.4, new MDL: unnecessary and bug #37346 "innodb does not
57 --echo # detect deadlock between update and alter table".
58 --echo # After WL#6671 "Improve scalability by not using thr_lock.c locks
59 --echo # for InnoDB tables" was implemented it no longer works for LOCK TABLES
60 --echo # ,,, READ as well.
61 --echo # LOCK TABLES locks are now completely handled by MDL subsystem.
62 --echo #
63
64 set @@innodb_table_locks=0;
65
66 create table t1 (id integer primary key, x integer) engine=INNODB;
67 insert into t1 values(0, 0),(1,1),(2,2);
68 commit;
69 SELECT * from t1 where id = 0 FOR UPDATE;
70
71 connection con2;
72 set autocommit=0;
73 set @@innodb_table_locks=0;
74
75 --echo # The following statement should block because SQL-level lock
76 --echo # is taken on t1 which will wait until concurrent transaction
77 --echo # is commited.
78 --echo # Sending:
79 --send lock table t1 write;
80
81 connection con1;
82 --echo # Wait until LOCK TABLE is blocked on SQL-level lock.
83 let $wait_condition=
84 select count(*) = 1 from information_schema.processlist
85 where state = "Waiting for table metadata lock" and
86 info = "lock table t1 write";
87 --source include/wait_condition.inc
88 --echo # We should be able to do UPDATEs and SELECTs within transaction.
89 update t1 set x=1 where id = 0;
90 select * from t1;
91 --echo # Unblock LOCK TABLE.
92 commit;
93
94 connection con2;
95 --echo # Reap LOCK TABLE.
96 --reap
97 unlock tables;
98
99 connection con1;
100
101 select * from t1 where id = 0 for update;
102
103 connection con2;
104 --echo # The following statement should block because SQL-level lock
105 --echo # is taken on t1 which will wait until concurrent transaction
106 --echo # is commited.
107 --echo # Sending:
108 --send lock table t1 read;
109
110 connection con1;
111 --echo # Wait until LOCK TABLE is blocked on SQL-level lock.
112 let $wait_condition=
113 select count(*) = 1 from information_schema.processlist
114 where state = "Waiting for table metadata lock" and
115 info = "lock table t1 read";
116 --source include/wait_condition.inc
117 --echo # We should be able to do UPDATEs and SELECTs within transaction.
118 update t1 set x=2 where id = 0;
119 select * from t1;
120 --echo # Unblock LOCK TABLE.
121 commit;
122
123 connection con2;
124 --echo # Reap LOCK TABLE.
125 --reap
126 unlock tables;
127
128 connection default;
129
130 drop table t1;
131
132 # End of 4.1 tests
133
134 --echo #
135 --echo #Bug#12842206 INNODB LOCKING REGRESSION FOR INSERT IGNORE
136 --echo #fixed by re-fixing Bug#7975
137 --echo #aka Bug#11759688 52020: InnoDB can still deadlock on just INSERT...
138 --echo #
139
140 connection default;
141 CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB;
142 INSERT INTO t1 VALUES(3,1);
143
144 BEGIN;
145 # this used to wrongly acquire an X lock; now it takes an S lock
146 INSERT IGNORE INTO t1 VALUES(3,14);
147
148 connection con1;
149 BEGIN;
150 # this used to wrongly acquire an X lock; now it takes an S lock
151 INSERT IGNORE INTO t1 VALUES(3,23);
152 --send
153 SELECT * FROM t1 FOR UPDATE;
154
155 connection con2;
156 # Check that the above SELECT is blocked
157 let $wait_condition=
158 select count(*) = 1 from information_schema.processlist
159 where state = 'Sending data' and
160 info = 'SELECT * FROM t1 FOR UPDATE';
161 --source include/wait_condition.inc
162 disconnect con2;
163
164 connection default;
165 COMMIT;
166 connection con1;
167 reap;
168 COMMIT;
169 connection default;
170 DROP TABLE t1;
171
172 --echo #
173 --echo # MDEV-11080 InnoDB: Failing assertion:
174 --echo # table->n_waiting_or_granted_auto_inc_locks > 0
175 --echo #
176
177 CREATE TABLE t1 (pk INTEGER AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
178 INSERT INTO t1 VALUES (NULL),(NULL);
179 CREATE TABLE t2 LIKE t1;
180
181 BEGIN;
182
183 connection con1;
184 BEGIN;
185 DELETE FROM t2;
186 connection default;
187 --send LOCK TABLE t2 READ;
188 connection con1;
189 SET innodb_lock_wait_timeout= 1, lock_wait_timeout= 2;
190 --error 0,ER_LOCK_WAIT_TIMEOUT
191 INSERT INTO t2 SELECT * FROM t1;
192 COMMIT;
193
194 connection default;
195 reap;
196 UNLOCK TABLES;
197 DROP TABLE t1, t2;
198
199 --echo #
200 --echo # MDEV-16709 InnoDB: Error: trx already had an AUTO-INC lock
201 --echo #
202
203 CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB
204 PARTITION BY key (pk) PARTITIONS 2;
205 # MDEV-515 takes X-lock on the table for the first insert.
206 # So concurrent insert won't happen on the table
207 INSERT INTO t1 VALUES(100);
208 INSERT INTO t1 VALUES(101);
209
210 CREATE TABLE t2 (a INT) ENGINE=InnoDB;
211 INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6);
212
213 CREATE TABLE t3 (b INT) ENGINE=InnoDB;
214 INSERT INTO t3 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9);
215
216 connection con1;
217 send INSERT t1 SELECT NULL FROM t2;
218
219 connection default;
220 --error 0,ER_DUP_ENTRY,ER_LOCK_DEADLOCK
221 INSERT t1 SELECT NULL FROM t3;
222
223 connection con1;
224 --error 0,ER_DUP_ENTRY,ER_LOCK_DEADLOCK
225 reap;
226 disconnect con1;
227 connection default;
228 DROP TABLE t1, t2, t3;