Debian Codegraph @

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

raw
1 #
2 # MDEV-13115 Implement SKIP LOCKED
3 #
4 --source include/have_innodb.inc
5
6
7 connect (con1,localhost,root,,);
8 SET SESSION innodb_lock_wait_timeout=1;
9
10 connection default;
11 SET SESSION innodb_lock_wait_timeout=1;
12
13 --echo # Case 1: Test primary index - CREATE TABLE .. SELECT .. SKIP LOCKED
14 CREATE TABLE t1(
15 seat_id INT,
16 state INT,
17 PRIMARY KEY(seat_id)
18 ) ENGINE=InnoDB;
19
20 INSERT INTO t1 VALUES(1,0), (2,0), (3,0), (4,0);
21
22 BEGIN;
23 SELECT * FROM t1 WHERE state = 0 LIMIT 2 LOCK IN SHARE MODE;
24
25 connection con1;
26 BEGIN;
27 CREATE TEMPORARY TABLE s0 AS SELECT * FROM t1 WHERE state = 0 LIMIT 2 LOCK IN SHARE MODE;
28 SELECT * FROM s0;
29
30 CREATE TEMPORARY TABLE s1 AS SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE SKIP LOCKED;
31 SELECT * FROM s1;
32
33 connection default;
34 CREATE TEMPORARY TABLE s0 AS SELECT * FROM t1 WHERE state = 0 LOCK IN SHARE MODE SKIP LOCKED;
35 SELECT * FROM s0;
36
37 COMMIT;
38 DROP TABLE s0;
39
40 connection con1;
41 COMMIT;
42 DROP TABLE s0, s1;
43
44 connection default;
45
46 --echo # Case 2: Test primary index - INSERT .. SELECT .. SKIP LOCKED
47
48 CREATE TABLE t2 LIKE t1;
49
50 BEGIN;
51 SELECT * FROM t1 WHERE state = 0 LIMIT 2 LOCK IN SHARE MODE;
52
53 connection con1;
54 BEGIN;
55
56 INSERT INTO t2 SELECT * FROM t1 WHERE state = 0 LIMIT 2 LOCK IN SHARE MODE RETURNING seat_id, state;
57
58 CREATE TEMPORARY TABLE t2s LIKE t1;
59 INSERT INTO t2s SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE SKIP LOCKED RETURNING seat_id, state;
60
61 connection default;
62 CREATE TEMPORARY TABLE t2s LIKE t1;
63 INSERT INTO t2s SELECT * FROM t1 WHERE state = 0 LOCK IN SHARE MODE SKIP LOCKED RETURNING seat_id, state;
64 COMMIT;
65 DROP TABLE t2s;
66
67 connection con1;
68 COMMIT;
69 DROP TABLE t2s;
70 DROP TABLE t2;
71
72 connection default;
73 DROP TABLE t1;