Database locking revisited

Introduction For me, it was hard to find a paper that would thoroughly explain to me how I can make database operations in my app thread-safe. Most of them focus on explaining @Transactional annotation and other framework mechanisms, but hardly ever elaborate on how it works in the database, or still believe that serializable transactions are executed sequentially (not true!). Here, I go into those details and explain on examples how you can leverage your database. Is it even worth to review the topic? Some people might be wondering whether it's necessary to review this topic, since it's already explained in the databases' docs. However, I feel that there is such need. When I was trying to understand all of this, like transactions, isolations, optimistic vs. pessimistic locking, etc., I could not really find a place that wholly covers this topic. I understand that it's a broad area, but still. Mostly there was just a reference to the documentation and that was it. Don't get me wrong, I don't underestimate reading documentation, it's often a source of truth, but sometimes it may be challenging and overwhelming, especially since it's separate of the language or library you use, and you might not be sure how your code actually translates to interactions with your database. Moreover, I was honestly surprised when I realized how much disinformation there is about this topic. For instance, as already mentioned, many authors still believe that serialization isolation is based on pessimistic locking, or even worse - that it executes the transactions sequentially. Obviously, this is not true, not at least in case of Postgres database, as well as in other commonly used relational databases. All isolation levels there are based on optimistic locks, which I will explain later on. What you need to already know? In this article, I assume that you understand the basics of concurrent processing, such as race conditions or locking. Concurrent processing is the reason why we have to use one or the other thread-safety mechanisms in databases. Additionally, it's worth explicitly pointing out that I will be working on Postgres database, since it's a one of the most popular choice and one that I'm most familiar with. Obviously, many of it's behaviors can be directly translated to other databases, however, not all of them, so make sure to take that into consideration, if you are working with another RDBMS. Without further ado, let's get into it! What are the race conditions the databases are vulnerable to? Firstly, let's quickly recap the basic nomenclature. In relational databases, in order to maintain strong data consistency and isolation between concurrent operations, a set of queries are enclosed within transactions. In transactions, either all or nothing succeeds - there is nothing in between. In case of any error within a transaction, all previous operations are reverted, and all next operations are cancelled, and we say that such transaction is rolled back. In case there are no errors, all operations are submitted (e.g., changes), and we say that such transaction is committed. The transactions, even though guarantee isolation, according to ACID principles, does not guarantee the ultimate isolation by default. Obviously, you can protect your data (e.g., a single database row) in a way that it could not be accessed by two concurrent processes at a time, but that would significantly limit concurrency capabilities of you database. Thankfully, this issue is already addressed by various, complex algorithms, that allow for more concurrent throughput. I will get back to those later. Let's start by listing race conditions we may face as database users. Race conditions are vulnerabilities in a software that may lead to nondeterministic, unexpected and incorrect outcomes in concurrent environment (e.g., dead locks, lost updates, outdated reads). In relational databases, we can distinguish the following: Dirty reads: Reading uncommitted changes from other transactions. Non-repeatable reads: Reading the same data within a transaction twice returns different results (e.g., some new changes got committed between the reads, causing returning inconsistent data). Phantom reads: Query that returns a set of results satisfying given search condition within a transaction twice returns different results (similar to non-repeatable read, but refers to search queries, rather than returning specific rows). Dirty writes: Overwriting data that another client has written, but not yet committed. Lost updates: Overwriting changes made by another transaction without incorporating them. (e.g., transaction A reads some value, transaction B reads the same value, then transaction A updates the value, and transaction B also updates the value, but does not consider the write made by transaction A, which may result in inconsistent state of the value). Write skew: Making a query, perparing write oper

Jun 26, 2025 - 16:10

Introduction

For me, it was hard to find a paper that would thoroughly explain to me how I can make database operations in my app thread-safe. Most of them focus on explaining @Transactional annotation and other framework mechanisms, but hardly ever elaborate on how it works in the database, or still believe that serializable transactions are executed sequentially (not true!). Here, I go into those details and explain on examples how you can leverage your database.

Is it even worth to review the topic?

