Re: the XID question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 1æ20æ, äå6æ46å, g...@xxxxxxxxxxxxxxx (Greg Smith) wrote:
> Kevin Grittner wrote:
> > Or just test it in psql. ÂBEGIN, run your query, look at pg_locks.
> > If an xid has been assigned, you'll see it there in the
> > transactionid column. ÂYou can easily satisfy yourself which
> > statements grab an xid...
>
> That's a good way to double-check exactly what's happening, but it's not
> even that hard:
>
> gsmith=# select txid_current();
> txid_current | 696
>
> gsmith=# select 1;
> ?column? | 1
>
> gsmith=# select 1;
> ?column? | 1
>
> gsmith=# select txid_current();
> txid_current | 697
>
> Calling txid_current bumps the number up, but if you account for that
> you can see whether the thing(s) in the middle grabbed a real txid by
> whether the count increased by 1 or more than that. ÂSo here's what one
> that did get a real xid looks like:
>
> gsmith=# select txid_current();
> txid_current | 702
>
> gsmith=# insert into t(i) values(1);
> INSERT 0 1
> gsmith=# select txid_current();
> txid_current | 704
>
> That proves the INSERT in the middle was assigned one.
>
> The commit message that added this feature to 8.3 has a good quick intro
> to what changed from earlier revs:http://archives.postgresql.org/pgsql-committers/2007-09/msg00026.php
>
> Don't have to actually read the source to learn a bit more, because it's
> actually documented! ÂMechanics are described at
> pgsql/src/backend/access/transam/README ; you need to know a bit more
> about subtransactions to follow all of it, but it gets the general idea
> across regardless:
>
> = Transaction and Subtransaction Numbering =
>
> Transactions and subtransactions are assigned permanent XIDs only when/if
> they first do something that requires one --- typically,
> insert/update/delete
> a tuple, though there are a few other places that need an XID assigned.
> If a subtransaction requires an XID, we always first assign one to its
> parent. ÂThis maintains the invariant that child transactions have XIDs
> later
> than their parents, which is assumed in a number of places.
>
> The subsidiary actions of obtaining a lock on the XID and and entering
> it into
> pg_subtrans and PG_PROC are done at the time it is assigned.
>
> A transaction that has no XID still needs to be identified for various
> purposes, notably holding locks. ÂFor this purpose we assign a "virtual
> transaction ID" or VXID to each top-level transaction. ÂVXIDs are formed
> from
> two fields, the backendID and a backend-local counter; this arrangement
> allows
> assignment of a new VXID at transaction start without any contention for
> shared memory. ÂTo ensure that a VXID isn't re-used too soon after backend
> exit, we store the last local counter value into shared memory at backend
> exit, and initialize it from the previous value for the same backendID slot
> at backend start. ÂAll these counters go back to zero at shared memory
> re-initialization, but that's OK because VXIDs never appear anywhere
> on-disk.
>
> Internally, a backend needs a way to identify subtransactions whether or not
> they have XIDs; but this need only lasts as long as the parent top
> transaction
> endures. ÂTherefore, we have SubTransactionId, which is somewhat like
> CommandId in that it's generated from a counter that we reset at the
> start of
> each top transaction. ÂThe top-level transaction itself has
> SubTransactionId 1,
> and subtransactions have IDs 2 and up. Â(Zero is reserved for
> InvalidSubTransactionId.) ÂNote that subtransactions do not have their
> own VXIDs; they use the parent top transaction's VXID.
>
> --
> Greg Smith  2ndQuadrant US  Âg...@xxxxxxxxxxxxxxx  Baltimore, MD
> PostgreSQL Training, Services, and 24x7 Support Âwww.2ndQuadrant.us
> "PostgreSQL 9.0 High Performance":http://www.2ndQuadrant.com/books
>
> --
> Sent via pgsql-performance mailing list (pgsql-performa...@xxxxxxxxxxxxxx)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-performance

every time, i execute this query string  "SELECT datname,
age(datfrozenxid), FROM pg_database;"  in the sql query of
pgAdminIII , the age will be increased by 5 , not 1. why???

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



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux