26

I'm new enough to Postgres, and I'm trying to figure out how to lock a specific row of a table.

As an example, I have a table of users:

Name: John, Money: 1
Name: Jack, Money: 2

In my backend, I want to select John and make sure that no other calls can update (or even select possibly) John's row until my transaction is complete.

I think I need an exclusive lock from what I've read up online? I can't seem to find a good example of locking just 1 row from a table online, any idea?

Edit - Should I be doing it at the method level like @SqlUpdate (or some form of that - using org.skife.jdbi.v2) or in the query itself?

funnydman
  • 9,083
  • 4
  • 40
  • 55
Greg
  • 271
  • 1
  • 3
  • 4

4 Answers4

27

If you want to lock the table in a specific selected row you need to LOCK FIRST them use the FOR UPDATE / FOR SHARE statement. For example, in your case if you need to lock the first row you do this:

BEGIN;

LOCK TABLE person IN ROW EXCLUSIVE MODE;

-- BLOCK 1

SELECT * FROM person WHERE name = 'John' and money = 1 FOR UPDATE;

-- BLOCK 2

UPDATE person set name = 'John 2' WHERE name = 'John' and money = 1;

END;

In the BLOCK1 before the SELECT statement you are doing nothing only telling the database "Hey, I will do something in this table, so when I do, lock this table in this mode". You can select / update / delete any row.

But in BLOCK2 when you use the FOR UPDATE you lock that row to other transactions to specific modes(read the doc for more details). Will be locked until that transaction ends.

If you need a example do a test and try to do another SELECT ... FOR UPDATE in BLOCK2 before end the first transaction. It will be waiting the first transaction to end and will select right after it.

Only an ACCESS EXCLUSIVE lock blocks a SELECT (without FOR UPDATE/SHARE) statement.

I am using it in a function to control subsequences and it is great. Hope you enjoy.

Gaspar
  • 1,515
  • 13
  • 20
  • This doesn't work, exclude mode doesn't prevent plain selects, see https://www.postgresql.org/docs/9.4/explicit-locking.html. I don't think there's a way to forbid pure selects. – chingis Aug 29 '19 at 06:15
  • "before end the first transaction. It will be waiting the first transaction to end and will select right after it." i'm certain of it, try it with `start transaction;` `do the lock;` open other console, start the transaction again and use `select ... for update` on that row, it will wait – Gaspar Sep 02 '19 at 17:40
  • 2
    I tried, doesn't matter what locks you set on the table and whether you use FOR UPDATE/SHARE, none of that will block plain select queries. Look at the table how for update/share selects lock each other, there's nothing saying about plain selects. And I think that's pretty logical, adding a select that will lock all other selects regardless of whether they used for something or not sounds like a bad practice. – chingis Sep 03 '19 at 06:03
  • 1
    there is a grey block in the link you sent saying exactly what is highlighted here: "Tip: Only an ACCESS EXCLUSIVE lock blocks a SELECT (without FOR UPDATE/SHARE) statement." and under it is what i said in my explanation "Once acquired, a lock is normally held till end of transaction."... – Gaspar Sep 03 '19 at 16:44
  • this is how tested https://pastebin.com/wHHYCYPe, the second transaction executed immediately without any 5 seconds timeout. PostgreSQL 11. – chingis Sep 04 '19 at 05:09
  • to see the lock run the lines 2, 3, 4, open other console, run line 9 – Gaspar Sep 05 '19 at 13:17
  • 2
    Select for update works in postgres 12 without `LOCK TABLE person IN ROW EXCLUSIVE MODE;` – Hemil Aug 12 '20 at 10:30
  • For those looking to lock a specific row, check your real need. Read the @a_horse_with_no_name reply. Sometimes it's not even necessary. – Gaspar Mar 01 '21 at 12:36
  • I do not see the difference between the statement sequence above versus without the first statement `LOCK... MODE`. Doesn't using the `FOR UPDATE` in the first block inherently lock the row without the need to use the `LOCK` statement beforehand? – Alexi Theodore Dec 23 '22 at 00:09
