Skip site navigation (1) Skip section navigation (2)

Re: Atomic/locked "allocation" of table row?

From: Sten Daniel Soersdal <netslists(at)gmail(dot)com>
To: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Atomic/locked "allocation" of table row?
Date: 2007-08-06 14:30:38
Message-ID: 46B7308E.30908@gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Richard Broersma Jr wrote:
> --- Sten Daniel Soersdal <netslists(at)gmail(dot)com> wrote:
> 
>> But i need to know that no other concurrently running session would 
>> allocate that specific row in between the SELECT ... and the UPDATE.
>>
>> Could this be done atomically somehow? Is it necessary to add some kind 
>> of locking? Help?
> 
> If you have many sessions that are going to run this update statement and you want to ensure that
> they are all isolatated, you could wrap all of these sessions' update statements in transactions.
> Then any other sessions that try to update the same row will return an exception stating that
> another transaction modified the specific row.
> 
> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> 
> UPDATE Tunnel
>    SET password = 'mysecret' 
>  WHERE username = ( SELECT username 
>                       FROM Tunnel 
>                      WHERE password IS NULL 
>                      LIMIT 1 );
> COMMIT TRANSACTION;
> 

According to the manual it says that this would sometimes fail when 
"serializable" isolation cannot be obtained, is there any way to make it 
wait for it? Or do i have to wrap the transaction with an exception clause?

-- 
Sten Daniel Soersdal

In response to

Responses

pgsql-novice by date

Next:From: Luca FerrariDate: 2007-08-06 14:57:29
Subject: Re: doubt about datum
Previous:From: Richard Broersma JrDate: 2007-08-06 13:56:29
Subject: Re: Atomic/locked "allocation" of table row?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group