CODEWITHSUNDEEP
rubypostgresqlsequel
Max
Max

Reputation: 629

Multi-threading with Sequel gem

I have a program that is storing JSON request data into a Postgres DB using the Sequel gem (it's basically a price aggregator). However, the data is being pulled from multiple locations rapidly using threads.

Once I get the appropriate data, I currently have a mutex.synchronize with the following:

dbItem = Item.where(:sku => sku).first
dbItem = Item.create(:sku => sku, :name => itemName) if dbItem == nil
dbPrice = Price.create(:price => foundPrice, :quantity => quantity)
dbItem.add_price(dbPrice)
store.add_price(dbPrice)

If I run this without a mutex, I get threading issues - for example, the code will try to create an item in the DB, but the item will have just been created by another thread.

However, with the mutex, this code gets slowed down significantly - I'm seeing my program take ~four-six times longer.

I'm new to the whole database thing honestly, so I'm just trying to figure out the best way to handle threading. Am I doing this wrong? The documentation for Sequel actually states that almost everything threadsafe... except for model instances, which I believe my item situation falls under. It states I should freeze models first, but I don't know how to apply that here..

Upvotes: 0

Views: 690

Answers (1)

Jeremy Evans
Jeremy Evans

Reputation: 12149

You aren't dealing with shared model instances, and this isn't a thread-safety issue, it is a race condition (it would happen using multiple processes, not just multiple threads). You need to use database-specific support to handle this in a race-condition free way. On PostgreSQL 9.5+, you would need to use Dataset#insert_conflict.

Sequel documentation: http://sequel.jeremyevans.net/rdoc-adapters/classes/Sequel/Postgres/DatasetMethods.html#method-i-insert_conflict

PostgreSQL documentation: https://www.postgresql.org/docs/9.5/static/sql-insert.html

Upvotes: 2

Related Questions