Some people might be wondering whether it's necessary to review this topic, since it's already explained in the databases' docs. However, I feel that there is such need. When I was trying to understand all of this, like transactions, isolations, optimistic vs. pessimistic locking, etc., I could not really find a place that wholly covers this topic. I understand that it's a broad area, but still. Mostly there was just a reference to the documentation and that was it. Don't get me wrong, I don't underestimate reading documentation, it's often a source of truth, but sometimes it may be challenging and overwhelming, especially since it's separate of the language or library you use, and you might not be sure how your code actually translates to interactions with your database.

Moreover, I was honestly surprised when I realized how much disinformation there is about this topic. For instance, as already mentioned, many authors still believe that serialization isolation is based on pessimistic locking, or even worse - that it executes the transactions sequentially. Obviously, this is not true, not at least in case of Postgres database, as well as in other commonly used relational databases. All isolation levels there are based on optimistic locks, which I will explain later on.

What you need to already know?

In this article, I assume that you understand the basics of concurrent processing, such as race conditions or locking. Concurrent processing is the reason why we have to use one or the other thread-safety mechanisms in databases. Additionally, it's worth explicitly pointing out that I will be working on Postgres database, since it's a one of the most popular choice and one that I'm most familiar with. Obviously, many of it's behaviors can be directly translated to other databases, however, not all of them, so make sure to take that into consideration, if you are working with another RDBMS.

Without further ado, let's get into it!

What are the race conditions the databases are vulnerable to?

Firstly, let's quickly recap the basic nomenclature.

In relational databases, in order to maintain strong data consistency and isolation between concurrent operations, a set of queries are enclosed within transactions. In transactions, either all or nothing succeeds - there is nothing in between. In case of any error within a transaction, all previous operations are reverted, and all next operations are cancelled, and we say that such transaction is rolled back. In case there are no errors, all operations are submitted (e.g., changes), and we say that such transaction is committed.

The transactions, even though guarantee isolation, according to ACID principles, does not guarantee the ultimate isolation by default. Obviously, you can protect your data (e.g., a single database row) in a way that it could not be accessed by two concurrent processes at a time, but that would significantly limit concurrency capabilities of you database. Thankfully, this issue is already addressed by various, complex algorithms, that allow for more concurrent throughput. I will get back to those later.

Let's start by listing race conditions we may face as database users. Race conditions are vulnerabilities in a software that may lead to nondeterministic, unexpected and incorrect outcomes in concurrent environment (e.g., dead locks, lost updates, outdated reads). In relational databases, we can distinguish the following:

Dirty reads: Reading uncommitted changes from other transactions.
Non-repeatable reads: Reading the same data within a transaction twice returns different results (e.g., some new changes got committed between the reads, causing returning inconsistent data).
Phantom reads: Query that returns a set of results satisfying given search condition within a transaction twice returns different results (similar to non-repeatable read, but refers to search queries, rather than returning specific rows).
Dirty writes: Overwriting data that another client has written, but not yet committed.
Lost updates: Overwriting changes made by another transaction without incorporating them. (e.g., transaction A reads some value, transaction B reads the same value, then transaction A updates the value, and transaction B also updates the value, but does not consider the write made by transaction A, which may result in inconsistent state of the value).
Write skew: Making a query, perparing write operations based on results of this query, then submitting the write, but at the time the write is submitted, the query made initially would produce different results and possibly change the prepared write operations (e.g., book a ticket for an event if amount of booked tickets for it is lower than 100, but between checking already booked tickets and booking a new one, some other transaction already committed a new booking to the same event and amount of booked events reached its limit, but we do not see that change in our transaction).

Hopefully each of those conditions are clear, but if you have some doubts, no worries! I provide practical examples for each of those in next sections.

Regarding examples, I will be working on accounts table having 1000 accounts, each with balance=50, has_loan=false, created with the following queries:

CREATE TABLE accounts (
    id BIGSERIAL PRIMARY KEY,
    balance NUMERIC(9, 2) NOT NULL,
    has_loan BOOLEAN NOT NULL
);

INSERT INTO accounts (balance, has_loan) (SELECT 50, false FROM GENERATE_SERIES(1,1000) n);

