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
