Transaction isolation levels

A transaction isolation level can be set by the developer by invoking the following SQL statement:

SET TRANSACTION ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED}

The SET TRANSACTION ISOLATION LEVEL statement should be called inside a transaction block before any query, otherwise, it will have no effect. An alternative is to use the following syntax:

BEGIN TRANSACTION ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED}

Finally, one can change the whole default database isolation level to SERIALIZABLE as follows:

ALTER DATABASE <DATABASE NAME> SET DEFAULT_TRANSACTION_ISOLATION TO SERIALIZABLE ;

As shown by the preceding SQL statement, the transaction isolation levels are:

  • SERIALIZABLE: The SERIALIZABLE isolation level is the strongest consistency level; it relieves the developer of planning for concurrency effects. The cost of using the serialize isolation model is often performance. In the SQL standard, SERIALIZABLE is the default isolation model. In PostgreSQL, the default isolation model is READ COMMITTED
  • REPEATABLE READ: REPEATABLE READ is the second strongest transaction isolation level; it is similar to READ COMMITTED in that it allows only the reading of committed data. It also guarantees that any data read cannot be changed.
  • READ COMMITTED: This is the default PostgreSQL model; it allows committed data to be read by the transaction. In this level, performance is favored over preciseness. 
  • READ UNCOMMITTED: This is the weakest transaction isolation level. It allows uncommitted data to be read.
READ UNCOMMITTED is not supported in PostgreSQL and is treated as READ COMMITTED. PostgreSQL only supports three levels.

The transaction isolation level can be explained by having a look at the side effects of each level, which are:

  • Dirty read: A dirty read occurs when a transaction reads data from a tuple that has been modified by another running transaction and not yet committed. In PostgreSQL, this cannot happen since READ UNCOMMITTED is unsupported.
  • Nonrepeatable read: A nonrepeatable read occurs when, during the course of a transaction, a row is retrieved twice and the values within the row differ between reads. A nonrepeatable read occurs in the READ COMMITTED isolation level and is often a result of updating a row several times with other transactions.
  • Phantom read: A phantom read occurs when a new row (or rows) disappears since the beginning of the transaction. This is often a result of committed inserts followed by committed deletes. A phantom read occurs when you select a set of rows several times and a different result is returned within the same transaction. A phantom read occurs in the READ COMMITTED and REPEATABLE READ levels based on the SQL standard definition. In PostgreSQL, a phantom read occurs only in READ COMMITTED.
  • Serialization anomaly: The result of executing a group of transactions is inconsistent with all possible ordering of running these transactions. This can occur only in REPEATABLE READ
SQL standard allows phantom read in the REPEATABLE READ isolation level. In PostgreSQL, that is not allowed. That means that only a serialization anomaly can happen in REPEATABLE READ.

To understand different transaction isolation side effects, let's show the nonrepeatable read side effect; for that, we will use a test_tx_level table, which is defined as follows:

postgres=# CREATE TABLE test_tx_level AS SELECT 1 AS val;
SELECT 1
postgres=# TABLE test_tx_level ;
val
-----
1
(1 row)

To test repeatable read, the default transaction isolation model READ COMMITTED will be used; the T as in T1 is used to indicate the execution time in ascending order, as follows: 

Session 1 Session 2
T1
postgres=# BEGIN;
BEGIN
postgres=# SELECT * FROM test_tx_level ;
val
-----
1
(1 row)
T2
postgres=# BEGIN;
BEGIN
postgres=# UPDATE test_tx_level SET val = 2;
UPDATE 1
postgres=# COMMIT;
COMMIT
T3
postgres=# SELECT * FROM test_tx_level ; val 
-----
2
(1 row)
postgres=# COMMIT;
COMMIT

 

 

The val value has changed from 1 to 2 in session 1. The change that are committed by session 2 are reflected in session 1. This is called a nonrepeatable read. Also notice that we have used BEGIN without specifying the transaction isolation level, and in this case, the default isolation level is used. 

To check phantom read, again, the default transaction isolation level will be used, as follows: 

Session 1 Session 2
T1
postgres=# BEGIN;
BEGIN
postgres=# SELECT count(*) FROM test_tx_level ;
count
-------
1
(1 row)
T2
postgres=# BEGIN;
BEGIN
postgres=# INSERT INTO test_tx_level SELECT 2;
INSERT 0 1
postgres=# COMMIT;
COMMIT
T3
postgres=# SELECT count(*) FROM test_tx_level ;
count
-------
2
(1 row)
postgres=# COMMIT;

 

