SHARED locks barging behaviour

From: Arul Ajmani <arula(at)cockroachlabs(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: SHARED locks barging behaviour
Date: 2023-01-17 17:18:28
Message-ID: CANxv=LFB9v10Ftt=yD7-RBSOYzCO8_s8VThf39KgF1ZDafYUCA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I'm trying to better understand the following barging behaviour with SHARED
locks.

*Setup:*

postgres=# create table t(a INT);
CREATE TABLE
postgres=# INSERT INTO t VALUES(1);
INSERT 0 1

Then, performing the following operations in 3 different sessions, in
order, we observe:

Session 1 Session 2 Session 3
BEGIN;
BEGIN
postgres=*# SELECT * FROM t WHERE a = 1 FOR SHARE;
a
---
1
(1 row)
postgres=# BEGIN;
BEGIN
postgres=*# SELECT * FROM t WHERE a = 1 FOR UPDATE;

* --- waits
BEGIN;
BEGIN
postgres=*# SELECT * FROM t WHERE a = 1 FOR SHARE;
a
---
1
(1 row)

* -- returns immediately

Given there is a transaction waiting to acquire a FOR UPDATE lock, I was
surprised to see the second FOR SHARE transaction return immediately
instead of waiting. I have two questions:

1) Could this barging behaviour potentially starve out the transaction
waiting to acquire the FOR UPDATE lock, if there is a continuous queue of
transactions that acquire a FOR SHARE lock briefly?
2) Assuming this is by design, I couldn't find (in code) where this
explicit policy choice is made. I was looking around LockAcquireExtended, but
it seems like the decision is made above this layer. Could someone more
familiar with this code point me at the right place?

Thanks

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Melanie Plageman 2023-01-17 17:22:14 Re: pg_stat_bgwriter.buffers_backend is pretty meaningless (and more?)
Previous Message Andres Freund 2023-01-17 17:02:40 Re: Sampling-based timing for EXPLAIN ANALYZE