Read Committed and Serializable Isolation Levels

The previous section illustrated that users see only committed transactions. It did not address what happens if someone commits a transaction while you are in your own transaction. In some cases, you need to control whether other transaction commits are seen by your transaction.

POSTGRESQL's default isolation level, READ COMMITTED, allows you to see other transaction commits while your transaction is open. Figure  illustrates this effect.  

        test=> BEGIN WORK;
        BEGIN
        test=> SELECT COUNT(*) FROM trans_test;
         count 
        -------
             5
        (1 row) 
         
        test=> --
        test=> -- someone commits INSERT INTO trans_test
        test=> --
        test=> SELECT COUNT(*) FROM trans_test;
         count 
        -------
             6
        (1 row) 
         
        test=> COMMIT WORK;
        COMMIT  

First, the transaction does a SELECT COUNT(*). Then, while you are sitting at a psql prompt, someone INSERTs into the table. The next SELECT COUNT(*) shows the newly INSERTED row. When another user commits a transaction, it is seen by the current transaction, even if it is committed after the current transaction started.

You can, however, prevent your transaction from seeing changes made to the database. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE changes the isolation level of the current transaction. SERIALIZABLE isolation prevents the current transaction from seeing commits made by other transactions. Thus, any commit made after the start of the first query of the transaction is not visible. Figure  shows an example of a SERIALIZABLE transaction.  

        test=> BEGIN WORK;
        BEGIN
        test=> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
        SET VARIABLE
        test=> SELECT COUNT(*) FROM trans_test;
         count 
        -------
             5
        (1 row) 
         
        test=> --
        test=> -- someone commits INSERT INTO trans_test
        test=> --
        test=> SELECT COUNT(*) FROM trans_test;
         count 
        -------
             5
        (1 row) 
         
        test=> COMMIT WORK;
        COMMIT  

 SERIALIZABLE isolation provides a stable view of the database for SELECT transactions. For transactions containing UPDATE and DELETE queries, SERIALIZABLE mode is more complicated. SERIALIZABLE isolation forces the database to execute all transactions as though they were run serially (one after another), even if they are run concurrently. If two concurrent transactions attempt to update the same row, serializability is impossible. In such a case, POSTGRESQL forces one transaction to roll back.

For SELECT-only transactions, use the SERIALIZABLE isolation level when you do not want to see other transaction commits during your transaction. For UPDATE and DELETE transactions, SERIALIZABLE isolation prevents concurrent modification of the same data row; it should therefore be used with caution.