PostgreSQL – Does it Support Different Transaction Isolation Per Connection?

jdbcpostgresql

I just discovered that H2 doesn't support concurrent connections using different transaction isolation levels. Meaning, changing the transaction isolation of one connection affects all other connections.

Does Postgresql support the use of different isolation levels for each connection?

Best Answer

Yes it does support different transaction isolation levels per-connection. You can set the transaction isolation level (as well as the read-only and deferrable status for transactions) for a connection with SET SESSION CHARACTERISTICS:

localhost:5432 postgres postgres  # SHOW transaction_isolation;
 transaction_isolation
-----------------------
 read committed
(1 row)


localhost:5432 postgres postgres  # SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET

localhost:5432 postgres postgres  # SHOW transaction_isolation;
 transaction_isolation
-----------------------
 repeatable read
(1 row)

You can override per-transaction with SET TRANSACTION ISOLATION LEVEL, or as you start a transaction with BEGIN TRANSACTION ISOLATION LEVEL:

localhost:5432 postgres postgres  # BEGIN;
BEGIN
Time: 0.227 ms
localhost:5432 postgres postgres * # SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET
Time: 0.229 ms
localhost:5432 postgres postgres * # SHOW transaction_isolation;
 transaction_isolation
-----------------------
 serializable
(1 row)


Time: 0.262 ms
localhost:5432 postgres postgres * # COMMIT;
COMMIT

localhost:5432 postgres postgres  # SHOW transaction_isolation;
 transaction_isolation
-----------------------
 repeatable read
(1 row)

The default is set with the default_transaction_isolation parameter:

localhost:5432 postgres postgres  # SHOW default_transaction_isolation;
 default_transaction_isolation
-------------------------------
 repeatable read
(1 row)

See the docs at http://www.postgresql.org/docs/current/static/sql-set-transaction.html

Related Question