From 533e9c6b0628f6557ddcaf3e5177081878ea7cb6 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Fri, 15 Jul 2016 14:17:20 -0400 Subject: [PATCH] Avoid serializability errors when locking a tuple with a committed update When key-share locking a tuple that has been not-key-updated, and the update is a committed transaction, in some cases we raised serializability errors: ERROR: could not serialize access due to concurrent update Because the key-share doesn't conflict with the update, the error is unnecessary and inconsistent with the case that the update hasn't committed yet. This causes problems for some usage patterns, even if it can be claimed that it's sufficient to retry the aborted transaction: given a steady stream of updating transactions and a long locking transaction, the long transaction can be starved indefinitely despite multiple retries. To fix, we recognize that HeapTupleSatisfiesUpdate can return HeapTupleUpdated when an updating transaction has committed, and that we need to deal with that case exactly as if it were a non-committed update: verify whether the two operations conflict, and if not, carry on normally. If they do conflict, however, there is a difference: in the HeapTupleBeingUpdated case we can just sleep until the concurrent transaction is gone, while in the HeapTupleUpdated case this is not possible and we must raise an error instead. Per trouble report from Olivier Dony. In addition to a couple of test cases that verify the changed behavior, I added a test case to verify the behavior that remains unchanged, namely that errors are raised when a update that modifies the key is used. That must still generate serializability errors. One pre-existing test case changes behavior; per discussion, the new behavior is actually the desired one. Discussion: https://www.postgresql.org/message-id/560AA479.4080807@odoo.com https://www.postgresql.org/message-id/20151014164844.3019.25750@wrigleys.postgresql.org Backpatch to 9.3, where the problem appeared. --- src/backend/access/heap/heapam.c | 16 +- .../expected/lock-committed-keyupdate.out | 496 +++++++++++++ .../expected/lock-committed-update.out | 667 ++++++++++++++++++ .../isolation/expected/lock-update-delete.out | 2 +- .../expected/update-locked-tuple.out | 55 ++ src/test/isolation/isolation_schedule | 3 + .../specs/lock-committed-keyupdate.spec | 66 ++ .../specs/lock-committed-update.spec | 62 ++ .../isolation/specs/update-locked-tuple.spec | 38 + 9 files changed, 1401 insertions(+), 4 deletions(-) create mode 100644 src/test/isolation/expected/lock-committed-keyupdate.out create mode 100644 src/test/isolation/expected/lock-committed-update.out create mode 100644 src/test/isolation/expected/update-locked-tuple.out create mode 100644 src/test/isolation/specs/lock-committed-keyupdate.spec create mode 100644 src/test/isolation/specs/lock-committed-update.spec create mode 100644 src/test/isolation/specs/update-locked-tuple.spec diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index 57da57a8cf..57f2f275e8 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -4540,7 +4540,7 @@ l3: */ return HeapTupleInvisible; } - else if (result == HeapTupleBeingUpdated) + else if (result == HeapTupleBeingUpdated || result == HeapTupleUpdated) { TransactionId xwait; uint16 infomask; @@ -4800,12 +4800,22 @@ l3: } /* + * Time to sleep on the other transaction/multixact, if necessary. + * + * If the other transaction is an update that's already committed, + * then sleeping cannot possibly do any good: if we're required to + * sleep, get out to raise an error instead. + * * By here, we either have already acquired the buffer exclusive lock, * or we must wait for the locking transaction or multixact; so below * we ensure that we grab buffer lock after the sleep. */ - - if (require_sleep) + if (require_sleep && result == HeapTupleUpdated) + { + LockBuffer(*buffer, BUFFER_LOCK_EXCLUSIVE); + goto failed; + } + else if (require_sleep) { /* * Acquire tuple lock to establish our priority for the tuple, or diff --git a/src/test/isolation/expected/lock-committed-keyupdate.out b/src/test/isolation/expected/lock-committed-keyupdate.out new file mode 100644 index 0000000000..69cdbfba0a --- /dev/null +++ b/src/test/isolation/expected/lock-committed-keyupdate.out @@ -0,0 +1,496 @@ +Parsed test spec with 2 sessions + +starting permutation: s1b s2b1 s1l s2l s1u s1c s1ul s2c +step s1b: BEGIN; +step s2b1: BEGIN ISOLATION LEVEL READ COMMITTED; +step s1l: SELECT pg_advisory_lock(578902068); +pg_advisory_lock + + +step s2l: SELECT * FROM lcku_table WHERE pg_advisory_lock(578902068) IS NOT NULL FOR KEY SHARE; +step s1u: UPDATE lcku_table SET id = 2 WHERE id = 3; +step s1c: COMMIT; +step s1ul: SELECT pg_advisory_unlock(578902068); +pg_advisory_unlock + +t +step s2l: <... completed> +id value + +1 one +2 two +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b1 s1l s1u s2l s1c s1ul s2c +step s1b: BEGIN; +step s2b1: BEGIN ISOLATION LEVEL READ COMMITTED; +step s1l: SELECT pg_advisory_lock(578902068); +pg_advisory_lock + + +step s1u: UPDATE lcku_table SET id = 2 WHERE id = 3; +step s2l: SELECT * FROM lcku_table WHERE pg_advisory_lock(578902068) IS NOT NULL FOR KEY SHARE; +step s1c: COMMIT; +step s1ul: SELECT pg_advisory_unlock(578902068); +pg_advisory_unlock + +t +step s2l: <... completed> +id value + +1 one +2 two +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b1 s1l s1u s1ul s2l s1c s2c +step s1b: BEGIN; +step s2b1: BEGIN ISOLATION LEVEL READ COMMITTED; +step s1l: SELECT pg_advisory_lock(578902068); +pg_advisory_lock + + +step s1u: UPDATE lcku_table SET id = 2 WHERE id = 3; +step s1ul: SELECT pg_advisory_unlock(578902068); +pg_advisory_unlock + +t +step s2l: SELECT * FROM lcku_table WHERE pg_advisory_lock(578902068) IS NOT NULL FOR KEY SHARE; +step s1c: COMMIT; +step s2l: <... completed> +id value + +1 one +2 two +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b1 s1l s2l s1u s1c s1hint s1ul s2c +step s1b: BEGIN; +step s2b1: BEGIN ISOLATION LEVEL READ COMMITTED; +step s1l: SELECT pg_advisory_lock(578902068); +pg_advisory_lock + + +step s2l: SELECT * FROM lcku_table WHERE pg_advisory_lock(578902068) IS NOT NULL FOR KEY SHARE; +step s1u: UPDATE lcku_table SET id = 2 WHERE id = 3; +step s1c: COMMIT; +step s1hint: SELECT * FROM lcku_table; +id value + +1 one +2 two +step s1ul: SELECT pg_advisory_unlock(578902068); +pg_advisory_unlock + +t +step s2l: <... completed> +id value + +1 one +2 two +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b1 s1l s1u s2l s1c s1hint s1ul s2c +step s1b: BEGIN; +step s2b1: BEGIN ISOLATION LEVEL READ COMMITTED; +step s1l: SELECT pg_advisory_lock(578902068); +pg_advisory_lock + + +step s1u: UPDATE lcku_table SET id = 2 WHERE id = 3; +step s2l: SELECT * FROM lcku_table WHERE pg_advisory_lock(578902068) IS NOT NULL FOR KEY SHARE; +step s1c: COMMIT; +step s1hint: SELECT * FROM lcku_table; +id value + +1 one +2 two +step s1ul: SELECT pg_advisory_unlock(578902068); +pg_advisory_unlock + +t +step s2l: <... completed> +id value + +1 one +2 two +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b1 s1l s1u s1ul s2l s1c s1hint s2c +step s1b: BEGIN; +step s2b1: BEGIN ISOLATION LEVEL READ COMMITTED; +step s1l: SELECT pg_advisory_lock(578902068); +pg_advisory_lock + + +step s1u: UPDATE lcku_table SET id = 2 WHERE id = 3; +step s1ul: SELECT pg_advisory_unlock(578902068); +pg_advisory_unlock + +t +step s2l: SELECT * FROM lcku_table WHERE pg_advisory_lock(578902068) IS NOT NULL FOR KEY SHARE; +step s1c: COMMIT; +step s2l: <... completed> +id value + +1 one +2 two +step s1hint: SELECT * FROM lcku_table; +id value + +1 one +2 two +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b2 s1l s2l s1u s1c s1ul s2c +step s1b: BEGIN; +step s2b2: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s1l: SELECT pg_advisory_lock(578902068); +pg_advisory_lock + + +step s2l: SELECT * FROM lcku_table WHERE pg_advisory_lock(578902068) IS NOT NULL FOR KEY SHARE; +step s1u: UPDATE lcku_table SET id = 2 WHERE id = 3; +step s1c: COMMIT; +step s1ul: SELECT pg_advisory_unlock(578902068); +pg_advisory_unlock + +t +step s2l: <... completed> +error in steps s1ul s2l: ERROR: could not serialize access due to concurrent update +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b2 s1l s1u s2l s1c s1ul s2c +step s1b: BEGIN; +step s2b2: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s1l: SELECT pg_advisory_lock(578902068); +pg_advisory_lock + + +step s1u: UPDATE lcku_table SET id = 2 WHERE id = 3; +step s2l: SELECT * FROM lcku_table WHERE pg_advisory_lock(578902068) IS NOT NULL FOR KEY SHARE; +step s1c: COMMIT; +step s1ul: SELECT pg_advisory_unlock(578902068); +pg_advisory_unlock + +t +step s2l: <... completed> +error in steps s1ul s2l: ERROR: could not serialize access due to concurrent update +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b2 s1l s1u s1ul s2l s1c s2c +step s1b: BEGIN; +step s2b2: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s1l: SELECT pg_advisory_lock(578902068); +pg_advisory_lock + + +step s1u: UPDATE lcku_table SET id = 2 WHERE id = 3; +step s1ul: SELECT pg_advisory_unlock(578902068); +pg_advisory_unlock + +t +step s2l: SELECT * FROM lcku_table WHERE pg_advisory_lock(578902068) IS NOT NULL FOR KEY SHARE; +step s1c: COMMIT; +step s2l: <... completed> +error in steps s1c s2l: ERROR: could not serialize access due to concurrent update +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b2 s1l s2l s1u s1c s1hint s1ul s2c +step s1b: BEGIN; +step s2b2: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s1l: SELECT pg_advisory_lock(578902068); +pg_advisory_lock + + +step s2l: SELECT * FROM lcku_table WHERE pg_advisory_lock(578902068) IS NOT NULL FOR KEY SHARE; +step s1u: UPDATE lcku_table SET id = 2 WHERE id = 3; +step s1c: COMMIT; +step s1hint: SELECT * FROM lcku_table; +id value + +1 one +2 two +step s1ul: SELECT pg_advisory_unlock(578902068); +pg_advisory_unlock + +t +step s2l: <... completed> +error in steps s1ul s2l: ERROR: could not serialize access due to concurrent update +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b2 s1l s1u s2l s1c s1hint s1ul s2c +step s1b: BEGIN; +step s2b2: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s1l: SELECT pg_advisory_lock(578902068); +pg_advisory_lock + + +step s1u: UPDATE lcku_table SET id = 2 WHERE id = 3; +step s2l: SELECT * FROM lcku_table WHERE pg_advisory_lock(578902068) IS NOT NULL FOR KEY SHARE; +step s1c: COMMIT; +step s1hint: SELECT * FROM lcku_table; +id value + +1 one +2 two +step s1ul: SELECT pg_advisory_unlock(578902068); +pg_advisory_unlock + +t +step s2l: <... completed> +error in steps s1ul s2l: ERROR: could not serialize access due to concurrent update +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b2 s1l s1u s1ul s2l s1c s1hint s2c +step s1b: BEGIN; +step s2b2: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s1l: SELECT pg_advisory_lock(578902068); +pg_advisory_lock + + +step s1u: UPDATE lcku_table SET id = 2 WHERE id = 3; +step s1ul: SELECT pg_advisory_unlock(578902068); +pg_advisory_unlock + +t +step s2l: SELECT * FROM lcku_table WHERE pg_advisory_lock(578902068) IS NOT NULL FOR KEY SHARE; +step s1c: COMMIT; +step s2l: <... completed> +error in steps s1c s2l: ERROR: could not serialize access due to concurrent update +step s1hint: SELECT * FROM lcku_table; +id value + +1 one +2 two +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b3 s1l s2l s1u s1c s1ul s2c +step s1b: BEGIN; +step s2b3: BEGIN ISOLATION LEVEL SERIALIZABLE; +step s1l: SELECT pg_advisory_lock(578902068); +pg_advisory_lock + + +step s2l: SELECT * FROM lcku_table WHERE pg_advisory_lock(578902068) IS NOT NULL FOR KEY SHARE; +step s1u: UPDATE lcku_table SET id = 2 WHERE id = 3; +step s1c: COMMIT; +step s1ul: SELECT pg_advisory_unlock(578902068); +pg_advisory_unlock + +t +step s2l: <... completed> +error in steps s1ul s2l: ERROR: could not serialize access due to concurrent update +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b3 s1l s1u s2l s1c s1ul s2c +step s1b: BEGIN; +step s2b3: BEGIN ISOLATION LEVEL SERIALIZABLE; +step s1l: SELECT pg_advisory_lock(578902068); +pg_advisory_lock + + +step s1u: UPDATE lcku_table SET id = 2 WHERE id = 3; +step s2l: SELECT * FROM lcku_table WHERE pg_advisory_lock(578902068) IS NOT NULL FOR KEY SHARE; +step s1c: COMMIT; +step s1ul: SELECT pg_advisory_unlock(578902068); +pg_advisory_unlock + +t +step s2l: <... completed> +error in steps s1ul s2l: ERROR: could not serialize access due to concurrent update +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b3 s1l s1u s1ul s2l s1c s2c +step s1b: BEGIN; +step s2b3: BEGIN ISOLATION LEVEL SERIALIZABLE; +step s1l: SELECT pg_advisory_lock(578902068); +pg_advisory_lock + + +step s1u: UPDATE lcku_table SET id = 2 WHERE id = 3; +step s1ul: SELECT pg_advisory_unlock(578902068); +pg_advisory_unlock + +t +step s2l: SELECT * FROM lcku_table WHERE pg_advisory_lock(578902068) IS NOT NULL FOR KEY SHARE; +step s1c: COMMIT; +step s2l: <... completed> +error in steps s1c s2l: ERROR: could not serialize access due to concurrent update +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b3 s1l s2l s1u s1c s1hint s1ul s2c +step s1b: BEGIN; +step s2b3: BEGIN ISOLATION LEVEL SERIALIZABLE; +step s1l: SELECT pg_advisory_lock(578902068); +pg_advisory_lock + + +step s2l: SELECT * FROM lcku_table WHERE pg_advisory_lock(578902068) IS NOT NULL FOR KEY SHARE; +step s1u: UPDATE lcku_table SET id = 2 WHERE id = 3; +step s1c: COMMIT; +step s1hint: SELECT * FROM lcku_table; +id value + +1 one +2 two +step s1ul: SELECT pg_advisory_unlock(578902068); +pg_advisory_unlock + +t +step s2l: <... completed> +error in steps s1ul s2l: ERROR: could not serialize access due to concurrent update +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b3 s1l s1u s2l s1c s1hint s1ul s2c +step s1b: BEGIN; +step s2b3: BEGIN ISOLATION LEVEL SERIALIZABLE; +step s1l: SELECT pg_advisory_lock(578902068); +pg_advisory_lock + + +step s1u: UPDATE lcku_table SET id = 2 WHERE id = 3; +step s2l: SELECT * FROM lcku_table WHERE pg_advisory_lock(578902068) IS NOT NULL FOR KEY SHARE; +step s1c: COMMIT; +step s1hint: SELECT * FROM lcku_table; +id value + +1 one +2 two +step s1ul: SELECT pg_advisory_unlock(578902068); +pg_advisory_unlock + +t +step s2l: <... completed> +error in steps s1ul s2l: ERROR: could not serialize access due to concurrent update +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b3 s1l s1u s1ul s2l s1c s1hint s2c +step s1b: BEGIN; +step s2b3: BEGIN ISOLATION LEVEL SERIALIZABLE; +step s1l: SELECT pg_advisory_lock(578902068); +pg_advisory_lock + + +step s1u: UPDATE lcku_table SET id = 2 WHERE id = 3; +step s1ul: SELECT pg_advisory_unlock(578902068); +pg_advisory_unlock + +t +step s2l: SELECT * FROM lcku_table WHERE pg_advisory_lock(578902068) IS NOT NULL FOR KEY SHARE; +step s1c: COMMIT; +step s2l: <... completed> +error in steps s1c s2l: ERROR: could not serialize access due to concurrent update +step s1hint: SELECT * FROM lcku_table; +id value + +1 one +2 two +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + diff --git a/src/test/isolation/expected/lock-committed-update.out b/src/test/isolation/expected/lock-committed-update.out new file mode 100644 index 0000000000..88a6f23eab --- /dev/null +++ b/src/test/isolation/expected/lock-committed-update.out @@ -0,0 +1,667 @@ +Parsed test spec with 2 sessions + +starting permutation: s1b s2b1 s1l s2l s1u s1c s1ul s2c +step s1b: BEGIN; +step s2b1: BEGIN ISOLATION LEVEL READ COMMITTED; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s1c: COMMIT; +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: <... completed> +id value + +1 one +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b1 s1l s1u s2l s1c s1ul s2c +step s1b: BEGIN; +step s2b1: BEGIN ISOLATION LEVEL READ COMMITTED; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +step s1c: COMMIT; +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: <... completed> +id value + +1 one +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b1 s1l s2l s1ul s1u s1c s2c +step s1b: BEGIN; +step s2b1: BEGIN ISOLATION LEVEL READ COMMITTED; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: <... completed> +id value + +1 one +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s1c: COMMIT; +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b1 s1l s1u s1ul s2l s1c s2c +step s1b: BEGIN; +step s2b1: BEGIN ISOLATION LEVEL READ COMMITTED; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +id value + +1 one +step s1c: COMMIT; +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b1 s1l s2l s1u s1c s1hint s1ul s2c +step s1b: BEGIN; +step s2b1: BEGIN ISOLATION LEVEL READ COMMITTED; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s1c: COMMIT; +step s1hint: SELECT * FROM lcu_table; +id value + +1 two +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: <... completed> +id value + +1 one +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b1 s1l s1u s2l s1c s1hint s1ul s2c +step s1b: BEGIN; +step s2b1: BEGIN ISOLATION LEVEL READ COMMITTED; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +step s1c: COMMIT; +step s1hint: SELECT * FROM lcu_table; +id value + +1 two +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: <... completed> +id value + +1 one +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b1 s1l s2l s1ul s1u s1c s1hint s2c +step s1b: BEGIN; +step s2b1: BEGIN ISOLATION LEVEL READ COMMITTED; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: <... completed> +id value + +1 one +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s1c: COMMIT; +step s1hint: SELECT * FROM lcu_table; +id value + +1 two +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b1 s1l s1u s1ul s2l s1c s1hint s2c +step s1b: BEGIN; +step s2b1: BEGIN ISOLATION LEVEL READ COMMITTED; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +id value + +1 one +step s1c: COMMIT; +step s1hint: SELECT * FROM lcu_table; +id value + +1 two +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b2 s1l s2l s1u s1c s1ul s2c +step s1b: BEGIN; +step s2b2: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s1c: COMMIT; +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: <... completed> +id value + +1 one +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b2 s1l s1u s2l s1c s1ul s2c +step s1b: BEGIN; +step s2b2: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +step s1c: COMMIT; +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: <... completed> +id value + +1 one +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b2 s1l s2l s1ul s1u s1c s2c +step s1b: BEGIN; +step s2b2: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: <... completed> +id value + +1 one +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s1c: COMMIT; +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b2 s1l s1u s1ul s2l s1c s2c +step s1b: BEGIN; +step s2b2: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +id value + +1 one +step s1c: COMMIT; +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b2 s1l s2l s1u s1c s1hint s1ul s2c +step s1b: BEGIN; +step s2b2: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s1c: COMMIT; +step s1hint: SELECT * FROM lcu_table; +id value + +1 two +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: <... completed> +id value + +1 one +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b2 s1l s1u s2l s1c s1hint s1ul s2c +step s1b: BEGIN; +step s2b2: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +step s1c: COMMIT; +step s1hint: SELECT * FROM lcu_table; +id value + +1 two +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: <... completed> +id value + +1 one +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b2 s1l s2l s1ul s1u s1c s1hint s2c +step s1b: BEGIN; +step s2b2: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: <... completed> +id value + +1 one +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s1c: COMMIT; +step s1hint: SELECT * FROM lcu_table; +id value + +1 two +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b2 s1l s1u s1ul s2l s1c s1hint s2c +step s1b: BEGIN; +step s2b2: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +id value + +1 one +step s1c: COMMIT; +step s1hint: SELECT * FROM lcu_table; +id value + +1 two +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b3 s1l s2l s1u s1c s1ul s2c +step s1b: BEGIN; +step s2b3: BEGIN ISOLATION LEVEL SERIALIZABLE; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s1c: COMMIT; +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: <... completed> +id value + +1 one +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b3 s1l s1u s2l s1c s1ul s2c +step s1b: BEGIN; +step s2b3: BEGIN ISOLATION LEVEL SERIALIZABLE; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +step s1c: COMMIT; +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: <... completed> +id value + +1 one +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b3 s1l s2l s1ul s1u s1c s2c +step s1b: BEGIN; +step s2b3: BEGIN ISOLATION LEVEL SERIALIZABLE; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: <... completed> +id value + +1 one +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s1c: COMMIT; +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b3 s1l s1u s1ul s2l s1c s2c +step s1b: BEGIN; +step s2b3: BEGIN ISOLATION LEVEL SERIALIZABLE; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +id value + +1 one +step s1c: COMMIT; +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b3 s1l s2l s1u s1c s1hint s1ul s2c +step s1b: BEGIN; +step s2b3: BEGIN ISOLATION LEVEL SERIALIZABLE; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s1c: COMMIT; +step s1hint: SELECT * FROM lcu_table; +id value + +1 two +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: <... completed> +id value + +1 one +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b3 s1l s1u s2l s1c s1hint s1ul s2c +step s1b: BEGIN; +step s2b3: BEGIN ISOLATION LEVEL SERIALIZABLE; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +step s1c: COMMIT; +step s1hint: SELECT * FROM lcu_table; +id value + +1 two +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: <... completed> +id value + +1 one +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b3 s1l s2l s1ul s1u s1c s1hint s2c +step s1b: BEGIN; +step s2b3: BEGIN ISOLATION LEVEL SERIALIZABLE; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: <... completed> +id value + +1 one +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s1c: COMMIT; +step s1hint: SELECT * FROM lcu_table; +id value + +1 two +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + + +starting permutation: s1b s2b3 s1l s1u s1ul s2l s1c s1hint s2c +step s1b: BEGIN; +step s2b3: BEGIN ISOLATION LEVEL SERIALIZABLE; +step s1l: SELECT pg_advisory_lock(380170116); +pg_advisory_lock + + +step s1u: UPDATE lcu_table SET value = 'two' WHERE id = 1; +step s1ul: SELECT pg_advisory_unlock(380170116); +pg_advisory_unlock + +t +step s2l: SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; +id value + +1 one +step s1c: COMMIT; +step s1hint: SELECT * FROM lcu_table; +id value + +1 two +step s2c: COMMIT; +pg_advisory_unlock_all + + +pg_advisory_unlock_all + + diff --git a/src/test/isolation/expected/lock-update-delete.out b/src/test/isolation/expected/lock-update-delete.out index 344a6ecb9a..3aa12539e2 100644 --- a/src/test/isolation/expected/lock-update-delete.out +++ b/src/test/isolation/expected/lock-update-delete.out @@ -156,7 +156,7 @@ t step s1l: <... completed> key value -1 2 +1 1 starting permutation: s2b s1l s2u s2_blocker1 s2r s2_unlock pg_advisory_lock diff --git a/src/test/isolation/expected/update-locked-tuple.out b/src/test/isolation/expected/update-locked-tuple.out new file mode 100644 index 0000000000..1982c75f52 --- /dev/null +++ b/src/test/isolation/expected/update-locked-tuple.out @@ -0,0 +1,55 @@ +Parsed test spec with 2 sessions + +starting permutation: s1b s2b s2u s2c s1u1 s1u2 s1c +step s1b: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s2b: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s2u: UPDATE users SET sometime = '1830-10-04' WHERE id = 1; +step s2c: COMMIT; +step s1u1: UPDATE orders SET name = 'order of olivier (2)', user_id = 1 WHERE id = 1; +step s1u2: UPDATE orders SET name = 'order of olivier (3)', user_id = 1 WHERE id = 1; +step s1c: COMMIT; + +starting permutation: s1b s2b s2u s1u1 s2c s1u2 s1c +step s1b: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s2b: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s2u: UPDATE users SET sometime = '1830-10-04' WHERE id = 1; +step s1u1: UPDATE orders SET name = 'order of olivier (2)', user_id = 1 WHERE id = 1; +step s2c: COMMIT; +step s1u2: UPDATE orders SET name = 'order of olivier (3)', user_id = 1 WHERE id = 1; +step s1c: COMMIT; + +starting permutation: s1b s2b s1u1 s2u s2c s1u2 s1c +step s1b: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s2b: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s1u1: UPDATE orders SET name = 'order of olivier (2)', user_id = 1 WHERE id = 1; +step s2u: UPDATE users SET sometime = '1830-10-04' WHERE id = 1; +step s2c: COMMIT; +step s1u2: UPDATE orders SET name = 'order of olivier (3)', user_id = 1 WHERE id = 1; +step s1c: COMMIT; + +starting permutation: s1b s1u1 s2b s2u s2c s1u2 s1c +step s1b: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s1u1: UPDATE orders SET name = 'order of olivier (2)', user_id = 1 WHERE id = 1; +step s2b: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s2u: UPDATE users SET sometime = '1830-10-04' WHERE id = 1; +step s2c: COMMIT; +step s1u2: UPDATE orders SET name = 'order of olivier (3)', user_id = 1 WHERE id = 1; +step s1c: COMMIT; + +starting permutation: s1b s1u1 s2b s1u2 s2u s2c s1c +step s1b: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s1u1: UPDATE orders SET name = 'order of olivier (2)', user_id = 1 WHERE id = 1; +step s2b: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s1u2: UPDATE orders SET name = 'order of olivier (3)', user_id = 1 WHERE id = 1; +step s2u: UPDATE users SET sometime = '1830-10-04' WHERE id = 1; +step s2c: COMMIT; +step s1c: COMMIT; + +starting permutation: s1b s1u1 s1u2 s2b s2u s2c s1c +step s1b: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s1u1: UPDATE orders SET name = 'order of olivier (2)', user_id = 1 WHERE id = 1; +step s1u2: UPDATE orders SET name = 'order of olivier (3)', user_id = 1 WHERE id = 1; +step s2b: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s2u: UPDATE users SET sometime = '1830-10-04' WHERE id = 1; +step s2c: COMMIT; +step s1c: COMMIT; diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule index fbd2192464..87ab7742fc 100644 --- a/src/test/isolation/isolation_schedule +++ b/src/test/isolation/isolation_schedule @@ -33,6 +33,9 @@ test: delete-abort-savept-2 test: aborted-keyrevoke test: multixact-no-deadlock test: multixact-no-forget +test: lock-committed-update +test: lock-committed-keyupdate +test: update-locked-tuple test: propagate-lock-delete test: tuplelock-conflict test: tuplelock-update diff --git a/src/test/isolation/specs/lock-committed-keyupdate.spec b/src/test/isolation/specs/lock-committed-keyupdate.spec new file mode 100644 index 0000000000..1630282d0f --- /dev/null +++ b/src/test/isolation/specs/lock-committed-keyupdate.spec @@ -0,0 +1,66 @@ +# Test locking of a tuple with a committed key-update. In this case, +# the update conflicts with the lock, so failures are expected, except +# in READ COMMITTED isolation mode. +# +# Some of the permutations are commented out that work fine in the +# lock-committed-update test, because in this case the update blocks. + +setup +{ + DROP TABLE IF EXISTS lcku_table; + CREATE TABLE lcku_table (id INTEGER PRIMARY KEY, value TEXT); + INSERT INTO lcku_table VALUES (1, 'one'); + INSERT INTO lcku_table VALUES (3, 'two'); +} + +teardown +{ + DROP TABLE lcku_table; +} + +session "s1" +step "s1b" { BEGIN; } +step "s1l" { SELECT pg_advisory_lock(578902068); } +step "s1u" { UPDATE lcku_table SET id = 2 WHERE id = 3; } +step "s1hint" { SELECT * FROM lcku_table; } +step "s1ul" { SELECT pg_advisory_unlock(578902068); } +step "s1c" { COMMIT; } +teardown { SELECT pg_advisory_unlock_all(); } + +session "s2" +step "s2b1" { BEGIN ISOLATION LEVEL READ COMMITTED; } +step "s2b2" { BEGIN ISOLATION LEVEL REPEATABLE READ; } +step "s2b3" { BEGIN ISOLATION LEVEL SERIALIZABLE; } +step "s2l" { SELECT * FROM lcku_table WHERE pg_advisory_lock(578902068) IS NOT NULL FOR KEY SHARE; } +step "s2c" { COMMIT; } +teardown { SELECT pg_advisory_unlock_all(); } + +permutation "s1b" "s2b1" "s1l" "s2l" "s1u" "s1c" "s1ul" "s2c" +permutation "s1b" "s2b1" "s1l" "s1u" "s2l" "s1c" "s1ul" "s2c" +#permutation "s1b" "s2b1" "s1l" "s2l" "s1ul" "s1u" "s1c" "s2c" +permutation "s1b" "s2b1" "s1l" "s1u" "s1ul" "s2l" "s1c" "s2c" + +permutation "s1b" "s2b1" "s1l" "s2l" "s1u" "s1c" "s1hint" "s1ul" "s2c" +permutation "s1b" "s2b1" "s1l" "s1u" "s2l" "s1c" "s1hint" "s1ul" "s2c" +#permutation "s1b" "s2b1" "s1l" "s2l" "s1ul" "s1u" "s1c" "s1hint" "s2c" +permutation "s1b" "s2b1" "s1l" "s1u" "s1ul" "s2l" "s1c" "s1hint" "s2c" + +permutation "s1b" "s2b2" "s1l" "s2l" "s1u" "s1c" "s1ul" "s2c" +permutation "s1b" "s2b2" "s1l" "s1u" "s2l" "s1c" "s1ul" "s2c" +#permutation "s1b" "s2b2" "s1l" "s2l" "s1ul" "s1u" "s1c" "s2c" +permutation "s1b" "s2b2" "s1l" "s1u" "s1ul" "s2l" "s1c" "s2c" + +permutation "s1b" "s2b2" "s1l" "s2l" "s1u" "s1c" "s1hint" "s1ul" "s2c" +permutation "s1b" "s2b2" "s1l" "s1u" "s2l" "s1c" "s1hint" "s1ul" "s2c" +#permutation "s1b" "s2b2" "s1l" "s2l" "s1ul" "s1u" "s1c" "s1hint" "s2c" +permutation "s1b" "s2b2" "s1l" "s1u" "s1ul" "s2l" "s1c" "s1hint" "s2c" + +permutation "s1b" "s2b3" "s1l" "s2l" "s1u" "s1c" "s1ul" "s2c" +permutation "s1b" "s2b3" "s1l" "s1u" "s2l" "s1c" "s1ul" "s2c" +#permutation "s1b" "s2b3" "s1l" "s2l" "s1ul" "s1u" "s1c" "s2c" +permutation "s1b" "s2b3" "s1l" "s1u" "s1ul" "s2l" "s1c" "s2c" + +permutation "s1b" "s2b3" "s1l" "s2l" "s1u" "s1c" "s1hint" "s1ul" "s2c" +permutation "s1b" "s2b3" "s1l" "s1u" "s2l" "s1c" "s1hint" "s1ul" "s2c" +#permutation "s1b" "s2b3" "s1l" "s2l" "s1ul" "s1u" "s1c" "s1hint" "s2c" +permutation "s1b" "s2b3" "s1l" "s1u" "s1ul" "s2l" "s1c" "s1hint" "s2c" diff --git a/src/test/isolation/specs/lock-committed-update.spec b/src/test/isolation/specs/lock-committed-update.spec new file mode 100644 index 0000000000..0495c11570 --- /dev/null +++ b/src/test/isolation/specs/lock-committed-update.spec @@ -0,0 +1,62 @@ +# Test locking of a tuple with a committed update. When the lock does not +# conflict with the update, no blocking and no serializability errors should +# occur. + +setup +{ + DROP TABLE IF EXISTS lcu_table; + CREATE TABLE lcu_table (id INTEGER PRIMARY KEY, value TEXT); + INSERT INTO lcu_table VALUES (1, 'one'); +} + +teardown +{ + DROP TABLE lcu_table; +} + +session "s1" +step "s1b" { BEGIN; } +step "s1l" { SELECT pg_advisory_lock(380170116); } +step "s1u" { UPDATE lcu_table SET value = 'two' WHERE id = 1; } +step "s1hint" { SELECT * FROM lcu_table; } +step "s1ul" { SELECT pg_advisory_unlock(380170116); } +step "s1c" { COMMIT; } +teardown { SELECT pg_advisory_unlock_all(); } + +session "s2" +step "s2b1" { BEGIN ISOLATION LEVEL READ COMMITTED; } +step "s2b2" { BEGIN ISOLATION LEVEL REPEATABLE READ; } +step "s2b3" { BEGIN ISOLATION LEVEL SERIALIZABLE; } +step "s2l" { SELECT * FROM lcu_table WHERE pg_advisory_lock(380170116) IS NOT NULL FOR KEY SHARE; } +step "s2c" { COMMIT; } +teardown { SELECT pg_advisory_unlock_all(); } + +permutation "s1b" "s2b1" "s1l" "s2l" "s1u" "s1c" "s1ul" "s2c" +permutation "s1b" "s2b1" "s1l" "s1u" "s2l" "s1c" "s1ul" "s2c" +permutation "s1b" "s2b1" "s1l" "s2l" "s1ul" "s1u" "s1c" "s2c" +permutation "s1b" "s2b1" "s1l" "s1u" "s1ul" "s2l" "s1c" "s2c" + +permutation "s1b" "s2b1" "s1l" "s2l" "s1u" "s1c" "s1hint" "s1ul" "s2c" +permutation "s1b" "s2b1" "s1l" "s1u" "s2l" "s1c" "s1hint" "s1ul" "s2c" +permutation "s1b" "s2b1" "s1l" "s2l" "s1ul" "s1u" "s1c" "s1hint" "s2c" +permutation "s1b" "s2b1" "s1l" "s1u" "s1ul" "s2l" "s1c" "s1hint" "s2c" + +permutation "s1b" "s2b2" "s1l" "s2l" "s1u" "s1c" "s1ul" "s2c" +permutation "s1b" "s2b2" "s1l" "s1u" "s2l" "s1c" "s1ul" "s2c" +permutation "s1b" "s2b2" "s1l" "s2l" "s1ul" "s1u" "s1c" "s2c" +permutation "s1b" "s2b2" "s1l" "s1u" "s1ul" "s2l" "s1c" "s2c" + +permutation "s1b" "s2b2" "s1l" "s2l" "s1u" "s1c" "s1hint" "s1ul" "s2c" +permutation "s1b" "s2b2" "s1l" "s1u" "s2l" "s1c" "s1hint" "s1ul" "s2c" +permutation "s1b" "s2b2" "s1l" "s2l" "s1ul" "s1u" "s1c" "s1hint" "s2c" +permutation "s1b" "s2b2" "s1l" "s1u" "s1ul" "s2l" "s1c" "s1hint" "s2c" + +permutation "s1b" "s2b3" "s1l" "s2l" "s1u" "s1c" "s1ul" "s2c" +permutation "s1b" "s2b3" "s1l" "s1u" "s2l" "s1c" "s1ul" "s2c" +permutation "s1b" "s2b3" "s1l" "s2l" "s1ul" "s1u" "s1c" "s2c" +permutation "s1b" "s2b3" "s1l" "s1u" "s1ul" "s2l" "s1c" "s2c" + +permutation "s1b" "s2b3" "s1l" "s2l" "s1u" "s1c" "s1hint" "s1ul" "s2c" +permutation "s1b" "s2b3" "s1l" "s1u" "s2l" "s1c" "s1hint" "s1ul" "s2c" +permutation "s1b" "s2b3" "s1l" "s2l" "s1ul" "s1u" "s1c" "s1hint" "s2c" +permutation "s1b" "s2b3" "s1l" "s1u" "s1ul" "s2l" "s1c" "s1hint" "s2c" diff --git a/src/test/isolation/specs/update-locked-tuple.spec b/src/test/isolation/specs/update-locked-tuple.spec new file mode 100644 index 0000000000..10dd3ef3d2 --- /dev/null +++ b/src/test/isolation/specs/update-locked-tuple.spec @@ -0,0 +1,38 @@ +# Test updating a locked tuple. When the lock doesn't conflict with the +# update, no blocking nor serializability problems should occur. + +setup +{ + DROP TABLE IF EXISTS users, orders; + CREATE TABLE users (id INTEGER PRIMARY KEY, + name varchar, + sometime timestamp); + CREATE TABLE orders (id INTEGER PRIMARY KEY, + name varchar, + user_id INTEGER REFERENCES users (id)); + INSERT INTO users (id, name) VALUES (1, 'olivier'); + INSERT INTO orders (id, name) VALUES (1, 'order of olivier (1)'); +} + +teardown +{ + DROP TABLE users, orders; +} + +session "s1" +step "s1b" { BEGIN ISOLATION LEVEL REPEATABLE READ; } +step "s1u1" { UPDATE orders SET name = 'order of olivier (2)', user_id = 1 WHERE id = 1; } +step "s1u2" { UPDATE orders SET name = 'order of olivier (3)', user_id = 1 WHERE id = 1; } +step "s1c" { COMMIT; } + +session "s2" +step "s2b" { BEGIN ISOLATION LEVEL REPEATABLE READ; } +step "s2u" { UPDATE users SET sometime = '1830-10-04' WHERE id = 1; } +step "s2c" { COMMIT; } + +permutation "s1b" "s2b" "s2u" "s2c" "s1u1" "s1u2" "s1c" +permutation "s1b" "s2b" "s2u" "s1u1" "s2c" "s1u2" "s1c" +permutation "s1b" "s2b" "s1u1" "s2u" "s2c" "s1u2" "s1c" +permutation "s1b" "s1u1" "s2b" "s2u" "s2c" "s1u2" "s1c" +permutation "s1b" "s1u1" "s2b" "s1u2" "s2u" "s2c" "s1c" +permutation "s1b" "s1u1" "s1u2" "s2b" "s2u" "s2c" "s1c" -- 2.40.0