Avoiding race conditions by appropriate isolation levels

When you start a new transaction, you start it with a certain isolation level. Their goal is to make operations within a transaction thread-safe. There are weaker, but more performant levels, and stronger, but less performant levels. You can define the level explicitily (e.g., in BEGIN clause that starts a new transaction, or isolation value in @Transactional annotation in Spring), or your database will use a default isolation level. In Postgres, there are the following isolation levels, ordered from the weakest to the strongest:

Read committed (default): Read operations see only committed changes, or changes made in current transaction.
Repeatable read: Makes subsequent reads repeatable, by working on a specific snapshot of the database throughout the entire transaction. It's based on algorithm known as Snapshot Isolation.
Serlializable: Automatically rolls back transactions that are interfered by some other concurrent transactions. It's based on algorithm known as Serializable Snapshot Isolation, which is essentially extension of Snapshot Isolation used in Repeatable read, that additionally puts non-blocking locks on accessed data to check if there weren't any concurrent operation that would cause serialization failure.

First of all, observant readers (or those that knew that already) would notice that all of available isolation levels prevent dirty reads and writes - it's impossible to face them in Postgres, so we don't need to care about them. These conditions are mostly always prevented in other databases as well.

Next thing is that all isolation levels are based on MVCC (Multiversion concurrency control), which is non-blocking concurrency mechanism. In other words, we can say that all isolation levels are actually based on optimistic locking, since it's allowed to perform conflicting operations without blocking, but they will be eventually rejected if such conflict happened.

Okay, but this is just a pure theory, which is additionally veeery brief, so let's get into more details of each level separately.

Read committed

This level is quite simple. Once you start a transaction with it, you won't see any uncomitted changes from other transactions. It's a default isolation level used by Postgres.

For example, consider having 2 sessions that are concurrently working on the same account. Both read its data, as well as update it.

session #1> BEGIN ISOLATION LEVEL READ COMMITTED;
> BEGIN

session #1> SELECT * FROM accounts WHERE id = 1;
>  id | balance | has_loan 
> ----+---------+----------
>   1 |   50.00 | f
> (1 row)

session #2> BEGIN ISOLATION LEVEL READ COMMITTED;
> BEGIN

session #2> SELECT * FROM accounts WHERE id = 1;
>  id | balance | has_loan 
> ----+---------+----------
>   1 |   50.00 | f
> (1 row)

session #2> UPDATE accounts SET balance = 100 WHERE id = 1;
> UPDATE 1

session #2> SELECT * FROM accounts WHERE id = 1;
>  id | balance | has_loan 
> ----+---------+----------
>   1 |  100.00 | f
> (1 row)

session #1> SELECT * FROM accounts WHERE id = 1;
>  id | balance | has_loan 
> ----+---------+----------
>   1 |   50.00 | f
> (1 row)

session #2> COMMIT;
> COMMIT

session #1> SELECT * FROM accounts WHERE id = 1;
>  id | balance | has_loan 
> ----+---------+----------
>   1 |  100.00 | f
> (1 row)

session #1> UPDATE accounts SET balance = 25 WHERE id = 1;
> UPDATE 1

session #1> COMMIT;
> COMMIT

As you can see, in session #2 we did not see changes made in session #1 unless they got commited. Once it happenned, we could see that.

Besides that, making update, even though there was concurrent change on the same data, wasn't blocked - if we didn't re-select it, we couldn't even notice that the change took place.

The update would have been blocked if there were two pending updates (or any conflicting writes) on the same rows in different transactions. Since changes are submitted on commit, the database waits till one change is committed (or rolled back), to continue with next changes. Changes are applied sequentially, to maintain data integrity.

Here is an example of that.

session #1> BEGIN ISOLATION LEVEL READ COMMITTED;
> BEGIN

session #2> BEGIN ISOLATION LEVEL READ COMMITTED;
> BEGIN

session #1> UPDATE accounts SET balance = 75 WHERE id = 1;
> UPDATE 1

--- the session is blocked till other change is either committed or rolled back
session #2> UPDATE accounts SET balance = 25 WHERE id = 1;

