PostgreSQL Transaction Isolation READ UNCOMMITTED

14
votes

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?

2
postgresqltransaction-isolation
Postgres does not support read uncommitted. From the manual: "In PostgreSQL READ UNCOMMITTED is treated as READ COMMITTED" - a_horse_with_no_name
Why do you want to do that? Handling isolation, concurrency, visibility etc is what the database is for. I've wanted to have been for debugging and data recovery sometimes, but there are facilities for that. The only time I really wanted READ UNCOMMITTED was to attempt a queuing system, and for that advisory locking works well. - Craig Ringer

2 Answers

30
votes

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.

0
votes

Lospejos, if you BEGIN a transaction and do an insert you can run a SELECT to see the rows because you are inside your transaction, other people can´t see this row. But if you issue another BEGIN it is another transaction, so don´t do it, just do (BEGIN, INSERT, SELECT, COMMIT). If you select your row and notice it is wrong do ROLLBACK in order to discard this row. The fact that Postgress don´t accept UNCOMMITED READ is a pity. I use this in DB2 and Teradata for instance. Specially if you want to see what other transaction is doing in a table without waiting it to COMMIT.