Re: My question about the transaction

On 10/19/23 04:36, Wen Yi wrote:
> Hi community,
> I am learning the transaction of the postgresql, and I try to test using 
> these:
> 
> ######
> 
> terminal 1:
> 
> postgres=# select * from t;
>   number
> --------
>        1
> (1 row)
> 
> postgres=# update t set number = 2;
> UPDATE 1
> postgres=# select * from t;
>   number
> --------
>        2
> (1 row)
> 
> postgres=# select * from t;
>   number
> --------
>        2
> (1 row)
> 
> postgres=#
> 
> ######
> 
> terminal 2:
> 
> postgres=# create table t (number integer);
> CREATE TABLE
> postgres=# insert into t values (1);
> INSERT 0 1
> postgres=# begin;
> BEGIN
> postgres=*# insert into t values (2);
> INSERT 0 1
> postgres=*# select * from t;
>   number
> --------
>        2
>        2
> (2 rows)
> 
> postgres=*# rollback;
> ROLLBACK
> 
> My question is, in my view, the transaction model should make sure that 
> when one on-process transaction don't commit itself, the data on this 
> transaction shouldn't can be modified by other transaction(I the 
> single-statement also be treated as a simple transaction), but why the 
> update works?(I think terminal 1 will block until the terminal 2's 
> transaction commit or rollback).
> Can someone share you opinion to me?

Assuming you did in order, where terminal 1 = t1 and terminal 2 = t2:

t2 create table t (number integer);
t2 insert into t values (1);
t1 select * from t;
t1 update t set number = 2;
t1 select * from t;
t2 begin;
t2 insert into t values (2);
t2 select * from t;
t1 select * from t;
t2 rollback;

Then it is as David said, the commands in t1 see the inserted  value of 
1 in table t and updates it as they are running in autocommit as where 
the commands in t2 before the begin;. Autocommit commits on each 
successful completion of a command. You then start am explicit 
transaction is t2 that sees the updated row and then adds a new row, 
both of which are seen in the t2 transaction but not in the t1 session.

My suggestion would be to read through this:

https://www.postgresql.org/docs/current/transaction-iso.html

several times. There is a lot going on there.

> Thanks in advance!
> 
> Yours,
> Wen Yi
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com