Thread: MVCC and insert

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

On Tue, May 03, 2005 at 12:28:05PM -0400, Allen wrote:
> 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?

Is there a unique index?  If there is, the index code will block.  You
can't see the row in your "mvcc space", so you don't have a way to know
beforehand whether the insertion would block or not.

I think the solution (only in 8.0) is:

:label
update
if rows updated == 0
   set a savepoint
   insert
   if it fails due to duplicate key
      rollback to savepoint
      restart at label

The real solution is MERGE, but we don't support that ATM.

> 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.

Sorry, I don't understand your scenario, can't help you further.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Before you were born your parents weren't as boring as they are now. They
got that way paying your bills, cleaning up your room and listening to you
tell them how idealistic you are."  -- Charles J. Sykes' advice to teenagers