Re: Simple Atomic Relationship Insert

Roxanne Reid-Bennett wrote:

> When you have a sequence of steps that need to be serialized across
> processes, choose or even create a table to use for locking

This can also be done with an advisory lock, presumably faster:
http://www.postgresql.org/docs/current/static/explicit-locking.html

DECLARE
lock_key int := hashtext(hometown_name);
BEGIN
SELECT pg_advisory_xact_lock(lock_key,0);
// check for existence and insert if it doesn't exist
END;

When several sessions try to insert the same town (or strictly speaking, with
the same hash), only one of them will be allowed to proceed, the others being
put to wait until the first one commits or rollbacks, and so on until every
session gets through. The lock is automatically released at the end of the
transaction. It makes no difference if the "check and insert" is crammed into
a single SQL statement or several statements in procedural style.

This technique is intended to work with the default "Read Committed"
isolation level, which allows the concurrent transactions to "see" the new
row inserted by the single other transaction that got the "it does not yet
exist" result in the check for existence, and proceeded to insert and
eventually commit.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org