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@xxxxxxxxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
