Обсуждение: non-zero xmax yet visible
I'm a little bit confused by the meaning of xmax. The documentation at http://www.postgresql.org/docs/current/static/ddl-system-columns.html says "xmax The identity (transaction ID) of the deleting transaction, or zero for an undeleted row version. It is possible for this column to be nonzero in a visible row version. That usually indicates that the deleting transaction hasn't committed yet, or that an attempted deletion was rolled back." According to this, it seems a committed change should result in an xmax value of zero. But a huge number of rows in our database have non-zero xmax values and are still visible. I did the following experiment with 2 sessions. Session 1 => create table test_data (id int, value int); => insert into test_data(id) values(1); => commit; => update test_data set value = 1 where id = 1; => select txid_current(); txid_current -------------- 362938838 Session 2 => select xmin, xmax, id, value from test_data; xmin | xmax | id | value -----------+-----------+----+------- 362938803 | 362938838 | 1 | => update test_data set value = 2 where id = 1; Session 1 => commit; Session 2 => select txid_current(); txid_current -------------- 362938861 => commit; => select xmin, xmax, id, value from test_data; xmin | xmax | id | value -----------+-----------+----+------- 362938861 | 362938861 | 1 | 2 So in this case, xmax is equal to xmin. I've also seen cases where xmax is larger than xmin and the row is visible. Is this an expected behavior? How shall we interpret xmax in these cases?
On Wed, Jan 15, 2014 at 1:26 AM, Ming Li <mli89257@gmail.com> wrote: > I'm a little bit confused by the meaning of xmax. > > The documentation at > http://www.postgresql.org/docs/current/static/ddl-system-columns.html > says > "xmax > > The identity (transaction ID) of the deleting transaction, or zero for > an undeleted row version. It is possible for this column to be nonzero > in a visible row version. That usually indicates that the deleting > transaction hasn't committed yet, or that an attempted deletion was > rolled back." > > According to this, it seems a committed change should result in an > xmax value of zero. But a huge number of rows in our database have > non-zero xmax values and are still visible. Not exactly, this is only the case of a tuple that has been only inserted in a transaction. To put it in simple words an inserted row will have its xmin set to the current transaction ID with xman set at 0, and a deleted row will have its xmax updated to the transaction ID of the transaction that removed it. An updated row is the combination of a deletion and an insertion. The data visible from other sessions depends as well on the isolation level: http://www.postgresql.org/docs/current/static/transaction-iso.html The default, read committed, means that the query will see data committed by other sessions before the *query* began. > I did the following experiment with 2 sessions. > > Session 1 > > => create table test_data (id int, value int); > => insert into test_data(id) values(1); > => commit; > => update test_data set value = 1 where id = 1; > => select txid_current(); > txid_current > -------------- > 362938838 > > Session 2 > > => select xmin, xmax, id, value from test_data; > xmin | xmax | id | value > -----------+-----------+----+------- > 362938803 | 362938838 | 1 | This session is using a transaction ID between 362938803 and 362938838, explaining why it is the one visible. You are also not giving all the information of session 2, a transaction began there as well. > => update test_data set value = 2 where id = 1; > > Session 1 > > => commit; > > Session 2 > > => select txid_current(); > txid_current > -------------- > 362938861 > > => commit; > => select xmin, xmax, id, value from test_data; > xmin | xmax | id | value > -----------+-----------+----+------- > 362938861 | 362938861 | 1 | 2 In this case what this session > So in this case, xmax is equal to xmin. I've also seen cases where > xmax is larger than xmin and the row is visible. With the isolation level read committed, changes committed by other sessions during a transaction are visible. > Is this an expected behavior? How shall we interpret xmax in these cases? This is part of how MVCC works in Postgres, xman is the transaction ID until when this tuple is visible for other sessions. Regards, -- Michael
On Wed, Jan 15, 2014 at 10:48 AM, Michael Paquier <michael.paquier@gmail.com> wrote: > On Wed, Jan 15, 2014 at 1:26 AM, Ming Li <mli89257@gmail.com> wrote: >> I'm a little bit confused by the meaning of xmax. >> >> The documentation at >> http://www.postgresql.org/docs/current/static/ddl-system-columns.html >> says >> "xmax >> >> The identity (transaction ID) of the deleting transaction, or zero for >> an undeleted row version. It is possible for this column to be nonzero >> in a visible row version. That usually indicates that the deleting >> transaction hasn't committed yet, or that an attempted deletion was >> rolled back." >> >> According to this, it seems a committed change should result in an >> xmax value of zero. But a huge number of rows in our database have >> non-zero xmax values and are still visible. > Not exactly, this is only the case of a tuple that has been only > inserted in a transaction. To put it in simple words an inserted row > will have its xmin set to the current transaction ID with xman set at > 0, and a deleted row will have its xmax updated to the transaction ID > of the transaction that removed it. > An updated row is the combination of a deletion and an insertion. Well, an update statement generates a new row version. The deletion xmax is set on the old row version and not on the new one? If only session 1 is doing an update concurrently, xmax of the final row version is set to 0 as expected. => update test_data set value = 3 where id = 1; => commit; => select xmin, xmax, id, value from test_data; xmin | xmax | id | value -----------+------+----+------- 363072457 | 0 | 1 | 3 If session 1 and 2 perform overlapping update transactions as in my previous example, xmax of the final committed row version is not zero. This is what I find confusing. Why is the new row version of an update transaction associated with a non-zero xmax? The new row version itself is neither deleted nor updated. > > The data visible from other sessions depends as well on the isolation level: > http://www.postgresql.org/docs/current/static/transaction-iso.html > The default, read committed, means that the query will see data > committed by other sessions before the *query* began. > >> I did the following experiment with 2 sessions. >> >> Session 1 >> >> => create table test_data (id int, value int); >> => insert into test_data(id) values(1); >> => commit; >> => update test_data set value = 1 where id = 1; >> => select txid_current(); >> txid_current >> -------------- >> 362938838 >> >> Session 2 >> >> => select xmin, xmax, id, value from test_data; >> xmin | xmax | id | value >> -----------+-----------+----+------- >> 362938803 | 362938838 | 1 | > This session is using a transaction ID between 362938803 and > 362938838, explaining why it is the one visible. You are also not > giving all the information of session 2, a transaction began there as > well. > >> => update test_data set value = 2 where id = 1; >> >> Session 1 >> >> => commit; >> >> Session 2 >> >> => select txid_current(); >> txid_current >> -------------- >> 362938861 >> >> => commit; >> => select xmin, xmax, id, value from test_data; >> xmin | xmax | id | value >> -----------+-----------+----+------- >> 362938861 | 362938861 | 1 | 2 > In this case what this session > >> So in this case, xmax is equal to xmin. I've also seen cases where >> xmax is larger than xmin and the row is visible. > With the isolation level read committed, changes committed by other > sessions during a transaction are visible. > >> Is this an expected behavior? How shall we interpret xmax in these cases? > This is part of how MVCC works in Postgres, xman is the transaction ID > until when this tuple is visible for other sessions. > Regards, > -- > Michael