Hows does MVCC handle two concurrent tasks trying to insert the same row? Example pseudo-code: > select row from table where... > if not found, > prepare row > insert row > else > update row > ... continue processing what happens if TASK1 inserts the row first, and continues processing (no commit yet), then TASK2 selects row (not found in its MVCC space), then tries to insert... would it get a duplicate key error? What if TASK1 later does a rollback? What's the best way to handle this? Using a "LOCK TABLE name IN EXCLUSIVE MODE" holds to end of transaction. This locks the whole table, limiting concurrency on other rows. I could not use a LOCK ROW as the row may not exist? (not sure how to use this yet either!) Would a PL/pgSQL stored procedure encapsulate this better? Specifically, my problem is using a table to count and limit the number of allowed transactions for a given key. The table would be used in different applications to perform the limit counting and checking. A commit immediately after this code may not be the best solution. I am getting a "Duplicate Key" error on my insert occasionally. I use Perl/DBI, FreeBSD 4.6.2, Postgres 7.2.3 (yes, upgrading soon!) Thanks, Allen ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
