From: Forest Wilkinson <fspam@home.com>
To: pgsql-sql@hub.org
Subject: How to get a self-conflicting row level lock?
Date: Fri, 07 Jul 2000 18:58:56 -0700
Message-ID: <ck2dms0t8ab52edhqbn5oetl4v3s2majsa@4ax.com> (raw)
I have become maintainer of a program that uses PostgreSQL 6.5.2 for
database functionality. It is littered with code blocks that do the
following:
1. SELECT * FROM some_table WHERE foo = bar FOR UPDATE;
2. -- Choose a new value for some_field, which might or might not
be based on its original value.
3. UPDATE some_table SET some_field = new_value WHERE foo = bar;
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. This tells me that when
two processes execute the same code block concurrently, this can happen:
1. Process A selects the desired row for update.
It now has a copy of the original values in that row.
2. Process B does the same. (This is allowed because ROW SHARE MODE
locks do not conflict with each other.)
It now has a copy of the original values in that row.
3. Process A chooses a new value for the desired field, based on
the original value.
4. Process B does the same.
5. Process A updates the row with its new value, and exits.
6. Process B updates the row with its new value, overwriting the
changes made by process A.
Is it true that SELECT ... FOR UPDATE only acquires a ROW SHARE MODE lock,
and that it isn't self-conflicting?
How can I acquire a self-conflicting row level lock?
What is the proper way to perform operations like those I'm describing?
Thank you,
Forest
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: fspam@home.com, pgsql-sql@hub.org
Subject: Re: How to get a self-conflicting row level lock?
In-Reply-To: <ck2dms0t8ab52edhqbn5oetl4v3s2majsa@4ax.com>
References: <ck2dms0t8ab52edhqbn5oetl4v3s2majsa@4ax.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