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.