Isolation Isn’t All Bad
(For Your Database)
March 2026
Sean McNealy
Sean McNealy
Introduction
sean@mcnealysoftware.com
https://github.com/seanmcnealy
JDBC sets isolation
What does this do?
�connection.setTransactionIsolation(TRANSACTION_SERIALIZABLE);
Why?
Do you have to?
SQL Isolation Levels[3]
The problem (maybe)
BEGIN TRANSACTION;
UPDATE table SET amount = amount - 1 where id = 1;
UPDATE table SET amount = amount + 1 where id = 2;
COMMIT TRANSACTION;
We’ve moved 1 “amount” from one record to another.
?
!
The problem #2
SELECT id, name FROM customers LIMIT 20;
SELECT COUNT(*) FROM customers;
You could get a different number of rows in the first select than the aggregation call without repeatable reads.
?
!
SQL Integrity Levels
Follow along with code
github.com/seanmcnealy/sql_examples
Isolation Implementations
Why choose levels?
Performance / Concurrency
Throughput
Latency and Backpressure
Transactions are configurable
Transactions can hint they are read only:
connection.setReadOnly(true);
Database Support
Everyone is different!
Database Support:
MySQL
Default is READ_COMMITTED
REPEATABLE_READ stops a phantom read
Database Support:
PostgreSQL
Default is READ_COMMITTED
REPEATABLE_READ stops a phantom read
READ_UNCOMMITTED is just READ_COMMITTED
Database Support:
Microsoft SQL Server
Default is READ_COMMITTED
Has its own extra level
TRANSACTION_SNAPSHOT, which is REPEATABLE_READ but hints how to do that read
Database Support:
SQLite
Default is SERIALIZABLE
*If caching is OFF then READ_UNCOMMITTED is equal to READ_COMMITTED
Database Support:
Oracle
Default is READ_COMMITTED
Supports SERIALIZABLE
Does not support READ_UNCOMMITTED or REPEATABLE_READ
Database Support:
DB2
Default is READ_COMMITTED
Database Support:
Snowflake
Only READ_COMMITTED
Database Support:
Hive, Cassandra, Druid…
Don’t set any level, it won’t work
These are just designed differently
Database Support:
H2
Legacy Mode:
Default is READ_COMMITTED
Supports other levels
MVCC Mode:
Only READ_COMMITTED
What Next
Application-level locking
Partitioning
By data
By time
Streaming
Code
@Transactional(readOnly = true, isolation = Isolation.READ_UNCOMMITTED)
When using SERIALIZABLE get write locks early
@Lock(LockModeType.PESSIMISTIC_WRITE)
connection.setAutoCommit(false);
connection.setReadOnly(true);
connection.setTransactionIsolation( Connection.TRANSACTION_READ_COMMITTED);
When using SERIALIZABLE get write locks early
SELECT ... FOR UPDATE
JDBC
Spring Annotations
Sean McNealy
Questions?
Thank you
McNealy Software Inc
sean@mcnealysoftware.com
Citations
1. Haerder, T.; Reuter, A. (1983). "Principles of transaction-oriented database recovery". ACM Computing Surveys.
2. ANSI X3.135-1986. https://nvlpubs.nist.gov/nistpubs/Legacy/FIPS/fipspub127.pdf
3. ISO/IEC 9075:1992. https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt