public inbox for [email protected]  

help / color / mirror / Atom feed

From: Joe <[email protected]>
To: David G. Johnston <[email protected]>
Cc: pgsql-novice <[email protected]>
Subject: Re: SELECT FOR UPDATE on rows that don't exist
Date: Wed, 23 Mar 2022 22:31:47 -0700
Message-ID: <CAD9Bb3s9acbwQ5FuMZ8nKEv8Ys__OBXpdk_5i9WMXpEsdcyjfg@mail.gmail.com> (raw)
In-Reply-To: <CAKFQuwZ4=c7J-g5=t2veA2-w5tTi3oER34HJvYQ3QsaqSNqEig@mail.gmail.com>
References: <CAD9Bb3s3MN-iebRetMhNGs=sjnDwK4jw4BYG8+W9LeBbhi6HyA@mail.gmail.com>
	<CAKFQuwZ4=c7J-g5=t2veA2-w5tTi3oER34HJvYQ3QsaqSNqEig@mail.gmail.com>

David,
Thank you very much. I missed the advisory locks feature. That feels like
the perfect thing; the BIGINT keys are a bit clunky but that's minor.

Your description of the sequence of events for the two SELECT FOR UPDATE
transactions makes sense. To apply it to my pseudo code, the second
transaction would unblock when the row was deleted in the first rather than
when the first transaction commits or rolls back.

One question though:

You've informed the system you are going to be updating rows on the table
> but as yet have not given it specific rows to protect.
>
>>
>> Without #*1, a simple experiment shows that two processes can be in the
>> critical section at the same time. Add #*1 seems to achieve the desired
>> behavior, but is it really?
>>
>
> The index will not allow duplicates to be inserted and the first one to
> try forces all other potential insertions to wait until the first one
> commits;
>

Is this true in all situations? That is, will an index insertion in one
transaction block the index insertion in another transaction? Presumably
only only if the index entries match, but how does this work in practice?
Does the second transaction block when it sees the conflict? Or does it
proceed on its own version of the index proceeding until it tries to commit?

Thanks again,
Joe

view thread (3+ messages)

reply

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: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: SELECT FOR UPDATE on rows that don't exist
  In-Reply-To: <CAD9Bb3s9acbwQ5FuMZ8nKEv8Ys__OBXpdk_5i9WMXpEsdcyjfg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox