CODEWITHSUNDEEP
postgresqltransactions
WynDiesel
WynDiesel

Reputation: 1214

Lock row, release later

I'm trying to understand how to lock a row, and only release that lock later.

I have a table like this :

create table testTable (Name varchar(100));

Some test data

insert into testTable (name) select 'Bob';
insert into testTable (name) select 'John';
insert into testTable (name) select 'Steve';

Now, I want to select one of those rows, and prevent other other queries from seeing this row. I achieve that like this :

begin transaction;
select * from testTable where name = 'Bob' for update;

In another window, I do this :

select * from testTable for update skip locked;

Great, I don't see 'Bob' in that result set. Now, I want to do something with the primary retrieved row (Bob), and after I did my work, I want to release that row again. Simple answer would be to do : commit transaction

However, I am running multiple transactions on the same connection, so I can't just begin and commit transactions all over the show. Ideally I would like to have a "named" transaction, something like :

begin transaction 'myTransaction';
select * from testTable where name = 'Bob' for update;
//do stuff with the data, outside sql then later call ...
commit transaction 'myTransaction';

But postgres doesn't support that. I have found "prepare transaction", but that seems to be a pear-shaped path I don't want to go down, especially as these transaction seem to persist through restarts even.

Is there anyway I can have a reference to commit/rollback for a specific transaction?

Upvotes: 0

Views: 807

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248030

You can have only one transaction in a database session, so the question as such is moot.

But I assume that you do not really want to run a transaction, you want to block access to a certain row for a while.

It is usually not a good idea to use regular database locks for such a purpose (the exception are advisory locks, which serve exactly that purpose, but are not tied to table rows). The problem is that long database transactions keep autovacuum from doing its job.

I recommend that you add a status column to the table and change the status rather than locking the row. That would server the same purpose in a more natural fashion and make your problem go away.

If you are concerned that the status flag might not get cleared due to application logic problems, replace it with a visible_from column of type timestamp with time zone that initially contains -infinity. Instead of locking the row, set the value to current_timestamp + INTERVAL '5 minutes'. Only select rows that fulfill WHERE visible_from < current_timestamp. That way the “lock” will automatically expire after 5 minutes.

Upvotes: 2

Related Questions