Re: Atomic/locked "allocation" of table row? - Mailing list pgsql-novice

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