19

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?

lospejos
  • 1,976
  • 3
  • 19
  • 35
ZZZ
  • 1,415
  • 5
  • 16
  • 29
  • 6
    Postgres does not support `read uncommitted`. [From the manual](http://www.postgresql.org/docs/current/static/sql-set-transaction.html): "*In PostgreSQL READ UNCOMMITTED is treated as READ COMMITTED*" –  Nov 11 '15 at 07:35
  • 1
    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 Nov 11 '15 at 10:33

2 Answers2

37

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 3
    It would be very useful indeed during testing. I tried this approach with other dbs that support READ_UNCOMMITED isolation mode and it works out fantastic. Just annotate your test with @Transactional and let it rollback any changes that might get db dirty. But this is only possible with dirty reads support, otherwise you cannot assert the expectations for the side-effects occurred within your transaction/test. – Whimusical May 29 '22 at 08:56
0

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.

Roger O
  • 1
  • It's not a "pity" that Postgres doesn't support dirty reads. In my opinion the READ UNCOMMITTED provided by other engines is a hack to overcome locking problems with their ACID implementation. –  Jul 21 '21 at 14:50
  • 2
    I disagree, see my comment in the accepted answer – Whimusical May 29 '22 at 08:58