How to get a self-conflicting row level lock?

From: Forest Wilkinson <fspam(at)home(dot)com>
To: pgsql-sql(at)hub(dot)org
Subject: How to get a self-conflicting row level lock?
Date: 2000-07-08 01:58:56
Message-ID: ck2dms0t8ab52edhqbn5oetl4v3s2majsa@4ax.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Philip Warner 2000-07-08 02:00:31 Re: Re: [SQL] Re: [GENERAL] lztext and compression ratios...
Previous Message Bruce Momjian 2000-07-08 01:54:54 Re: Re: [SQL] Re: [GENERAL] lztext and compression ratios...