Debian Codegraph @

mysql-test/suite/innodb/r/create_table_insert_skip_locked.result

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