Introduction
Transaction Isolation is the “I” from the ACID acronym and is one of the properties that needs to be addressed in order to leave the database in a valid state even in the event of unexpected errors. Isolation is all about detecting and avoiding issues that occur due to concurrent requests. There are well known anomalies that can occur due to concurrent requests. Various transaction isolation levels are meant to handle these issues. The highest isolation level handles all concurrent issues while the lowest offers the least protection. However, there is a trade-off : the higher the isolation level, the slower the database system. Understanding transaction isolation levels is crucial for maintaining data integrity and performance in databases. In this article, we’ll explore four primary isolation levels defined by the SQL standard – Read Uncommitted, Read Committed, Repeatable Read, and Serializable. We will have a look at the anomalies each of these levels mitigate.
Although there is the SQL standard for isolation levels, there are variations in its implementation from database to database. That said, it is important to know how far your database of choice adheres to the standard. It is also important to know the default isolation level of the database you are using.
In this article we will work with postgresql (postgres), a Multi-Version Concurrency Control (MVCC) database, which offers read isolation without locks but with lock detection or conflict detection. This form of isolation (fail and retry) is called optimistic locking because it assumes the likelihood of a conflict is low. Postgres can also be made to use locks whereby other transactions need to wait to acquire the lock before continuing (wait-to-continue scenario). This wait-to-continue isolation is called pessimistic locking. The default isolation level of postgresql is read committed.
The Model
We have an Account entity with 3 fields: id, amount and owner. We will use spring boot and also configure the application to log SQL statements to the console. To be able to reproduce the various anomalies, we have an abstract class called Funneler, which implements the Runnable interface. It uses a CoutDownLatch and a Semaphore to achieve this. The reason for the strange name is that in a way it acts as a funnel.
public abstract class Funneler implements Runnable {
private static final Logger logger = LoggerFactory.getLogger(Funneler.class);
protected final CountDownLatch countDownLatch;
protected final Semaphore semaphore;
protected Funneler(CountDownLatch countDownLatch, Semaphore semaphore) {
this.countDownLatch = countDownLatch;
this.semaphore = semaphore;
}
protected void waitForOtherThreadThenAct() {
try {
countDownLatch.countDown();
logger.info("Before calling await from thread {}" , Thread.currentThread().getName());
countDownLatch.await();
logger.info("Before acquiring semaphore from thread {}" , Thread.currentThread().getName());
semaphore.acquire();
}
catch (InterruptedException e) {
throw new RuntimeException(e);
}
}
@Override
public void run() {
executeSynchronized(this::waitForOtherThreadThenAct);
semaphore.release();
}
public abstract void executeSynchronized(Runnable syncCallback);
}
Each class that extends the Funneler class needs to implement executeSynchronized(Runnable syncCallback) with its execution logic. It calls the syncCallback runnable at the point where the threads need to wait for each other for an anomaly to happen. The waitForOtherThreadThenAct() method implemented on lines 11-22 is what makes the threads that call Funneler wait for each other before acquiring permission from the semaphore to proceed.
Read Uncommitted
This is the lowest and the fastest isolation level. This level allows dirty reads. That is, it lets transactions read uncommitted changes of other transactions. In a single user application, this level maybe acceptable. Consider the following scenario in a multi-user application. You have an account entity and 2 transactions, T1 and T2. T1 writes an amount to the account but has not yet committed, then T2 uses this value to perform some calculation. T1 aborts its transaction and rolls back. We see that T2 has used data that was never committed, eventually causing inconsistency. Due to the nature of postgres (MVCC), the read uncommitted is not possible because postgres reads from a snapshot of the database it took during the transaction.
Read Committed
This is the default isolation level of postgres. It prevents dirty reads. This isolation level does not prevent lost updates nor does it provide repeatable read isolation. The code below from the accountService class helps us write reproducable lost update tests.
@Transactional
public Optional<Account> updateAmount(long id, long amt, Runnable preUpdateAction) {
final Optional<Account> accountOpt = accountRepo.findById(id);
preUpdateAction.run();
accountOpt.ifPresent(account -> {
final long fetchedAmt = account.getAmt();
final long finalAmt = fetchedAmt + amt;
account.setAmt(finalAmt);
logger.info("Thread {} updates value to {}" , Thread.currentThread().getName(), finalAmt);
});
return accountOpt;
}
With the preUpdateAction a chance is given to another thread to run so that we can produce lost updates reliably. In the real world, the lost update will happen because a concurrent request was executed exactly at that point.
Below is the Runnable implementation needed to be run by 2 or more threads in order to update an account.
class AccountUpdater extends Funneler {
private final long updateAmt;
private final long accountId;
private final AccountService accountService;
protected AccountUpdater(CountDownLatch countDownLatch,
Semaphore semaphore,
long updateAmt,
long accountId,
AccountService accountService) {
super(countDownLatch, semaphore);
this.updateAmt = updateAmt;
this.accountId = accountId;
this.accountService = accountService;
}
@Override
public void executeSynchronized(Runnable syncCallback) {
accountService.updateAmount(accountId, updateAmt, syncCallback);
}
}
On line 19 we can see it calls “accountService.updateAmount()” passing in the the accountId, updateAmt and syncCallback. In the previous code listing on line 4 we can see that syncCallback is invoked after the specific account is fetched. This means the threads that will be funneled will all fetch the account first before each makes the update on the account individually, thereby leading to the lost update. Below is the test method.
@Test
void updateAmountWithLostUpdate() throws InterruptedException {
final long initialAmt = 1000;
final Account account = createAccount();
account.setAmt(initialAmt);
final Account persistedAccount = accountService.persistAccount(account);
final Long accountId = persistedAccount.getId();
final CountDownLatch countDownLatch = new CountDownLatch(2);
final Semaphore semaphore = new Semaphore(1);
final long decrement = -200;
final Thread decrementThread = getThread(countDownLatch, semaphore, decrement, accountId);
final long increment = 450;
final Thread incrementThread = getThread(countDownLatch, semaphore, increment, accountId);
decrementThread.start();
incrementThread.start();
//fetch from db only after both threads above have exited
decrementThread.join();
incrementThread.join();
final Optional<Account> fetchedAccountOpt = accountService.fetchAccountById(accountId);
assertThat(fetchedAccountOpt).isPresent();
final Account fetchedAccount = fetchedAccountOpt.get();
final long updatedAmt = fetchedAccount.getAmt();
assertThat(updatedAmt).isNotEqualTo(initialAmt + increment + decrement).isNotEqualTo(initialAmt);
if(updatedAmt != (initialAmt + increment) && updatedAmt != (initialAmt + decrement)) {
final String msg = "Expected 1 transaction to succeed and update account amount. " +
"The final amount should not be 'initialAmt + increment': %d or 'initialAmt + decrement': %d. " +
"Got updatedAmt: %d";
fail(msg.formatted(initialAmt + increment, initialAmt + decrement, updatedAmt));
}
}
private @NotNull Thread getThread(CountDownLatch countDownLatch,
Semaphore semaphore,
long updateAmt,
long accountId) {
final AccountUpdater accountUpdater = new AccountUpdater(countDownLatch,
semaphore,
updateAmt,
accountId,
accountService);
return new Thread(accountUpdater);
}
“accountService.updateAmount()” is executed with the default isolation scope. That is “read committed”. This scope does not prevent lost updates so we get the lost update issue. We can solve the problem by changing the isolation scope to repeatable read as displayed below.
@Transactional(isolation = Isolation.REPEATABLE_READ)
public Optional<Account> updateAmountRepeatableRead(long id, long amt, Runnable preUpdateAction) {
return update(id, amt, preUpdateAction);
}
Repeatable Read
In this scope, the lost update issue is not possible. You also cannot have phantom reads because whatever results you got for a query during a transaction with this isolation will always be the same at every point of the transaction. This means if a row is added or removed by a different transaction, the query results in the current transaction would not reflect that throughout the transaction.
Below is the Funneler implementation that runs basically the same code but this time with the service method annotated as a repeatable read.
class AccountUpdaterWithRepeatableRead extends Funneler {
private final long updateAmt;
private final long accountId;
private final AccountService accountService;
protected AccountUpdaterWithRepeatableRead(CountDownLatch countDownLatch,
Semaphore semaphore,
long updateAmt,
long accountId,
AccountService accountService) {
super(countDownLatch, semaphore);
this.updateAmt = updateAmt;
this.accountId = accountId;
this.accountService = accountService;
}
@Override
public void executeSynchronized(Runnable syncCallback) {
accountService.updateAmountRepeatableRead(accountId, updateAmt, syncCallback);
}
}
As we can see on line 19, “accountService.updateAmountRepeatableRead()” is invoked, which is basically the same as “accountService.updateAmount()” but just with the repeatable read isolation.
Below is the test method.
@Test
void updateAmountRepeatableRead() throws InterruptedException {
final long initialAmt = 1000;
final Account account = createAccount();
account.setAmt(initialAmt);
final Account persistedAccount = accountService.persistAccount(account);
final Long accountId = persistedAccount.getId();
final CountDownLatch countDownLatch = new CountDownLatch(2);
final Semaphore semaphore = new Semaphore(1);
final long decrement = -200;
final AccountUpdaterWithRepeatableRead accountUpdaterWithRepeatableRead = new AccountUpdaterWithRepeatableRead(countDownLatch,
semaphore,
decrement,
accountId,
accountService);
final Thread decrementThread = new Thread(accountUpdaterWithRepeatableRead);
final long increment = 450;
final AccountUpdaterWithRepeatableRead accountUpdaterWithRepeatableRead2 = new AccountUpdaterWithRepeatableRead(countDownLatch,
semaphore,
increment,
accountId,
accountService);
final Thread incrementThread = new Thread(accountUpdaterWithRepeatableRead2);
decrementThread.start();
incrementThread.start();
//fetch from db only after both threads above have exited
decrementThread.join();
incrementThread.join();
final Optional<Account> fetchedAccountOpt = accountService.fetchAccountById(accountId);
assertThat(fetchedAccountOpt).isPresent();
final Account fetchedAccount = fetchedAccountOpt.get();
final long updatedAmt = fetchedAccount.getAmt();
if(updatedAmt != (initialAmt + decrement) && updatedAmt != (initialAmt + increment)) {
fail(String.format("Expected updated amount to be (initialAmt + decrement): %d or (initialAmt + increment): %d",
(initialAmt + decrement), (initialAmt + increment)));
}
}
Now only 1 of both transactions makes it. The other one fails and can be retried. It is worth mentioning here that the same isolation can be achieved at the query level. To see it in action please refer to the “updateAmountRepeatableReadWithReadForUpdate()” test method in the “LostUpdateDemoTest” test class.
To produce the phantom read and see it mitigated, please refer to the PhantomReadTest class.
There is an anomaly that the repeatable read isolation cannot detect and prevent. This is the write skew.
Serializable
This isolation level is supposed to be the “catch all” level. All anomalies are supposed to be detected and prevented by the database. This level does not actually serialize transactions. Instead it ensures that the end result is the same as if the transactions were serialized.
Let’s take the use case where Peter and Paul work in the same department. In their department, each month they can convert their bonuses to increase their account amounts. Only one person at any given month can have the account above 10,000 after the conversion. Peter requests the bonus (T1) then Paul also requests (T2). T1 checks and sees that with the bonus Paul’s account would exceed 10,000. It also sees that no other person has an amount above 10,000. T2 does the same for Peter but T1 commits just before T2 commits. Here we have a write skew.
Refer to the WriteSkewDemoTest class to see how this is produced and mitigated.
Conclusion
We have seen the 4 levels of transaction isolation and how they can be produced and mitigated. All these have been explain with postgres as the underlying implementation. Below is the overview of the isolaation-anomaly table for postgres.
| Isolation Level | Dirty Read | Nonrepeatable Read | Phantom Read | Serialization Anomaly |
|------------------|------------------------|--------------------|--------------|-----------------------|
| Read uncommitted | Not possible | Possible | Possible | Possible |
| Read committed | Not possible | Possible | Possible | Possible |
| Repeatable read | Not possible | Not possible | Not possible | Possible |
| Serializable | Not possible | Not possible | Not possible | Not possible |
The accompanying code can be got from here.
Code Major 
Leave a comment