PostgreSQL SET TRANSACTION EXAMPLE WITH PRACTICAL

• The SET TRANSACTION command sets the characteristics of the current transaction. It has no effect on any subsequent transactions. SET SESSION CHARACTERISTICS sets the default transaction characteristics for subsequent transactions of a session. These defaults can be overridden by SET TRANSACTION for an individual transaction.
• The available transaction characteristics are the transaction isolation level, the transaction access mode (read/write or read-only), and the deferrable mode. In addition, a snapshot can be selected, though only for the current transaction, not as a session default.

The isolation level of a transaction determines what data the transaction can see when other transactions are running concurrently:

SYNTAX:

SET TRANSACTION transaction_mode [, ...]
SET TRANSACTION SNAPSHOT snapshot_id
SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]

where transaction_mode is one of:

    ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
    READ WRITE | READ ONLY
    [ NOT ] DEFERRABLE

Isolation Level	Description
1. READ COMMITTED	A statement can only see rows committed before it began. This is the default.
2. REPEATABLE READ	All statements of the current transaction can only see rows committed before the first query or data-modification statement was executed in this transaction.
3. SERIALIZABLE	All statements of the current transaction can only see rows committed before the first query or data-modification statement was executed in this transaction. If a pattern of reads and writes among concurrent serializable transactions would create a situation which could not have occurred for any serial (one-at-a-time) execution of those transactions, one of them will be rolled back with a serialization_failure error.

EXAMPLES 1:

To begin a new transaction with the same snapshot as an already existing transaction, first export the snapshot from the existing transaction. That will return the snapshot identifier, for example:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT pg_export_snapshot();
 pg_export_snapshot
--------------------
 000003A1-1

(1 row)

Then give the snapshot identifier in a SET TRANSACTION SNAPSHOT command at the beginning of the newly opened transaction:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT '000003A1-1';

EXAMPLES 2:

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.

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.