0

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.

AnswerRex
  • 180
  • 1
  • 1
  • 11
  • 1
    You can't if for no other reason then rows in a table are not ordered. You will get a better answer if you provide an more complete explanation of the reasoning and desired end result for the process. Add as update to your question, – Adrian Klaver Feb 02 '22 at 19:21
  • 1
    That sounds like you need a persistent queue, not a table. – The Impaler Feb 02 '22 at 19:32

1 Answers1

3

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

Ramin Faracov
  • 3,032
  • 1
  • 2
  • 8
  • Thanks, I found about FOR UPDATE and FOR UPDATE SKIP by digging up docs of PG, and implemented it two days ago. Thanks, it would helpful for other newbies like me. – AnswerRex Feb 07 '22 at 17:15
  • Per [Postgres docs](https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-ROWS), this row CAN be selected by other sessions when they're doing a pure 'select'. That 'select' would be disallowed if it's also a 'select for update', etc. – ormu5 Jul 18 '23 at 11:46
  • @ormu5 - yes, for update locks row for other (other sessions) for updates. But simple select queries will be works. – Ramin Faracov Jul 19 '23 at 13:43