Phantom read and nonrepeatable read can occur, as shown in the preceding examples, in the READ COMMITTED transaction isolation level. The scope of nonrepeatable read is often a certain row or set of rows, while the scope of phantom read is all the table. Nonrepeatable read occurs when the transaction reads committed updates from another transaction, while phantom read happens as a result of committed inserts and deletes.

If one runs the same preceding examples with the SERIALIZABLE and REPEATABLE READ isolation level, we will see that the result of session 1 is not affected by session 2, as shown in the following example:

Session 1 Session 2
T1
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
BEGIN
postgres=# SELECT count(*) FROM test_tx_level ;
count
-------
2
(1 row)

T2
postgres=# BEGIN;
BEGIN
postgres=# INSERT INTO test_tx_level SELECT 2;
INSERT 0 1
postgres=# COMMIT;
COMMIT
T3
postgres=# SELECT count(*) FROM test_tx_level ;
count
-------
2
(1 row)

 

In PostgreSQL versions prior to 9.1, PostgreSQL has only two transaction isolation levels. This changed with the introduction of PostgreSQL 9.1, where a true SERIALIZABLE transaction isolation model is submitted. The SERIALIZABLE transaction isolation level protects data against many anomalies, such as write skew. Write skew happens when two transactions read overlapping data, concurrently make updates, and finally commit the data. For example, let's assume we have a table containing ones and zeros; the first transaction wants to change the ones to zeros, and the second transaction wants to change the zeros to ones. If transactions are executed in a serial manner, we should get either ones or zeros based on which transaction is executed first. To demonstrate this anomaly, let's create a table, as follows:

postgres=# CREATE TABLE zero_or_one (val int);
CREATE TABLE
postgres=# INSERT INTO zero_or_one SELECT n % 2 FROM generate_series(1,10) as foo(n) ;
INSERT 0 10
postgres=# SELECT array_agg(val) FROM zero_or_one ;
array_agg
-----------------------
{1,0,1,0,1,0,1,0,1,0}
(1 row)

To see the effect of write skew, let's start two sessions with the REPEATABLE isolation level, as follows:

Session 1 Session 2
T1
postgres=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
BEGIN
postgres=# UPDATE zero_or_one SET val = 1 WHERE val = 0;
UPDATE 5
T2
postgres=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
BEGIN
postgres=# UPDATE zero_or_one SET val =0 WHERE val =1;
UPDATE 5
postgres=# COMMIT;
COMMIT
T3
postgres=# COMMIT;
COMMIT

 

Let's now have a look at the final result of the table:

postgres=# SELECT array_agg(val) FROM zero_or_one ;
array_agg
-----------------------
{1,1,1,1,1,0,0,0,0,0}
(1 row)

To see what happens in the serializable transaction mode, let's truncate the tables and rerun the example:

postgres=# truncate zero_or_one ;
TRUNCATE TABLE
postgres=# INSERT INTO zero_or_one SELECT n % 2 FROM generate_series(1,10) as foo(n) ;
INSERT 0 10
Session 1 Session 2
T1
postgres=# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
BEGIN
postgres=# UPDATE zero_or_one SET val = 1 WHERE val = 0;
UPDATE 5
T2
postgres=# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
BEGIN
postgres=# UPDATE zero_or_one SET val =0 WHERE val =1;
UPDATE 5
postgres=# COMMIT;
T3
postgres=# 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.

 

In the repeatable read isolation mode, both transactions were executed without an error. However, the end result was wrong. In the case of the serializable isolation level, when there is write skew, transactions will proceed until one transaction commits. The first committer wins and other transactions are rolled back. The first committer wins rule guarantees that we will make progress. Finally, note that only one transaction succeeded and the other has failed. Also note the hint the transaction might succeed if retried, to see the final result:

postgres=# SELECT array_agg(val) FROM zero_or_one ;
array_agg
-----------------------
{0,0,0,0,0,0,0,0,0,0}
(1 row)

More information on the REPEATABLE READ and SERIALIZABLE transaction isolation levels can be found on the serializable snapshot isolation (SSI) wiki page at https://wiki.postgresql.org/wiki/SSI.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset