Re: table locking and SELECT FOR UPDATE

The situation I have is that there are some records in a table that need
to be updated, some of which don't exist and need to be created. So I
start a transaction and do a SELECT FOR UPDATE to lock the existing rows
and LOCK TABLE IN SHARE MODE to keep inserts from happening. The
problem is that one transaction can lock the rows, and the other
transaction locks the table, which leads to a deadlock.

Tom Lane wrote:
> Joseph Shraibman <jks(at)selectacast(dot)net> writes:
>
>>How come when a share lock is held and update can't be done on the
>>table, but a SELECT FOR UPDATE can be done? I can't SELECT FOR UPDATE
>>the same row in two transactions, but I can SELECT FOR UPDATE a row that
>>I will won't be able to update because the other table is held in a
>>SHARE lock.
>
>
> [ shrug... ] Prohibiting that buys nothing that I can see, and up until
> 8.1 would have actively broken useful functionality (since SELECT FOR
> UPDATE was the only way to lock individual rows, whether or not you had
> any intention of updating them).
>
> regards, tom lane