Introduction

Hi everyone, I am currently digging in SQL wizardly and try to in some way use it with rails. In this article I am digging a little bit more in the SELECT FOR UPDATE command in SQL.
SELECT FOR UPDATE enables you to ensure data consistency without rejection of your transaction, so I think it worth the work to learn it.

Let's go !

Transactional context

This kind of SQL queries are use in a transactional context. Where we want to have our ACID principles applied. I will not dig into that because it is not the subject but there is one letter in this which interest us. I will come back later on that you will see why.

So what it is a transaction in SQL ? pretty simple you could tell me. In a transaction you ensure that you perform all the modification in your queries and in a block to have a consistent state at the start and at the end, if one fails then all is rollback.

Nevertheless, something is missing and I'll show you what in the next part.

The needs for SQL FOR UPDATE

So I told you about a special letter, in our case, in the ACID anagram and it is the I. Why because it stands for Isolation.
Isolation enables you to ensure data consistency even when transaction are executed in parallel.
It ensures you that the database will have the same state as if the transaction had been executed sequentially.

There are several level of Isolation, from the weakest that guarantee consistency to the strongest we have :

1. Read Uncommitted: This isolation level allows a transaction to read data that has been modified by concurrent transactions but not yet committed. It provides the highest level of concurrency but also the lowest level of consistency and can lead to dirty reads. However, this isolation level is not supported in PostgreSQL.

2. Read Committed: This is the default isolation level in PostgreSQL, and it ensures that each query within a transaction sees only data committed before the query started. This level of isolation provides a balance between concurrency and consistency.

3. Repeatable Read: This isolation level ensures that all queries within a transaction see the same snapshot of data, even if concurrent transactions modify the data. It prevents non-repeatable reads but can lead to phantom reads.

4. Serializable: This is the strictest isolation level in PostgreSQL. It ensures that transactions executing concurrently produce the same result as if they were executed serially (one after the other). It prevents non-repeatable reads and phantom reads but can result in more transaction rollbacks due to serialization failures.

By default it is READ COMMITTED in Postgres. The higher level of Isolation is SERIALIZABLE will basically reject all others transaction trying to access the same row as a previous one already running. Though it is not totally what we could want, maybe you do not want your second transaction to be rejected but just to wait the end of the first transaction.

Coffee Order Jobs Example

Let's say you are a coffee selling company for roaster. People order on your website. When the people order coffee there is a background job that send an email notification to them.

Let's say they order another coffee in another order and for some reasons the 2 jobs are executing simultaneously. Then you could end with an inconsistent because the 2 jobs will try to modify the same user.

If you the highest Isolation level SERIALIZATION then you will reject the second query. But you will lose an order it is not what you want.

Here the good way to do it is to use SELECT FOR UPDATE which will wait for the end of the first transaction to execute

How can use it in Rails ?

When I have began to dig this subject, I don't know why ,I firstly thought that the Rails API should be complicated for this one. Not at all you can use the simple lock method after your model name like this:

class SendCoffeeOrderEmailJob < ActiveJob
  queue_as :default

  def perform(user_id:, order_id:)
    ActiveRecord::Base.transaction do
      user = User.where(id: user_id).lock(true)
      user.sendEmail(order_id)
    end
  end
end

Yes it is that simple. I am still not used to the simplicity brought by rails.

Conclusion

SELECT FOR UPDATE is really useful in SQL. When you are in transaction context often you should it to maintain data consistency and avoid conflicts.
It's also easy to use in Rails by using the lock method so take a look !

Keep in Touch

On Twitter : @yet_anotherDev