Debian Codegraph @

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

raw
1 SET SESSION innodb_lock_wait_timeout=1;
2 SET SESSION innodb_lock_wait_timeout=1;
3 #
4 # Test READ UNCOMMITTED
5 #
6 CREATE TABLE t1(
7 seat_id INT,
8 pos POINT NOT NULL,
9 state INT,
10 PRIMARY KEY(seat_id)
11 ) ENGINE=InnoDB;
12 INSERT INTO t1 VALUES
13 (1,ST_PointFromText('POINT(1 0)'),0),
14 (3,ST_PointFromText('POINT(2 0)'),0),
15 (4,ST_PointFromText('POINT(1 1)'),0),
16 (6,ST_PointFromText('POINT(2 1)'),0);
17 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
18 BEGIN;
19 SELECT seat_id, state, ST_AsText(pos) FROM t1
20 WHERE state = 0 AND seat_id >= 1 LIMIT 2 FOR SHARE;
21 seat_id state ST_AsText(pos)
22 1 0 POINT(1 0)
23 3 0 POINT(2 0)
24 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
25 BEGIN;
26 INSERT INTO t1 VALUES(2,ST_PointFromText('POINT(2 1)'),0);
27 SELECT seat_id, state, ST_AsText(pos) FROM t1 WHERE state = 0
28 FOR UPDATE NOWAIT;
29 SELECT seat_id, state, ST_AsText(pos) FROM t1 WHERE state = 0
30 FOR UPDATE SKIP LOCKED;
31 seat_id state ST_AsText(pos)
32 2 0 POINT(2 1)
33 4 0 POINT(1 1)
34 6 0 POINT(2 1)
35 SET @g = ST_GeomFromText('POLYGON((0 0,0 3,3 3,0 3,0 0))');
36 SELECT seat_id, state, ST_AsText(pos) FROM t1
37 WHERE state = 0 AND MBRWithin(pos, @g) LIMIT 2 FOR UPDATE NOWAIT;
38 ERROR HY000: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
39 SELECT seat_id, state, ST_AsText(pos) FROM t1
40 WHERE state = 0 AND MBRWithin(pos, @g) LIMIT 2 FOR UPDATE SKIP LOCKED;
41 seat_id state ST_AsText(pos)
42 2 0 POINT(2 1)
43 4 0 POINT(1 1)
44 COMMIT;
45 COMMIT;
46 DROP TABLE t1;
47 #
48 # Test READ COMMITTED
49 #
50 CREATE TABLE t1(
51 seat_id INT,
52 pos POINT NOT NULL,
53 state INT,
54 PRIMARY KEY(seat_id)
55 ) ENGINE=InnoDB;
56 INSERT INTO t1 VALUES
57 (1,ST_PointFromText('POINT(1 0)'),0),
58 (3,ST_PointFromText('POINT(2 0)'),0),
59 (4,ST_PointFromText('POINT(1 1)'),0),
60 (6,ST_PointFromText('POINT(2 1)'),0);
61 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
62 BEGIN;
63 SELECT seat_id, state, ST_AsText(pos) FROM t1
64 WHERE state = 0 AND seat_id >= 1 LIMIT 2 FOR SHARE;
65 seat_id state ST_AsText(pos)
66 1 0 POINT(1 0)
67 3 0 POINT(2 0)
68 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
69 BEGIN;
70 INSERT INTO t1 VALUES(2,ST_PointFromText('POINT(2 1)'),0);
71 SELECT seat_id, state, ST_AsText(pos) FROM t1 WHERE state = 0
72 FOR UPDATE NOWAIT;
73 SELECT seat_id, state, ST_AsText(pos) FROM t1 WHERE state = 0
74 FOR UPDATE SKIP LOCKED;
75 seat_id state ST_AsText(pos)
76 2 0 POINT(2 1)
77 4 0 POINT(1 1)
78 6 0 POINT(2 1)
79 SET @g = ST_GeomFromText('POLYGON((0 0,0 3,3 3,0 3,0 0))');
80 SELECT seat_id, state, ST_AsText(pos) FROM t1
81 WHERE state = 0 AND MBRWithin(pos, @g) LIMIT 2 FOR UPDATE NOWAIT;
82 ERROR HY000: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
83 SELECT seat_id, state, ST_AsText(pos) FROM t1
84 WHERE state = 0 AND MBRWithin(pos, @g) LIMIT 2 FOR UPDATE SKIP LOCKED;
85 seat_id state ST_AsText(pos)
86 2 0 POINT(2 1)
87 4 0 POINT(1 1)
88 COMMIT;
89 COMMIT;
90 DROP TABLE t1;
91 #
92 # Test REPEATABLE READ
93 #
94 CREATE TABLE t1(
95 seat_id INT,
96 pos POINT NOT NULL,
97 state INT,
98 PRIMARY KEY(seat_id)
99 ) ENGINE=InnoDB;
100 INSERT INTO t1 VALUES
101 (1,ST_PointFromText('POINT(1 0)'),0),
102 (3,ST_PointFromText('POINT(2 0)'),0),
103 (4,ST_PointFromText('POINT(1 1)'),0),
104 (6,ST_PointFromText('POINT(2 1)'),0);
105 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
106 BEGIN;
107 SELECT seat_id, state, ST_AsText(pos) FROM t1
108 WHERE state = 0 AND seat_id >= 1 LIMIT 2 FOR SHARE;
109 seat_id state ST_AsText(pos)
110 1 0 POINT(1 0)
111 3 0 POINT(2 0)
112 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
113 BEGIN;
114 INSERT INTO t1 VALUES(2,ST_PointFromText('POINT(2 1)'),0);
115 SELECT seat_id, state, ST_AsText(pos) FROM t1 WHERE state = 0
116 FOR UPDATE NOWAIT;
117 SELECT seat_id, state, ST_AsText(pos) FROM t1 WHERE state = 0
118 FOR UPDATE SKIP LOCKED;
119 seat_id state ST_AsText(pos)
120 4 0 POINT(1 1)
121 6 0 POINT(2 1)
122 SET @g = ST_GeomFromText('POLYGON((0 0,0 3,3 3,0 3,0 0))');
123 SELECT seat_id, state, ST_AsText(pos) FROM t1
124 WHERE state = 0 AND MBRWithin(pos, @g) LIMIT 2 FOR UPDATE NOWAIT;
125 ERROR HY000: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
126 SELECT seat_id, state, ST_AsText(pos) FROM t1
127 WHERE state = 0 AND MBRWithin(pos, @g) LIMIT 2 FOR UPDATE SKIP LOCKED;
128 seat_id state ST_AsText(pos)
129 4 0 POINT(1 1)
130 6 0 POINT(2 1)
131 COMMIT;
132 COMMIT;
133 DROP TABLE t1;
134 #
135 # Test SERIALIZABLE
136 #
137 CREATE TABLE t1(
138 seat_id INT,
139 pos POINT NOT NULL,
140 state INT,
141 PRIMARY KEY(seat_id)
142 ) ENGINE=InnoDB;
143 INSERT INTO t1 VALUES
144 (1,ST_PointFromText('POINT(1 0)'),0),
145 (3,ST_PointFromText('POINT(2 0)'),0),
146 (4,ST_PointFromText('POINT(1 1)'),0),
147 (6,ST_PointFromText('POINT(2 1)'),0);
148 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
149 BEGIN;
150 SELECT seat_id, state, ST_AsText(pos) FROM t1
151 WHERE state = 0 AND seat_id >= 1 LIMIT 2 ;
152 seat_id state ST_AsText(pos)
153 1 0 POINT(1 0)
154 3 0 POINT(2 0)
155 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
156 BEGIN;
157 INSERT INTO t1 VALUES(2,ST_PointFromText('POINT(2 1)'),0);
158 SELECT seat_id, state, ST_AsText(pos) FROM t1 WHERE state = 0
159 FOR UPDATE NOWAIT;
160 SELECT seat_id, state, ST_AsText(pos) FROM t1 WHERE state = 0
161 FOR UPDATE SKIP LOCKED;
162 seat_id state ST_AsText(pos)
163 4 0 POINT(1 1)
164 6 0 POINT(2 1)
165 SET @g = ST_GeomFromText('POLYGON((0 0,0 3,3 3,0 3,0 0))');
166 SELECT seat_id, state, ST_AsText(pos) FROM t1
167 WHERE state = 0 AND MBRWithin(pos, @g) LIMIT 2 FOR UPDATE NOWAIT;
168 ERROR HY000: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
169 SELECT seat_id, state, ST_AsText(pos) FROM t1
170 WHERE state = 0 AND MBRWithin(pos, @g) LIMIT 2 FOR UPDATE SKIP LOCKED;
171 seat_id state ST_AsText(pos)
172 4 0 POINT(1 1)
173 6 0 POINT(2 1)
174 COMMIT;
175 COMMIT;
176 DROP TABLE t1;