Search Postgresql Archives

Re: MVCC and insert

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux