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

--- 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;