Transaction isolation levels

Eugenia Kuzina

Contents

Read committed isolation level
Repeatable read isolation level
Serializable isolation level

The SQL standard defines four levels of transaction isolation. The most strict is Serializable. According to the SQL standard, any concurrent execution of a set of Serializable transactions should produce the same effect as running them one at a time in some order. The other three levels are defined in terms of phenomena. These phenomena are the result of interactions between concurrent transactions. Some of them must not occur at a particular level of transaction isolation. None of these phenomena are possible at the Serializable level.

The following phenomena may occur:

Dirty read — a transaction reads data written by a concurrent uncommitted transaction.
Non-repeatable read — a transaction re-reads data it previously read and finds that the data has been modified by another transaction (that was committed since the initial read).
Phantom read — a transaction re-executes a query that returns a set of rows for some condition and discovers that the set of rows satisfying the condition has changed due to another recently committed transaction.
Serialization anomaly — the result of successfully committing a group of transactions is inconsistent with all possible orderings of running these transactions one at a time.

The transaction isolation levels are described in the table below. Not allowed in ADPG/PostgreSQL is used for phenomena that are allowed in the SQL standard but are prohibited in ADPG/PostgreSQL.

Isolation level

Dirty read

Non-repeatable read

Phantom read

Serialization anomaly

Read uncommitted

Not allowed in ADPG/PostgreSQL

Possible

Read committed

Not possible

Possible

Repeatable read

Not possible

Not allowed in ADPG/PostgreSQL

Possible

Serializable

Not possible

In ADPG/PostgreSQL, you can request any of four standard transaction isolation levels, but internally only three distinct isolation levels are implemented. The PostgreSQL Read uncommitted mode behaves like Read committed. This is because it is the only way to map the standard isolation levels to the PostgreSQL multiversion concurrency control architecture.

The table also shows that the PostgreSQL Repeatable read implementation does not allow phantom reads. This is acceptable under the SQL standard because the standard specifies which anomalies must not occur at certain isolation levels. More stringent restrictions are acceptable.

IMPORTANT

Some PostgreSQL data types and functions have special rules regarding transactional behavior. In particular, changes made to a sequence (and therefore the counter of a column declared using serial) are immediately visible to all other transactions and are not rolled back if the transaction that made the changes aborts.

The default isolation level in ADPG/PostgreSQL is Read committed. To confirm this, run the following psql command:

SHOW TRANSACTION ISOLATION LEVEL;

The result:

 transaction_isolation
-----------------------
 read committed
(1 row)

To select the desired transaction isolation level, use the SET TRANSACTION command.

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
...
COMMIT;

It is also possible to set the transaction isolation level in the BEGIN and START TRANSACTION commands.

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
...
COMMIT;

START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
...
COMMIT;

Read committed isolation level

As mentioned above, Read committed is the default isolation level in ADPG/PostgreSQL. When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) sees only data committed before the query began. It never accesses either uncommitted data or changes committed during query execution by concurrent transactions. Essentially, a SELECT query sees a snapshot of the database at the time the query starts executing. However, SELECT sees the results of previous updates performed within its own transaction, even if the results have not yet been committed. Also note that two successive SELECT commands can get different data, even they are executed within a single transaction, if other transactions commit changes after the first SELECT starts and before the second SELECT runs.

The UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they only find target rows that were committed when the command started. However, a target row can be updated, deleted, or locked by another concurrent transaction by the time it is found. In this case, the updater will wait for the first updating transaction to commit or roll back if it is still in progress. If the first updater rolls back, the second updater can proceed to update the originally found row. If the first updater commits, the second updater will attempt to apply its operation to the updated version of the row. It will ignore the row if the first updater deleted it. The search condition of the command (the WHERE clause) is re-evaluated to see if the updated row still matches the search condition. If so, the second modifying transaction continues with the modified version of the row. In the case of SELECT FOR UPDATE and SELECT FOR SHARE, the updated version of the row is locked and returned to the client.

INSERT with ON CONFLICT DO UPDATE clause behaves similarly. In Read committed mode, each row proposed for insertion will be either inserted or updated. If a conflict is caused by another transaction whose result is not yet visible to INSERT, the UPDATE clause will act on that row, even though possibly no version of that row is conventionally visible to the command.

In Read committed mode, when you issue an INSERT with an ON CONFLICT DO NOTHING clause, the row may not be added as a result of another transaction whose effect is not visible in the snapshot of the INSERT command.

