Re: MVCC and insert

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[(at)]dcc(dot)uchile(dot)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