PostgreSQL Transaction Isolation READ UNCOMMITTED

Question

I want to try transaction isolation using PostgreSQL with pgadmin. First I inserted a new record inside BEGIN but without COMMIT.

BEGIN;
INSERT INTO my_table(id,value) VALUES (1,'something');

//UNCOMMITTED

Then, I tried to read the uncommitted data

BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM my_table
COMMIT;

But I couldn't find the new record. What's wrong?

Answer 1

PostgreSQL does not support dirty reads (READ UNCOMMITTED). As @a_horse_with_no_name pointed out, the manual says:

The SQL standard defines one additional level, READ UNCOMMITTED. In PostgreSQL READ UNCOMMITTED is treated as READ COMMITTED.

This is fitting with the rule in the standard that the database must treat unsupported isolation levels as the strongest supported level.

There is no supported way to read uncommitted tuples from an in-progress transaction in PostgreSQL. If there was you'd be able to get things like duplicate values for primary keys and general chaos so it wouldn't be very useful anyway.

There are a few ways in-progress transactions can communicate and affect each other:

• Via a shared client application (of course)
• SEQUENCE (and SERIAL) updates happen immediately, not at commit time
• advisory locking
• Normal row and table locking, but within the rules of READ COMMITTED visibility
• UNIQUE and EXCLUSION constraints

---

It's possible to see uncommitted tuple data using superuser-only debug facilities like pageinspect, but only if you really understand the innards of the datastore. It's suitable for data recovery and debugging only. You'll see multiple versions of data in a wall of hexadecimal output.