postgresql system columns explain
xmin
Transaction ID for insertion. For identifying different transactions.
Will changes after new transaction(WhetherInsert
/Update
) commits.xmax
Transaction ID for deletion. Not being 0 means this row is not yet committed or rollbacked.cmin
Command ID for one transaction.
Identify different commands in one transaction. Start from 0.cmax
Command identifier for deletion transaction. In source code, it is the same withcmin
.ctid
Similar to Oraclerowid
, but will this ID will change as transactions on current row take effect. So it is better not always use this column to access rows.
How PG MVCC works
Suppose we have 2 sessions, accessing the same table test
with some rows.
- Session 1 starts transaction
- Session 1 updates 1 row
- PG allocates an
XID
for this new transaction - PG replicates the original row.
Now the origin row named as old version and the new one is called new version - PG sets
xmin
of new row version to the fresh allocatedXID
, say 2 for example - PG sets the
xmax
of old version to theXID
of new transaction - PG updates that new row version with new value from session 1.
- PG allocates an
- Session 2 accesses that row
- PG searches row that has max value in
xmax
, indicating latest consistent row version
This will get the old version
- PG searches row that has max value in
- Session 1 commits
- PG sets the
xmax
of old version to 0
- PG sets the
- Session 2 accesses that row again
- Again PG searches row that has max value in
xmax
. But they are all 0. - PG finds there is no transaction operating on this row.
Fetches the version with maxxmin
value, indicating latest consistent version.
This will get the new version
- Again PG searches row that has max value in
Example
1 |
|
We can find that these 3 rows share the same xmin=20775
, this means they are in the same transaction.
From cmin
and cmax
we could find are executed in certain order.
1 |
|
Notice xmin
for id=3
increases, this is because it is involved in another transaction, while its xmax
does not change.
Also the ctid
of this row is set to 0, which means the first command of transaction.
MVCC of PostgreSQL reserves all rows before vacuum, this row is actually newly inserted.
The old row version is still kept in table, but its xmax
is updated to the XID
of new transaction. By doing so, other sessions that accessing this row will know it is involved in a transaction, so as to ensure transactional consistency.
The view from another session is different.
1 |
|
The xmax
column indicates this row is involved in transaction 20776.
This is because PG notice we are query a row that is involved in a transaction, so PG will get the row with old version.
Because session 1 has not committed, we can not see newer version. In this way, MVCC in PostgreSQL ensures transactional consistency.
Let’s do some more operation in session 1.
1 |
|
As we can see, row id=2
has added in transaction 20776 and its cmin
is set to 1 because it is the second command in this transaction.
Session witnesses the change on row id=2
.
1 |
|
After commit of session 1, both sessions could see the same version of row.
1 |
|
1 |
|