The difference lies between a query and a transaction. A transaction can contain any number of queries. To illustrate the difference, I set up a small example:
CREATE TABLE table_to_be_updated (
id serial PRIMARY KEY,
other_column text,
column_changing text
);
INSERT INTO table_to_be_updated (other_column, column_changing)
VALUES
('value', 'old_value'),
('value', 'other_value'),
('nonvalue', 'doesnt matter');
Then run two transactions concurrently (issuing the commands one by one, the middle line wants to depict the timeline):
| <-- BEGIN;
|
|
| UPDATE table_to_be_updated
BEGIN; -----------------------> | SET column_changing = 'new_value'
| WHERE
| other_column = 'value' AND
| column_changing = 'old_value';
|
|
SELECT column_changing -------> | -- update not yet committed
FROM table_to_be_updated |
WHERE other_column = 'value'; | <-- COMMIT;
|
|
SELECT column_changing -------> |
FROM table_to_be_updated |
WHERE other_column = 'value'; |
|
|
COMMIT; ----------------------> |
Running these in READ COMMITTED
isolation level, the first query returns a row with 'old_value', while the second one shows a row with 'new_value'. On an other run, I change the left-hand-side transaction isolation level:
SET transaction ISOLATION LEVEL REPEATABLE READ;
(The command must be the first statement in a transaction.)
Now both SELECTs return the same rowset, while a third one after committing both transactions will show the new row.
A connection from the pool will have the isolation level set by the last client to use that connection. Yes, it really is that scary.
The long and the short of it is that if you change the isolation level of a connection you must explicitly set it back to READ COMMITTED
before closing. Better is to explicitly declare your required isolation level at the start of any batch, to ensure your code isn't impacted by somebody else being sloppy, and return it to the default at the end.
This behaviour, while baffling, is apparently by-design.
Best Answer
Using the query from the SO answer Vincent Malgrat referenced, here is how you can get the transaction isolation level for the transaction in progress:
If you are not already in a transaction you can start one with the following:
It seems like there would be an easier way than this. I don't know how to get the default isolation level for the session if that is what you are looking for.