Debian Codegraph @

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

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