Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nX9K3-0005Jt-If for pgsql-novice@arkaria.postgresql.org; Wed, 23 Mar 2022 22:19:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nX9K2-0000gn-4H for pgsql-novice@arkaria.postgresql.org; Wed, 23 Mar 2022 22:19:18 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nX9K1-0000ge-LK for pgsql-novice@lists.postgresql.org; Wed, 23 Mar 2022 22:19:17 +0000 Received: from mail-ej1-x630.google.com ([2a00:1450:4864:20::630]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nX9Jz-0006LM-5K for pgsql-novice@lists.postgresql.org; Wed, 23 Mar 2022 22:19:16 +0000 Received: by mail-ej1-x630.google.com with SMTP id dr20so5646182ejc.6 for ; Wed, 23 Mar 2022 15:19:15 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=ZU7xurGKCeecL8fiB35DlvyOvo0gBMT0j9qUlKEWBVE=; b=eOaynnsgam/UFbweApcrkyxZ2DR3ilifSV729y75y3Xf5R1XxaI5ipqERO8LEixKZk BOCbrwsc7vVEhMkD2QCkMGE3Ztzrzx5gsEta0kTHGmt33qhJDIIzso1/xaxn05S9ByMX JJhg4xqu4W9BoUZ89MVLXl1TvVhPMc1M0myWtdxIHsMMnV2JfUojQG9JnYyNounzjPn7 rbjV5mBxcqbejLHojG4Cm1nFtRl+GStnPOxIfsSxAx17JcMukGbSfJZ2UjD0lzMnkuVf pA2Rc8Kvc3NYzkl5CJvMDIM+s4BPbFOVThvNusSJI+6X3Z1TZQ+UIAUYIJe/e6cLV4kq 7hNA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=ZU7xurGKCeecL8fiB35DlvyOvo0gBMT0j9qUlKEWBVE=; b=doghIYGp5BbyuUqg49fmPauSdwOMJ2JIHfXrKH9CCiNC4SV62AKg2P0JYIdPD5D5/W p1kSaL1ZTa5QzFEVIa++Um/UdomwqyVW5237083njr58E4SpEQYVR3xDXLwhUOSo3+2H LRBVZIXU/2ejZuh+XT0sCPsVXj5AcRDn/5IeQt97K8DwFQPliB+g6LhqLNI85cZdBeLz TvsRIrhZSdsJxwvxMmkvVmbslNjvZBiRtBqJZGkolnmRzf4W+zmUW3xbdFQhTKpye6Ob f0f+PcR2e6bn/sWrHadSFH3TsKOMrAKMFqSjz8vn0nmXtJurjd9XkofnnPKspcdnY9QU FBQA== X-Gm-Message-State: AOAM532viknRQlkXtvEZiXB80tQnnG+4xEg0RvCenDPWZksVa20sOBPM pSPBpPWV2PR2W/mIiMrvzS8Px8kAlE4XZh18Vuo= X-Google-Smtp-Source: ABdhPJw0jMpZeKvpmak5/ybYBj+O12eFEg/oHeFa9aDQ+Tw9Mqc7Ft3+wnpH8mvkvXHPdJBAM7EPUFCwvUQXa8dpC2A= X-Received: by 2002:a17:907:8a25:b0:6d7:d59:6912 with SMTP id sc37-20020a1709078a2500b006d70d596912mr2499769ejc.259.1648073953256; Wed, 23 Mar 2022 15:19:13 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: "David G. Johnston" Date: Wed, 23 Mar 2022 15:18:56 -0700 Message-ID: Subject: Re: SELECT FOR UPDATE on rows that don't exist To: Joe Cc: pgsql-novice Content-Type: multipart/alternative; boundary="0000000000008c2b1405daea1ef2" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000008c2b1405daea1ef2 Content-Type: text/plain; charset="UTF-8" On Wed, Mar 23, 2022 at 12:52 PM Joe wrote: > 1) What are the semantics of SELECT FOR UPDATE when the row doesn't exist > yet? > 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; https://www.postgresql.org/docs/current/locking-indexes.html "Short-term share/exclusive page-level locks are used for read/write access. Locks are released immediately after each index row is fetched or inserted. These index types provide the highest concurrency without deadlock conditions." But, once the insertion happens the second transaction sees the potentially conflicting record and so continues waiting to see whether it commits with the conflict in place (at which point it returns the duplicate key error) or not (in which case one of them then gets to proceed with their insertion). This manifests as a lock on the transactionid of the first session as is most easily seen in in pg_stat_activity wait_event/wait_event_type fields. I picked up the last point through my own experimentation, if there is documentation someone else will hopefully point it out. > 2) The DELETE @ #2 is so that the row is never present when not executing > in the critical section mainly so that #1 can be a simple insert rather > than an upsert. Is there a more standard pattern for this? > Not sure. But while definitely not standard, and somewhat limited, there is the advisory locks feature. https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS > 3) Using the DB as a distributed mutex seems like a common application > but nothing came up in various DB and PostgreSQL books I consulted or on > the web. Is this a bad idea, or are there gotchas I'm missing? > > For a job queue typically you'd fire off an update to claim the work - ensuring only one session gets the assignment - and another to indicate completion. The "critical section" is usually so long as to be harmful to the database to be performed while holding a transaction open specifically for locking purposes. I have to imagine there are better tools, especially in-memory ones, designed to handle distributed process coordination. That said, the goodness or badness of doing it in the database heavily depends on knowing more than just "distributed mutex". The critical section details, not just existence, factors into such a subjective evaluation. The fact you have a functioning database does make things appealing. David J. --0000000000008c2b1405daea1ef2 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Mar 23, 2022 at 12:52 PM Joe <symphony.red+pg@gmail.com> wrote:
1) What are the semantics of SELE= CT FOR UPDATE when the row doesn't exist yet?