session #1> COMMIT;
> COMMIT

--- now it's unblocked
session #2> 
> UPDATE 1

session #2> COMMIT;
> COMMIT

This isolation level prevents only dirty writes and reads, but is still vulnerable to other race conditions.

Keep in mind one, really important thing - it's the default isolation level, vulnerable to most race conditions. During transaction execution, you can't tell whether you faced them or not. There are no warning or messages informing user that some, for example, lost update may have happenned. Don't get me wrong, this level is great in its simplicity to certain use-cases, however, it can be quite risky if not used properly.

Imagine that you are developing a Spring (or whatever) application, use Postgres as your database, and all you do is enclosing your operations within a request in transaction. Many people believe it's all you need to make your app thread-safe. Obviously, it is not enough! Unless you are the only user of your app, or you perform only simple queries within your app (e.g., SELECT posts ORDER BY publication_time DESC). It might be a good idea to change the default isolation level to a stronger one, which can be done via Postgres configuration, or somehow force developers to always explicitly define isolation level, so that they would be more conscious of what their code does.

Pros and cons

Pros:
- The most performant isolation level.
- Fits great where you execute only one query within a transaction, or many queries but each on completely unrelated data (e.g., fetch signed-in user data, fetch most recent posts), or work with data that cannot be modfied concurrently (e.g., data that belongs to the user, like their posts, comments, etc. and the user can access it only from one device at a time), or slight inconsistencies can be accepted.
Cons:
- Narrow set of use-cases where it can be applied.
- Vulnerable to most race conditions.
- Used improperly can lead to unrecoverable data consistency issues.

Repeatable read

This isolation level is based on algorithm known as Snapshot Isolation. It essentially takes a snapshot of data at the beginning of transaction, and works on this snapshot till the transaction is finished.

By doing that, it's ensured that repeated reads of the same rows would always return the same data, meaning it prevents non-repeatable reads. Moreover, this algorithm prevents phantom reads too, so not only reads of the same rows would remain consistent, but reads matching given search condition would remain consistent across transaction as well. This is great for read only transactions, as it addresses all read-related race conditions.

Look at the example below, where again, we have 2 sessions that are doing operations concurrently.

session #1> BEGIN ISOLATION LEVEL REPEATABLE READ;
> BEGIN

session #1> SELECT * FROM accounts WHERE id = 1 OR id = 2 ORDER BY id;
>  id | balance | has_loan 
> ----+---------+----------
>   1 |   50.00 | f
>   2 |   50.00 | f
> (2 rows)

session #2> BEGIN ISOLATION LEVEL READ COMMITTED;
> BEGIN

session #2> UPDATE accounts SET balance = 75 WHERE id = 1;
> UPDATE 1

session #2> UPDATE accounts SET balance = 25 WHERE id = 2;
> UPDATE 1

session #1> SELECT * FROM accounts WHERE id = 1 OR id = 2 ORDER BY id;
>  id | balance 
> ----+---------
>   1 |   50.00 | f
>   2 |   50.00 | f
> (2 rows)

session #2> COMMIT;
> COMMIT

session #1> SELECT * FROM accounts WHERE id = 1 OR id = 2 ORDER BY id;
>  id | balance 
> ----+---------
>   1 |   50.00 | f
>   2 |   50.00 | f
> (2 rows)

session #1> COMMIT;
> COMMIT

As you can see, no matter what happened in other transactions, or whether there were some changes committed in the meantime, results returned within session #1 are consistent.

What about writes? Well, it depends. When we go to Postgres documentation, section about repeatable isolation level, we can find note about making updates on rows that are being concurrently updated by another transaction. Basically, if we try to update a row that is already being updated by concurrent transaction, our repeatable read transaction will wait till the other transaction finishes. If that transaction is committed, our transaction will be rolled back with ERROR: could not serialize access due to concurrent update message. If that transaction is rolled back, our transaction will made its changes, as now they are not impacted by concurrent change. Putting it shortly: repeatable read prevents lost updates.

Let's see that on examples.

