Thread
-
My question about the transaction
Wen Yi <wen-yi@qq.com> — 2023-10-19T11:36:26Z
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? Thanks in advance! Yours, Wen Yi
-
Re: My question about the transaction
David G. Johnston <david.g.johnston@gmail.com> — 2023-10-19T14:59:08Z
On Thursday, October 19, 2023, Wen Yi <wen-yi@qq.com> wrote: > Hi community, > I am learning the transaction of the postgresql, and I try to test using > these: > You failed to show how the two sessions interleave. Also, your first session doesn’t use multi-statement transactions. postgres=# insert into t values (1); > INSERT 0 1 > postgres=# begin; > BEGIN > Session one can see this row and modify it just fine… David J.
-
Re: My question about the transaction
Adrian Klaver <adrian.klaver@aklaver.com> — 2023-10-19T15:40:54Z
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
-
Re: My question about the transaction
Juan Rodrigo Alejandro Burgos Mella <rodrigoburgosmella@gmail.com> — 2023-10-19T16:05:40Z
By default, psql is configured with autocommit on. Use another administrator, like phpadmin, and configure the connection with autocommit off. Atte JRBM El jue, 19 oct 2023 a las 11:49, Wen Yi (<wen-yi@qq.com>) escribió: > 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? > Thanks in advance! > > Yours, > Wen Yi > > >
-
Re: My question about the transaction
David G. Johnston <david.g.johnston@gmail.com> — 2023-10-19T16:33:08Z
On Thursday, October 19, 2023, Juan Rodrigo Alejandro Burgos Mella < rodrigoburgosmella@gmail.com> wrote: > By default, psql is configured with autocommit on. > Use another administrator, like phpadmin, and configure the > connection with autocommit off. > No. Please leave autocommit on and incorporate explicit begin/commit commands into the script if you want to demonstrate concurrency behavior. David J.