Prevent MySQL deadlocks in your Rails application

Deadlocks are dreadful. They can be a minor annoyance or, depending on your application’s domain, can seriously affect functionality.

At Brightbox, many of our systems are highly concurrent and we’ve had our fair share of deadlocks. We’ve learnt a few lessons about preventing them and we’re hoping those lessons will be useful to others.

Before we delve into it, I recommend that you read our earlier post on Mysql Locking for busy web developer.

If you were too busy to read that guide, one thing to keep in mind is that acquiring an explicit lock via SELECT .. FOR UPDATE is not the only way you can lock a row in MySQL. Any record you update within a transaction is also locked until you commit the transaction (In this post we will call such locks implicit locks.)

What is a deadlock

In MySQL, a deadlock is when two transactions each hold a lock on a row that the other requires to continue working. This leaves the two transactions waiting on the other transaction to release the lock, which will obviously never happen since they’re both stuck waiting on the other (eventually MySQL times out one or both of the transactions and releases the locks).

The simplest approach is to lock as few rows as possible and for the shortest possible time.

It’s worth noting that you have to be using a transactional table engine for this to mean anything - if you’re still using MyISAM then you don’t have any problems. Or more accurately, deadlocks are the one kind of problem you don’t have ;)

So here we go:

Tip#1 - Keep your transactions short

This seems obvious. Locks typically get released at the end of a transaction, so keeping transactions short will definitely help. However, ActiveRecord lifecycle callbacks can extend a transaction without you realizing it.

Remember that ActiveRecord automatically starts a transaction for saving any record and all the ActiveRecord callbacks fire within that transaction. This can cause serious problems in your code.

For example, let’s say your app uploads the logo to a storage service before saving the record. Your code may look like:

class Profile
  before_save :update_logo
  def update_logo
    ... upload the thumbnail ..
  end
end

This means that the profile row is locked until the logo is uploaded. Performing a network operation within a transaction like this can keep rows locked a long time, exposing you to deadlocks. You should use after_commit for such long running tasks, to keep them outside of the transaction.

But thats not the only way ActiveRecord lifecycle callbacks can wreak havoc in your code. It is not uncommon for people to update related objects in those callbacks, for example:

class User
  after_save :set_permissions
  def set_permissions
    user.permissions.update_attributes!(admin: true)
  end
end

The above code will actually first acquire lock on the user object and then permission object within a transaction. If some other connection has a lock on permission object, it can deadlock. In other words, modifying a whole bunch of records in ActiveRecord callbacks is a recipe for creating unpredictable deadlocks.

To avoid these kind of problems (and overuse of AR callbacks in general is problematic), we have started to move entirely away from ActiveRecord callbacks.

Use form objects, interactors, service objects - whatever suits your fancy. Performing updates on records in a linear fashion without relying on callbacks will let you control the scope of a transaction, thereby avoiding deadlocks.

Tip#2 - Be orderly about locks your code is going to acquire.

If your transaction locks more than one record (explicitly or implicitly) then ensure the order in which rows are locked always remains the same.

For example, lets say you are working on an ecommerce application and your users are given coupon codes that are only applicable to certain items (which can be used only once by a user) and users should not be able to purchase out of stock items. Code may look something like:

def purchase(purchase_params)
  Item.transaction do
    # lets ensure nobody else can purchase selected item
    item = Item.available.where(item_id: purchase_params[:item_id]).lock(true)
    item.update_inventory
    order.add(item)
    # lets ensure discount cant be used elsewhere
    discount = Discount.unused.where(discount_id: purchase_params[:discount_id]).lock(true)
    process_payment
    discount.mark_discount_as_used
  end
end

As long as locks are acquired in same order on item and discount row, the code should not create deadlocks.

But lets take another example, you are working on a blog app and whenever a user updates a post, all posts needs to be republished. The code in question looks something like:

User.transaction do
  ..
  user.posts.order("posts.updated_at").each {|post| post.publish! }
  ..
end

The above code can cause deadlocks in a concurrent system because different transactions running at the same time can acquire lock on posts in a different order.

For example, one transaction may get user.posts.order("posts.updated_at") as [post1, post2, post3] while another transaction may get it as [post2, post1, post3]. Transaction#1 locks post1, while Transaction#2 locks post2, then Transaction#1 tries to lock post2 and is blocked, while Transaction#1 tries to lock post1 and is blocked, resulting in a classic deadlock!

Tip#3 - Acquire locks as soon as possible with a transaction

As we saw from previous example, sometimes it is not easy to guarantee order in which records will be locked or it may not be easy to reason about the order in which records will be locked.

In such cases, it is far safer to acquire locks as soon as possible.

The post updating code, for example, can be rewritten as:

User.transaction do
  # lets ensure nobody else can update these posts
  user.posts.lock(true)
  ..
  user.posts.order("posts.updated_at").each {|post| post.publish! }
  ..
end

This will ensure that two transactions will never deadlock updating those posts.

Though in many cases, it’s often better to just use update_all:

User.transaction do
  ..
  Post.where(user_id: user.id).update_all(state: 'published')
  ..
end

Tip#4 - Nested transactions don’t release locks

If you are starting a nested transaction and are acquiring a lock on a record within it, the lock will only be released when the outer transaction is committed because, with MySQL, nested transactions are just savepoints:

User.transaction do
  Post.transaction do
    post = Post.find(1).lock(true)
  end # lock on post will not be released here.
end

Tip#5 - Serialize your transactions with table locks.

We haven’t had to resort to this yet, but locking an entire table can eliminate deadlocks at the cost of reducing concurrency:

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

Which is a bit like a MyISAM-emulator - a pretty ugly thing to do, but sometimes you have to do ugly things.

That’s all I have for now, though there is more to come. Feel free to leave a comment below if I have missed something or shout on @brightbox

Remember, you can sign up for Brightbox in just a couple of minutes, build a server in less than 30 seconds and get £50 off your first month’s bill.