From: Oleksii Kliukin <alexk@hintbits.com>
To: Álvaro Herrera <alvherre@2ndquadrant.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: upgrades in row-level locks can deadlock
Date: Thu, 13 Jun 2019 15:42:53 +0200
Message-ID: <40AE3BAD-CE9B-4A34-9DB8-3017A66653C3@hintbits.com> (raw)
In-Reply-To: <20190612164649.GA10230@alvherre.pgsql>
References: <20190612164649.GA10230@alvherre.pgsql>
Hello,
Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> On 2019-Jun-12, Oleksii Kliukin wrote:
>
>> Thank you! I can make it even simpler; s1 just acquires for share lock, s3
>> gets for update one and s2 takes for share lock first, and then tries to
>> acquire for update one; once s1 finishes, s3 deadlocks.
>
> Cool. I think it would be worthwhile to include a number of reasonable
> permutations instead of just one, and make sure they all work correctly.
> I don't think we need to include all possible permutations, just a few.
> I think we need at least enough permutations to cover the two places of
> the code that are modified by the patch, for both values of
> have_current_xid (so there should be four permutations, I think).
Makes sense. For the symmetry I have included those that perform lock
upgrades in one session and those that doesn’t, while the other sessions
acquire locks, do updates or deletes. For those that don’t upgrade locks the
test checks that the locks are acquired in the correct order.
> Please don't simplify the table name to just "t" -- the reason I used
> another name is that we want these tests to be able to run concurrently
> at some point; ref.
> https://postgr.es/m/20180124231006.z7spaz5gkzbdvob5@alvherre.pgsql
Alright, thanks.
>
>>> But semantically, I wonder if your transactions are correct. If you
>>> intend to modify the row in s1 and s2, shouldn't you be acquiring FOR NO
>>> KEY UPDATE lock instead? I don't see how can s1 and s2 coexist
>>> peacefully. Also, can your Y transaction use FOR NO KEY UPDATE instead
>>> .. unless you intend to delete the tuple in that transaction?
>>
>> It is correct. I wanted to make sure jobs that acquire for key share lock
>> can run concurrently most of the time; they only execute one update at the
>> end of the job, and it is just to update the last run timestamp.
>
> I see. Under READ COMMITTED it works okay, I suppose.
>
>>> I'm mulling over your proposed fix. I don't much like the idea that
>>> DoesMultiXactIdConflict() returns that new boolean -- seems pretty
>>> ad-hoc -- but I don't see any way to do better than that ... (If we get
>>> down to details, DoesMultiXactIdConflict needn't initialize that
>>> boolean: better let the callers do.)
>>
>> I am also not happy about the new parameter to DoesMultiXactIdConflict, but
>> calling a separate function to fetch the presence of the current transaction
>> in the multixact would mean doing the job of DoesMultiXactIdConflict twice.
>> I am open to suggestions on how to make it nicer.
>
> Yeah, I didn't find anything better either. We could make things more
> complex that we resolve the multixact once and then extract the two
> sepraate bits of information that we need from that ... but it ends up
> being uglier and messier for no real gain. So let's go with your
> original idea.
Ok, the v4 is attached. I have addressed your suggestion for the isolation
tests, added a paragraph to README.tuplock explaining why do we skip
LockTuple to avoid a deadlock in the session that upgrades its lock.
Cheers,
Oleksii
Attachments:
[application/octet-stream] deadlock_on_row_lock_upgrades_v4.diff (14.2K, ../40AE3BAD-CE9B-4A34-9DB8-3017A66653C3@hintbits.com/2-deadlock_on_row_lock_upgrades_v4.diff)
download | inline diff:diff --git a/src/backend/access/heap/README.tuplock b/src/backend/access/heap/README.tuplockindex b2f3a4ce90..d7a085366a 100644--- a/src/backend/access/heap/README.tuplock+++ b/src/backend/access/heap/README.tuplock@@ -36,6 +36,17 @@ do LockTuple as well, if there is any conflict, to ensure that they don't
starve out waiting exclusive-lockers. However, if there is not any active
conflict for a tuple, we don't incur any extra overhead.
+We make an exception from the rule above for those lockers that already hold+some lock on a tuple and attempt to acquire a stronger one on it. We have to+let them skip the LockTuple() call even when there are conflicts, provided+that the target tuple is being locked, updated or deleted by multiple sessions+concurrently. If we didn't make such an exception we could have a deadlock,+e.g. between a session that has recorded its weaker lock in the tuple header+and is waiting on a LockTuple() call to upgrade to the stronger lock level and+another one that has already acquired the tuple-level lock from the+LockTuple() but at the same time waiting for the first session transaction to+finish due to a conflicting lock level.+
We provide four levels of tuple locking strength: SELECT FOR UPDATE obtains an
exclusive lock which prevents any kind of modification of the tuple. This is
the lock level that is implicitly taken by DELETE operations, and also by
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.cindex 8ac0f8a513..45100bab1d 100644--- a/src/backend/access/heap/heapam.c+++ b/src/backend/access/heap/heapam.c@@ -95,7 +95,7 @@ static void GetMultiXactIdHintBits(MultiXactId multi, uint16 *new_infomask,
static TransactionId MultiXactIdGetUpdateXid(TransactionId xmax,
uint16 t_infomask);
static bool DoesMultiXactIdConflict(MultiXactId multi, uint16 infomask,
- LockTupleMode lockmode);+ LockTupleMode lockmode, bool *has_current_xid);
static void MultiXactIdWait(MultiXactId multi, MultiXactStatus status, uint16 infomask,
Relation rel, ItemPointer ctid, XLTW_Oper oper,
int *remaining);
@@ -2547,15 +2547,22 @@ l1:
*/
if (infomask & HEAP_XMAX_IS_MULTI)
{
+ bool has_current_xid = false;
/* wait for multixact */
if (DoesMultiXactIdConflict((MultiXactId) xwait, infomask,
- LockTupleExclusive))+ LockTupleExclusive, &has_current_xid))
{
LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
- /* acquire tuple lock, if necessary */- heap_acquire_tuplock(relation, &(tp.t_self), LockTupleExclusive,- LockWaitBlock, &have_tuple_lock);+ /*+ * Acquire the lock, if necessary. Avoid acquiring it when+ * getting the stronger lock while holding a weaker one to+ * prevent deadlocks with other lockers that already have+ * a tuple lock and waiting for our transaction to finish.+ */+ if (!has_current_xid)+ heap_acquire_tuplock(relation, &(tp.t_self), LockTupleExclusive,+ LockWaitBlock, &have_tuple_lock);
/* wait for multixact */
MultiXactIdWait((MultiXactId) xwait, MultiXactStatusUpdate, infomask,
@@ -3126,15 +3133,23 @@ l2:
{
TransactionId update_xact;
int remain;
+ bool has_current_xid = false;
if (DoesMultiXactIdConflict((MultiXactId) xwait, infomask,
- *lockmode))+ *lockmode, &has_current_xid))
{
LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
- /* acquire tuple lock, if necessary */- heap_acquire_tuplock(relation, &(oldtup.t_self), *lockmode,- LockWaitBlock, &have_tuple_lock);+ /*+ * Acquire the lock, if necessary. Avoid acquiring it when+ * getting the stronger lock while holding a weaker one to+ * prevent deadlocks with other lockers that already have+ * a tuple lock and waiting for our transaction to finish.+ */++ if (!has_current_xid)+ heap_acquire_tuplock(relation, &(oldtup.t_self), *lockmode,+ LockWaitBlock, &have_tuple_lock);
/* wait for multixact */
MultiXactIdWait((MultiXactId) xwait, mxact_status, infomask,
@@ -4028,6 +4043,7 @@ l3:
uint16 infomask;
uint16 infomask2;
bool require_sleep;
+ bool skip_tuple_lock;
ItemPointerData t_ctid;
/* must copy state data before unlocking buffer */
@@ -4053,6 +4069,7 @@ l3:
if (first_time)
{
first_time = false;
+ skip_tuple_lock = false;
if (infomask & HEAP_XMAX_IS_MULTI)
{
@@ -4081,6 +4098,21 @@ l3:
result = TM_Ok;
goto out_unlocked;
}
+ else+ {+ /*+ * Avoid acquiring the tuple level lock.+ *+ * Otherwise, when promoting a weaker lock, we may+ * deadlock with another potential locker that has+ * acquired a conflicting row lock and waits for our+ * transaction to finish.+ *+ * Note that we will wait for the multixact+ * if required to avoid acquiring conflicting locks.+ */+ skip_tuple_lock = true;+ }
}
if (members)
@@ -4235,7 +4267,7 @@ l3:
if (infomask & HEAP_XMAX_IS_MULTI)
{
if (!DoesMultiXactIdConflict((MultiXactId) xwait, infomask,
- mode))+ mode, NULL))
{
/*
* No conflict, but if the xmax changed under us in the
@@ -4318,7 +4350,8 @@ l3:
* this arranges that we stay at the head of the line while
* rechecking tuple state.
*/
- if (!heap_acquire_tuplock(relation, tid, mode, wait_policy,+ if (!skip_tuple_lock &&+ !heap_acquire_tuplock(relation, tid, mode, wait_policy,
&have_tuple_lock))
{
/*
@@ -6516,10 +6549,12 @@ HeapTupleGetUpdateXid(HeapTupleHeader tuple)
* tuple lock of the given strength?
*
* The passed infomask pairs up with the given multixact in the tuple header.
+ * has_current_xid is set when not-NULL if the given multixact contains current+ * transaction.
*/
static bool
DoesMultiXactIdConflict(MultiXactId multi, uint16 infomask,
- LockTupleMode lockmode)+ LockTupleMode lockmode, bool *has_current_xid)
{
int nmembers;
MultiXactMember *members;
@@ -6540,15 +6575,24 @@ DoesMultiXactIdConflict(MultiXactId multi, uint16 infomask,
TransactionId memxid;
LOCKMODE memlockmode;
- memlockmode = LOCKMODE_from_mxstatus(members[i].status);+ if (result && (has_current_xid == NULL || *has_current_xid))+ break;- /* ignore members that don't conflict with the lock we want */- if (!DoLockModesConflict(memlockmode, wanted))- continue;+ memlockmode = LOCKMODE_from_mxstatus(members[i].status);
/* ignore members from current xact */
memxid = members[i].xid;
if (TransactionIdIsCurrentTransactionId(memxid))
+ {+ if (has_current_xid != NULL)+ *has_current_xid = true;+ continue;+ }+ else if (result)+ continue;++ /* ignore members that don't conflict with the lock we want */+ if (!DoLockModesConflict(memlockmode, wanted))
continue;
if (ISUPDATE_from_mxstatus(members[i].status))
@@ -6567,10 +6611,11 @@ DoesMultiXactIdConflict(MultiXactId multi, uint16 infomask,
/*
* Whatever remains are either live lockers that conflict with our
* wanted lock, and updaters that are not aborted. Those conflict
- * with what we want, so return true.+ * with what we want. Return true on the next loop iteration unless+ * we need to look for the current transaction among the multixact+ * members.
*/
result = true;
- break;
}
pfree(members);
}
diff --git a/src/test/isolation/expected/rowlock-upgrade-deadlock.out b/src/test/isolation/expected/rowlock-upgrade-deadlock.outnew file mode 100644index 0000000000..99d12b125b--- /dev/null+++ b/src/test/isolation/expected/rowlock-upgrade-deadlock.out@@ -0,0 +1,110 @@+Parsed test spec with 3 sessions++starting permutation: s1_share s2_for_update s3_share s3_for_update s1_rollback s3_rollback s2_rollback+step s1_share: select id from tlu_job where id = 1 for share;+id ++1 +step s2_for_update: select id from tlu_job where id = 1 for update; <waiting ...>+step s3_share: select id from tlu_job where id = 1 for share;+id ++1 +step s3_for_update: select id from tlu_job where id = 1 for update; <waiting ...>+step s1_rollback: rollback;+step s3_for_update: <... completed>+id ++1 +step s3_rollback: rollback;+step s2_for_update: <... completed>+id ++1 +step s2_rollback: rollback;++starting permutation: s1_keyshare s2_for_update s3_keyshare s1_update s3_update s1_rollback s3_rollback s2_rollback+step s1_keyshare: select id from tlu_job where id = 1 for key share;+id ++1 +step s2_for_update: select id from tlu_job where id = 1 for update; <waiting ...>+step s3_keyshare: select id from tlu_job where id = 1 for key share;+id ++1 +step s1_update: update tlu_job set name = 'b' where id = 1;+step s3_update: update tlu_job set name = 'b' where id = 1; <waiting ...>+step s1_rollback: rollback;+step s3_update: <... completed>+step s3_rollback: rollback;+step s2_for_update: <... completed>+id ++1 +step s2_rollback: rollback;++starting permutation: s1_keyshare s2_for_update s3_keyshare s3_delete s1_rollback s3_rollback s2_rollback+step s1_keyshare: select id from tlu_job where id = 1 for key share;+id ++1 +step s2_for_update: select id from tlu_job where id = 1 for update; <waiting ...>+step s3_keyshare: select id from tlu_job where id = 1 for key share;+id ++1 +step s3_delete: delete from tlu_job where id = 1; <waiting ...>+step s1_rollback: rollback;+step s3_delete: <... completed>+step s3_rollback: rollback;+step s2_for_update: <... completed>+id ++1 +step s2_rollback: rollback;++starting permutation: s1_share s2_for_update s3_for_update s1_rollback s2_rollback s3_rollback+step s1_share: select id from tlu_job where id = 1 for share;+id ++1 +step s2_for_update: select id from tlu_job where id = 1 for update; <waiting ...>+step s3_for_update: select id from tlu_job where id = 1 for update; <waiting ...>+step s1_rollback: rollback;+step s2_for_update: <... completed>+id ++1 +step s2_rollback: rollback;+step s3_for_update: <... completed>+id ++1 +step s3_rollback: rollback;++starting permutation: s1_share s2_update s3_update s1_rollback s2_rollback s3_rollback+step s1_share: select id from tlu_job where id = 1 for share;+id ++1 +step s2_update: update tlu_job set name = 'b' where id = 1; <waiting ...>+step s3_update: update tlu_job set name = 'b' where id = 1; <waiting ...>+step s1_rollback: rollback;+step s2_update: <... completed>+step s2_rollback: rollback;+step s3_update: <... completed>+step s3_rollback: rollback;++starting permutation: s1_share s2_delete s3_delete s1_rollback s2_rollback s3_rollback+step s1_share: select id from tlu_job where id = 1 for share;+id ++1 +step s2_delete: delete from tlu_job where id = 1; <waiting ...>+step s3_delete: delete from tlu_job where id = 1; <waiting ...>+step s1_rollback: rollback;+step s2_delete: <... completed>+step s2_rollback: rollback;+step s3_delete: <... completed>+step s3_rollback: rollback;diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_scheduleindex 889b4d827a..3b3bc10a65 100644--- a/src/test/isolation/isolation_schedule+++ b/src/test/isolation/isolation_schedule@@ -42,6 +42,7 @@ test: delete-abort-savept-2
test: aborted-keyrevoke
test: multixact-no-deadlock
test: multixact-no-forget
+test: rowlock-upgrade-deadlock
test: lock-committed-update
test: lock-committed-keyupdate
test: update-locked-tuple
diff --git a/src/test/isolation/specs/rowlock-upgrade-deadlock.spec b/src/test/isolation/specs/rowlock-upgrade-deadlock.specnew file mode 100644index 0000000000..7e0230c7f7--- /dev/null+++ b/src/test/isolation/specs/rowlock-upgrade-deadlock.spec@@ -0,0 +1,52 @@+# This test checks that multiple sessions locking a single row in a table+# does not deadlock each other, particularly when one of them upgrades its+# lock while the others are waiting.++setup+{+ drop table if exists tlu_job;+ create table tlu_job (id integer primary key, name text);++ insert into tlu_job values(1, 'a');+}+++teardown+{+ drop table tlu_job;+}++session "s1"+setup { begin; }+step "s1_keyshare" { select id from tlu_job where id = 1 for key share;}+step "s1_share" { select id from tlu_job where id = 1 for share; }+step "s1_update" { update tlu_job set name = 'b' where id = 1; }+step "s1_delete" { delete from tlu_job where id = 1; }+step "s1_rollback" { rollback; }++session "s2"+setup { begin; }+step "s2_for_update" { select id from tlu_job where id = 1 for update; }+step "s2_update" { update tlu_job set name = 'b' where id = 1; }+step "s2_delete" { delete from tlu_job where id = 1; }+step "s2_rollback" { rollback; }++session "s3"+setup { begin; }+step "s3_keyshare" { select id from tlu_job where id = 1 for key share; }+step "s3_share" { select id from tlu_job where id = 1 for share; }+step "s3_for_update" { select id from tlu_job where id = 1 for update; }+step "s3_update" { update tlu_job set name = 'b' where id = 1; }+step "s3_delete" { delete from tlu_job where id = 1; }+step "s3_rollback" { rollback; }++# test that s2 will not deadlock with s3 when s1 is rolled back+permutation "s1_share" "s2_for_update" "s3_share" "s3_for_update" "s1_rollback" "s3_rollback" "s2_rollback"+# test that update does not cause deadlocks if it can proceed+permutation "s1_keyshare" "s2_for_update" "s3_keyshare" "s1_update" "s3_update" "s1_rollback" "s3_rollback" "s2_rollback"+# test that delete does not cause deadlocks if it can proceed+permutation "s1_keyshare" "s2_for_update" "s3_keyshare" "s3_delete" "s1_rollback" "s3_rollback" "s2_rollback"+# test that sessions that don't upgrade locks acquire them in order+permutation "s1_share" "s2_for_update" "s3_for_update" "s1_rollback" "s2_rollback" "s3_rollback"+permutation "s1_share" "s2_update" "s3_update" "s1_rollback" "s2_rollback" "s3_rollback"+permutation "s1_share" "s2_delete" "s3_delete" "s1_rollback" "s2_rollback" "s3_rollback"
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: alexk@hintbits.com, alvherre@2ndquadrant.com, pgsql-hackers@lists.postgresql.org
Subject: Re: upgrades in row-level locks can deadlock
In-Reply-To: <40AE3BAD-CE9B-4A34-9DB8-3017A66653C3@hintbits.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox