Pessimistic Locking ( Rails Internal )

Recently, I had the opportunity to interview Ben Sheldon, the creator and maintainer of GoodJob. You can listen at it here : https://share.transistor.fm/s/6bc570cc

I want to create an article to understand more about the gem we discover or rediscover in GemRubyShow.

GoodJob is ActiveJob Backend for your background Job. It works only with Rails and is backed by Postgres. It uses this interesting Pub/Sub mechanism of Postgres called LISTEN NOTIFY.

If you want to learn more about GoodJob, you can listen to the podcast. It was an excellent discussion with Ben Sheldon.

Locks in Rails

According to Postgres documentation: Advisory Locks are locks that have application-defined meanings. This is called Advisory Locks because it is up to you (and your app) to use them correctly. Advisory Locks avoid table bloat and are automatically cleaned up by the server at the end of the session.

There are two locking options in Rails :

• Optimistic Locking (activerecord/lib/active_record/locking/optimistic.rb)
• Pessimistic Locking (activerecord/lib/active_record/locking/pessimistic.rb)

Let's go with Pessimistic Lock for this article.

Pessimistic Lock

In Rails, pessimistic Lock is based upon row-level locking of SQL. It might be worth it to understand Row Level locking.

We have several levels of row locking from stronger to weaker: FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE.

We will only look for the FOR UPDATE keyword since it is the most used today and the default behavior in Rails.

But you can learn more about row-level locking in Postgres documentation. There is also this excellent article.

FOR UPDATE

With FOR UPDATE, the more robust row-level locking, You will have different behavior depending on your transaction isolation level.

If you are in READ COMMITTED, you will wait for the transaction to finish before getting the lock and be able to do the SELECT FOR UPDATE.

Nevertheless, if you have a higher level of Isolation READ REPEATABLE or SERIALIZABLE, you could have an ActiveRecord::SerializationFailure raised.

For example, in the following case :

Let's say you launch a transaction where you lock the first company, update the company's name, and do a bunch of stuff materialized by the sleep 10.

ActiveRecord::Base.transaction do
  p1 = Company.lock('FOR SHARE').first
  p1.name = 'Hey'
  sleep 10
  p1.save!
end

And meanwhile, you (or your colleague) trigger this transaction

ActiveRecord::Base.transaction do
  c1 = Company.first
  c1.name = 'Basecamp'
  p "Updating first company name to #{c1.name}"
  c1.save!
end
p 'new name'
p Company.first.name

In your terminals, you will see that the first transaction is processing while the second will wait for the row to be unlocked

##Waiting for the first transaction to be end
Updating first company name to Hey

REPEATABLE READ

Then, in another session and transaction, you try to get the first company, lock it, and update the name.

The difference is that you increase the transaction level with REPEATABLE READ, for example.

p1 = Company.first
ActiveRecord::Base.transaction(isolation: :repeatable_read) do
  p1.lock!
  p1.name = 'Hey'
  p1.save!
end

In this context, the error is raised when you try to acquire a lock on a row that has been modified since you have read it for the first time.

Rails stack trace

Rails implementation is simple because all the complexity is old by Postgres for the Row level locking.

Nonetheless, I find it interesting to dig into the Rails code and understand this framework more.

If we go down in the stack trace :

-lock
--reload
---_find_record
----lock (again, but another one)

So let's dive in the lock method :

def lock(locking = Arel.sql("FOR UPDATE"))
  case locking
      when true
        locking = Arel.sql("FOR UPDATE")
      when Arel::Nodes::SqlLiteral
      when String
        locking = Arel.sql locking
  end

  @ast.lock = Nodes::Lock.new(locking)
  self
end

This is pretty simple. We add FOR UPDATE at the end of our Query if no lock option is set.
And if there is one lock level we add it at the end of the SQL.

And that's it now you know a bit more about Advisory locks and Row-Level Locking in Postgres :)!

Conclusion

Now we know what advisory lock is with the pessimistic locking technique. But we also learn how to use the different Row-Level locking and what they are useful for.

Advisory locks are powerful and are extensively used in GoodJob. They are a good way to ensure Consistency in your database without degrading too much.

What could be interesting, though, is to understand the other row-level locking. And what they are useful for from my tests except for SELECT FOR UPDATE another one seems to not work as expected.