Re: Basic locking question

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


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/