Because of the above rules, it is possible for an updating command to see an inconsistent snapshot: it can see the effects of concurrent updating commands on the same rows it is trying to update, but it does not see effects on other rows in the database. This behavior makes the Read committed mode unsuitable for commands that involve complex search conditions. However, you can use it for simpler cases. For example, consider updating bank balances — transferring money from one account to another with the following transactions:

BEGIN;
UPDATE accounts SET balance = balance - 1000.00 WHERE acc_number = 5432;
UPDATE accounts SET balance = balance + 1000.00 WHERE acc_number = 1234;
COMMIT;

BEGIN;
UPDATE accounts SET balance = balance - 1000.00 WHERE acc_number = 6543;
UPDATE accounts SET balance = balance + 1000.00 WHERE acc_number = 1234;
COMMIT;

If these two transactions concurrently try to change the balance of the 1234 account, the second transaction must operate on the modified version of the row.

BEGIN;
UPDATE accounts SET balance = balance - 1000.00 WHERE acc_number = 5432;

/* Run from another session:
BEGIN;
UPDATE accounts SET balance = balance - 1000.00 WHERE acc_number = 6543;
UPDATE accounts SET balance = balance + 1000.00 WHERE acc_number = 1234;
COMMIT;
*/

UPDATE accounts SET balance = balance + 1000.00 WHERE acc_number = 1234;
COMMIT;

Since each command only affects a specific row, if the command sees a modified version of the row, this will not lead to inconsistency problems.

More complex scenarios can produce undesirable results in Read committed mode. For example, consider a DELETE command operating on data that is being both added and removed from its restriction criteria by another command. For example, website is a two-row table with website.hits that equal 9 and 10. Perform the following transactions:

BEGIN;
UPDATE website SET hits = hits + 1;
-- Run from another session: DELETE FROM website WHERE hits = 10;
COMMIT;

DELETE will have no effect, even though there is a row with hits equaling 10 before and after UPDATE. This occurs because the pre-update row value 9 is skipped, and when UPDATE completes and DELETE obtains a lock, the new row value is no longer 10 but 11, which does not match the criteria.

The partial transaction isolation provided by Read committed mode is suitable for many applications. This mode is fast and easy to use, but it is not suitable for all situations. Applications that perform complex queries and changes may require a more rigorously consistent view of the database than the Read committed mode provides.

Repeatable read isolation level

In Repeatable read mode, only the data that was committed before the transaction started is visible, but uncommitted data and changes made by other transactions while the transaction was in progress are not visible. However, the query sees the effects of previous updates executed within its own transaction, even though they are not yet committed. This prevents all the phenomena described in the phenomena table except for serialization anomalies.

This level differs from Read committed in that a query in a Repeatable read transaction sees a snapshot of the data at the start of the first statement in the transaction (transaction control commands are not taken into account). Successive SELECT commands in the same transaction see the same data — they do not see changes made and committed by other transactions after the current transaction began.

Applications using this level must be prepared to retry transactions due to serialization failures.

The UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave like SELECT when searching for target rows. They only find target rows that were committed at the time the transaction began. However, by the time they are found, these target rows may already have been modified, deleted, or locked by another concurrent transaction. In this case, a transaction in Repeatable read mode waits for the first transaction that changes data to be committed or rolled back (if it is still in progress). If the first updater rolls back, the Repeatable read transaction can proceed with updating the originally found row. If the first updater commits and updates or deletes the row, not just locks it, the Repeatable read transaction will be rolled back with the message: ERROR: could not serialize access due to concurrent update. A Repeatable read transaction cannot modify or lock rows changed by other transactions after the Repeatable read transaction began. When an application receives this error message, it should abort the current transaction and try to retry it from the beginning. Note that only updating transactions might need to be retried. Read-only transactions will never have serialization conflicts.

The Repeatable read mode provides a rigorous guarantee that each transaction sees a completely stable view of the database. However, this view will not necessarily always be consistent with some serial execution of concurrent transactions of the same level.

The Repeatable read isolation level is implemented using a technique known as Snapshot isolation.

Serializable isolation level

The Serializable isolation level provides the strictest transaction isolation. This level emulates serial transaction execution for all committed transactions, as if transactions are executed one after another, serially, rather than concurrently. However, as with the Repeatable read level, applications must be prepared to retry transactions due to serialization failures.

