PostgreSQL Transaction Isolation

• 14 十月 2021
• PostgreSQL,
• Transaction

The ANSI standard defines four levels of transaction isolation and they are: Read Uncommitted, Read Committed, Repeatable Read and Serializable. However, the transaction isolation levels are different from database to database. For example, the database vendor PostgreSQL supports three levels: Read Committed, Repeatable Read and Serializable and Read Committed is the default. While the MySQL database support all of them and the default isolation level is Repeatable Read. Each isolation level specifies the kinds of phenomenon that are either prohibited or permitted while concurrent transactions are executing. The phenomena are shown below:

• dirty read

  A transaction reads uncommitted data from another transaction.

• nonrepeatable read

  A transaction reads same data twice but values are different because another transaction commits changes after the first read and before the second read starts.

• phantom read

  A transaction reads same data twice but selected rows are different different because another transaction commits changes (insert or delete rows) after the first read and before the second read starts.

• serialization anomaly

  According to the PostgreSQL docs, serialization anomaly is explained as follows:

  The result of successfully committing a group of transactions is inconsistent with all possible orderings of running those transactions one at a time

The relationship between SQL standard (read phenomena) and transaction isolation level in PostgreSQL:

Preparation #

In the following examples, we will create two transactions and this can be acheived by connecting PostgreSQL server using two terminals.

Connect to postgres database:

psql postgres

Start a transaction:

BEGIN;

or

BEGIN TRANSACTION;

End a transaction:

COMMIT;

or

END TRANSACTION;

Undo a transaction:

ROLLBACK;

Set transaction isolation level:

SET TRANSATION ISOLATION LEVEL [transaction_mode]

Create a table named t_user and insert some records:

CREATE TABLE t_user (
    id bigint NOT NULL,
    created_date timestamp without time zone,
    password character varying(255),
    username character varying(255)
);
ALTER TABLE ONLY t_user
    ADD CONSTRAINT t_user_pkey PRIMARY KEY (id);

INSERT INTO t_user (id, created_date, username, password) VALUES (1, CURRENT_TIMESTAMP, 'caizhenhua', '123');

Read Committed Level Example #

Nonrepeatable Read (Possible) #

1. Create transaction 1 and tranaction 2

2. They have the same transaction isolation level which is Read Committed by default

3. In transaction 1, execute this query select * from t_user where id = 4;

    id |        created_date        | password | username 
   ----+----------------------------+----------+----------
     4 | 2021-10-12 21:15:48.483905 | 123      | admin
   (1 row)
   

4. In transaction 2, update the above password to anything but 123 and end the transaction

   update t_user set password = 'abc' where id = 4;
   commit;

5. Now go to transaction 1, execute the select query again

    id |        created_date        | password | username 
   ----+----------------------------+----------+----------
     4 | 2021-10-12 21:15:48.483905 | abc      | admin
   (1 row)
   

This proves that nonrepeatable read is possible in Read Committed level.

Phantom Read (Possible) #

1. Create transaction 1 and tranaction 2

2. In transaction 1, execute this query select count(*) from t_user;

    count 
   -------
        8
   (1 row)
   

3. In transaction 2, insert a new row into the table t_user and commit

   INSERT INTO t_user (id, created_date, username, password) VALUES (13, CURRENT_TIMESTAMP, 'caizhenhua', '123');
   commit;

4. Now go to transaction 1, execute the select query again

    count 
   -------
        9
   (1 row)
   

This proves that phantom read is possible in Read Committed level.

Serialization Anomaly (Possible) #

1. Create transaction 1 and tranaction 2

2. In both transaction 1 and tranaction 2, insert an identical record except id and commit them one by one

   -- tx1
   INSERT INTO t_user (id, created_date, username, password) VALUES (14, CURRENT_TIMESTAMP, 'caizhenhua', '123');
   commit;
   -- tx2
   INSERT INTO t_user (id, created_date, username, password) VALUES (15, CURRENT_TIMESTAMP, 'caizhenhua', '123');
   commit;

3. Both of them (transactions) are successfully committed

    14 | 2021-10-14 20:57:12.546539 | 123      | caizhenhua
    15 | 2021-10-14 20:57:15.049221 | 123      | caizhenhua
   

This proves that duplicate records is not prevented in Read Committed level. This example actually does not demonstrate the the serialization anomaly correctly because the values of created_date for these two transactions are different. One possible solution is to ignore the timestamp.

