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