Multiple worker threads working on the same database - how to make it work properly?
datamapper, ruby, ruby-on-rails, sinatra
Solution
The key step is when you select an unlocked row from the database and mark it as locked. If you can do that safely then everything else will be fine.
2 ways I know of that can make this safe are pessimistic and optimistic locking. They both rely on your database as the ultimate guarantor when it comes to concurrency.
Pessimistic Locking
Pessimistic locking means acquiring a lock upfront when you select the rows you want to work with, so that no one else can read them. Something like
SELECT * from some_table WHERE ... FOR UPDATE
works with mysql and postgres (and possibly others) and will prevent any other connection to the database from reading the rows returned to you (how granular that lock is depends on the engine used, indexes etc - check your database's documentation). It's called pessimistic because you are assuming that a concurrency problem will occur and acquire the lock preventatively. It does mean that you bear the cost of locking even when not necessary and may reduce your concurrency depending on the granularity of the lock you have.
Optimistic Locking
Optimistic locking refers to a technique where you don't want the burden of a pessimistic lock because most of the time there won't be concurrent updates (if you update the row setting the locked flag to true as soon as you have read the row, the window is relatively small). AFAIK this only works when updating one row at a time
First add an integer column `lock_version` to the table. Whenever you update the table, increment `lock_version` by 1 alongside the other updates you are making. Assume the current `lock_version` is 3. When you update, change the update query to
update some_table set ... where id=12345 and lock_version = 3
and check the number of rows updated (the db driver returns this). if this updates 1 row then you know everything was ok. If this updates 0 rows then either the row you wanted was deleted or its lock version has changed, so you go back to step 1 in your process and search for a new row to work on.
I'm not a datamapper user so I don't know whether it / plugins for it provide support for these approaches. Active Record supports both so you can look there for inspiration if data mapper doesn't.
Problem
I have a database that has a list of rows that need to be operated on. It looks something like this: ``` id remaining delivered locked ============================================ 1 10 24 f 2 6 0 f 3 0 14 f ``` I am using DataMapper with Ruby, but really I think this is a general programming question that isn't specific to the exact implementation I'm using... I am creating a bunch of worker threads that do something like this (pseudo-ruby-code): ``` while true do t = any_row_in_database_where_remaining_greater_than_zero_and_unlocked t.lock # update database to set locked = true t.do_some_stuff t.delivered += 1 t.remaining -= 1 t.unlock end ``` Of course, the problem is, these threads compete with each other and the whole thing isn't really thread safe. The first line in the while loop can easily pull out the same row in multiple threads before they get a chance to get locked. I need to make sure one thread is only working on one row at the same time. What is the best way to do this?