have trouble understanding xmin and xmax with update operations from two different sessions

On 06/30/2017 11:32 PM, rajan wrote:

have trouble understanding xmin and xmax with update operations from two
different sessions

So, as found below, Session2 is trying to update a row which is already
updated with a different value and it's update fails with *UPDATE 0*

But from Session3, I see that xmax value is visible as Session2's txid. Why
is it like that?
Can we not put Session2's txid to xmin instead(although the update failed)?
And if we try to consider that xmax is update with Session2's txid bcoz the
update failed, then why bother updating the xmax?

Please help me understand this.

One thing to remember is that txid_current() is more then informational:

https://www.postgresql.org/docs/9.6/static/functions-info.html

txid_current() bigint get current transaction ID, assigning a new one
if the current transaction does not have one

So calling it can advance the xid manually. Some testing here showed
that what xmin or xmax is created depends on when you call txid_current
in either the original session or the concurrent sessions.

Also worth noting that an UPDATE in Postgres is a DELETE/INSERT process.
The clue is the ctid value. In Session 2 you are looking at the
original row(ctid=(0, 2) which has been marked as deleted(non-zero
xmax). In Session 3 you are looking at the new row(ctid(0, 4)).

*Session1*
testdb=# BEGIN;
BEGIN
testdb=# select ctid, xmin, xmax, * from numbers;
ctid | xmin | xmax | number
-------+--------+------+--------
(0,2) | 519107 | 0 | 14
(0,3) | 519112 | 0 | 23
(2 rows)

testdb=# select txid_current();
txid_current
--------------
519114
(1 row)

testdb=# update numbers set number=24 where number=14;
UPDATE 1
testdb=# COMMIT;
COMMIT

*Session 2*
testdb=# BEGIN;
BEGIN
testdb=# select txid_current();
txid_current
--------------
519115
(1 row)

testdb=# select ctid, xmin, xmax, * from numbers;
ctid | xmin | xmax | number
-------+--------+------+--------
(0,2) | 519107 | 0 | 14
(0,3) | 519112 | 0 | 23
(2 rows)

testdb=# select ctid, xmin, xmax, * from numbers;
ctid | xmin | xmax | number
-------+--------+--------+--------
(0,2) | 519107 | 519114 | 14
(0,3) | 519112 | 0 | 23
(2 rows)

testdb=# update numbers set number=25 where number=14;
UPDATE 0
testdb=# COMMIT;
COMMIT

*Session 3*
testdb=# select txid_current();
txid_current
--------------
519116
(1 row)

testdb=# select ctid, xmin, xmax, * from numbers;
ctid | xmin | xmax | number
-------+--------+--------+--------
(0,3) | 519112 | 0 | 23
(0,4) | 519114 | 519115 | 24
(2 rows)

-----
--
Thanks,
Rajan.
--
View this message in context: http://www.postgresql-archive.org/have-trouble-understanding-xmin-and-xmax-with-update-operations-from-two-different-sessions-tp5969629.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

this chapter introduces mvcc
http://www.interdb.jp/pg/pgsql05.html

-------- 原始訊息 --------自： rajan <vgmonnet@gmail.com> 日期： 2017/7/1 14:32 (GMT+08:00) 至： pgsql-general@postgresql.org 主旨： [GENERAL] have trouble understanding xmin and xmax with update operations from two different sessions
have trouble understanding xmin and xmax with update operations from two
different sessions

So, as found below, Session2 is trying to update a row which is already
updated with a different value and it's update fails with *UPDATE 0*

But from Session3, I see that xmax value is visible as Session2's txid. Why
is it like that?
Can we not put Session2's txid to xmin instead(although the update failed)?
And if we try to consider that xmax is update with Session2's txid bcoz the
update failed, then why bother updating the xmax?

Please help me understand this.

*Session1*
testdb=# BEGIN;
BEGIN
testdb=# select ctid, xmin, xmax, * from numbers;
ctid  |  xmin  | xmax | number
-------+--------+------+--------
(0,2) | 519107 |    0 |     14
(0,3) | 519112 |    0 |     23
(2 rows)

testdb=# select txid_current();
txid_current
--------------
       519114
(1 row)

testdb=# update numbers set number=24 where number=14;
UPDATE 1
testdb=# COMMIT;
COMMIT

*Session 2*
testdb=# BEGIN;
BEGIN
testdb=# select txid_current();
txid_current
--------------
       519115
(1 row)

testdb=# select ctid, xmin, xmax, * from numbers;
ctid  |  xmin  | xmax | number
-------+--------+------+--------
(0,2) | 519107 |    0 |     14
(0,3) | 519112 |    0 |     23
(2 rows)

testdb=# select ctid, xmin, xmax, * from numbers;
ctid  |  xmin  |  xmax  | number
-------+--------+--------+--------
(0,2) | 519107 | 519114 |     14
(0,3) | 519112 |      0 |     23
(2 rows)

testdb=# update numbers set number=25 where number=14;
UPDATE 0
testdb=# COMMIT;
COMMIT

*Session 3*
testdb=# select txid_current();
txid_current
--------------
       519116
(1 row)

testdb=# select ctid, xmin, xmax, * from numbers;
ctid  |  xmin  |  xmax  | number
-------+--------+--------+--------
(0,3) | 519112 |      0 |     23
(0,4) | 519114 | 519115 |     24
(2 rows)

-----
--
Thanks,
Rajan.
--
View this message in context: http://www.postgresql-archive.org/have-trouble-understanding-xmin-and-xmax-with-update-operations-from-two-different-sessions-tp5969629.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

hello,

thanks for replies, Adrian, Steven.

So calling it can advance the xid manually. Some testing here showed
that what xmin or xmax is created depends on when you call txid_current
in either the original session or the concurrent sessions.

I understand this and I am executing my statements inside a Transaction
block so the xid is not incremented when calling it.

Also worth noting that an UPDATE in Postgres is a DELETE/INSERT process.
The clue is the ctid value. In Session 2 you are looking at the
original row(ctid=(0, 2) which has been marked as deleted(non-zero
xmax). In Session 3 you are looking at the new row(ctid(0, 4)).

Yes. But why (ctid(0,4)) in *Session 3* carries the xmax of the txid 519115
in which the update failed with *UPDATE 0* . This is where I can not
understand,
1. Row (0,4) is updated with correct value and (0,3) is not visible in
Session 2, which is good.
2. but in *Session 3* (0,4) also carries xmax which means what? Is it also
marked for deletion? It can't be, right?

-----
--
Thanks,
Rajan.
--
View this message in context: http://www.postgresql-archive.org/Re-have-trouble-understanding-xmin-and-xmax-with-update-operations-from-two-different-sessions-tp5969644p5969656.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, Jul 1, 2017 at 6:32 PM, rajan <vgmonnet@gmail.com> wrote:

hello,

thanks for replies, Adrian, Steven.

So calling it can advance the xid manually. Some testing here showed
that what xmin or xmax is created depends on when you call txid_current
in either the original session or the concurrent sessions.

I understand this and I am executing my statements inside a Transaction
block so the xid is not incremented when calling it.

Also worth noting that an UPDATE in Postgres is a DELETE/INSERT process.
The clue is the ctid value. In Session 2 you are looking at the
original row(ctid=(0, 2) which has been marked as deleted(non-zero
xmax). In Session 3 you are looking at the new row(ctid(0, 4)).

Yes. But why (ctid(0,4)) in *Session 3* carries the xmax of the txid 519115
in which the update failed with *UPDATE 0* . This is where I can not
understand,
1. Row (0,4) is updated with correct value and (0,3) is not visible in
Session 2, which is good.
2. but in *Session 3* (0,4) also carries xmax which means what? Is it also
marked for deletion? It can't be, right?

When session 2 encounters the locked row which meets the criterion for the
update, it has to wait for the locking transaction to finish. At that
point it locks the row (by writing its transaction into the xmax, and
setting a flag not visible to you, unless you use pgeinspect) and then
re-evaluates if it still meets the criterion. Since it doesn't meet the
criterion anymore, it doesn't finish updating the tuple.

Cheers,

Jeff

Thanks, Jeff. That helps understanding it 50%.

*Session 2* fails to UPDATE the record which is in *(0,2)* and this tuple is
marked for deletion. It means that *(0,2) never exists* when Session 2 is
trying to perform the update.

In that case, how *Session 3's new row (0,4)* contains the xmax as *Session
2's txid*.

-----
--
Thanks,
Rajan.
--
View this message in context: http://www.postgresql-archive.org/Re-have-trouble-understanding-xmin-and-xmax-with-update-operations-from-two-different-sessions-tp5969644p5969661.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, Jul 1, 2017 at 8:55 PM, rajan <vgmonnet@gmail.com> wrote:

Thanks, Jeff. That helps understanding it 50%.

*Session 2* fails to UPDATE the record which is in *(0,2)* and this tuple
is
marked for deletion. It means that *(0,2) never exists* when Session 2 is
trying to perform the update.

That it never exists is an appearance presented to the user. The database
system works hard to maintain that illusion but the database system itself
sees through the illusion. It blocks on (0,2) waiting for session 1 to
commit, and then once that happens session 2 goes and finds the new version
of that row ((0,4) in this case) and locks it. If you use pageinspect, you
can see that (0,2) has left a pointer behind pointing to (0,4) to make it
easy to find.

Cheers,

Jeff

Thanks for the explanation.

will I be able to view the information using this function,
SELECT * FROM heap_page_items(get_raw_page('testnumbers', 0));

Also, please let me know which column I should refer for viewing the
pointer.

-----
--
Thanks,
Rajan.
--
View this message in context: http://www.postgresql-archive.org/Re-have-trouble-understanding-xmin-and-xmax-with-update-operations-from-two-different-sessions-tp5969644p5969767.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Jul 3, 2017 at 3:02 AM, rajan <vgmonnet@gmail.com> wrote:

Thanks for the explanation.

will I be able to view the information using this function,
SELECT * FROM heap_page_items(get_raw_page('testnumbers', 0));

Also, please let me know which column I should refer for viewing the
pointer.

It is 't_ctid'

Cheers,

Jeff

Thanks, Jeff.

Now I am going back to my old question.

Even though *Session 2* fails to update with UPDATE 0 message, its txid is
saved in xmax of updated(by *Session 1*) tuple.

As it becomes an old txid, how come new txids are able to view it?

-----
--
Thanks,
Rajan.
--
View this message in context: http://www.postgresql-archive.org/Re-have-trouble-understanding-xmin-and-xmax-with-update-operations-from-two-different-sessions-tp5969644p5969857.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Jul 3, 2017 at 10:39 AM, rajan <vgmonnet@gmail.com> wrote:

Thanks, Jeff.

Now I am going back to my old question.

Even though *Session 2* fails to update with UPDATE 0 message, its txid is
saved in xmax of updated(by *Session 1*) tuple.

As it becomes an old txid, how come new txids are able to view it?

The database can see everything. That is its job. It constructs the
principles of ACID out of non-ACID components. But once you use
pageinspect or select the system columns mxin and xmax, you start to peek
through that illusion.

Cheers,

Jeff