Postgres Lock(For Update) row level locking returns value post lock

Question

I am trying to implement rowlevel lock using below query

BEGIN;
    
SELECT * FROM public.student where student_code=1 For update;

The above query i ran in pgadmin.

Now through the application if i try to do a select the select returns a value. Ideally this should not

What am i doing wrong here?

If you want your selects to block each other, then both of them need to be "FOR UPDATE". — jjanes
– jjanes, Commented Oct 20, 2020 at 15:30

Laurenz Albe · Accepted Answer · 2020-10-20 10:50:39Z

2

This is a feature, not a bug; see the documentation:

Read Committed Isolation Level

[...]

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row. If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row. In the case of SELECT FOR UPDATE and SELECT FOR SHARE, this means it is the updated version of the row that is locked and returned to the client.

After all, that is the meaning of “READ COMMITTED” – you see that latest committed version of the row..

If you want to avoid that, you can use the REPEATABLE READ isolation level. Then you get read stability, so that you only see the same state of the database for the whole transaction. In that case, you will receive a serialization error, since the row version that can be locked (the latest one) is not the one you see.

answered Oct 20, 2020 at 10:50

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Learner Over a year ago

Okay the use case which i have is like i want to query based on some where clause. Post which no other transaction should happen on the table's row. Even select. Do we have such option?

Laurenz Albe Over a year ago

Can you translate that comment into English? I don't follow. If it is a lengthy explanation, please edit the question and add it there.

Collectives™ on Stack Overflow

Postgres Lock(For Update) row level locking returns value post lock

1 Answer 1

Read Committed Isolation Level

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Read Committed Isolation Level

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related