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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
