1 files changed, 61 insertions, 0 deletions
diff --git a/src/test/isolation/specs/lock-update-delete.spec b/src/test/isolation/specs/lock-update-delete.spec
new file mode 100644
index 0000000..b9dd7d1
--- /dev/null
+++ b/src/test/isolation/specs/lock-update-delete.spec
@@ -0,0 +1,61 @@
+# This test verifies behavior when traversing an update chain during
+# locking an old version of the tuple.  There are three tests here:
+# 1. update the tuple, then delete it; a second transaction locks the
+# first version.  This should raise an error if the DELETE succeeds,
+# but be allowed to continue if it aborts.
+# 2. Same as (1), except that instead of deleting the tuple, we merely
+# update its key.  The behavior should be the same as for (1).
+# 3. Same as (2), except that we update the tuple without modifying its
+# key. In this case, no error should be raised.
+# When run in REPEATABLE READ or SERIALIZABLE transaction isolation levels, all
+# permutations that commit s2 cause a serializability error; all permutations
+# that rollback s2 can get through.
+#
+# We use an advisory lock (which is locked during s1's setup) to let s2 obtain
+# its snapshot early and only allow it to actually traverse the update chain
+# when s1 is done creating it.
+
+setup
+{
+  DROP TABLE IF EXISTS foo;
+  CREATE TABLE foo (
+	key		int PRIMARY KEY,
+	value	int
+  );
+
+  INSERT INTO foo VALUES (1, 1);
+}
+
+teardown
+{
+  DROP TABLE foo;
+}
+
+session s1
+# obtain lock on the tuple, traversing its update chain
+step s1l	{ SELECT * FROM foo WHERE pg_advisory_xact_lock(0) IS NOT NULL AND key = 1 FOR KEY SHARE; }
+
+session s2
+setup		{ SELECT pg_advisory_lock(0); }
+step s2b	{ BEGIN; }
+step s2u	{ UPDATE foo SET value = 2 WHERE key = 1; }
+step s2_blocker1	{ DELETE FROM foo; }
+step s2_blocker2	{ UPDATE foo SET key = 2 WHERE key = 1; }
+step s2_blocker3	{ UPDATE foo SET value = 2 WHERE key = 1; }
+step s2_unlock		{ SELECT pg_advisory_unlock(0); }
+step s2c	{ COMMIT; }
+step s2r	{ ROLLBACK; }
+
+permutation s2b s1l s2u s2_blocker1 s2_unlock s2c
+permutation s2b s1l s2u s2_blocker2 s2_unlock s2c
+permutation s2b s1l s2u s2_blocker3 s2_unlock s2c
+permutation s2b s1l s2u s2_blocker1 s2_unlock s2r
+permutation s2b s1l s2u s2_blocker2 s2_unlock s2r
+permutation s2b s1l s2u s2_blocker3 s2_unlock s2r
+
+permutation s2b s1l s2u s2_blocker1 s2c s2_unlock
+permutation s2b s1l s2u s2_blocker2 s2c s2_unlock
+permutation s2b s1l s2u s2_blocker3 s2c s2_unlock
+permutation s2b s1l s2u s2_blocker1 s2r s2_unlock
+permutation s2b s1l s2u s2_blocker2 s2r s2_unlock
+permutation s2b s1l s2u s2_blocker3 s2r s2_unlock