4 min read

Database Transactions and Distributed Locks

Database Transactions and Distributed Locks

Race conditions are something I rarely think about when I'm developing web applications.

This is primarily because when I think of race conditions, my mind automatically jumps to managing threads, which frankly does happen with Android development at times, but rarely in Android development either as long as you utilize the libraries provided by Google.

However, race conditions can happen in web development as well, especially when multiple users are trying to access the same record in the database and modifying it at the same time. This means that you have to ensure that your code can cope with concurrent data access. The standard tools that we use in web development to deal with concurrent data access are database transactions and distributed locks.

In this post, I'll cover what transactions and distributed locks are, and when to use either of them.

** The sample code is written in Rails, but the concept can be applied to any database backed applications.**

Transactions

Let's take a look at a typical create action in ActiveRecord.

user = User.find(1)
user.products.create!(name: "First product")

The above code creates a new product for a single user. This code is fine, since it'll only affect one user if it fails. If it fails, you'll probably get notified by your bug tracking tool and address it individually. Now, let's look at some more code that works with a user and related products that are associated with the user.

# Initial setup of 5 products for the user
user = User.find(1)
5.times { |i| user.products.create(name: "Product #{i}") }

# This is the real meat of the code that we want to focus on
user2 = User.find(2)
user.products.each { |product| user2.products << product }

At first glance, the above code looks completely fine. But, it has issues.

  1. What if one of the products of user fails to be added to the user2's list of products in the middle of the loop?
  2. What if some other process comes in and runs the code for adding the list of products to user2's list of products while the first process is running?

If either of the two happens, the code that we've written won't have its intended affect. In fact, the dataset that we end up with for user2 will make our head spin as we debug what happened to user2's records.

This is where we want to use transactions. Transactions is just a general database concept that we can utilize easily in ActiveRecord. All ActiveRecord classes have the transaction method that you can wrap your database calls in. Let's wrap our loop inside a transaction like this.

# Initial setup of 5 products for the user
user = User.find(1)
5.times { |i| user.products.create(name: "Product #{i}") }

# This is the real meat of the code that we want to focus on
User.transaction do
  user2 = User.find(2)
  user.products.each { |product| user2.products << product }
end

Wrapping our code in a transaction will execute the code in a single operation. If any part of the code inside the transaction fails, the entire operation will be rolled back. In our specific example, this has the benefit of ensuring that all of user's products will be copied over to user2's list of products.

The gist of this whole example is, if you're ever in a situation where you're affecting more than one row in a database at a time, group your code together inside a transaction.

When transaction can go wrong

Transactions sound great right? It is until you have two or multiple processes running the same transaction at the same time accessing the same dataset. What happens in this situation is that the two (or multiple) processes running the same transaction will commence at the same time, and make their database commits regardless of the actions committed by the other processes. If we're working with transactions that update existing records, this can easily create race conditions that can create unpredictable results in our dataset.

Distributed locks

We can remedy the problem of concurrent data access with distributed locks. What locks do is ensure that the locked code only runs in a single thread at a time. This means, that no two concurrent processes can run the same piece of code at the same time, preventing race conditions. In Rails, we have a convenient method that we can call on ActiveRecord objects to lock them.

user = User.find(1)
user.lock!

In Rails, this is referred to as Pessimistic locking.

Let's see the difference between calling transaction vs with_lock on an ActiveRecord object.

user = User.find(1)

user.transaction { puts "== locked?" }
  (0.2ms)  BEGIN
== locked?
  (0.4ms)  COMMIT
=> nil

user.with_lock{ puts "== locked!" }
(0.2ms)  BEGIN
  User Load (1.8ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 FOR UPDATE  [["id", 6], ["LIMIT", 1]]
== locked!
(1.9ms)  COMMIT
 => nil

If you look, the transaction method simply opens a begin / commit transaction while with_lock method reloads the record with for update added to the SQL, which prevents any other database connections from loading the record for modification.

So what do I do with this information?

If you've ever ran into an issue where you suspected race condition was the culprit, I would begin by inspecting the suspected parts of your codebase where you're making database calls and wrap hem properly in a transaction or lock records properly.

If you are working on a large production application, it probably doesn't make sense to go through all of your code that makes database calls and determine whether that line of code is the culprit. Instead, anytime you get a bug report where the user is asking questions that goes something like "why did this happen to my data?", see if your code is written in a way where concurrent data access could be the culprit.