Transactions are essential to concurrent systems, ensuring data integrity and consistency across operations, and preventing race conditions and concurrency conflicts in the right isolation levels. The configured isolation level plays a crucial role in the behavior and guarantees of transactions, with a trade-off for performance. Administrators need to understand the available options to make the right choice for their application's needs.
SQL Transactions
A transaction is a series of SQL statements that are treated as a single operation, where either the entire transaction succeeds, or none of it. For example:
BEGIN;
INSERT INTO users (id, name) VALUES (1, "bob");
INSERT INTO users (id, name) VALUES (1, "joe");
COMMIT;
If the second INSERT statement fails, then the entire transaction will be rolled back, including the first INSERT that had no problems. A transaction can also be intentionally discarded by executing ROLLBACK instead of COMMIT at the end.
Isolation levels
Isolation levels don't affect this basic behavior of transactions, but rather how transactions interact with other transactions running at the same time, in other words how well they are isolated from each other. There are four standardized isolation levels: Read Uncommitted, Read Committed, Repeatable Read and Serializable. SQL-compatible databases will offer most of these isolation levels, although some may not include all (for example PostgreSQL does not support Read Uncommitted, because it's architecture effectively makes such data inaccessible to queries).
The isolation level can be set with the SET TRANSACTION statement, either for a single transaction or an entire session. If no isolation level was set manually, a default value from the database or it's configuration is assumed for transactions. How exactly transaction isolation levels are specified varies between databases, for example PostgreSQL allows changing the isolation level from within a transaction:
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Transaction logic
COMMIT;
whereas MySQL expects the isolation level to be set immediately before starting a transaction:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
-- Transaction logic
COMMIT;
The isolation level in MySQL is automatically reset to the default value after it's transaction block ends, which may lead to confusion.
Read Uncommitted
The first type of isolation level can be interpreted as "none", as it effectively gives no consistency guarantees whatsoever. Read Uncommitted allows read operations within transactions to read the newest possible value, even if that comes from a transaction that is still running, and may possibly be rolled back. This concept is also referred to as "dirty reads", as reading data may well return values that will later be rejected by the database if the transaction fails.
To give an example, let's assume a first transaction starts like this:
transaction #1
BEGIN;
UPDATE users SET name="tom" WHERE id=1;
While the first transaction is still running, a second one reads the data:
transaction #2
BEGIN:
SELECT name FROM users WHERE id=1; -- returns "tom"
COMMIT;
Now, the first transaction encounters a problem and rolls back:
transaction #1
ROLLBACK;
What happens here? Even though transaction #1 rolls back in the end, Read Uncommitted allows reading the most recent data, which was the partial update from transaction #1, so transaction #2 which ran at the same time happened to catch that "dirty" value, which didn't end up being committed to the database.
As you can see, this isolation level is equivalent to having no isolation at all, but that also offers significant performance benefits, as no locking or checks need to be in place for isolation requirements. It trades consistency for increased performance, which may be acceptable for systems that can deal with temporarily wrong data, like analytical systems generating estimates from large datasets, or monitoring applications that do not focus on single values, but identify trends or thresholds instead.
Read Committed
The next step up from no isolation will ensure that transactions can only read data was successfully committed by other transactions. This fixes the dirty read problem, but still leaves room for a consistency issue: Running the same read query twice may produce different results if a transaction changed the value inbetween reads, called "unrepeatable reads" (repeating a read within a transaction is not guaranteed to produce the same result). Effectively, transaction in this isolation level can experience race conditions.
Let's have a closer look with an example of a banking application: A bank allows it's clients to send money to other accounts, but only if they have enough for the transfer (no credit or negative balances allows). To facilitate this, they start all transfer transactions with a SELECT to verify the user has enough balance to make the transfer. Assume user "bob" has $100 in their bank account and wants to send it all to another account, starting a first transactions:
transaction #1
BEGIN;
SELECT amount FROM users WHERE name="bob";
-- returns 100, so user has enough money to make the transfer
-- bank now waits on external system, maybe to check if remote account exists
In the meantime, user "bob" accidentally clicks the transfer button again, starting a second transaction:
transaction #2
BEGIN;
SELECT amount FROM users WHERE name="bob";
-- returns 100, so user has enough money to make the transfer
-- bank now waits on external system, maybe to check if remote account exists
The second transaction also reads "100" for the account's balance, because the first one has not committed the new value after the complete transfer yet. Now the first transaction completes:
transaction #1
UPDATE users SET amount = amount-100 WHERE name="bob";
COMMIT;
After the first commit, user "bob" has now $0 in their account, but the second transaction will also complete:
transaction #2
UPDATE users SET amount = amount-100 WHERE name="bob";
COMMIT;
After the second transaction completes, "bob" now has -$100 in their account! Despite the transaction including a SELECT query to verify the user has enough funds to transfer, the race condition still allowed them to send more money than they had in their account.
While obviously problematic for financial applications, using Read Committed is a reasonable choice of isolation level for most applications, from website backends and CMS to social media feeds and analytics platforms. Slight inconsistencies in returned values will typically not cause issues for those systems, at worst they may display an outdated page or a post that was deleted a second ago, causing no real harm to the user experience or security features - but the performance impact for isolation is almost negligible.
Repeatable Read
In order to prevent race conditions on read operations, the Repeatable Read isolation level guarantees that reading the same value again will always produce the same value within a transaction. By fixing this consistency issue, one last problem remains that affects even transactions of this isolation level: phantom reads. A phantom read is similar to the race condition of unrepeatable reads, but in a specific situation where a write transaction races with a read transaction.
Assuming a simplified example:
transaction #1
BEGIN;
SELECT name FROM users; -- returns "bob", "jane"
The first part of the transaction returns two rows that were already in the table. At the same time, a second transaction runs:
transaction #2
BEGIN;
UPDATE users SET name="tim" WHERE name="bob";
INSERT INTO users (name) VALUES ("joe");
COMMIT;
This transaction changes "bob"'s name to "tom", then adds a new row for user "joe". The first transaction includes a second read which runs after this:
transaction #1
SELECT name FROM users; -- returns "bob", "jane", "joe"
COMMIT;
While the second read returns the old value for user "bob", ignoring the changed value (aka the read is repeatable, it returns the same data as before), it does include the newly added row that wasn't available on the first read. This is the phantom read mentioned earlier, and is not covered by the Repeatable Read guarantees, which only guarantees that a row value that was read once will always return the same read value within the transaction, but newly added rows weren't read before so they are out of scope.
Using the Repeatable Read isolation level puts significantly more strain on the system, as guaranteeing that reads produce the same values requires more work in one way or the other, for example caching of previous results, locking or snapshot logic. Only some applications have reasonable arguments to choose this isolation level, like e-commerce platforms or inventory management applications. The added complexity and cost heavily depends on the database implementation, with the more popular ones still providing a decent tradeoff between consistency and performance at this level, albeit significantly slower than Read Committed.
Serializable
The strongest isolation level fully serializes transactions, meaning they work in a series (or queue) and cannot physically affect one another. Implementing this requires a lot more processing than the other isolation levels, often involving locking at row-level or table-level to work. Serializable isolation aims to eliminate all side-effects of concurrent transactions, at the cost of concurrency: Some transactions may still run concurrently (for example multiple read-only ones), but anything that could have side-effects will need to be queued sequentially, causing heavy slowdowns of processing speeds.
This isolation level is only useful for applications that need absolute consistency, like financial systems or medical/scientific application backbones, that need access to consistent and accurate data at all times, no matter the cost. Using this isolation level on busy or highly concurrent systems will either overload the database or be excessively expensive in resource usage, making it unsuitable for applications that don't explicitly demand these consistency guarantees.
Know your database
Isolation levels are important to understand, but the design of the database using them is equally as important. A naive comparison would observe that MySQL's default isolation level is Repeatable Read, whereas PostgreSQL defaults to Read Committed, concluding that PostgreSQL's default configuration is less consistent than MySQL's. At closer inspection however, PostgreSQL uses MVCC (multi version concurrency control), effectively providing a point-in-time snapshot to transactions that mitigates almost all cases of non-repeatable and even phantom reads. Practically, PostgreSQL's design allows it to have consistency almost matching MySQL's Serializable isolation level, but at the performance of Repeatable Read isolation.
It is important to understand how your database works, how they comply with the standardized isolation levels, and what caveats that may have. For the MySQL example, choosing different storage engines means different implementations of isolation levels, with some combinations being at risk of deadlock situations, where a lock on a table or row is held indefinitely (effectively making the database unavailable). Other databases may not follow the exact academic definition of an isolation level, or decide to make exceptions to the consistency guarantees, so be sure to carefully read the documentation of your specific SQL database!