Doesn't select ... for update only lock the rows returned in the select?
In my case, I'm worried about situations when no rows will be returned
and two separate transactions will try to insert the same key.
Joshua D. Drake wrote:
> Ben wrote:
>
>> I'm not very familiar with explicitly locking things in SQL, so this
>> may be a dumb question, but.....
>>
>> I've got a stored proc that takes a text key and returns an ID. If
>> the key isn't in a lookup table, it adds it and will return the
>> auto-generated serial number. If it's already there, it simply
>> returns the existing serial number.
>>
>> I'm concerned about multiple sessions calling this at the same time
>> with the same key. While I *could* just put in a unique constraint
>> and let things fail, that's hardly graceful. It seems I should be
>> able to lock the lookup table, but I don't really know what mode to
>> lock that table in. Will ROW EXCLUSIVE keep the same key from being
>> entered twice?
>
>
> You are probably looking for select for update:
>
>
> http://www.postgresql.org/docs/current/static/sql-select.html#SQL-FOR-UPDATE
>
>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>
>
>