39

Maybe this is a dumb beginner question, but I cannot find an answer anywhere. Everywhere I read about the Transaction Isolation which solves the visibility of data within the concurrent transactions. My concern is the behavior within a single transaction.

If I start a transaction, insert some data, am I going to be able to select them right after - still within the same, yet uncommitted, transaction? If yes, can this behavior be changed in a similar way like the mentioned Transaction Isolation in the case of concurrent transactions?

To be specific, I'm targetting PostgreSQL 9.4.

NumberFour
  • 577
  • 1
  • 7
  • 10

2 Answers2

35

Yes.
Everything you did inside the same transaction is visible to later commands inside the same transaction. Just not to other transactions until committed. This is true for all isolation levels except Read uncommitted where "dirty reads" are possible (but that does not affect your question per se).

It's implemented with the MVCC model (Multiversion Concurrency Control) based on TransactionIds determining relative age and visibility for every table row. Every new row version written in the same transaction gets the same xmin and is considered to have happened "at the same time".

There is a corner case for multiple CTEs (Common Table Expression) in the same command. One might think those are executed sequentially, but unless one CTE references the other their sequence is arbitrary. And all of them see the same snapshot from the start of the query. That's why it's disallowed to UPDATE the same row more than once in multiple CTEs of the same query: would be ambiguous.

Example:

Advanced example:

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • If I'm within a transaction X. I update a row A to have value 1 in transaction X. In another transaction Z someone updates row A to value 2 and commits transaction Z. After transaction Z is committed I now read the value of row A in transaction X. Isn't it true that with READ_COMMITTED I will no longer be able to read the value, 1, that I wrote to row A (this would require REPEATABLE_READ)? – Jordan Feb 14 '23 at 15:53
  • @Jordan: The UPDATE in transaction X locks the row, so transaction Z has to wait till X commits / rolls back. And X can rely on seeing what it did earlier in the same transaction. That is true for default READ_COMMITTED or stricter. – Erwin Brandstetter Feb 14 '23 at 21:35
  • oh yeah obviously that's right ... my brain wasn't working yesterday :laughing: – Jordan Feb 15 '23 at 14:54
6

Let's try :

CREATE OR REPLACE FUNCTION public.sp_get_user()
 RETURNS json
 LANGUAGE plpgsql
AS $function$BEGIN

INSERT INTO users (name, password) VALUES ('deadeye', 'test');
RETURN row_to_json(row) FROM (SELECT name, password FROM users WHERE name = 'deadeye') row;


END;$function$

Now, let's test :

SELECT sp_get_user();
{"name":"deadeye","password":"test"}

It works! As Erwin said, everything made in a transaction is visible inside the transaction. Isolation is only between different threads.

Eric Ly
  • 1,212
  • 12
  • 20