2

I'm trying to better understand MVCC and am confused by the following (contrived) scenario. I was under the impression that for a row to be visible to a transaction, the transaction's id must be greater than the tuple's xmin value.

create table test (id int);

(session 1)
begin;
select txid_current();
┌──────────────┐
│ txid_current │
├──────────────┤
│          704 │
└──────────────┘

(session 2)
begin;
insert into test values (1);
commit;

(session 1)
select xmin, xmax, id from test;
┌──────┬──────┬────┐
│ xmin │ xmax │ id │
├──────┼──────┼────┤
│  705 │    0 │  1 │
└──────┴──────┴────┘

How can a transaction with an id of 704 see a tuple with an xmin of 705?

Update:

Reading http://www.interdb.jp/pg/pgsql05.html helped clarify things a lot more for me. A few videos I had previously watched seemed to imply that, as a hard rule, a tuple is only visible if its xmin is less than the current transaction's txid.

However, the above reading painted a slightly different picture for me. In an isolation level of READ COMMITTED, a transaction gets a new snapshot of the state of other transactions at the start of each query.

The first query in session 1 would get a snapshot of 704:704: (you can see this by running select txid_current_snapshot()) which means that from the point of view of txid 704, all txids < 704 are inactive (committed or aborted) and all transactions >= 704 are active (in progress or not yet started).

The second query of session 1 would get a new snapshot of 704:706: which means that from the point of view of txid 704, all txids < 704 are inactive, and all txids >= 706 are active. However, since txid 705 fits in between these values, and since it has in fact been committed, it too is part of the new snapshot that txid 704 received, and is visible. Therefore, it is not a hard rule that a tuple is only visible if its xmin is less than the current transaction's txid.

1

This depends on the isolation level. The default level for postgresql is "Read Committed" at which level this can happen. In postgresql it won't happen at "Repeatable Read" or "Serializable".

This behavior is often not a problem and higher serialization levels can slow down the server which is the reason why they are not the default.

For an extensive explanation please see the manual https://www.postgresql.org/docs/9.6/static/transaction-iso.html

  • TIt isn't that 704 can see 705. – jjanes Nov 12 '16 at 17:47
0

In session 1, your select txid_current(); is out of date. Repeat that query after session 2 has done its insertion, you will find it returns a different value, which is greater than 705.

If you want to lock txid_current() in place, you have to be in a transaction with the isolation level set to at least repeatable read. Otherwise, it will advance between each statement (given other commit activity in the database).

  • That's not the behaviour I'm observing. If I run select txid_current(); in session 1, right after session 2's insert, I still see txid of 704. – Dave Stibrany Nov 12 '16 at 23:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.