Concurrency control is a process that maintains atomicity and isolation, properties of ACID, of which there are three techniques. In Multi-version Concurrency Control, each write operation creates a new data item while retaining the old version which is in contrast to Strict Two Phase Locking which must block readers when a writer writes an item because the writer acquires an exclusive lock for the item. PostgreSQL used Snapshot Isolation which is a variation of MVCC which uses rollback segments. When reading items, PostgreSQL selects the appropriate version of an item in response to an individual transaction by applying visibility check rules.
Whenever a transaction begins, a unique identifier, referred to as a transaction id (txid) which is a 32-bit unsigned integer, approximately 4.2 billion.
Heap tuples in table pages are classified as a usual data tuple and a TOAST tuple. This section describes only the usual tuple.
While the HeapTupleHeaderData structure contains seven fields, four fields are required in the subsequent sections which are t_xmin, t_xmax, t_cid and t_ctid
FSM is used to insert and update tuples.
For insertion, a new tuple is inserted directly into a page of the target table. For this the header fields would be as follows:
t_xmin = 99
t_xmax =0
t_cid =0
t_ctid =(0,1)
For deletion, target tuple is deleted logically and in header fields, t_xmax is set to 111
In the update operation, PostgreSQL logically deletes the latest tuple and inserts a new one
Tuple_1:
t_xmax is set to 100.
t_ctid is rewritten from (0, 1) to (0, 2).
Tuple_2:
t_xmin is set to 100.
t_xmax is set to 0.
t_cid is set to 0.
t_ctid is set to (0,2)
When inserting a heap or an index tuple, PostgreSQL uses the FSM of the corresponding table or index to select the page which can be inserted it. PostgreSQL , and is used throughout transaction processing. PostgreSQL defines four transaction states, i.e. IN_PROGRESS, COMMITTED, ABORTED, and SUB_COMMITTED. A transaction snapshot is a dataset that stored information about whether all transactions are active, at a certain point in time for an individual transaction. Here an active transaction means it is in progress or has not yet started. Visibility check rules are a set of rules used to determine whether each tuple is visible or invisible using both the t_xmin and t_xmax of the tuple, the clog, and the obtained transaction snapshot.
Top comments (0)