In case of commit in the other transaction, our transaction throws SQL error and, according to the documentation, should be rolled back and optionally retried.

session #1> BEGIN ISOLATION LEVEL REPEATABLE READ;
> BEGIN

session #1> SELECT * FROM accounts WHERE id = 1;
>  id | balance | has_loan 
> ----+---------+----------
>   1 |   50.00 | f
> (1 row)

session #2> BEGIN ISOLATION LEVEL READ COMMITTED;
> BEGIN

session #2> SELECT * FROM accounts WHERE id = 1;
>  id | balance | has_loan 
> ----+---------+----------
>   1 |   50.00 | f
> (1 row)

session #2> UPDATE accounts SET balance = 100 WHERE id = 1;
> UPDATE 1

--- session #1 is blocked till the transaction in session #2 is finished
session #1> UPDATE accounts SET balance = 75 WHERE id = 1;

session #2> COMMIT;
> COMMIT

--- throws SQL error, since concurrent change was committed
session #1> 
>  ERROR:  could not serialize access due to concurrent update

session #1> ROLLBACK;
> ROLLBACK

In case of rollback in the other transaction, we are able to commit our changes.

session #1> BEGIN ISOLATION LEVEL REPEATABLE READ;
> BEGIN

session #1> SELECT * FROM accounts WHERE id = 1;
>  id | balance | has_loan 
> ----+---------+----------
>   1 |   50.00 | f
> (1 row)

session #2> BEGIN ISOLATION LEVEL READ COMMITTED;
> BEGIN

session #2> SELECT * FROM accounts WHERE id = 1;
>  id | balance | has_loan 
> ----+---------+----------
>   1 |   50.00 | f
> (1 row)

session #2> UPDATE accounts SET balance = 100 WHERE id = 1;
> UPDATE 1

--- session #1 is blocked till the transaction in session #2 is finished 
session #1> UPDATE accounts SET balance = 75 WHERE id = 1;

session #2> ROLLBACK;
> ROLLBACK

--- update is performed succesfully, since the other transaction rolled back
session #1> 
>  UPDATE 1

session #1> COMMIT;
> COMMIT

By reviewing the examples, we can actually say that repeatable read isolation level applies optimistic locking on updated rows. It tries to update a row, but if it turns out that there were concurrent updates that committed change to that row, transaction is aborted. Sounds familiar to optimistic locking implemented on app-level via additional version column (as explained here and here), huh? Yeah, it actually works on pretty much the same assumptions, it's just implemented entirely within the database, and does not require any special handling in the application.

I don't know why, but it's not widely known that the optimistic locking can be entirely achieved by using proper isolation level. I understand that perhaps other database systems might implement it differently and perhaps do not support that, but still, Postgres is a really popular choice. Maybe since app-level optimistic locking is often fully automated by frameworks and the fact that it can be succesfully used in every database made it that popular? Maybe. Nonetheless, it's worth to actually understand how your database approaches that, to not end up in situations where you apply optimistic locking twice: one on app-level via version column, one on database-level via repeatable reads.

Okay, but what about write skews, what if I made a search query, and basic on its result make a decision about update? What if we updated different rows in concurrent transactions? These kind of operations are allowed there.

Pros and cons

Pros:
- Prevents all read-related race conditions, even for more complex queries.
- Regarding writes, prevents dirty writes and lost updates.
- Really well balanced between being performant and secured against most race conditions.
Cons:
- Still susceptible to write skews.

Serializable

Guarantees the strictest isolation and prevents all possible race conditions.

This isolation level is based on algorithm known as Snapshot Serializable Isolation. As mentioned on Postgres documentation, Serializable Isolation Level section, this level works pretty much the same as Repeatable Read, but additionally uses predicate, non-blocking locks to make sure that concurrent transactions did not change anything during the transaction, that would cause serialization failure. If that is the case, transaction rolls back and throws ERROR: could not serialize access due to read/write dependencies among transactions message. If transaction wasn't interfeered, it is committed successfully. So we can say that serializable isolation level is based on optimistic locking and prevents all race conditions. In theory, this is great, but in practice - slightly more complicated.

