CODEWITHSUNDEEP
ruby-on-rails-3postgresqlactiverecord
David Morales
David Morales

Reputation: 18064

How to lock some rows in PostgreSQL when updating and let others to read?

I want to update some rows in a table, and I want to lock them so other processes don't update them as well at the same time. But I'd like other processes to read them.

The way I'm trying to accomplish it is like this:

MyTable.where(mycolumn: "some value").lock(true).update_all(mycolumn: "other value")

I think that lock(true) is locking the selected rows (it's what the documentation says), but I don't know if they are available for other selects at the same time. So I think I should force a "SHARE MODE".

I'm trying this:

MyTable.where(mycolumn: "some value").lock("LOCK IN SHARE MODE").update_all(mycolumn: "other value")

But it raises this error:

STATEMENT:  SELECT  "my_table".* FROM "my_table"  WHERE "my_table"."mycolumn" = $1 LIMIT 1 LOCK IN SHARE MODE
ERROR:  syntax error at or near "LOCK" at character 74

It works on SQLite though.

Upvotes: 1

Views: 1465

Answers (2)

filiprem
filiprem

Reputation: 7124

I want to update some rows in a table, and I want to lock them so other processes don't update them as well at the same time. But I'd like other processes to read them.

This is guaranteed automatically. It looks like you are trying to solve non-existent problem :-)

Other processes don't update until first update is finished.

In postgres, reads are NEVER blocked by writes.

Upvotes: 2

Amitabh Kant
Amitabh Kant

Reputation: 145

I don't know anything about RoR, but for PostgreSQL, I don't think the SQL statement in your error message would work. Locks in PostgreSQL are generally done via: SELECT * from [YOUR_TABLE] where [CONDITION] FOR UPDATE

See this page for more documentation: http://www.postgresql.org/docs/9.2/static/explicit-locking.html

Upvotes: 1

Related Questions