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

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