CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
dinomordelon
dinomordelon

Reputation: 1

Can a row be locked so it can't be read [using Rails/Postgres]?

I'm currently working on a rails API. I need to be able to lock a single row from a table so it can't be read before it is updated. I have to do this so the program doesn't retrieve information that is currently being used to make some calculations, thus generating incorrect results.

As I understand, ActiveRecord provides methods for implementing a lock with Postgres or MySQL. There's even a way for locking a whole table for reading, but I haven't been able to find anything regarding blocking a single row readability.

Someone recommended me to add a flag to my Model so from moment it is accessed it updates the record's column to in_use = true immediately. When the next query for retrieving that specific Model's instance is run it has to check for that flag to be false. My only fear is that there's a very small window of time before the record is committed that could lead to reading a row that is being used. Any other suggestions?

Upvotes: 0

Views: 1795

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324355

PostgreSQL does not provide a way to lock a row against all reads.

You can lock a row against reads by queries that use SELECT ... FOR SHARE or SELECT ... FOR UPDATE, but not against a plain SELECT.

If you control the read sites, you can as Doon suggested use SELECT ... FOR SHARE for the reader(s) you wish to block when the row is locked, and SELECT ... FOR UPDATE to lock the row.

Upvotes: 1

Related Questions