1 files changed, 208 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/t/skip_locked_nowait.test b/mysql-test/suite/innodb/t/skip_locked_nowait.test
new file mode 100644
index 00000000..ea744ca5
--- /dev/null
+++ b/mysql-test/suite/innodb/t/skip_locked_nowait.test
@@ -0,0 +1,208 @@
+#
+# wl#8919  Implement NOWAIT and SKIP LOCKED
+#
+--source include/have_innodb.inc
+
+
+connect (con1,localhost,root,,);
+SET SESSION innodb_lock_wait_timeout=1;
+
+connection default;
+SET SESSION innodb_lock_wait_timeout=1;
+
+--echo # Case 1: Test primary index
+CREATE TABLE t1(
+        seat_id    INT,
+        state      INT,
+        PRIMARY KEY(seat_id)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES(1,0), (2,0), (3,0), (4,0);
+
+BEGIN;
+SELECT * FROM t1 WHERE state = 0 LIMIT 2 LOCK IN SHARE MODE;
+
+connection con1;
+BEGIN;
+
+SELECT * FROM t1 WHERE state = 0 LIMIT 2 LOCK IN SHARE MODE;
+
+SELECT * FROM t1 WHERE state = 0 LIMIT 2 LOCK IN SHARE MODE SKIP LOCKED;
+
+--error ER_LOCK_WAIT_TIMEOUT
+SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE;
+
+--error ER_LOCK_WAIT_TIMEOUT
+SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE NOWAIT;
+SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE SKIP LOCKED;
+
+--error ER_LOCK_WAIT_TIMEOUT
+SELECT * FROM t1 WHERE seat_id > 0 LIMIT 2 FOR UPDATE NOWAIT;
+SELECT * FROM t1 WHERE seat_id > 0 LIMIT 2 FOR UPDATE SKIP LOCKED;
+
+COMMIT;
+
+connection default;
+SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE;
+
+connection con1;
+--error ER_LOCK_WAIT_TIMEOUT
+SELECT * FROM t1 WHERE state = 0 LIMIT 2 LOCK IN SHARE MODE;
+
+--error ER_LOCK_WAIT_TIMEOUT
+SELECT * FROM t1 WHERE state = 0 LIMIT 2 LOCK IN SHARE MODE NOWAIT;
+SELECT * FROM t1 WHERE state = 0 LIMIT 2 LOCK IN SHARE MODE SKIP LOCKED;
+
+--error ER_LOCK_WAIT_TIMEOUT
+SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE;
+
+--error ER_LOCK_WAIT_TIMEOUT
+SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE NOWAIT;
+SELECT * FROM t1 WHERE state = 0 LIMIT 2 FOR UPDATE SKIP LOCKED;
+
+--error ER_LOCK_WAIT_TIMEOUT
+SELECT * FROM t1 WHERE seat_id > 0 LIMIT 2 FOR UPDATE NOWAIT;
+SELECT * FROM t1 WHERE seat_id > 0 LIMIT 2 FOR UPDATE SKIP LOCKED;
+
+COMMIT;
+
+connection default;
+COMMIT;
+
+DROP TABLE t1;
+
+--echo # Case 2: Test primary index & secondary index
+CREATE TABLE t1(
+        seat_id    INT,
+        row_id     INT,
+        state      INT,
+        PRIMARY KEY(seat_id),
+        KEY(row_id)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES(1,1,0), (2,1,0), (3,2,0), (4,2,0);
+
+--echo # Test secondary key
+# Case 2a: secondary blocks secondary/primary
+BEGIN;
+SELECT * FROM t1 WHERE state = 0 AND row_id = 1 LIMIT 1 FOR UPDATE NOWAIT;
+
+connection con1;
+BEGIN;
+--error ER_LOCK_WAIT_TIMEOUT
+SELECT * FROM t1 WHERE state = 0 AND row_id = 1 LIMIT 1 FOR UPDATE NOWAIT;
+SELECT * FROM t1 WHERE state = 0 AND row_id = 1 LIMIT 1 FOR UPDATE SKIP LOCKED;
+
+--error ER_LOCK_WAIT_TIMEOUT
+SELECT * FROM t1 WHERE state = 0 AND row_id > 0 LIMIT 1 FOR UPDATE NOWAIT;
+SELECT * FROM t1 WHERE state = 0 AND row_id > 0 LIMIT 1 FOR UPDATE SKIP LOCKED;
+
+--error ER_LOCK_WAIT_TIMEOUT
+SELECT * FROM t1 WHERE state = 0 FOR UPDATE NOWAIT;
+SELECT * FROM t1 WHERE state = 0 FOR UPDATE SKIP LOCKED;
+
+COMMIT;
+
+connection default;
+COMMIT;
+
+# Case 2b: primary blocks secondary/primary
+BEGIN;
+SELECT * FROM t1 WHERE seat_id = 1 FOR UPDATE NOWAIT;
+
+connection con1;
+BEGIN;
+--error ER_LOCK_WAIT_TIMEOUT
+SELECT * FROM t1 WHERE state = 0 AND row_id = 1 LIMIT 1 FOR UPDATE NOWAIT;
+SELECT * FROM t1 WHERE state = 0 AND row_id = 1 LIMIT 1 FOR UPDATE SKIP LOCKED;
+
+--error ER_LOCK_WAIT_TIMEOUT
+SELECT * FROM t1 WHERE state = 0 FOR UPDATE NOWAIT;
+SELECT * FROM t1 WHERE state = 0 FOR UPDATE SKIP LOCKED;
+
+COMMIT;
+
+connection default;
+COMMIT;
+
+DROP TABLE t1;
+
+--echo # Case 3: Test primary index & spatial index
+CREATE TABLE t1(
+        seat_id    INT,
+        pos        POINT NOT NULL,
+        state      INT,
+        PRIMARY KEY(seat_id),
+        SPATIAL KEY(pos)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES
+(1,ST_PointFromText('POINT(1 0)'),0),
+(2,ST_PointFromText('POINT(1 1)'),0),
+(3,ST_PointFromText('POINT(2 0)'),0),
+(4,ST_PointFromText('POINT(2 1)'),0),
+(5,ST_PointFromText('POINT(3 0)'),0),
+(6,ST_PointFromText('POINT(3 1)'),0);
+
+# Case 3a: secondary blocks secondary/primary
+BEGIN;
+SET @g = ST_GeomFromText('POLYGON((0 0,0 2,2 2,0 2,0 0))');
+# the first 4 records in the rtree index page are locked
+SELECT seat_id, state, ST_AsText(pos) FROM t1 FORCE INDEX (pos)
+WHERE state = 0 AND MBRWithin(pos, @g) FOR UPDATE NOWAIT;
+
+connection con1;
+BEGIN;
+SET @g = ST_GeomFromText('POLYGON((0 0,0 4,4 4,0 4,0 0))');
+--error ER_LOCK_WAIT_TIMEOUT
+SELECT seat_id, state, ST_AsText(pos) FROM t1 FORCE INDEX (pos)
+WHERE state = 0 AND MBRWithin(pos, @g) FOR UPDATE NOWAIT;
+
+# TODO Needs fixing
+#SELECT seat_id, state, ST_AsText(pos) FROM t1 FORCE INDEX (pos)
+#WHERE state = 0 AND MBRWithin(pos, @g)  FOR UPDATE SKIP LOCKED;
+
+--error ER_LOCK_WAIT_TIMEOUT
+SELECT seat_id, state, ST_AsText(pos) FROM t1
+WHERE state = 0 FOR UPDATE NOWAIT;
+
+SELECT seat_id, state, ST_AsText(pos) FROM t1
+WHERE state = 0 FOR UPDATE SKIP LOCKED;
+
+COMMIT;
+
+connection default;
+COMMIT;
+
+# Case 3b: primary blockes secondary/primary
+connection con1;
+SET @g = ST_GeomFromText('POLYGON((0 0,0 3,3 3,0 3,0 0))');
+SELECT seat_id, state, ST_AsText(pos) FROM t1 FORCE INDEX (pos)
+WHERE state = 0 AND MBRWithin(pos, @g) FOR UPDATE;
+
+connection default;
+BEGIN;
+SELECT seat_id, state, ST_AsText(pos) FROM t1
+WHERE seat_id = 4 FOR UPDATE NOWAIT;
+
+connection con1;
+--error ER_LOCK_WAIT_TIMEOUT
+SELECT seat_id, state, ST_AsText(pos) FROM t1 FORCE INDEX (pos)
+WHERE state = 0 AND MBRWithin(pos, @g) FOR UPDATE NOWAIT;
+
+SELECT seat_id, state, ST_AsText(pos) FROM t1 FORCE INDEX (pos)
+WHERE state = 0 AND MBRWithin(pos, @g) FOR UPDATE SKIP LOCKED;
+
+--error ER_LOCK_WAIT_TIMEOUT
+SELECT seat_id, state, ST_AsText(pos) FROM t1
+WHERE state = 0 FOR UPDATE NOWAIT;
+
+SELECT seat_id, state, ST_AsText(pos) FROM t1
+WHERE state = 0 FOR UPDATE SKIP LOCKED;
+
+connection default;
+COMMIT;
+
+DROP TABLE t1;
+
+disconnect con1;