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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster

cliff@krumvieda.com writes: > 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: Whichever one manages to get to the index page first will go through. The second one will block waiting to see if the first one commits, and will error out if so --- or proceed, if it aborts. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly

Hi, Tom: >Whichever one manages to get to the index page first will go through. >The second one will block waiting to see if the first one commits, >and will error out if so --- or proceed, if it aborts. I see, this makes sense. What if the two transactions insert rows that don't violate the constraint: will they be able to proceed in parallel? Or will one wait for the other, because they both need to update the shared index? I.e., does the mechanism work by waiting for one index update to commit before permitting the next? --Cliff ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend