Re: functions, transactions, key violations

On Wed, 2008-06-04 at 18:21 -0400, Michael Glaesemann wrote:
> Yes, I saw the comment. I'm guessing I'm missing something wrt
> transaction isolation level or locking. Would I need to use
> SERIALIZABLE or some kind of locking? Is the function in the example
> any different than the following explicit transaction (with the UPDATE
> not affecting any rows)?

I think the best way to explain this is with a timeline of two
concurrent sessions, s1 and s2.

s1: BEGIN;
s2: BEGIN;
s1: UPDATE db SET b = data WHERE a = key; -- matches no rows
s2: UPDATE db SET b = data WHERE a = key; -- matches no rows
s1: INSERT INTO db(a,b) VALUES (key, data); -- inserts with a = key
s1: COMMIT;
s2: INSERT INTO db(a,b) VALUES (key, data); -- unique violation!

Notice that neither of the updates block, because neither match any
rows, so there is no conflict.

The exception handling in the loop in the example then retries s2
entirely, which then (correctly) updates the tuple rather than
inserting. There's some degenerate case, I suppose, when sessions are
perfectly synchronized with DELETEs such that it causes an infinite
loop, but that's a pretty unrealistic scenario.

SERIALIZABLE transactions don't really affect this, because the updates
still don't match any rows. Serializable transactions really only affect
the snapshot that you see and whether an UPDATE/DELETE causes a
serialization error (which can only happen if they match some rows).

The thing about a relation constraint (like UNIQUE) is that two
completely separate tuples can conflict with each other. That requires a
relation-level synchronization mechanism, because it can't assure that
the constraint is satisfied by examining tuples (or any proper subsets
of the relation) independently.

The general way to implement a relation constraint is by using LOCK
TABLE to prevent other concurrent sessions from interfering (as you
suggest above). This obviously has very bad performance, which is why
UNIQUE indexes provide another synchronization mechanism at the sub-
transaction level.

Regards,
Jeff Davis