Locking a specific row in postgres

Question

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 method level like @SqlUpdate (or some form of that - using org.skife.jdbi.v2) or in the query itself?

Answer 1

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.

Answer 2

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.