From 9ee16b49f0aac819bd4823d9b94485ef608b34e8 Mon Sep 17 00:00:00 2001 From: Heikki Linnakangas Date: Mon, 25 Aug 2014 20:12:01 +0300 Subject: [PATCH] Add regression tests for SELECT FOR UPDATE/SHARE NOWAIT. Thomas Munro --- src/test/isolation/expected/nowait-2.out | 43 ++++++++++++++++ src/test/isolation/expected/nowait-3.out | 17 +++++++ src/test/isolation/expected/nowait.out | 65 ++++++++++++++++++++++++ src/test/isolation/isolation_schedule | 3 ++ src/test/isolation/specs/nowait-2.spec | 37 ++++++++++++++ src/test/isolation/specs/nowait-3.spec | 33 ++++++++++++ src/test/isolation/specs/nowait.spec | 25 +++++++++ 7 files changed, 223 insertions(+) create mode 100644 src/test/isolation/expected/nowait-2.out create mode 100644 src/test/isolation/expected/nowait-3.out create mode 100644 src/test/isolation/expected/nowait.out create mode 100644 src/test/isolation/specs/nowait-2.spec create mode 100644 src/test/isolation/specs/nowait-3.spec create mode 100644 src/test/isolation/specs/nowait.spec diff --git a/src/test/isolation/expected/nowait-2.out b/src/test/isolation/expected/nowait-2.out new file mode 100644 index 0000000000..6e24bbbf26 --- /dev/null +++ b/src/test/isolation/expected/nowait-2.out @@ -0,0 +1,43 @@ +Parsed test spec with 2 sessions + +starting permutation: s1a s2a s2b s1b s2c +step s1a: SELECT * FROM foo FOR SHARE NOWAIT; +id data + +1 x +step s2a: SELECT * FROM foo FOR SHARE NOWAIT; +id data + +1 x +step s2b: SELECT * FROM foo FOR UPDATE NOWAIT; +ERROR: could not obtain lock on row in relation "foo" +step s1b: COMMIT; +step s2c: COMMIT; + +starting permutation: s2a s1a s2b s1b s2c +step s2a: SELECT * FROM foo FOR SHARE NOWAIT; +id data + +1 x +step s1a: SELECT * FROM foo FOR SHARE NOWAIT; +id data + +1 x +step s2b: SELECT * FROM foo FOR UPDATE NOWAIT; +ERROR: could not obtain lock on row in relation "foo" +step s1b: COMMIT; +step s2c: COMMIT; + +starting permutation: s2a s2b s1a s1b s2c +step s2a: SELECT * FROM foo FOR SHARE NOWAIT; +id data + +1 x +step s2b: SELECT * FROM foo FOR UPDATE NOWAIT; +id data + +1 x +step s1a: SELECT * FROM foo FOR SHARE NOWAIT; +ERROR: could not obtain lock on row in relation "foo" +step s1b: COMMIT; +step s2c: COMMIT; diff --git a/src/test/isolation/expected/nowait-3.out b/src/test/isolation/expected/nowait-3.out new file mode 100644 index 0000000000..844464654a --- /dev/null +++ b/src/test/isolation/expected/nowait-3.out @@ -0,0 +1,17 @@ +Parsed test spec with 3 sessions + +starting permutation: s1a s2a s3a s1b s2b s3b +step s1a: SELECT * FROM foo FOR UPDATE; +id data + +1 x +step s2a: SELECT * FROM foo FOR UPDATE; +step s3a: SELECT * FROM foo FOR UPDATE NOWAIT; +ERROR: could not obtain lock on row in relation "foo" +step s1b: COMMIT; +step s2a: <... completed> +id data + +1 x +step s2b: COMMIT; +step s3b: COMMIT; diff --git a/src/test/isolation/expected/nowait.out b/src/test/isolation/expected/nowait.out new file mode 100644 index 0000000000..a6343b4afa --- /dev/null +++ b/src/test/isolation/expected/nowait.out @@ -0,0 +1,65 @@ +Parsed test spec with 2 sessions + +starting permutation: s1a s1b s2a s2b +step s1a: SELECT * FROM foo FOR UPDATE NOWAIT; +id data + +1 x +step s1b: COMMIT; +step s2a: SELECT * FROM foo FOR UPDATE NOWAIT; +id data + +1 x +step s2b: COMMIT; + +starting permutation: s1a s2a s1b s2b +step s1a: SELECT * FROM foo FOR UPDATE NOWAIT; +id data + +1 x +step s2a: SELECT * FROM foo FOR UPDATE NOWAIT; +ERROR: could not obtain lock on row in relation "foo" +step s1b: COMMIT; +step s2b: COMMIT; + +starting permutation: s1a s2a s2b s1b +step s1a: SELECT * FROM foo FOR UPDATE NOWAIT; +id data + +1 x +step s2a: SELECT * FROM foo FOR UPDATE NOWAIT; +ERROR: could not obtain lock on row in relation "foo" +step s2b: COMMIT; +step s1b: COMMIT; + +starting permutation: s2a s1a s1b s2b +step s2a: SELECT * FROM foo FOR UPDATE NOWAIT; +id data + +1 x +step s1a: SELECT * FROM foo FOR UPDATE NOWAIT; +ERROR: could not obtain lock on row in relation "foo" +step s1b: COMMIT; +step s2b: COMMIT; + +starting permutation: s2a s1a s2b s1b +step s2a: SELECT * FROM foo FOR UPDATE NOWAIT; +id data + +1 x +step s1a: SELECT * FROM foo FOR UPDATE NOWAIT; +ERROR: could not obtain lock on row in relation "foo" +step s2b: COMMIT; +step s1b: COMMIT; + +starting permutation: s2a s2b s1a s1b +step s2a: SELECT * FROM foo FOR UPDATE NOWAIT; +id data + +1 x +step s2b: COMMIT; +step s1a: SELECT * FROM foo FOR UPDATE NOWAIT; +id data + +1 x +step s1b: COMMIT; diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule index 36acec1ca8..10c89ff126 100644 --- a/src/test/isolation/isolation_schedule +++ b/src/test/isolation/isolation_schedule @@ -25,3 +25,6 @@ test: propagate-lock-delete test: drop-index-concurrently-1 test: alter-table-1 test: timeouts +test: nowait +test: nowait-2 +test: nowait-3 diff --git a/src/test/isolation/specs/nowait-2.spec b/src/test/isolation/specs/nowait-2.spec new file mode 100644 index 0000000000..007d0237c2 --- /dev/null +++ b/src/test/isolation/specs/nowait-2.spec @@ -0,0 +1,37 @@ +# Test NOWAIT with multixact locks. + +setup +{ + CREATE TABLE foo ( + id int PRIMARY KEY, + data text NOT NULL + ); + INSERT INTO foo VALUES (1, 'x'); +} + +teardown +{ + DROP TABLE foo; +} + +session "s1" +setup { BEGIN; } +step "s1a" { SELECT * FROM foo FOR SHARE NOWAIT; } +step "s1b" { COMMIT; } + +session "s2" +setup { BEGIN; } +step "s2a" { SELECT * FROM foo FOR SHARE NOWAIT; } +step "s2b" { SELECT * FROM foo FOR UPDATE NOWAIT; } +step "s2c" { COMMIT; } + +# s1 and s2 both get SHARE lock, creating a multixact lock, then s2 +# tries to upgrade to UPDATE but aborts because it cannot acquire a +# multi-xact lock +permutation "s1a" "s2a" "s2b" "s1b" "s2c" +# the same but with the SHARE locks acquired in a different order, so +# s2 again aborts because it can't acquired a multi-xact lock +permutation "s2a" "s1a" "s2b" "s1b" "s2c" +# s2 acquires SHARE then UPDATE, then s1 tries to acquire SHARE but +# can't so aborts because it can't acquire a regular lock +permutation "s2a" "s2b" "s1a" "s1b" "s2c" \ No newline at end of file diff --git a/src/test/isolation/specs/nowait-3.spec b/src/test/isolation/specs/nowait-3.spec new file mode 100644 index 0000000000..58dec7fa44 --- /dev/null +++ b/src/test/isolation/specs/nowait-3.spec @@ -0,0 +1,33 @@ +# Test NOWAIT with tuple locks. + +setup +{ + CREATE TABLE foo ( + id int PRIMARY KEY, + data text NOT NULL + ); + INSERT INTO foo VALUES (1, 'x'); +} + +teardown +{ + DROP TABLE foo; +} + +session "s1" +setup { BEGIN; } +step "s1a" { SELECT * FROM foo FOR UPDATE; } +step "s1b" { COMMIT; } + +session "s2" +setup { BEGIN; } +step "s2a" { SELECT * FROM foo FOR UPDATE; } +step "s2b" { COMMIT; } + +session "s3" +setup { BEGIN; } +step "s3a" { SELECT * FROM foo FOR UPDATE NOWAIT; } +step "s3b" { COMMIT; } + +# s3 skips to second record due to tuple lock held by s2 +permutation "s1a" "s2a" "s3a" "s1b" "s2b" "s3b" \ No newline at end of file diff --git a/src/test/isolation/specs/nowait.spec b/src/test/isolation/specs/nowait.spec new file mode 100644 index 0000000000..73580cd1b2 --- /dev/null +++ b/src/test/isolation/specs/nowait.spec @@ -0,0 +1,25 @@ +# Test NOWAIT when regular row locks can't be acquired. + +setup +{ + CREATE TABLE foo ( + id int PRIMARY KEY, + data text NOT NULL + ); + INSERT INTO foo VALUES (1, 'x'); +} + +teardown +{ + DROP TABLE foo; +} + +session "s1" +setup { BEGIN; } +step "s1a" { SELECT * FROM foo FOR UPDATE NOWAIT; } +step "s1b" { COMMIT; } + +session "s2" +setup { BEGIN; } +step "s2a" { SELECT * FROM foo FOR UPDATE NOWAIT; } +step "s2b" { COMMIT; } -- 2.40.0