This isolation mode works in the same way as Repeatable read, only it additionally monitors for conditions under which the result of serializable transactions executed in parallel may not be consistent with the result of the same transactions executed in turn. This monitoring does not introduce any blocking beyond that present in Repeatable read, but it creates some additional overhead. If a serialization anomaly is detected, the serialization fails.

As an example, consider table1 that initially contains the following data:

 id | category | value
----+----------+-------
  1 |        a |    10
  2 |        a |    20
  3 |        b |   300
  4 |        b |   400

Suppose that the serializable transaction 1 calculates the sum of values in the a category and inserts it in the table as a value of the b category:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO table1(id, category, value) VALUES
(5,'b',
(SELECT SUM(value) FROM table1 WHERE category = 'a'));

The serializable transaction 2 calculates the sum of values in the b category and inserts it in the table as a value of the a category:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO table1(id, category, value) VALUES
(6,'a',
(SELECT SUM(value) FROM table1 WHERE category = 'b'));

Now, if you execute COMMIT of the transaction 1, it is committed without errors. Then, if you run COMMIT of the transaction 2, the following error occurs:

ERROR: could not serialize access due to read/write dependencies among transactions

This is because if the transaction 1 had executed before the transaction 2, the transaction 2 would have computed the sum 730, not 700, and similarly the other order would have resulted in a different sum computed by the transaction 1.

When relying on Serializable transactions to prevent anomalies, it is important that any data read from a permanent table not be considered valid until the transaction which read it has successfully committed. This is true even for read-only transactions, except that data read within a deferrable read-only transaction is known to be valid as soon as it is read, because such a transaction waits until it can acquire a snapshot guaranteed to be free from such problems before starting to read any data. In all other cases, applications should not rely on the results of reading data in a transaction that has not been committed. In case of error and rollback, applications must retry the transaction until it completes successfully.

To fully guarantee serializability, ADPG/PostgreSQL uses predicate locks, which are locks that allow you to determine when a write would affect the result of a previous read from a concurrent transaction if that write was executed first. These locks do not result in actual data locks and therefore cannot cause transaction deadlocks in any way. They help identify and mark dependencies between concurrent Serializable level transactions, which in certain combinations can lead to serialization anomalies. Read committed or Repeatable read transactions, on the contrary, to ensure data integrity, must either lock entire tables, which will prevent users from accessing these tables, or use SELECT FOR UPDATE or SELECT FOR SHARE, which not only can block other transactions but also create additional load on the disk.

Predicate locks are based on data actually accessed by a transaction. They are displayed in the pg_locks system view with a mode value of SIReadLock. The particular locks acquired during the execution of a query depend on the query plan, and multiple finer-grained locks (e.g., tuple locks) may be combined into fewer coarser-grained locks (e.g., page locks) to save memory spent on lock tracking.

When used correctly, serializable transactions can simplify application development. It is important that an environment, which uses this technique, has a generalized way of handling serialization failures (which always return with an SQLSTATE value of 40001), because it will be very hard to predict exactly which transactions need to be rolled back to prevent serialization anomalies.

When using serializable transactions to manage concurrency, consider the following guidelines:

Declare transactions as READ ONLY when possible.
Control the number of active connections, using a connection pool if needed. This is always an important performance consideration, but it can be particularly important in a busy system using serializable transactions.
Contain no more commands in a single transaction than are necessary to ensure integrity.
Do not leave connections dangling "idle in transaction" longer than necessary. The configuration parameter idle_in_transaction_session_timeout can be used to automatically disconnect lingering sessions.
Eliminate explicit locks, SELECT FOR UPDATE, and SELECT FOR SHARE where no longer needed due to the protections provided by Serializable transactions.
When the system is forced to combine multiple page-level predicate locks into a single relation-level predicate lock due to memory pressure, the frequency of serialization failures may increase. You can avoid this by increasing the max_pred_locks_per_transaction, max_pred_locks_per_relation, and/or max_pred_locks_per_page parameters.
A sequential scan will always necessitate a relation-level predicate lock. This can result in an increased rate of serialization failures. It may be helpful to encourage the use of index scans by reducing random_page_cost and/or increasing cpu_tuple_cost. However, here it is important to compare the gain from reducing the number of rollbacks and restarts of transactions with the loss from possible less efficient execution of queries.

You can specify parameters mentioned above in the ADPG configurations section of the ADPG service settings. See Configuration parameters.

The Serializable isolation level is implemented using a technique known as Serializable snapshot isolation, which builds on Snapshot isolation by adding checks for serialization anomalies.

Found a mistake? Seleсt text and press Ctrl+Enter to report it