I have User, Order, Contest and Position models in my Rails app.
There are many-to-many relationship between User and Order and one-to-many between Position and Order, User and Contest.
I have order processor service with execute method which processes order. To prevent race condition I use transaction with lock! inside.
The problem is that when I'm invoking two or more execute method with the same order in argument at the same time sometimes I'm getting deadlock. The strangest thing is that first method starts execution while the second is awaiting for order to unlock. All as expected. But my first method (which is executing) is locking in the middle. And more stranger is that method is locking in lines where not any locked records are processed. Plus the line where method is executing forever can randomly change if I comment some unrelated lines and again sometimes (not always) it is locking on lines which are not changing or even not using any locked records.
Here is my execute method
user = order.user
contest = user.contest
ActiveRecord::Base.transaction do
# the second method awaiting here as excpected
order.lock!
# some processing stuff here like
order.executed = true
position = find_or_create_position(order)
position.lock!
# .where.not(id: order.id) just in case to ignore locked row
# but based on business logic it can't really be anyway.
#
# Plus it is not seems like the reason because
# if I comment this and the next lines deadlock occurs later
open_orders = user.orders.open.where.not(id: order.id)
# commonly method stops here
closed_orders = close_open_orders!(order, price, open_orders)
# method stops here if I comment two lines above
order.save!
# I left this line because as I remember once
# after some random changes like commenting some lines
# I got method stopped even here
commission = contest.commission * order.count
closed_orders.each { |closed_order| closed_order.save! }
# here some position processing and finally
position.save!
user.lock!
# and user processing
user.save!
end
By processing in comments I mean just assignment attributes to records.
So here is beginning of my close_open_orders! method
rest_count = order.count
# here it stops. each just stops without any iteration.
open_orders.each do |open_order|
But I don't think that the problem is in each statement because as I said I'm getting deadlock if I ignore this method.
And everything works ok when only one method is executing.
I can also attach pg logs but I don't see any deadlocks there: two selects of orders for update. than select for user.orders.open.where.not(id: order.id) and that's all. After that unrelated (or not?):
03:58:13 [21231-1] LOG: statement: set client_encoding to 'UTF8'
03:58:13 [21231-2] LOG: statement: set client_encoding to 'unicode'
03:58:13 [21231-3] LOG: statement: SET client_min_messages TO 'warning'
I can add some more pg logs if needed.
UPD I'm using sidekiq to execute jobs asynchronously
