current community

your communities

more stack exchange communities

Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question
2  
Yes that works just fine. You can also cahnge the isolation level of one transaction without problems. –  a_horse_with_no_name Sep 14 '14 at 6:43
    
@a_horse_with_no_name: Thank you. I wish I could "accept" your comment. –  Gili Sep 14 '14 at 6:50
    
I have no evidence to support that, that's why I added it as a comment. As far as I can tell, there is nothing in the docs postgresql.org/docs/current/static/transaction-iso.html that would suggest such a broken behaviour –  a_horse_with_no_name Sep 14 '14 at 6:55
    
@a_horse_with_no_name, fair enough. Thanks again for the clarification. –  Gili Sep 14 '14 at 7:17

2 Answers 2

up vote 2 down vote accepted

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

share|improve this answer
up vote 2 down vote

Transaction isolation level is set up per transaction basis. Even one connection can have different isolation level on each transaction.

Of course different connection can have different isolation level.

Here's some testing :

postgres=# begin;
BEGIN
postgres=# show transaction_isolation ;
 transaction_isolation
-----------------------
 read committed
(1 row)

postgres=# set transaction isolation level serializable ;
SET
postgres=# show transaction_isolation ;
 transaction_isolation
-----------------------
 serializable
(1 row)

At the same time on another session :

postgres=# begin;
BEGIN
postgres=# show transaction_isolation
;
 transaction_isolation
-----------------------
 read committed
(1 row)

postgres=# set transaction isolation level read uncommitted ;
SET
postgres=# show transaction_isolation
;
 transaction_isolation
-----------------------
 read uncommitted
(1 row)

Once You commit a transaction and begin another new transaction, default isolation level will be used which set on postgresql.conf :

#default_transaction_isolation = 'read committed'

I try this on Postgres 9.1.

Hope this help, cheers.

share|improve this answer
1  
Note that READ UNCOMMITTED has the same effect as READ COMMITTED in PostgreSQL; see postgresql.org/docs/current/static/sql-set-transaction.html –  hbn Sep 14 '14 at 19:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.