-- tx1
INSERT INTO t_user (id, username, password) VALUES (14, 'caizhenhua', '123');
-- tx2
INSERT INTO t_user (id, username, password) VALUES (15, 'caizhenhua', '123');

Repeatable Read Level Example #

Nonrepeatable Read (Not Possible) #

1. Create transaction 1 and tranaction 2 and set the transaction isolation level of transaction 1 to Repeatable Read

   BEGIN;
   set transaction isolation level repeatable read;

2. Verify the transaction isolation level by show transaction isolation level;

    transaction_isolation 
   -----------------------
    repeatable read
   (1 row)
   

3. In transaction 1, execute this query select * from t_user where id = 4;

    id |        created_date        | password | username 
   ----+----------------------------+----------+----------
     4 | 2021-10-12 21:15:48.483905 | abc      | admin
   (1 row)
   

4. In transaction 2, update the above password to anything but 123 and end the transaction

   update t_user set password = 'efg' where id = 4;
   commit;

5. Now go to transaction 1, execute the select query again

    id |        created_date        | password | username 
   ----+----------------------------+----------+----------
     4 | 2021-10-12 21:15:48.483905 | abc      | admin
   (1 row)
   

The password remains unchanged! This proves that repeatable read is possible in Repeatable Read level.

Phantom Read (Not Possible) #

1. Create transaction 1 and tranaction 2 and set the transaction isolation level of transaction 1 to Repeatable Read

2. In transaction 1, execute this query select count(*) from t_user;

    count 
   -------
       11
   (1 row)
   

3. In transaction 2, insert a new row into the table t_user and commit

   INSERT INTO t_user (id, created_date, username, password) VALUES (16, CURRENT_TIMESTAMP, 'caizhenhua', '123');
   commit;

4. Now go to transaction 1, execute the select query again

    count 
   -------
        11
   (1 row)
   

The total number of rows in t_user remains 11, this proves that the Repeatable Read level in PostgreSQL also disallows the Phantom Read phenomena.

Serializable Level Example #

Serialization Anomaly (Not Possible) #

1. Create transaction 1 and tranaction 2 and set both the transaction isolation level to Serializable

   -- tx1
   BEGIN;
   set transaction isolation level serializable;
   -- tx2
   BEGIN;
   set transaction isolation level serializable;

2. Verify the transaction isolation level by show transaction isolation level;

    transaction_isolation 
   -----------------------
    serializable
   (1 row)
   

3. In transaction 1, execute this query select * from t_user;

    id |        created_date        | password |  username  
   ----+----------------------------+----------+------------
     4 | 2021-10-12 21:15:48.483905 | efg      | admin
    16 | 2021-10-14 21:14:40.904828 | 123      | caizhenhua
    18 | 2021-10-14 21:22:06.894146 | 123      | caizhenhua
    19 | 2021-10-14 21:22:09.319714 | 123      | caizhenhua
   

4. In transaction 1, insert the following record:

   INSERT INTO t_user (id, username, password) VALUES (20, 'caizhenhua', '123');

5. In transaction 1, execute this query select * from t_user; again

    id |        created_date        | password |  username  
   ----+----------------------------+----------+------------
     4 | 2021-10-12 21:15:48.483905 | efg      | admin
    16 | 2021-10-14 21:14:40.904828 | 123      | caizhenhua
    18 | 2021-10-14 21:22:06.894146 | 123      | caizhenhua
    19 | 2021-10-14 21:22:09.319714 | 123      | caizhenhua
    20 |                            | 123      | caizhenhua
    (15 rows)
   

6. In transaction 2, execute this query select * from t_user;, the result is same as step 3

7. In transaction 2, insert the following record:

   INSERT INTO t_user (id, username, password) VALUES (21, 'caizhenhua', '123');

8. In transaction 2, execute this query select * from t_user; again

     4 | 2021-10-12 21:15:48.483905 | efg      | admin
    16 | 2021-10-14 21:14:40.904828 | 123      | caizhenhua
    18 | 2021-10-14 21:22:06.894146 | 123      | caizhenhua
    19 | 2021-10-14 21:22:09.319714 | 123      | caizhenhua
    21 |                            | 123      | caizhenhua
   (15 rows)
   

9. Commit transaction 1

10. Commit transaction 2 and throws an error:

    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.
    

This proves that in Serializable level, the inconsistent commits of a group of transactions are prevented.

• Previous: Java 11 HTTP Client Examples
• Next: Spring @Transactional Annotation