Hi:
I'd like to know how PostgreSQL's transaction isolation mechanisms
interact with (e.g., UNIQUE) constraints. Section 12.2 of the manual
mentions that UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE
commands may block when a concurrent transaction updates a target row
(for both isolation levels, Read Committed and Serializable). But
suppose a table has a UNIQUE constraint on a column, and two
concurrent transactions attempt to INSERT a row with the same value
for that column:
o Will the "first" INSERT wait to see if the "second" aborts (as is
done with UPDATE, DELETE, et al.)?
o Or will it immediately abort?
o Or will it continue until immediately before commit, then abort?
It's not clear when the constraint check is run (or what version of
the table it sees).
Note this point isn't specific to INSERTs, another example would be
two concurrent transactions that UPDATE completely different rows and
in so doing violate a UNIQUE constraint.
Thanks.
--Cliff Krumvieda