pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Forest Wilkinson <fspam@home.com>
Cc: pgsql-sql@hub.org
Subject: Re: How to get a self-conflicting row level lock?
Date: Sat, 08 Jul 2000 00:40:17 -0400
Message-ID: <23719.963031217@sss.pgh.pa.us> (raw)
In-Reply-To: <ck2dms0t8ab52edhqbn5oetl4v3s2majsa@4ax.com>
References: <ck2dms0t8ab52edhqbn5oetl4v3s2majsa@4ax.com>

Forest Wilkinson <fspam@home.com> writes:
> I'm worried about concurrent process synchronization.  According to the
> PostgreSQL docs on the LOCK command, SELECT ... FOR UPDATE acquires a "ROW
> SHARE MODE" lock, which is not self-conflicting.

That doc is only telling part of the story, I guess.  ROW SHARE MODE
lock on a *table* is not self-conflicting, and should not be, because
you want two different transactions to be able to acquire FOR UPDATE
locks on different rows of the table concurrently.  But SELECT FOR
UPDATE also acquires a *per-tuple* lock on each selected row, and
that lock will prevent another transaction from acquiring a FOR UPDATE
lock on the same row(s).  Try it and see.

The reason SELECT FOR UPDATE acquires a table-wide lock at all is
just to prevent table-wide conflicts, such as someone else trying
to do a VACUUM or DROP TABLE on that table.  I suspect that it
doesn't really need to get a different kind of table lock than an
UPDATE gets, but haven't thought about the details.

			regards, tom lane



view thread (3+ messages)

Message-ID: <23719.963031217@sss.pgh.pa.us>
Permalink:  ../23719.963031217@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/23719.963031217@sss.pgh.pa.us

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, fspam@home.com, pgsql-sql@hub.org
  Subject: Re: How to get a self-conflicting row level lock?
  In-Reply-To: <23719.963031217@sss.pgh.pa.us>
  References: <ck2dms0t8ab52edhqbn5oetl4v3s2majsa@4ax.com> <23719.963031217@sss.pgh.pa.us>

* 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