20

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.

17

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 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.

Example:

Advanced example:

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.