First of all, it is possible to get false positives when it comes to serialization failures, meaning you can get serialization error even though your transaction was not really interfeered by concurrent transaction. This is caused by the way how predicate locks are being acquired. Predicate locks can be acquired on each row separately (e.g., each modified row), but those can be promoted to page-level locks (page in a sense of a B-Tree page, or to put it simply: on multiple rows that are stored close to each other), or even to relation-level locks (e.g., tables, indexes, relations between tables). So with serializable transactions, you always need to be ready to get serialization errors, and optionally try to retry such transactions. Unfortunately, this can visibly impact performance of the application.

Theoretically, the rate of these errors can be minimized. For instance, if you have thousands rather than hundreds of rows, page-level locks would not be conflicting that often. Additionally, you can try to modify locking configuration to minimize the rate of serialization errors. Nonetheless, you cannot eliminate them completely.

Without further ado, let's get into examples.

Let's consider a situation where we want to give loans to accounts where balance is equal or greater than 100. Concurrently, we will update one account to have the balance increased to 125.

session #1> BEGIN ISOLATION LEVEL SERIALIZABLE;
> BEGIN

session #2> BEGIN ISOLATION LEVEL SERIALIZABLE;
> BEGIN

session #1> SELECT * FROM accounts WHERE id = 1;
>  id | balance | has_loan 
> ----+---------+----------
>   1 |   50.00 | f
> (1 row)

session #1> UPDATE accounts SET balance = 125 WHERE id = 1;
>  UPDATE 1

session #1> COMMIT;
> COMMIT

session #2> UPDATE accounts SET has_loan = true WHERE balance >= 100;
>  UPDATE 0

session #2> COMMIT;
> COMMIT

You might think that it's not how it supposed to be working and expected to get serialization error on session #2, because its update did not incorporate changes commited in session #1. However, in case of such concurrent transactions, Postgres checks whether comitting them would be correct if they were performed sequentially in any order, so it's allowed to reorder concurrent transactions, no matter which one started first. Here, even though session #1 started first, changes in those 2 sessions had no impact on each other if all changes in session #2 were committed first, and those in session #1 - second. Since those transactions are already concurrent, it's perfectly fine and acceptable.

What if there is circular dependency between two transactions? Assume that we want to give a loan plus a bonus for start to all users that are not having any loan at the moment, and concurrently we want to close loan for users that currently are having a loan, plus take some installment fee.

--- accounts with odd id have loan, with even id - not.
session #1> UPDATE accounts SET has_loan = CASE WHEN id % 2 = 1 THEN true ELSE false END;
> UPDATE 1000

session #1> BEGIN ISOLATION LEVEL SERIALIZABLE;
> BEGIN

session #1> UPDATE accounts SET balance = balance + 25, has_loan = true WHERE has_loan = false;
>  UPDATE 500

session #2> BEGIN ISOLATION LEVEL SERIALIZABLE;
> BEGIN

session #2> UPDATE accounts SET balance = balance - 25, has_loan = false WHERE has_loan = true;
>  UPDATE 500

session #1> COMMIT;
> COMMIT

session #2> COMMIT;
> ERROR:  could not serialize access due to read/write dependencies among transactions
> DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
> HINT:  The transaction might succeed if retried.

Here, the transactions are interdependent. If session #1 submitted its changes first, then session #2 should've updated 1000 rows, not only 500. If session #2 was first, then session #1 should've updated 1000 rows too. Since we work on a snapshot, we don't see changes committed concurrently. In any order, commit of the second transaction would lead to write skew, so the first committed transaction wins, but the other throws serialization error.

What is also worth to note here is the fact, that the transaction would fail only if both are executed in serializable isolation level. If any of them applied any lower isolation level, both transactions would succeed, since its considered then that one of those can accept write skew and it would not harm the system to commit such transactions.

I also mentioned that it's possible to get false positives, when predicate locks are promoted to page or relation-level locks. Consider a situation where you want to update 1000 independent accounts concurrently.

session #1> BEGIN ISOLATION LEVEL SERIALIZABLE;
> BEGIN

