Thread: How to get a self-conflicting row level lock?

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
hasa 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

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

> Is it true that SELECT ... FOR UPDATE only acquires a ROW 
> SHARE MODE lock, and that it isn't self-conflicting?  

SELECT FOR UPDATE acquires ROW SHARE LOCK on *table* level.
But rows returned by SELECT FOR UPDATE is locked *exclusively*
- so any other transaction which tries to mark the same row
for update (or delete/update it) will be blocked... and will
return *updated* row version after 1st transaction committed.

Vadim