Debian Codegraph @

mysql-test/suite/innodb/include/skip_locked_nowait_isolation.inc

raw
1 --echo #
2 --echo # Test $trx_isolation_level
3 --echo #
4
5 connection default;
6
7 CREATE TABLE t1(
8 seat_id INT,
9 pos POINT NOT NULL,
10 state INT,
11 PRIMARY KEY(seat_id)
12 ) ENGINE=InnoDB;
13
14 INSERT INTO t1 VALUES
15 (1,ST_PointFromText('POINT(1 0)'),0),
16 (3,ST_PointFromText('POINT(2 0)'),0),
17 (4,ST_PointFromText('POINT(1 1)'),0),
18 (6,ST_PointFromText('POINT(2 1)'),0);
19
20 eval SET SESSION TRANSACTION ISOLATION LEVEL $trx_isolation_level;
21 BEGIN;
22
23 eval SELECT seat_id, state, ST_AsText(pos) FROM t1
24 WHERE state = 0 AND seat_id >= 1 LIMIT 2 $for_share;
25
26 connection con1;
27
28 eval SET SESSION TRANSACTION ISOLATION LEVEL $trx_isolation_level;
29 BEGIN;
30
31 --error 0,ER_LOCK_WAIT_TIMEOUT
32 INSERT INTO t1 VALUES(2,ST_PointFromText('POINT(2 1)'),0);
33
34 --error 0,ER_LOCK_NOWAIT
35 SELECT seat_id, state, ST_AsText(pos) FROM t1 WHERE state = 0
36 FOR UPDATE NOWAIT;
37
38 SELECT seat_id, state, ST_AsText(pos) FROM t1 WHERE state = 0
39 FOR UPDATE SKIP LOCKED;
40
41 SET @g = ST_GeomFromText('POLYGON((0 0,0 3,3 3,0 3,0 0))');
42 --error ER_LOCK_NOWAIT
43 SELECT seat_id, state, ST_AsText(pos) FROM t1
44 WHERE state = 0 AND MBRWithin(pos, @g) LIMIT 2 FOR UPDATE NOWAIT;
45
46 SELECT seat_id, state, ST_AsText(pos) FROM t1
47 WHERE state = 0 AND MBRWithin(pos, @g) LIMIT 2 FOR UPDATE SKIP LOCKED;
48
49 COMMIT;
50
51 connection default;
52 COMMIT;
53
54 DROP TABLE t1;