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

Forest Wilkinson <fspam(at)home(dot)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