Re: [GENERAL] have trouble understanding xmin and xmax with update operations from two different sessions
this chapter introduces mvcc http://www.interdb.jp/pg/pgsql05.html 原始訊息 自： rajan <vgmon...@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
Re: [GENERAL] 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.kla...@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
[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
