2
# Test how do we handle locking in various cases when
3
# we read data from InnoDB tables.
5
# In fact by performing this test we check two things:
6
# 1) That SQL-layer correctly determine type of thr_lock.c
7
# lock to be acquired/passed to InnoDB engine.
8
# 2) That InnoDB engine correctly interprets this lock
9
# type and takes necessary row locks or does not
10
# take them if they are not necessary.
12
# This test makes sense only in REPEATABLE-READ mode as
13
# in SERIALIZABLE mode all statements that read data take
14
# shared lock on them to enforce its semantics.
15
select @@session.tx_isolation;
16
@@session.tx_isolation
18
# Prepare playground by creating tables, views,
19
# routines and triggers used in tests.
20
drop table if exists t0, t1, t2, t3, t4, t5, te;
21
drop view if exists v1, v2;
22
drop procedure if exists p1;
23
drop procedure if exists p2;
24
drop function if exists f1;
25
drop function if exists f2;
26
drop function if exists f3;
27
drop function if exists f4;
28
drop function if exists f5;
29
drop function if exists f6;
30
drop function if exists f7;
31
drop function if exists f8;
32
drop function if exists f9;
33
drop function if exists f10;
34
drop function if exists f11;
35
drop function if exists f12;
36
drop function if exists f13;
37
drop function if exists f14;
38
drop function if exists f15;
39
create table t1 (i int primary key) engine=innodb;
40
insert into t1 values (1), (2), (3), (4), (5);
41
create table t2 (j int primary key) engine=innodb;
42
insert into t2 values (1), (2), (3), (4), (5);
43
create table t3 (k int primary key) engine=innodb;
44
insert into t3 values (1), (2), (3);
45
create table t4 (l int primary key) engine=innodb;
46
insert into t4 values (1);
47
create table t5 (l int primary key) engine=innodb;
48
insert into t5 values (1);
49
create table te(e int primary key);
50
insert into te values (1);
51
create view v1 as select i from t1;
52
create view v2 as select j from t2 where j in (select i from t1);
53
create procedure p1(k int) insert into t2 values (k);
54
create function f1() returns int
57
select i from t1 where i = 1 into j;
60
create function f2() returns int
63
select i from t1 where i = 1 into k;
64
insert into t2 values (k + 5);
67
create function f3() returns int
69
return (select i from t1 where i = 3);
71
create function f4() returns int
73
if (select i from t1 where i = 3) then
79
create function f5() returns int
81
insert into t2 values ((select i from t1 where i = 1) + 5);
84
create function f6() returns int
87
select i from v1 where i = 1 into k;
90
create function f7() returns int
93
select j from v2 where j = 1 into k;
96
create function f8() returns int
99
select i from v1 where i = 1 into k;
100
insert into t2 values (k+5);
103
create function f9() returns int
105
update v2 set j=j+10 where j=1;
108
create function f10() returns int
112
create function f11() returns int
116
insert into t2 values (k+5);
119
create function f12(p int) returns int
121
insert into t2 values (p);
124
create function f13(p int) returns int
128
create procedure p2(inout p int)
130
select i from t1 where i = 1 into p;
132
create function f14() returns int
136
insert into t2 values (k+5);
139
create function f15() returns int
145
create trigger t4_bi before insert on t4 for each row
148
select i from t1 where i=1 into k;
151
create trigger t4_bu before update on t4 for each row
153
if (select i from t1 where i=1) then
157
# Trigger below uses insertion of duplicate key in 'te'
158
# table as a way to abort delete operation.
159
create trigger t4_bd before delete on t4 for each row
161
if !(select i from v1 where i=1) then
162
insert into te values (1);
165
create trigger t5_bi before insert on t5 for each row
169
create trigger t5_bu before update on t5 for each row
176
# Set common variables to be used by scripts called below.
179
# 1. Statements that read tables and do not use subqueries.
182
# 1.1 Simple SELECT statement.
184
# No locks are necessary as this statement won't be written
185
# to the binary log and InnoDB supports snapshots.
186
Success: 'select * from t1' doesn't take row locks on 't1'.
188
# 1.2 Multi-UPDATE statement.
190
# Has to take shared locks on rows in the table being read as this
191
# statement will be written to the binary log and therefore should
192
# be serialized with concurrent statements.
193
Success: 'update t2, t1 set j= j - 1 where i = j' takes shared row locks on 't1'.
195
# 1.3 Multi-DELETE statement.
197
# The above is true for this statement as well.
198
Success: 'delete t2 from t1, t2 where i = j' takes shared row locks on 't1'.
200
# 1.4 DESCRIBE statement.
202
# This statement does not really read data from the
203
# target table and thus does not take any lock on it.
204
# We check this for completeness of coverage.
205
Success: 'describe t1' doesn't take row locks on 't1'.
207
# 1.5 SHOW statements.
209
# The above is true for SHOW statements as well.
210
Success: 'show create table t1' doesn't take row locks on 't1'.
211
Success: 'show keys from t1' doesn't take row locks on 't1'.
213
# 2. Statements which read tables through subqueries.
216
# 2.1 CALL with a subquery.
218
# A strong lock is not necessary as this statement is not
219
# written to the binary log as a whole (it is written
220
# statement-by-statement) and thanks to MVCC we can always get
221
# versions of rows prior to the update that has locked them.
222
# But in practice InnoDB does locking reads for all statements
223
# other than SELECT (unless it is a READ-COMITTED mode or
224
# innodb_locks_unsafe_for_binlog is ON).
225
Success: 'call p1((select i + 5 from t1 where i = 1))' takes shared row locks on 't1'.
227
# 2.2 CREATE TABLE with a subquery.
229
# Has to take shared locks on rows in the table being read as
230
# this statement is written to the binary log and therefore
231
# should be serialized with concurrent statements.
232
Success: 'create table t0 engine=innodb select * from t1' takes shared row locks on 't1'.
234
Success: 'create table t0 engine=innodb select j from t2 where j in (select i from t1)' takes shared row locks on 't1'.
237
# 2.3 DELETE with a subquery.
239
# The above is true for this statement as well.
240
Success: 'delete from t2 where j in (select i from t1)' takes shared row locks on 't1'.
242
# 2.4 MULTI-DELETE with a subquery.
244
# Same is true for this statement as well.
245
Success: 'delete t2 from t3, t2 where k = j and j in (select i from t1)' takes shared row locks on 't1'.
247
# 2.5 DO with a subquery.
249
# In theory should not take row locks as it is not logged.
250
# In practice InnoDB takes shared row locks.
251
Success: 'do (select i from t1 where i = 1)' takes shared row locks on 't1'.
253
# 2.6 INSERT with a subquery.
255
# Has to take shared locks on rows in the table being read as
256
# this statement is written to the binary log and therefore
257
# should be serialized with concurrent statements.
258
Success: 'insert into t2 select i+5 from t1' takes shared row locks on 't1'.
259
Success: 'insert into t2 values ((select i+5 from t1 where i = 4))' takes shared row locks on 't1'.
261
# 2.7 LOAD DATA with a subquery.
263
# The above is true for this statement as well.
264
Success: 'load data infile '../../std_data/rpl_loaddata.dat' into table t2 (@a, @b) set j= @b + (select i from t1 where i = 1)' takes shared row locks on 't1'.
266
# 2.8 REPLACE with a subquery.
268
# Same is true for this statement as well.
269
Success: 'replace into t2 select i+5 from t1' takes shared row locks on 't1'.
270
Success: 'replace into t2 values ((select i+5 from t1 where i = 4))' takes shared row locks on 't1'.
272
# 2.9 SELECT with a subquery.
274
# Locks are not necessary as this statement is not written
275
# to the binary log and thanks to MVCC we can always get
276
# versions of rows prior to the update that has locked them.
278
# Also serves as a test case for bug #46947 "Embedded SELECT
279
# without FOR UPDATE is causing a lock".
280
Success: 'select * from t2 where j in (select i from t1)' doesn't take row locks on 't1'.
282
# 2.10 SET with a subquery.
284
# In theory should not require locking as it is not written
285
# to the binary log. In practice InnoDB acquires shared row
287
Success: 'set @a:= (select i from t1 where i = 1)' takes shared row locks on 't1'.
289
# 2.11 SHOW with a subquery.
291
# Similarly to the previous case, in theory should not require locking
292
# as it is not written to the binary log. In practice InnoDB
293
# acquires shared row locks.
294
Success: 'show tables from test where Tables_in_test = 't2' and (select i from t1 where i = 1)' takes shared row locks on 't1'.
295
Success: 'show columns from t2 where (select i from t1 where i = 1)' takes shared row locks on 't1'.
297
# 2.12 UPDATE with a subquery.
299
# Has to take shared locks on rows in the table being read as
300
# this statement is written to the binary log and therefore
301
# should be serialized with concurrent statements.
302
Success: 'update t2 set j= j-10 where j in (select i from t1)' takes shared row locks on 't1'.
304
# 2.13 MULTI-UPDATE with a subquery.
306
# Same is true for this statement as well.
307
Success: 'update t2, t3 set j= j -10 where j=k and j in (select i from t1)' takes shared row locks on 't1'.
309
# 3. Statements which read tables through a view.
312
# 3.1 SELECT statement which uses some table through a view.
314
# Since this statement is not written to the binary log
315
# and old version of rows are accessible thanks to MVCC,
316
# no locking is necessary.
317
Success: 'select * from v1' doesn't take row locks on 't1'.
318
Success: 'select * from v2' doesn't take row locks on 't1'.
319
Success: 'select * from t2 where j in (select i from v1)' doesn't take row locks on 't1'.
320
Success: 'select * from t3 where k in (select j from v2)' doesn't take row locks on 't1'.
322
# 3.2 Statements which modify a table and use views.
324
# Since such statements are going to be written to the binary
325
# log they need to be serialized against concurrent statements
326
# and therefore should take shared row locks on data read.
327
Success: 'update t2 set j= j-10 where j in (select i from v1)' takes shared row locks on 't1'.
328
Success: 'update t3 set k= k-10 where k in (select j from v2)' takes shared row locks on 't1'.
329
Success: 'update t2, v1 set j= j-10 where j = i' takes shared row locks on 't1'.
330
Success: 'update v2 set j= j-10 where j = 3' takes shared row locks on 't1'.
332
# 4. Statements which read tables through stored functions.
335
# 4.1 SELECT/SET with a stored function which does not
336
# modify data and uses SELECT in its turn.
338
# Calls to such functions won't get into the binary log and
339
# thus don't need to acquire strong locks.
340
# In 5.5 due to fix for bug #53921 "Wrong locks for SELECTs
341
# used stored functions may lead to broken SBR" strong locks
342
# are taken (we accepted it as a trade-off for this fix).
343
Success: 'select f1()' doesn't take row locks on 't1'.
344
Success: 'set @a:= f1()' doesn't take row locks on 't1'.
346
# 4.2 INSERT (or other statement which modifies data) with
347
# a stored function which does not modify data and uses
350
# Since such statement is written to the binary log it should
351
# be serialized with concurrent statements affecting the data
352
# it uses. Therefore it should take row locks on the data
354
# But due to bug #53921 "Wrong locks for SELECTs used stored
355
# functions may lead to broken SBR" no lock is taken.
356
Success: 'insert into t2 values (f1() + 5)' doesn't take row locks on 't1'.
358
# 4.3 SELECT/SET with a stored function which
359
# reads and modifies data.
361
# Since a call to such function is written to the binary log,
362
# it should be serialized with concurrent statements affecting
363
# the data it uses. Hence, row locks on the data read
365
# But due to bug #53921 "Wrong locks for SELECTs used stored
366
# functions may lead to broken SBR" no lock is taken.
367
Success: 'select f2()' doesn't take row locks on 't1'.
368
Success: 'set @a:= f2()' doesn't take row locks on 't1'.
370
# 4.4. SELECT/SET with a stored function which does not
371
# modify data and reads a table through subselect
372
# in a control construct.
374
# Again, in theory a call to this function won't get to the
375
# binary log and thus no locking is needed. But in practice
376
# we don't detect this fact early enough (get_lock_type_for_table())
377
# to avoid taking row locks.
378
Success: 'select f3()' takes shared row locks on 't1'.
379
Success: 'set @a:= f3()' takes shared row locks on 't1'.
380
Success: 'select f4()' takes shared row locks on 't1'.
381
Success: 'set @a:= f4()' takes shared row locks on 't1'.
383
# 4.5. INSERT (or other statement which modifies data) with
384
# a stored function which does not modify data and reads
385
# the table through a subselect in one of its control
388
# Since such statement is written to the binary log it should
389
# be serialized with concurrent statements affecting data it
390
# uses. Therefore it should take row locks on the data
392
Success: 'insert into t2 values (f3() + 5)' takes shared row locks on 't1'.
393
Success: 'insert into t2 values (f4() + 6)' takes shared row locks on 't1'.
395
# 4.6 SELECT/SET which uses a stored function with
396
# DML which reads a table via a subquery.
398
# Since call to such function is written to the binary log
399
# it should be serialized with concurrent statements.
400
# Hence reads should take row locks.
401
Success: 'select f5()' takes shared row locks on 't1'.
402
Success: 'set @a:= f5()' takes shared row locks on 't1'.
404
# 4.7 SELECT/SET which uses a stored function which
405
# doesn't modify data and reads tables through
408
# Once again, in theory, calls to such functions won't
409
# get into the binary log and thus don't need row
410
# locks. In practice this fact is discovered
411
# too late to have any effect.
412
# But due to bug #53921 "Wrong locks for SELECTs used stored
413
# functions may lead to broken SBR" no lock is taken
414
# in case of simple SELECT.
415
Success: 'select f6()' doesn't take row locks on 't1'.
416
Success: 'set @a:= f6()' doesn't take row locks on 't1'.
417
Success: 'select f7()' takes shared row locks on 't1'.
418
Success: 'set @a:= f7()' takes shared row locks on 't1'.
420
# 4.8 INSERT which uses stored function which
421
# doesn't modify data and reads a table
424
# Since such statement is written to the binary log and
425
# should be serialized with concurrent statements affecting
426
# the data it uses. Therefore it should take row locks on
428
# But due to bug #53921 "Wrong locks for SELECTs used stored
429
# functions may lead to broken SBR" no lock is taken
430
# in case of simple SELECT.
431
Success: 'insert into t3 values (f6() + 5)' doesn't take row locks on 't1'.
432
Success: 'insert into t3 values (f7() + 5)' takes shared row locks on 't1'.
434
# 4.9 SELECT which uses a stored function which
435
# modifies data and reads tables through a view.
437
# Since a call to such function is written to the binary log
438
# it should be serialized with concurrent statements.
439
# Hence, reads should take row locks.
440
# But due to bug #53921 "Wrong locks for SELECTs used stored
441
# functions may lead to broken SBR" no lock is taken
442
# in case of simple SELECT.
443
Success: 'select f8()' doesn't take row locks on 't1'.
444
Success: 'select f9()' takes shared row locks on 't1'.
446
# 4.10 SELECT which uses stored function which doesn't modify
447
# data and reads a table indirectly, by calling another
450
# Calls to such functions won't get into the binary log and
451
# thus don't need to acquire strong locks.
452
# In 5.5 due to fix for bug #53921 "Wrong locks for SELECTs
453
# used stored functions may lead to broken SBR" strong locks
454
# are taken (we accepted it as a trade-off for this fix).
455
Success: 'select f10()' doesn't take row locks on 't1'.
457
# 4.11 INSERT which uses a stored function which doesn't modify
458
# data and reads a table indirectly, by calling another
461
# Since such statement is written to the binary log, it should
462
# be serialized with concurrent statements affecting the data it
463
# uses. Therefore it should take row locks on data it reads.
464
# But due to bug #53921 "Wrong locks for SELECTs used stored
465
# functions may lead to broken SBR" no lock is taken.
466
Success: 'insert into t2 values (f10() + 5)' doesn't take row locks on 't1'.
468
# 4.12 SELECT which uses a stored function which modifies
469
# data and reads a table indirectly, by calling another
472
# Since a call to such function is written to the binary log
473
# it should be serialized from concurrent statements.
474
# Hence, reads should take row locks.
475
# But due to bug #53921 "Wrong locks for SELECTs used stored
476
# functions may lead to broken SBR" no lock is taken.
477
Success: 'select f11()' doesn't take row locks on 't1'.
479
# 4.13 SELECT that reads a table through a subquery passed
480
# as a parameter to a stored function which modifies
483
# Even though a call to this function is written to the
484
# binary log, values of its parameters are written as literals.
485
# So there is no need to acquire row locks on rows used in
487
# But due to the fact that in 5.1 for prelocked statements
488
# THD::in_lock_tables is set to TRUE we acquire strong locks
489
# (see also bug#44613 "SELECT statement inside FUNCTION takes
490
# a shared lock" [sic!!!]).
491
Success: 'select f12((select i+10 from t1 where i=1))' takes shared row locks on 't1'.
493
# 4.14 INSERT that reads a table via a subquery passed
494
# as a parameter to a stored function which doesn't
497
# Since this statement is written to the binary log it should
498
# be serialized with concurrent statements affecting the data it
499
# uses. Therefore it should take row locks on the data it reads.
500
Success: 'insert into t2 values (f13((select i+10 from t1 where i=1)))' takes shared row locks on 't1'.
502
# 5. Statements that read tables through stored procedures.
505
# 5.1 CALL statement which reads a table via SELECT.
507
# Since neither this statement nor its components are
508
# written to the binary log, there is no need to take
509
# row locks on the data it reads.
510
Success: 'call p2(@a)' doesn't take row locks on 't1'.
512
# 5.2 Function that modifies data and uses CALL,
513
# which reads a table through SELECT.
515
# Since a call to such function is written to the binary
516
# log, it should be serialized with concurrent statements.
517
# Hence, in this case reads should take row locks on data.
518
# But due to bug #53921 "Wrong locks for SELECTs used stored
519
# functions may lead to broken SBR" no lock is taken.
520
Success: 'select f14()' doesn't take row locks on 't1'.
522
# 5.3 SELECT that calls a function that doesn't modify data and
523
# uses a CALL statement that reads a table via SELECT.
525
# Calls to such functions won't get into the binary log and
526
# thus don't need to acquire strong locks.
527
# In 5.5 due to fix for bug #53921 "Wrong locks for SELECTs
528
# used stored functions may lead to broken SBR" strong locks
529
# are taken (we accepted it as a trade-off for this fix).
530
Success: 'select f15()' doesn't take row locks on 't1'.
532
# 5.4 INSERT which calls function which doesn't modify data and
533
# uses CALL statement which reads table through SELECT.
535
# Since such statement is written to the binary log it should
536
# be serialized with concurrent statements affecting data it
537
# uses. Therefore it should take row locks on data it reads.
538
# But due to bug #53921 "Wrong locks for SELECTs used stored
539
# functions may lead to broken SBR" no lock is taken.
540
Success: 'insert into t2 values (f15()+5)' doesn't take row locks on 't1'.
542
# 6. Statements that use triggers.
545
# 6.1 Statement invoking a trigger that reads table via SELECT.
547
# Since this statement is written to the binary log it should
548
# be serialized with concurrent statements affecting the data
549
# it uses. Therefore, it should take row locks on the data
551
# But due to bug #53921 "Wrong locks for SELECTs used stored
552
# functions may lead to broken SBR" no lock is taken.
553
Success: 'insert into t4 values (2)' doesn't take row locks on 't1'.
555
# 6.2 Statement invoking a trigger that reads table through
556
# a subquery in a control construct.
558
# The above is true for this statement as well.
559
Success: 'update t4 set l= 2 where l = 1' takes shared row locks on 't1'.
561
# 6.3 Statement invoking a trigger that reads a table through
564
# And for this statement.
565
Success: 'delete from t4 where l = 1' takes shared row locks on 't1'.
567
# 6.4 Statement invoking a trigger that reads a table through
570
# And for this statement.
571
# But due to bug #53921 "Wrong locks for SELECTs used stored
572
# functions may lead to broken SBR" no lock is taken.
573
Success: 'insert into t5 values (2)' doesn't take row locks on 't1'.
575
# 6.5 Statement invoking a trigger that reads a table through
578
# And for this statement.
579
# But due to bug #53921 "Wrong locks for SELECTs used stored
580
# functions may lead to broken SBR" no lock is taken.
581
Success: 'update t5 set l= 2 where l = 1' doesn't take row locks on 't1'.
601
drop table t1, t2, t3, t4, t5, te;
added
removed
mysql-test/r/innodb_mysql_lock2.result
