Pessimistic vs Optimistic Locking in Laravel

Introduction

When two or more users want to update the same record, a conflict may occur that is also known as a race condition. To prevent such a conflict, a pessimist system assumes the worst, i.e., updates always occur at the same time. Thus, it eliminates the race condition by locking the record. Pessimist systems typically rely on database locking facilities, for example InnoDB’s row-level lock.

On the other hand, an optimistic system assumes that although race conditions may occur, they are very rare. So, instead of locking the record on every access, it looks for indications that the users actually did try to update the record at the same time.

Both pessimistic and optimistic locking mechanism have special use cases which are beyond the scope of this article. Here, what we are interested in is to find out how these mechanisms behave for applications with high load of update requests.

Some frameworks have built-in support for both mechanisms. For example, with Rails and its ORM framework, Active Record, one can choose between Locking::Optimistic and Locking::Pessimistic definitions. However, Laravel, which is used in this article and also is highly influenced by Rails, lacks such capability¹. Therefore, we’ve also presented a simple implementation of optimistic locking here².


Application

To benchmark both mechanisms, we consider a simple application: a banking system consisting several accounts and each account having a balance. The system is supposed to provide an API, called transfer, that transfers an amount from one account to another. To prevent race conditions, which can cause transferring an insufficient fund or losing transferred amount on the receiving account, the system has to use a locking mechanism.

The pessimistic locking is easy to implement using DB transaction as below. In Laravel, all the DB operations between beginTransaction and commit are guaranteed to be performed atomically.

Pessimistic Transaction

And, the optimistic locking is, further, implemented as below. Note that SELECT and INSERT queries are read/write operations that do not require locks, in general. In this code snippet, the magic occurs inside while loops. All accounts have an updated_at timestamp which is updated automatically when a record appears in an UPDATE query. In each iteration, the loop ensures that the record is not updated between consequent SELECT and UPDATE queries by narrowing the update down with a WHERE clause on updated_at column. And when an intrusion is detected (i.e., no update occurs), the whole process is retried. This snippet considers no maximum iteration count, that is a bad practice, and implements no rollback mechanism. However, it is possible to implement those features with a slightly complex algorithm.

Optimistic Transaction

Benchmark

Both implementations are run using valet³ and MySQL database. Then, they are put into test using locust tool with the same initial data⁴ set on the same machine⁵.

The benchmark results show that both implementations reach around 19 RPS with 200 concurrent connections without a significant difference.

That being observed, the problem is that the transfers are executed so fast that leave a mere chance for race conditions to occur. So, for locks to be retained longer, we inject sleep(1) instruction into the code to force the transaction to be hold for one second. Now, the benchmark results are less obvious:

  • The optimistic locking results in about 6 RPS with 11% of requests failed.
  • The pessimistic locking results in about 3 RPS with 45% of requests failed.

To be concise, the benchmark shows a 100% more efficiency for optimistic locking than the pessimistic one and the requests are less likely to fail.

It is important to know the cause of the failures. The results indicate that for the optimistic locking, all of the failed requests are rejected by Nginx (i.e., there were no available php-fpm processes for the request). But, for the pessimist locking, about two-third of the failures are caused by MySQL reporting a lock-wait error (i.e., trying to lock an already locked row).


SUMMARY

Both pessimistic and optimistic locking mechanism have use cases that they fit in. But, for systems with high load of updates which hold locks for a significant time, the latter shows better efficiency. However, it must be noted that the higher RPS doesn’t necessarily means faster algorithm, since an important amount of requests are failed because of the lock-waits in pessimistic mechanism.


Next Step

This article represents a naive, error-prone implementation for optimistic locking. There are third-party libraries which have more features. The same benchmark can be performed by such implementations.

Also, here a small set of data is considered, because a small machine is used for the benchmark. A bigger data set on a more powerful maching can give a more precise result for real world applications.


¹ This implementation is for benchmarking purpose only and is not meant to be used in production. The source is accessible freely from github.

² Laravel coordinators have rejected proposals for optimistic lock support. However, it can be supported using third-party libraries.

³ Valet uses Nginx and php-fpm and other stuff for running a Laravel application.

⁴ We have used a set of only 10 accounts to make race conditions more likey to occur.

⁵ MacBook Pro, macOS High Sierra, Core i5 Processor, 8 GB Installed RAM, and 256 GB SSD Hard Drive