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

CREATE TABLE tunnel (
    username    integer UNIQUE, -- ranges from 1 to ~250.000
    password    varchar
);

I need to write a function to "allocate" an empty "tunnel" on a
prepopulated table where all fields, except username, IS NULL by default.

In essence this function would do;

UPDATE SET password = 'mysecret' WHERE username = ( SELECT username FROM
tunnel WHERE password IS NULL LIMIT 1 );

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?

--
Sten Daniel Soersdal

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

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

Sten Daniel Soersdal <netslists@gmail.com> writes:
> 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?

If you don't want it to fail on conflicts, don't use SERIALIZABLE.
Instead, use SELECT FOR UPDATE followed by UPDATE within a transaction.

            regards, tom lane

On Aug 5, 9:02 pm, netsli...@gmail.com (Sten Daniel Soersdal) wrote:
> Help?

SELECT FOR UPDATE:

http://www.postgresql.org/docs/8.2/static/sql-select.html#SQL-FOR-UPDATE-SHARE

-- Example --
CREATE OR REPLACE FUNCTION FOO(MYSECRET TEXT) RETURNS INT AS
$$
DECLARE
  U INT;
BEGIN
  SELECT USERNAME
  INTO U
  FROM TUNNEL
  WHERE PASSWORD IS NULL LIMIT 1 FOR UPDATE;
  UPDATE TUNNEL SET PASSWORD = MYSECRET WHERE USERNAME = U;
  RETURN U;
END;
$$ LANGUAGE PLPGSQL;