Skip site navigation (1) Skip section navigation (2)

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

From: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
To: Sten Daniel Soersdal <netslists(at)gmail(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Atomic/locked "allocation" of table row?
Date: 2007-08-06 13:56:29
Message-ID: 737139.36885.qm@web31809.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
--- 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;

In response to

Responses

pgsql-novice by date

Next:From: Sten Daniel SoersdalDate: 2007-08-06 14:30:38
Subject: Re: Atomic/locked "allocation" of table row?
Previous:From: Rodrigo De LeónDate: 2007-08-06 03:44:29
Subject: Re: Atomic/locked "allocation" of table row?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group