MVCC Concurrency Architecture: PostgreSQL way of implementation – II

This is in continuation from my last blog on PostgreSQL implementation of MVCC. We will understand transaction id (XID), versioning of a tuple (xmin & xmax) in more detail.

Before we move to understand the implementation of a transaction using MVCC, let’s first understand how a tuple is created/updated/deleted.

PostgreSQL assigns the current transaction id as a tuple identifier and names it as xmin when it got created. Whenever this tuple got updated, PostgreSQL does not update the record in place. It copies the entire tuple into a new location and in the same way, it assigns the current transaction id as xmin. When the database receives a read request for this tuple, PostgreSQL looks for a tuple with the higest xmin number. It simply discards all others with lower xmin.

When a delete is received by the database, it does not do anything but just update the tuple with xmax number which is again the current transaction id. Hence, a tuple with xmax is treated as deleted and will not be fetched.

Let’s understand how transactions are implemented with the help of a pictorial representation.

However, before we move to explain this picture, With the help of this transaction timeline, we will explain how it moves forward.

For sake of understanding lets assume current transaction id is 1 which starts with our transaction T1. During T1 we inserts a record in our posts table. This record will assign the xmin value as 1 since the current transaction id is 1.

Meanwhile, before T1 ends, a new transaction T2 started and is querying the same table. However, T2 will not get the information generated by T1 because T1 is yet to complete. Whereas, T3 which started after T1 gets completed will get the records created by T1.

This explains two requirements for transactions (say T1 is out case) to be reflected in the world outside that transactions.
1. xmin should be less than the XID.
2. Transaction T1 should be complete.

This is why, T3 gets only one row as it was completed before T4 completed. In the same way, T5 got 2 rows and T7 got only one.

Implementing MVCC this way is having their own strengths and weakness. We will explore these in next blog.