Pessimistic vs Optimistic Locking in Laravel
Introduction
When two or more clients want to update the same record, a conflict may occur which is known as a race condition. To prevent such a conflict, a pessimist system assumes the worst, i.e., the updates always occur at the same time. Thus, it eliminates the race condition by locking the record. Pessimist systems typically rely on the 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. Therefore, instead of locking the record on every access, it looks for indications which denote the clients 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 loads 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. However, Laravel, which is used in this article—and also, is highly influenced by Rails, lacks such a capability.¹ Therefore, we’ve also presented here a simple implementation of optimistic locking.²
Application
To benchmark both mechanisms, we consider a simple application: a banking system consisted of several accounts, and each account having a balance. The system is supposed to provide an API, called transfer, which transfers a given amount from one account to the another. To prevent race conditions, which can cause transferring an insufficient fund or losing the transferred amount on the receiving account, the application 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 committed atomically.
And, the optimistic locking is, further, implemented as below. Note that the SELECT and INSERT queries are read/write operations which do not require locks, in general. In this code snippet, the magic occurs inside the 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 by narrowing the update down with a WHERE clause on the updated_at column. When an intrusion is detected (i.e., no update occurs), the whole process is retried. (Note: this snippet considers no maximum iteration count, that is a bad practice, and implements no rollback. However, it is possible to implement those features with a slightly complex algorithm.)
Benchmark
Both implementations are run using valet³ and MySQL database. Then, they are put into test using locust with the same initial dataset⁴ 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, there is a problem 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 a 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 approach results in about 6 RPS with 11% of the requests being failed.
- The pessimistic approach results in about 3 RPS with 45% of the requests being 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 investigate the cause for 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). On the other hand, 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 mechanisms have use cases that they fit in, however, for systems with high loads of updates which hold locks for a significant time interval, the latter shows better efficiency. Also, it must be noted that the higher RPS doesn’t necessarily means a faster algorithm, since an important amount of requests are failed because of the lock-wait errors in the pessimistic mechanism.
Next Step
This article represents a naive, error-prone implementation for optimistic locking. There are third-party libraries which provide a more robust implementation with more features. The same benchmark can be performed by such implementations.
Also, a small set of data is considered here, because a small machine is used for the benchmark. A bigger dataset on a more powerful maching can give a more precise result for the real world applications.
¹ This implementation is for benchmarking purposes 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, Core i5 Processor, 8 GB Installed RAM, SSD Hard Drive