You've informed the system you are going to be upd= ating rows on the table but as yet have not given it specific rows to prote= ct.

Without #*1, a simple experiment s= hows that two processes can be in the critical section at the same time. Ad= d #*1 seems to achieve the desired behavior, but is it really?
<= /blockquote>

The index will not allow duplicates to be ins= erted and the first one to try forces all other potential insertions to wai= t until the first one commits;

https://www.postgresql.or= g/docs/current/locking-indexes.html
"Short-term share/= exclusive page-level locks are used for read/write access. Locks are releas= ed immediately after each index row is fetched or inserted. These index typ= es provide the highest concurrency without deadlock conditions."
=

But, once the insertion happens the second transaction sees= the potentially conflicting record and so continues waiting to see whether= it commits with the conflict in place (at which point it returns the dupli= cate key error) or not (in which case one of them then gets to proceed with= their insertion).=C2=A0 This manifests as a lock on the transactionid of t= he first session as is most easily seen in in pg_stat_activity wait_event/w= ait_event_type fields.

I picked up the last point thro= ugh my own experimentation, if there is documentation someone else will hop= efully point it out.


2) The DELETE @ #2 is so= that the row is never present when not executing in the critical section m= ainly so that #1 can be a simple insert rather than an upsert. Is there a m= ore standard pattern for this?

=
Not sure.=C2=A0 But while definitely not standard, and somewhat limited,= there is the advisory locks feature.

https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIO= NS-ADVISORY-LOCKS


3)=C2=A0 Us= ing the DB as a distributed mutex seems like a common application but nothi= ng came up in various DB and PostgreSQL books I consulted or on the web. Is= this a bad idea, or are there gotchas I'm missing?


For a job queue typically you'd f= ire off an update to claim the work - ensuring only one session gets the as= signment - and another to indicate completion.=C2=A0 The "critical sec= tion" is usually so long as to be harmful to the database to be perfor= med while holding a transaction open specifically for locking purposes.=C2= =A0 I have to imagine there are better tools, especially in-memory ones, de= signed to handle distributed process coordination. That said, the goodness = or badness of doing it in the database heavily depends on knowing more than= just "distributed mutex".=C2=A0 The critical section details, no= t just existence, factors into such a subjective evaluation.=C2=A0 The fact= you have a functioning database does make things appealing.

David J.

--0000000000008c2b1405daea1ef2--