CODEWITHSUNDEEP
sqldatabasepostgresqlrowlocking
AnswerRex
AnswerRex

Reputation: 210

Postgres row locking

I am new to Postgres(even to SQL). I am building a key DB which has a table named Key. My question is how can I perform the following on pg.

If one connection is reading the first record and simultaneously the second connection comes in, it should read 2nd record rather than 1st. Its the same thing for the 3rd 4th and so on.

Upvotes: 0

Views: 4095

Answers (1)

Ramin Faracov
Ramin Faracov

Reputation: 3303

You can do it using select for update. 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.

ATTENTION:

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)

Now I will write for you query samples and explain how to do it: Suppose we have one such table:

Table name: key_table

key     is_used
00001   true 
00002   true 
00003   false 
00004   false 
00005   false 

select key from key_table
where 
    is_used = false 
order by key desc
limit 1 for update
into v_key; 

update key_table
set
    is_used = true
where key = v_key;

After the select command selected row will be locked. And this row cannot be selected by user in any other session until be updated. All users will be waiting for this update when selecting this row. After update command users can be shown next row which is_used = false

Upvotes: 3

Related Questions