5

As soon as you update (and not commit) the row, no other transaction will be able to update that row.

If you want to lock the row before doing the update (which seems useless), you can do so using select ... for update.

You can not prevent other sessions from reading that row, and frankly that doesn't make sense either.

Even if your transaction hasn't finished (=committed) other sessions will not see any intermediate (inconsistent) values - they will see the state of the database as it was before your transaction started. That's the whole point of having a relational database that supports transactions.

  • 2
    Thanks. Not sure how it's useless though. In my backend I want to update the value of "Money". So my idea was: to select with a lock, do some checks, add a value to "Money" and then finally update it. I can't let another call see the value of "Money" while I'm still doing some checks really can I? – Greg Jun 23 '18 at 16:09
  • 2
    @Greg: why do you think you can't let other transactions see the "old" value? They won't be able to update the row and they won't see an inconsistent state of the database. –  Jun 23 '18 at 16:44
  • @a_horse_with_no_name when you have a lot of transactions async and that async method do a select, pick some data to validate and update in this little time it can be the old value becaus of other transactions. I don't use the lock i answered anymore, don't make sense in my model anymore, but that's a example when it can be used. But yeah, you should not select a value that will be changed but select the logic field and change the target filed instead – Gaspar Aug 15 '19 at 13:11
  • There are use cases, in my case I have a server where clients query for the next available work unit. I then update the work unit row to denote which client is assigned to it. I don't want to assign the same work unit to multiple users, so I `select ... for update` to avoid this race condition. – NeuroXc May 03 '21 at 22:38
2

You can use

LOCK TABLE table IN ACCESS EXCLUSIVE MODE;

when you are ready to read from your table. "SELECT" and all other operations will be queued until the end of the transaction (commit changes or rollback).

Note that this will lock the entire table and referring to PostgreSQL there is no table level lock that can lock exclusively a specific row.

So you can use

FOR UPDATE

row level lock in all your SELECT that will update your row and this will prevent all those SELECT that will update a row from reading your row !

PostgreSQL Documentation :

FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these rows will be blocked until the current transaction ends; conversely, SELECT FOR UPDATE will wait for a concurrent transaction that has run any of those commands on the same row, and will then lock and return the updated row (or no row, if the row was deleted). Within a REPEATABLE READ or SERIALIZABLE transaction, however, an error will be thrown if a row to be locked has changed since the transaction started. For further discussion see Section 13.4.

The FOR UPDATE lock mode is also acquired by any DELETE on a row, and also by an UPDATE that modifies the values on certain columns. Currently, the set of columns considered for the UPDATE case are those that have a unique index on them that can be used in a foreign key (so partial indexes and expressional indexes are not considered), but this may change in the future.*

abdelgrib
  • 843
  • 7
  • 11
2

I'm using my own table, my table name is paid_properties and it has two columns user_id and counter.

As you want one transaction at a time, so you can use one of the following locks:

  • FOR UPDATE mode assumes a total change (or delete) of a row.
  • FOR NO KEY UPDATE mode assumes a change only to the fields that are not involved in unique indexes (in other words, this change does not affect foreign keys).

The UPDATE command itself selects the minimum appropriate locking mode; rows are usually locked in the FOR NO KEY UPDATE mode.

To test it run following query in one tab (I'm using pgadmin4):

BEGIN;
SELECT * FROM paid_properties WHERE user_id = 37 LIMIT 1 FOR NO KEY UPDATE;
SELECT pg_sleep(60);
UPDATE paid_properties set counter = 4 where user_id = 37;
-- ROLLBACK; -- If you want to discard the operations you did above
END;

And the following query in another tab:

UPDATE paid_properties set counter = counter + 90 where user_id = 37;

You'll see that you're the second query will not be executed until the first one finishes and you'll have an answer of 94 which is correct in my case.

For more information: https://postgrespro.com/blog/pgsql/5968005 https://www.postgresql.org/docs/current/explicit-locking.html

Hope this is helpful

Muhammad Ahmed Hassan
  • 369
  • 3
  • 6