From: | stevenchang1213 <stevenchang1213(at)gmail(dot)com> |
---|---|
To: | rajan <vgmonnet(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: have trouble understanding xmin and xmax with update operations from two different sessions |
Date: | 2017-07-01 23:59:44 |
Message-ID: | 59583778.48d6620a.bd975.c4d6@mx.google.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
this chapter introduces mvcc
http://www.interdb.jp/pg/pgsql05.html
-------- 原始訊息 --------自: rajan <vgmonnet(at)gmail(dot)com> 日期: 2017/7/1 14:32 (GMT+08:00) 至: pgsql-general(at)postgresql(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | rajan | 2017-07-02 01:32:54 | Re: have trouble understanding xmin and xmax with update operations from two different sessions |
Previous Message | Adrian Klaver | 2017-07-01 17:00:07 | Re: have trouble understanding xmin and xmax with update operations from two different sessions |