session #2> BEGIN ISOLATION LEVEL SERIALIZABLE;
> BEGIN

--- update accounts with id 1, 3, ..., 999
session #1> UPDATE accounts SET balance = balance - 25 WHERE id IN (SELECT n FROM generate_series(1,1000,2) n); 
> UPDATE 500

--- update accounts with id 2, 4, 6, ..., 1000
session #2> UPDATE accounts SET balance = balance + 25 WHERE id IN (SELECT n FROM generate_series(2,1000,2) n); 
> UPDATE 500

session #1> COMMIT;
> COMMIT

session #2> COMMIT;
> ERROR:  could not serialize access due to read/write dependencies among transactions
> DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
> HINT:  The transaction might succeed if retried.

Even though those transactions were working on completely different accounts, we got serialization error. We might think about tweaking Postgres settings, but it is not really possible to unequivocally configure them deterministically (for instance, to not go for page-level locks till some number of row-level locks is reached). On the other hand, you might notice that this kind of operation does not actually require serializable isolation, since it's vulnerable only to lost updates, not to write skews, which means that we could go with repeatable read here and it would properly block conflicting updates.

When it comes to read-only transactions, there is narrow and really specific set of use-cases where serializable transaction throws serializable error, but repeatable read accepts such transactions . You can find them on Postgres Wiki, SSI algoritm page. Since these examples are rather niche, I would not go into details about them there.

Pros and cons

Pros:
- Based on optimistic locking algorithm known as SSI, that allows for high concurrency considering that it's serializable isolation level.
- Prevents any race conditions.
Cons:
- Even though it's based on optimistic locking, it's still complex isolation level that can lead to performance decline.
- Database users must be prepared to handle serialization errors, which may impact the performance even more.
- Can throw false positive serialization errors. Thankfully, it can be limited if you had a lot of data, so that even page or relation-level locks would not collide with each other.

As you can see, there a a couple of considerations you might need to go through before deciding to go for serializable isolation. Whether it's worth it depends on multiple factors. It might be great in certain areas of your system, or become a bottleneck in others. Nonetheless it's not as bad as many people suggest, so if it suits your example, it's worth giving it a shot.

What if pessimistic locking is preferred?

Okay, we've reviewed all isolation levels and now we know how to get advantage of them. The thing is that all of them are based on optimistic locking, which is not always suitable.

Optimistic locking is a great option if you don't have conflicting operations on concurrent transactions, but once they become more and more common, you end up with a bunch of aborted transactions, that could result in bad end-user experience, since the response time is increased (in case of transaction retry), or the request is unexpectedly canceled. There are situations where you might actually prefer to go for pressimistic locking, the locking that will wait till the locked resource is released, and once that is the case it will acquire it for its operations, and release on transaction finish (either commit or rollback).

I already mentioned that when you execute UPDATE query, Postgres acquires exclusive locks on modified rows, so that if given row is modified by multiple transactions concurrently, the changes are applied sequentially. Depending on used isolation level, commit/rollback of such update is ignored (like in Read Committed) or can cause SQL errors (like in Repeatable Read). Same exclusive lock is used by other write operations too (e.g., DELETE or MERGE).

Basically, on row-level, it's possible to acquire the following locks:

Shared lock (aka read-only lock): a lock that can be held by multiple transactions at a time.
Exclusive lock (aka write lock): a lock that can be held by only one transaction at a time, and none other transaction can hold any lock (neither exclusive or shared) at that time.

Those locks can be acquired on demand, not only by Postgres itself. All you need to do is add special clause at the end of your SELECT query.

FOR SHARE for shared locks.
FOR UPDATE for exclusive locks.

In Spring, you can use @Lock annotation to apply pessimistic locking on a repository method.

Now, how to use those? Acquiring locks assures that the data we work on would not be modified concurrently, and optionally we can let other transactions perform read-only operations.

If you want data within your transaction to remain consistent, block any concurrent modifications, and will perform read-only operations, use shared locks - it lets concurrent transactions perform reading on locked data as well.
If you need to perform some write operations - use exclusive locks, so that no other transaction would be allowed to access locked data till the lock is released.

