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

Richard Broersma Jr wrote:
> --- Sten Daniel Soersdal <netslists@gmail.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