CODEWITHSUNDEEP
postgresqlgo
Tilak Raj
Tilak Raj

Reputation: 1499

thread safe increment value in db

I have come across a problem, not sure how to implement it with DB. I have go lang on the application side.

I have product table with column assigned as last_port_used. I need to assign ports to services when someone hits an api. It need to increment the last_port_id by 1 against its product name.

one possible solution would have been to use redis server and sync this value over there. Since we dont have redis. I wanted to achieve the same by psql.

I read more about locks and i think i need ACCESS EXCLUSIVE lock. is this the right way to do it?

product

How to handle it concurrently properly?

Upvotes: 0

Views: 1172

Answers (1)

bobflux
bobflux

Reputation: 11581

You could do simply:

UPDATE products SET last_port_used = last_port_used+1
WHERE   id=...
    AND last_port_used < end_port
RETURNING *

This will perform the update in a thread-safe manner, and only if a port number is available (last_port_used < end_port) and return the assigned port.

If you need to lock the row, you can also use SELECT FOR UPDATE.

Upvotes: 1

Related Questions