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@xxxxxxxxxxx
