How to make ActiveRecord ThreadSafe

Question

How can I make the following controller threadsafe in rails 4 with postgresql:

def controller_action
  if Model.exists(column_name:"some_value")
  else
    @model=Model.new(column_name:"some_value")
    @model.save
  end
end

I am running puma, so my concern is that if two threads run this controller at the same time, and a row doesn't exist with the specified value of column_name, two records will be created whereas I only want 1.

Postgres will lock the transactions so the first transaction will block the second transaction at the db layer. This is traditionally how this race condition is handled. — TheIrishGuy, Mar 05 '14 at 23:59
Your problem doesn't actually have anything to do with threads, you have a run of the mill race condition. The solution is to add a constraint inside the database and deal with the inevitable exceptions (i.e. put the logic in the database where it belongs). — mu is too short, Mar 06 '14 at 00:09
So let me make sure I understand this...one thread wins the race condition and executes Model.exists first; meanwhile the other thread is locked out. After @model.save postgresql removes the lock and the other thread sees that a row now exists with the desired value. If that is how it works, how does postgresql know when to remove the lock? Does it always wait for all of the logic in the controller to finish before lifting the lock? — kempchee, Mar 06 '14 at 02:23
@TheIrishGuy Nonsense. Concurrent updates are subject to locking. Concurrent inserts don't block each other. See http://stackoverflow.com/questions/17267417/how-do-i-do-an-upsert-merge-insert-on-duplicate-update-in-postgresql — Craig Ringer, Mar 06 '14 at 04:37

score 14 · Accepted Answer · edited May 23 '17 at 12:34

Contrary to the comments, concurrent inserts on the same table are entirely permissible in PostgreSQL, so there's a race condition here.

To make this safe you must have a unique constraint (or primary key) on column_name. Duplicate inserts will then throw an exception which you can catch and retry with an update.

If you don't have a unique constraint, then you must LOCK TABLE ... IN EXCLUSIVE MODE to prevent concurrent upserts. Or use one of the concurrency-safe methods described in:

How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?

score 0 · Answer 2 · answered Mar 06 '14 at 00:02

Assume that threads have a 1:1 relationship with connections. So if you have 100 threads that need to access the database, you should crank the connection pool up to 100. If you have more worker threads than connections, you need to use a Queue (or some other thread safe data structure) to manage communication between them.

How to make ActiveRecord ThreadSafe

2 Answers2

Linked