The key difference here comparing to using purely isolation levels is blocking concurrent access, which is what makes those locks pessimistic.

Consider an example where we try to update the same account in two concurrent transactions in a select-modify-update manner. First transaction will add 25 to the balance, second - subtract 25 from the balance. By relying purely on isolation levels, in case of conflict we could get either inconsistent results (read committed), or optimistic locking error (repeatable read or serializable). By using locks, we can block one transaction till the other is completed.

session #1> BEGIN ISOLATION LEVEL READ COMMITTED;
> BEGIN

session #2> BEGIN ISOLATION LEVEL READ COMMITTED;
> BEGIN

session #1> SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
>  id | balance | has_loan 
> ----+---------+----------
>   1 |   50.00 | f
> (1 row)

--- session #2 is blocked, since exclusive lock is held by session #1
session #2> SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

--- calculate new balance on the app and put the new value here
session #1> UPDATE accounts SET balance = 75 WHERE id = 1;
> UPDATE 1

session #1> COMMIT;
> COMMIT

--- lock is released, so session #2 is unblocked and acquires the lock on given account
session #2> 
>  id | balance | has_loan 
> ----+---------+----------
>   1 |   75.00 | f
> (1 row)

--- calculate new balance on the app and put the new value here
session #1> UPDATE accounts SET balance = 50 WHERE id = 1;
> UPDATE 1

session #2> COMMIT;
> COMMIT

Pessimistic locking is good choice if we know that certain records are often vulnerable to concurrent modifications, that would eventually cause transaction rollback. It allows to maintain data consistency even in read committed isolation level. However, bear in mind that those locks make the whole database less concurrent, since it's not able to process as many concurrent requests as it could by using optimistic locking via isolation levels. It's actually advised in Postgres documentation to try to utilize isolation levels first if you want to make your database more performant. If the isolation itself does not suit your needs, then go for pessimistic locks.

There are other types of locks that can be used as well, like table-level locks, or more sophisticated row-level locks. Most often the locks explained above will be sufficient for you, but if you are willing to learn more, check out Postgres documentation, Explicit locking section.

If all types of locks can be acquired on database level, does it still make sense to use optimistic locking on application level?

Obviously... it depends. Now at least you know how all of this works.

Optimistic locking on application level is quite popular, and can be successfully applied on various databases, even those that do not support optimistic locking on database level. Additionally, you can have more control over the locking, and it may be already well supported by the framework you use.

On the other hand, why reinventing the wheel? It's already there, it's more secure if database is accessed by multiple clients (e.g. multiples apps, or apps and users), since we always rely on mechanisms provided by the database. The locking may be more consistent, since we do not mix custom solutions with those provided by the database.

I would say that both decisions stand on their own, and it often might be a matter of preference and habits. When it comes to performance, there might be slight advantage in favor of database locking, but it's a topic for another article.

Summary

In this article, I thoroughly reviewed all possibilities when it comes to locking in a database and put some fresh perception on the problem.

Let me quickly summarize how I usually approach database locking.

Try to utilize isolation levels first.
- Use read committed when:
  - Executing a single and simple read/write query.
  - Executing multiple read/write queries, each on completely unrelated data.
  - Executing read/write queries where slight inconsistency is acceptable.
- Use repeatable read when:
  - Executing complex, embedded read queries (e.g. SELECT within SELECT).
  - Executing multiple read queries on related data, so that the returned results remain consistent throughout transaction.
  - Executing read/write queries and want to prevent lost updates via optimistic locking.
- Use serializable when:
  - Executing read/write queries and want to prevent all race conditions via optimistic locking.
  - You can afford to abort (or retry) transactions that would throw serialization failures, even in case of false positives.
  - You can accept impact on performance possibly generated by serialization checks.
If optimistic locking is not suitable (high conflicts rate), go for pessimistic locks.
- Use SELECT with FOR SHARE clause to acquire shared locks (e.g., read-only transactions).
- Use SELECT with FOR UPDATE clause to acquire exlusive locks (e.g., read and write transactions).

Hopefully you'll find this article valueable.