Locks are one of the fundamental building blocks of any concurrent
computer program. When many things are happening simultaneously,
programmers reach out to locks to guarantee the mutual exclusion of
concurrent access to a resource. Locks (and other mutual exclusion
primitives) exist in many different layers of the stack from low-level
CPU instructions to application-level APIs (such as
sync.Mutex in Go).
When working with relational databases, one of the common needs of
application developers is the ability to acquire a lock on records.
inventory table, listing items available for sale on
an e-commerce website. This table might have a column named
that could either be set to
purchased. avoid the
scenario where two users think they have successfully purchased the
same inventory item, the application must prevent two operations
from mutating the item from an available to a purchased state.
How can the application guarantee this? Having the server check
if the desired item is
available before setting it to
would not be good enough. Imagine a scenario where two users
simultaneously try to purchase the same item. Two requests would
travel from their browsers to the application server and arrive
roughly at the same time. Both would query the database for the
item's state, and see the item is
available. Seeing this, both
request handlers would issue an
UPDATE query setting the state
purchased and the
buyer_id to the id of the requesting user.
Both queries will succeed, but the final state of the record will
be that the user who issued the
UPDATE query last will be
considered the buyer of the item.
Over the years, different techniques have evolved to allow developers to write applications that provide these guarantees to users. Some of them involve explicit locking mechanisms provided by databases, while others rely on more general ACID properties of databases to achieve mutual exclusion. In this post we will explore the implementation of two of these techniques using Ent.
Optimistic locking (sometimes also called Optimistic Concurrency Control) is a technique that can be used to achieve locking behavior without explicitly acquiring a lock on any record.
On a high-level, this is how optimistic locking works:
- Each record is assigned a numeric version number. This value must be monotonically increasing. Often Unix timestamps of the latest row update are used.
- A transaction reads a record, noting its version number from the database.
UPDATEstatement is issued to modify the record:
- The statement must include a predicate requiring that the
version number has not changed from its previous value. For example:
WHERE id=<id> AND version=<previous version>.
- The statement must increase the version. Some applications will increase the current value by 1, and some will set it to the current timestamp.
- The statement must include a predicate requiring that the version number has not changed from its previous value. For example:
- The database returns the amount of rows modified by
UPDATEstatement. If the number is 0, this means someone else has modified the record between the time we read it, and the time we wanted to update it. The transaction is considered failed, rolled back and can be retried.
Optimistic locking is commonly used in "low contention" environments (situations where the likelihood of two transactions interfering with one another is relatively low) and where the locking logic can be trusted to happen in the application layer. If there are writers to the database that we cannot ensure to obey the required logic, this technique is rendered useless.
Let’s see how this technique can be employed using Ent.
We start by defining our
ent.Schema for a
User. The user has an
online boolean field to specify whether they are currently
online and an
int64 field for the current version number.
Next, let's implement a simple optimistically locked update to our
Next, let's write a test to verify that if two processes try to edit the same record, only one will succeed:
Running our test:
Great! Using optimistic locking we can prevent two processes from stepping on each other's toes!
As we've mentioned above, optimistic locking isn't always
appropriate. For use cases where we prefer to delegate the
responsibility for maintaining the integrity of the lock to
the databases, some database engines (such as MySQL, Postgres,
and MariaDB, but not SQLite) offer pessimistic locking
capabilities. These databases support a modifier to
statements that is called
SELECT ... FOR UPDATE. The MySQL
A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.
Alternatively, users can use
SELECT ... FOR SHARE statements, as
explained by the docs,
SELECT ... FOR SHARE:
Sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.
Ent has recently added support for
statements via a feature-flag called
sql/lock. To use it,
generate.go file to include
Next, let's implement a function that will use pessimistic
locking to make sure only a single process can update our
Now, let's write a test that verifies that if two processes try to edit the same record, only one will succeed:
A few things are worth mentioning in this example:
- Notice that we use a real MySQL instance to run this test
against, as SQLite does not support
SELECT .. FOR UPDATE.
- For the simplicity of the example, we used the
sql.NoWaitoption to tell the database to return an error if the lock cannot be acquired. This means that the calling application needs to retry the write after receiving the error. If we don't specify this option, we can create flows where our application blocks until the lock is released and then proceeds without retrying. This is not always desirable but it opens up some interesting design options.
- We must always commit our transaction. Forgetting to do so can result in some serious issues. Remember that while the lock is maintained, no one can read or update this record.
Running our test:
Great! We have used MySQL's "locking reads" capabilities and Ent's new support for it to implement a locking mechanism that provides real mutual exclusion guarantees.
We began this post by presenting the type of business requirements that lead application developers to reach out for locking techniques when working with databases. We continued by presenting two different approaches to achieving mutual exclusion when updating database records and demonstrated how to employ these techniques using Ent.
Have questions? Need help with getting started? Feel free to join our Slack channel.