{"id":97471,"date":"2023-08-11T20:36:55","date_gmt":"2023-08-11T20:36:55","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=97471"},"modified":"2024-03-07T11:45:37","modified_gmt":"2024-03-07T11:45:37","slug":"database-concurrency-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/postgresql\/database-concurrency-in-postgresql\/","title":{"rendered":"Database Concurrency in PostgreSQL"},"content":{"rendered":"<p>Concurrency control is an essential aspect of database systems that deals with multiple concurrent transactions. PostgreSQL employs various techniques to ensure concurrent access to the database while maintaining data consistency using atomicity and isolation of ACID (stands for Atomicity, Consistency, Isolation and Durability &#8211; <a href=\"https:\/\/en.wikipedia.org\/wiki\/ACID\">https:\/\/en.wikipedia.org\/wiki\/ACID<\/a>) properties.<\/p>\n<h2>Concurrency Techniques<\/h2>\n<p>Broadly there are three concurrency techniques available for any database management system, pessimistic, optimistic, and multi-valued concurrency control (MVCC). In this section, I will introduce the techniques.<\/p>\n<h3>Pessimistic Locking<\/h3>\n<p>This concurrency control technique is used in database systems to handle concurrent access to shared data. It takes a cautious approach by assuming that conflicts between transactions are likely to occur, and it prevents conflicts by acquiring locks on database objects (rows or tables). Pessimistic locking ensures exclusive access to data, but it can lead to increased blocking and reduced concurrency compared to optimistic locking approaches.<\/p>\n<p>An example of pessimistic locking is <em><a href=\"https:\/\/www.geeksforgeeks.org\/two-phase-locking-protocol\/\">Strict Two-Phase Locking (2PL)<\/a><\/em> (<a href=\"https:\/\/en.wikipedia.org\/wiki\/Two-phase_locking\">https:\/\/en.wikipedia.org\/wiki\/Two-phase_locking<\/a>) and it ensures that concurrent transactions acquire and release locks in a strict and consistent manner, preventing conflicts and maintaining the data integrity. Strict 2PL consists of two distinct phases: the lock growing phase and the lock shrinking phase. In the lock growing phase, a transaction acquires locks on database objects before accessing or modifying them. These locks can be either shared (read) locks or exclusive (write) locks based on the transaction type. In the lock release phase, a transaction releases the locks it holds on database objects. This typically happens at the end of the transaction (a commit or a rollback).<\/p>\n<p>The pros of this technique are, simple to implement and guaranteed consistency and integrity of data. Whereas the cons are, high lock contention such as lock-waits, lock escalations leading to performance bottlenecks.<\/p>\n<h3>Optimistic Locking<\/h3>\n<p>This concurrency control technique takes an optimistic approach by assuming that conflicts between transactions are rare, and it allows transactions to proceed without acquiring locks on database objects during the execution of the entire transaction. Conflicts are validated, detected and resolved only at the time of committing the transaction.<\/p>\n<p>The pros of this technique are, increased concurrency, reduced lock overhead leading to linear high scalability. Whereas the cons are, high abort rate due to detection of conflict at commit time, and data integrity challenges.<\/p>\n<h3>Multi Version Concurrency Control (MVCC)<\/h3>\n<p>This concurrency control technique is used in database systems to allow concurrent access to shared database objects while maintaining data consistency and isolation. MVCC provides each transaction with a consistent snapshot of the data as it existed at the start of the transaction, even when other transactions modify the data concurrently.<\/p>\n<p>MVCC works very well within PostgreSQL for read operations. When it comes to updating the data, PostgreSQL would still acquire locks (which is pessimistic locking) at row level to ensure data consistency and prevent conflict between concurrent transactions. PostgreSQL provides various types of locks to manage concurrency and ensure data consistency in multi-user environments. These locks can be classified into several categories based on their scope and purpose. Below are the most common types of locks available (reference: https:\/\/www.postgresql.org\/docs\/15\/explicit-locking.html):<\/p>\n<ul>\n<li>Row Level\/Tuple Level Locks<\/li>\n<li>Page Level Locks<\/li>\n<li>Page Level Locks<\/li>\n<li>Advisory Locks<\/li>\n<\/ul>\n<p>PostgreSQL engine handles lock management automatically in the background for most common operations such as update and delete. The database engine acquires and releases locks based on the SQL statements being executed, the transaction isolation level and the volume of data. We will be discussing these in detail in the next article \u2013 Deep dive into PostgreSQL locks and deadlocks.<\/p>\n<p>Now that we know about the concurrency control aspects of databases. Let\u2019s dive deep into the building blocks of MVCC specific to PostgreSQL.<\/p>\n<h2>Building Blocks of MVCC in PostgreSQL<\/h2>\n<p>There are 6 key building blocks of MVCC which are very important to understand how concurrency controls works within PostgreSQL:<\/p>\n<ul>\n<li>Transaction IDs<\/li>\n<li>Versioning of Tuple<\/li>\n<li>Visibility Check Rules<\/li>\n<li>Read Consistency<\/li>\n<li>Write Operations<\/li>\n<li>Garbage Collection<\/li>\n<\/ul>\n<p>In this section I will introduce each of these topics to help make the basics clear.<\/p>\n<h3>Transaction IDs (XIDs):<\/h3>\n<p>Each transaction is assigned a unique transaction identifier (<code>ID<\/code>) called a Transaction ID (<code>XID<\/code>). It is a 32-bit (4 bytes) value that uniquely identifies a transaction within a PostgreSQL database cluster. It is automatically generated by the transaction manager when a transaction begins, and it remains associated with the transaction until it either commits or rollbacks the transaction.<\/p>\n<p>With 2^ (32 -1), we can have up to 2,147,483,648 transaction IDs in the cluster. Upon reaching this limit, we will experience a wraparound and transaction IDs will be reused.<\/p>\n<p><em>Note: <\/em><\/p>\n<p><em>Transaction ID 0 (zero) is reserved, 1 is used as bootstrap transaction id during the cluster initialization phase and 2 is used as frozen transaction id. <\/em><\/p>\n<p>The built-in function <code>pg_current_xact_id()<\/code> returns the current transaction ID (<code>XID<\/code>) of the calling session. This call by itself creates a new transaction when you call it for the first time within the unit of work. For example:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-97472\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/07\/a-white-screen-with-black-text-description-automa-1.png\" alt=\"A white screen with black text\n\nDescription automatically generated\" width=\"588\" height=\"235\" \/><\/p>\n<p>The PostgreSQL transaction manager allocates <code>XID<\/code> when <code>pg_current_xact_id ()<\/code> is called or an <code>UPDATE<\/code>, <code>DELETE<\/code>, <code>INSERT<\/code> operation is executed. However, a <code>BEGIN<\/code> transaction or a <code>SELECT<\/code> operation will not allocate a transaction <code>ID<\/code>. The built-in function <code>pg_current_xact_id_if_assigned ()<\/code> can be used to return the current transaction ID or NULL if XID is not assigned to the transaction.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-97473\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/07\/a-screenshot-of-a-computer-code-description-autom-4.png\" alt=\"A screenshot of a computer code\n\nDescription automatically generated\" width=\"708\" height=\"723\" \/><\/p>\n<p><strong>A Word About 8-byte Transaction ID (xid8) Enhancement in PostgreSQL<\/strong><\/p>\n<p>There were enhancements made to the code since PostgreSQL 13 to have a 64-bit (8 bytes) transaction IDs leading up to 9,223,372,036,854,775,808 (9223372 billion transaction IDs compared to 2.2 billion transaction IDs) that does not wrap around during the life of an installation. The following data structures have been modified to make use of 8-byte transaction ID (<code>xid8<\/code>):<\/p>\n<ul>\n<li>PostgreSQL system catalog<\/li>\n<li>Functions and procedures<\/li>\n<li>Backend worker processes which deal with SQL queries<\/li>\n<li>PostgreSQL utilities<\/li>\n<li>Replication slots and <code>VACUUM<\/code><\/li>\n<\/ul>\n<p>However, the challenge is to convert an existing <code>XMIN<\/code> and <code>XMAX<\/code> data structure from xid4 to xid8 on a running database makes it difficult to perform an in-place conversion. Please look out for more details about xid8 in the future article.<\/p>\n<h3>Versioning of Tuples<\/h3>\n<p>In PostgreSQL, each tuple (row) in a database table contains two transaction ID (<code>XID<\/code>) fields known as the <code>xmin<\/code> and <code>xmax<\/code>. These fields represent the minimum and maximum transaction IDs that are permitted to see or access a specific row. They play a crucial role in the Multi-Version Concurrency Control (MVCC) mechanism for maintaining data visibility and consistency.<\/p>\n<p>Below are the characteristics of <code>xmin<\/code> and <code>xmax<\/code> fields:<\/p>\n<p><code>xmin<\/code> (Transaction ID of the creating transaction):<\/p>\n<ul>\n<li>The <code>xmin<\/code> field stores the Transaction ID (<code>XID<\/code>) of the transaction that created the row version.<\/li>\n<li>It indicates the minimum Transaction ID that is allowed to see the row version.<\/li>\n<li>Any transaction with a Transaction ID lower than <code>xmin<\/code> can see and access the row version.<\/li>\n<li><code>xmin<\/code> is set when a new row version is inserted or created.<\/li>\n<\/ul>\n<p><code>xmax<\/code> (Transaction ID of the deleting transaction):<\/p>\n<ul>\n<li>The <code>xmax<\/code> field stores the Transaction ID (<code>XID<\/code>) of the transaction that deleted or marked the row version as deleted.<\/li>\n<li>It indicates the maximum Transaction ID that is allowed to see the row version.<\/li>\n<li>Only transactions with a Transaction ID lower than <code>xmax<\/code> can see the row version.<\/li>\n<li>If <code>xmax<\/code> is set to infinity (represented as 0), it means the row version is currently not deleted or marked as deleted by any transaction.<\/li>\n<\/ul>\n<p>Let\u2019s take an example and see how versioning is maintained in a table.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1307\" height=\"554\" class=\"wp-image-97474\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/07\/a-close-up-of-a-document-description-automaticall-2.png\" alt=\"A close up of a document\n\nDescription automatically generated\" \/><\/p>\n<p>In this example, <code>xmin<\/code> for the first tuple is <code>609167<\/code>. It is the <code>XID<\/code> of the transaction which inserted this tuple. The <code>xmax<\/code> column value is set to <code>0<\/code>, which means the tuple is active (validity is set to infinity) and there was no update or delete performed on this tuple.<\/p>\n<p>The column <code>ctid<\/code> is a system column that represents the physical location or address of a row within a table. It consists of two parts: block number and tuple index. The block number identifies the disk block where the row is stored, and tuple index identifies the position of the row within the block.<\/p>\n<p>Figure 1 illustrates a PostgreSQL tuple versioning with a simple example. The session #1 read tuples from table account_info, where xmax was set to infinity (zero) \u2013 that means no modifications were made to tuples and were active versions. You will also notice, the <code>ctid<\/code> is set to <code>(0,1) (0,2)<\/code> that means both the tuples are stored sequentially in block 0 and this table has only 2 tuples. When session #2 updates the account_type for account=1 tuple, <code>xmin<\/code> is changed from <code>txid<\/code> 609167 to 609169 to reflect the transaction performing the update. You will also notice the change in ctid from (0,1) (0,2) to (0,2) (0,3) that means we have 3 tuples in the table stored in block 0. Why do we have 3 tuples in the table when we only see 2 tuples? That\u2019s because PostgreSQL has retained the older version of the tuple for 2 reasons, (1) \u2013 there is an open transaction which reads the earlier version of the tuple which is valid between <code>txid<\/code> 609167 and 609169 and (2) \u2013 the recent change is not committed yet from session #2.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1674\" height=\"1098\" class=\"wp-image-97475\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/07\/word-image-97471-4.jpeg\" \/><\/p>\n<p><strong>Figure 1: PostgreSQL Versioning Example<\/strong><\/p>\n<p>The <code>pageinspect<\/code> module in PostgreSQL provides a set of functions that allow you to inspect and analyze the contents of database pages at a low level. This module is particularly useful for debugging, troubleshooting, and understanding the internal structure of PostgreSQL database pages. For example, we can inspect the table <code>account_info<\/code> to extract all 3 tuples details (2 active tuples and 1 dead tuple).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-97476\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/07\/a-screenshot-of-a-computer-code-description-autom-5.png\" alt=\"A screenshot of a computer code\n\nDescription automatically generated\" width=\"821\" height=\"360\" \/><\/p>\n<h3>Visibility Check Rules<\/h3>\n<p>In PostgreSQL&#8217;s Multi-Version Concurrency Control (MVCC) mechanism, visibility check rules are used to determine which data versions are visible to a transaction&#8217;s snapshot. These rules ensure that each transaction sees a consistent snapshot of the database as of its start time.<\/p>\n<p>The Visibility Check Rules in PostgreSQL are as follows:<\/p>\n<p>1. <strong>Transaction ID (XID) Comparison<\/strong>: Each data version in PostgreSQL has an associated Transaction ID (<code>XID<\/code>) indicating the transaction that created or modified it. To determine if a data version is visible to a transaction&#8217;s snapshot, the following rule is applied:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">xmin &lt;= pg_current_xact_id () \r\n     AND (xmax = 0 OR pg_current_xact_id () &lt; xmax)<\/pre>\n<ul>\n<li>If the <code>xmin<\/code> of the data version is lower than the transaction&#8217;s XID, it is considered committed before the transaction&#8217;s start time and visible to the transaction.<\/li>\n<li>If the <code>xmin<\/code> of the data version is greater than or equal to the transaction&#8217;s <code>XID<\/code>, it is considered not yet committed or modified after the transaction&#8217;s start time and not visible to the transaction. However, a tuple will be visible even when xmin &gt; pg_current_xact_id ()with READ COMMITTED isolation level. For example:<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1140\" height=\"540\" class=\"wp-image-97477\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/07\/a-screenshot-of-a-computer-code-description-autom-6.png\" alt=\"A screenshot of a computer code\n\nDescription automatically generated\" \/><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1065\" height=\"473\" class=\"wp-image-97478\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/07\/a-screenshot-of-a-computer-code-description-autom-7.png\" alt=\"A screenshot of a computer code\n\nDescription automatically generated\" \/><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1134\" height=\"277\" class=\"wp-image-97479\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/07\/a-close-up-of-a-document-description-automaticall-3.png\" alt=\"A close-up of a document\n\nDescription automatically generated\" \/><\/p>\n<p>In this example, <code>txid<\/code> <code>609176<\/code> is still able to view committed data from <code>txid<\/code> <code>609177<\/code> which is a future transaction id. In PostgreSQL, when a transaction in <code>READ COMMITTED<\/code> isolation level starts a new query, it takes a new snapshot of the database state. This snapshot represents the committed state of the database at the point in time the query starts. This is the reason for allowing tuples to be visible with <code>xmin &gt;  pg_current_xact_id ()<\/code>.<\/p>\n<p>2. <strong>Snapshot Transaction ID Range:<\/strong> A transaction&#8217;s snapshot includes a transaction ID range that determines the XIDs of the data versions visible to the transaction. For example, you can use <code>pg_current_snapshot ()<\/code> snapshot information function to list the snapshots transaction ID range.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">mvcc=# begin;\r\nBEGIN\r\nmvcc=*# update account_info \r\nset account_type = 'Checking' \r\nwhere account = 2;\r\nUPDATE 1\r\nmvcc=*# select pg_current_snapshot();\r\n pg_current_snapshot\r\n---------------------\r\n 609172:609172:\r\n(1 row)\r\nmvcc=*# select pg_last_committed_xact ();\r\n           pg_last_committed_xact\r\n--------------------------------------------\r\n (609169,\"2023-07-09 01:12:03.883742+00\",0)\r\n(1 row)<\/pre>\n<p>The snapshot includes the transaction&#8217;s own <code>XID<\/code> and may include a range of other <code>XID<\/code> values based on the chosen isolation level.<\/p>\n<ul>\n<li><strong>Read Committed:<\/strong> A transaction&#8217;s snapshot includes its own <code>XID<\/code> only, allowing it to see only committed data versions that existed before its start time.<\/li>\n<li><strong>Repeatable Read<\/strong>: A transaction&#8217;s snapshot includes its own <code>XID<\/code> and all <code>XID<\/code> values that were already committed at its start time, allowing it to see a consistent snapshot of the data throughout the transaction&#8217;s duration.<\/li>\n<li><strong>Serializable<\/strong>: A transaction&#8217;s snapshot includes a range of XID values up to the transaction&#8217;s <code>XID<\/code>, allowing it to see a consistent snapshot and preventing conflicts with concurrent transactions modifying the same data.<\/li>\n<\/ul>\n<p>3. <strong>Visibility Information<\/strong>: Each data version in PostgreSQL has visibility information associated with it, including the minimum XID (<code>xmin<\/code>) and the maximum XID (<code>xmax<\/code>) that are allowed to see the version. These values are used to determine visibility based on the transaction&#8217;s snapshot.<\/p>\n<ul>\n<li>If the transaction&#8217;s <code>XID<\/code> is greater than <code>xmin<\/code>, the data version is visible to the transaction.<\/li>\n<li>If the transaction&#8217;s <code>XID <\/code>is greater than or equal to <code>xmax<\/code>, the data version is not visible to the transaction.<\/li>\n<li>If <code>xmin<\/code> is less than the transaction&#8217;s <code>XID<\/code> and <code>xmax<\/code> is greater than the transaction&#8217;s <code>XID<\/code>, additional checks may be performed to handle special cases, such as in-progress transactions or locks have been held at tuple level.<\/li>\n<\/ul>\n<p>By applying these visibility check rules, PostgreSQL ensures that each transaction sees a consistent snapshot of the data based on its start time and isolation level. This allows for concurrent access to the database while maintaining read consistency and transaction isolation.<\/p>\n<h3>Read Consistency<\/h3>\n<p>The read consistency refers to the guarantee that a transaction sees a consistent snapshot of the database as of the transaction&#8217;s start time, regardless of concurrent changes made by other transactions. Read consistency ensures that the data accessed by a transaction remains stable and consistent throughout its execution.<\/p>\n<p>Let\u2019s take a closer look at read <em>phenomena<\/em>, also known as <em>anomalies<\/em>, are undesirable behaviours that can occur in concurrent database transactions when multiple transactions are reading and modifying the same data concurrently. These phenomena can result in inconsistent or unexpected results if proper isolation and concurrency control measures are not in place.<\/p>\n<p><strong><em>Dirty Read<\/em><\/strong> &#8211; This phenomenon can occur during concurrent database transactions. It happens when one transaction reads data that has been modified by another transaction that has not yet been committed. In other words, a transaction reads uncommitted or <em>dirty<\/em> data. This is completely prevented in PostgreSQL (however this is still possible in other relational database systems such as SQL Server and Db2) and we cannot read uncommitted data.<\/p>\n<p><a id=\"post-97471-_Hlk141106695\"><\/a><strong><em>Non-Repeatable Read<\/em><\/strong> &#8211; This phenomenon can occur when a transaction reads the same row or tuple multiple times during its execution, but the data values change or vanishes (is deleted) between the reads due to concurrent modifications by other transactions.<\/p>\n<p><strong><em>Phantom Read<\/em><\/strong> \u2013 This occurs when a transaction retrieves a set of rows based on a condition, and between consecutive reads, another transaction inserts that satisfy the same condition. As a result, the second read includes additional rows or misses previously retrieved rows, leading to an inconsistent result set.<\/p>\n<p><strong><em>Serialization Anomaly<\/em><\/strong>: This occurs when the outcome of executing a group of transactions concurrently is inconsistent with the outcome of executing the same transactions sequentially in all possible orderings. In other words, the final result of a set of concurrent transactions is not equivalent to any possible serial execution of those transactions.<\/p>\n<p>In PostgreSQL, various transaction isolation levels are available to control the level of concurrency and consistency in database transactions. Each isolation level defines the visibility and locking behavior for concurrent transactions and PostgreSQL supports the following isolation levels:<\/p>\n<p>1. <strong>Read Committed (default):<\/strong><\/p>\n<ul>\n<li>Provides read consistency by ensuring that a transaction only sees data that has been committed at the time the query begins.<\/li>\n<li>Prevents dirty reads by requiring data to be committed before it becomes visible to other transactions.<\/li>\n<li>Allows non-repeatable reads and phantom reads, as concurrent transactions may modify the data between reads.<\/li>\n<li>Offers a good balance between consistency and concurrency and is suitable for many applications.<\/li>\n<\/ul>\n<p>2. <strong>Repeatable Read:<\/strong><\/p>\n<ul>\n<li>Provides a higher level of read consistency than <code>Read Committed<\/code>.<\/li>\n<li>Ensures that a transaction sees a consistent snapshot of the database as of the transaction&#8217;s start time.<\/li>\n<li>Prevents dirty reads and non-repeatable reads by acquiring read locks on accessed data, preventing concurrent modifications.<\/li>\n<li>Allows phantom reads, as concurrent transactions may insert new rows that match the query criteria.<\/li>\n<li>Provides a stronger guarantee of data consistency but can result in increased concurrency issues due to acquired locks.<\/li>\n<\/ul>\n<p>3. <strong>Serializable<\/strong>:<\/p>\n<ul>\n<li>Provides the highest level of isolation and guarantees serializability of transactions.<\/li>\n<li>Ensures that concurrent transactions appear as if they were executed serially, without any concurrency anomalies.<\/li>\n<li>Prevents dirty reads, non-repeatable reads, and phantom reads.<\/li>\n<li>May result in increased locking and potential serialization conflicts, leading to more blocking and reduced concurrency.<\/li>\n<\/ul>\n<p>Table 1 shows the phenomenon which can occur in a specific isolation level. Even though PostgreSQL allows setting the isolation level to uncommitted, it behaves exactly like read committed.<\/p>\n<table>\n<tbody>\n<tr>\n<td>\n<p><strong>Isolation Level\/Phenomenon<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>Dirty Read<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>Non-Repeatable Read<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>Phantom Read<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>Serialization Anomaly<\/strong><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>READ COMMITTED<\/p>\n<\/td>\n<td>\n<p>Not Possible<\/p>\n<\/td>\n<td>\n<p>Possible<\/p>\n<\/td>\n<td>\n<p>Possible<\/p>\n<\/td>\n<td>\n<p>Possible<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>REPEATABLE READ<\/p>\n<\/td>\n<td>\n<p>Not Possible<\/p>\n<\/td>\n<td>\n<p>Not Possible<\/p>\n<\/td>\n<td>\n<p>Not Possible<\/p>\n<\/td>\n<td>\n<p>Possible<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>SERIALIZABLE<\/p>\n<\/td>\n<td>\n<p>Not Possible<\/p>\n<\/td>\n<td>\n<p>Not Possible<\/p>\n<\/td>\n<td>\n<p>Not Possible<\/p>\n<\/td>\n<td>\n<p>Not Possible<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><em>Table 1: Transaction Isolation Levels and Read Phenomenon<\/em><\/p>\n<p>Let\u2019s look at the Read Committed isolation level and its behavior. Figure 2 illustrates how the dirty read behavior would work. PostgreSQL always reads a committed version of the tuple and dirty reads are not possible. As a side note, you can set the transaction isolation at transactional level or at the database level.<\/p>\n<p>Note: The default isolation level set at the database level is <code>read committed<\/code>. You can use ALTER DATABASE command to make the change as shown below:<\/p>\n<pre class=\"lang:c# theme:vs2012\">ALTER DATABASE ${dbname} \r\n   SET DEFAULT_TRANSACTION_ISOLATION TO 'repeatable read';<\/pre>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1212\" height=\"862\" class=\"wp-image-97480\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/07\/word-image-97471-9.png\" \/><\/p>\n<p><strong>Figure 2: Dirty Read phenomenon is not a possibility in PostgreSQL<\/strong><\/p>\n<p>Figure 3 illustrates the possibility of having non-repeatable read and phantom phenomenon when the isolation level set to <code>READ COMMITTED<\/code>.<\/p>\n<p>&nbsp;<\/p>\n<p><strong><img loading=\"lazy\" decoding=\"async\" width=\"1210\" height=\"691\" class=\"wp-image-97481\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/07\/word-image-97471-10.png\" \/><\/strong><\/p>\n<p><strong><img loading=\"lazy\" decoding=\"async\" width=\"1212\" height=\"370\" class=\"wp-image-97482\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/07\/word-image-97471-11.png\" \/><\/strong><\/p>\n<p><strong>Figure 3: Non-Repeatable read and Phantom read phenomenon are possible in PostgreSQL<\/strong><\/p>\n<p>Let\u2019s look at the Repeatable Read isolation level and its behavior. Figure 4 showcases how PostgreSQL resolves the non-repeatable read and phantom read anomalies. The serialization anomaly can still occur based on how the application is designed.<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1212\" height=\"859\" class=\"wp-image-97483\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/07\/word-image-97471-12.png\" \/><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1209\" height=\"82\" class=\"wp-image-97484\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/07\/word-image-97471-13.png\" \/><\/p>\n<p><em>Figure 4: Repeatable Read Isolation Level Operations<\/em><\/p>\n<p>What happens if an update is attempted to the same tuple from session #2? Let\u2019s try.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">mvcc=*# update ledger set amount = 2200 where accno = 1;\r\nERROR:  could not serialize access due to concurrent update\r\nmvcc=!# select pg_current_snapshot ();\r\nERROR:  current transaction is aborted, commands ignored until end of transaction block\r\nmvcc=!# rollback;\r\nROLLBACK<\/pre>\n<p>The error message indicates a serialization failure during concurrent transactions. It occurs when two or more transactions attempt to modify the same data simultaneously, resulting in conflicts that violate the strict serializability guarantee.<\/p>\n<p>&nbsp;<\/p>\n<p><strong><img loading=\"lazy\" decoding=\"async\" width=\"1212\" height=\"634\" class=\"wp-image-97485\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/07\/word-image-97471-14.png\" \/><\/strong><\/p>\n<p><strong><img loading=\"lazy\" decoding=\"async\" width=\"1212\" height=\"469\" class=\"wp-image-97486\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/07\/word-image-97471-15.png\" \/><\/strong><\/p>\n<p><strong>Figure 5: Serializable Isolation Level Operations<\/strong><\/p>\n<p>In PostgreSQL&#8217;s SERIALIZABLE isolation level, transactions are executed as if they were running in a serial order, which ensures data consistency. To maintain this strict serializability, PostgreSQL may identify a transaction as a &#8220;pivot&#8221; when conflicts occur with other concurrent transactions.<\/p>\n<p>A &#8220;pivot&#8221; transaction is one that serves as a reference point for the serialization process. When conflicts arise, PostgreSQL may choose one transaction as the pivot and abort the other conflicting transactions to preserve consistency. The error message indicates that the transaction being canceled was identified as the pivot during the commit attempt.<\/p>\n<p>For more information, please refer to <a href=\"https:\/\/wiki.postgresql.org\/wiki\/Serializable\">https:\/\/wiki.postgresql.org\/wiki\/Serializable<\/a>.<\/p>\n<h3>Write Operations<\/h3>\n<p>In PostgreSQL databases, an <code>INSERT<\/code> is simple and no different than other databases. The background writer and WAL writer processes handle the data write operation from shared buffers to the transaction log file and the data pages.<\/p>\n<p>The interest is more in <code>UPDATE<\/code> and <code>DELETE<\/code> operations. Let\u2019s take a look at <code>UPDATE<\/code> operation. When an <code>UPDATE<\/code> operation in PostgreSQL modifies a row, it follows a multi-version concurrency control (MVCC) mechanism. PostgreSQL will perform a delete and insert internally instead of modifying the existing row in place. This is done to ensure data consistency and handle concurrency control effectively by writers not blocking readers and readers not blocking the writers.<\/p>\n<p>Every <code>UPDATE<\/code> operation performs pseudo<code> d<\/code>elete, i.e., the original row is marked for deletion, creating a new version of the row with a delete flag. And an insert, a new row is inserted with the updated values, creating a new version of the row.<\/p>\n<p>For simplicity, let\u2019s take an <code>UPDATE<\/code> example with the <code>READ COMMITTED<\/code> (default) isolation level. In the example below, either of sessions can start the execution first or can execute both the sessions concurrently.<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1212\" height=\"795\" class=\"wp-image-97487\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/07\/word-image-97471-16.png\" \/><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1210\" height=\"571\" class=\"wp-image-97488\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/07\/word-image-97471-17.png\" \/><\/p>\n<p>The <code>pageinspect<\/code> module functions can be used to analyze data at page level for both table and indexes. In the case of a <code>DELETE<\/code> operation, the <code>xmax<\/code> will be updated with the transaction id which executes the <code>DELETE<\/code> and marks the tuple as dead.<\/p>\n<h3>Garbage Collection<\/h3>\n<p>In PostgreSQL, garbage collection refers to the process of reclaiming disk space occupied by deleted or obsolete data. When data is deleted or updated in PostgreSQL, it is not immediately removed from the disk. Instead, it is marked as eligible for garbage collection, and the actual disk space is reclaimed later by the <code>autovacuum<\/code> process.<\/p>\n<p>Based on our example, we have 4 live tuples and 12 dead tuples, these dead tuples can be cleaned using <code>VACUUM<\/code>. The <code>VACUUM<\/code> command is used to perform a table vacuum (data reorganization). It reclaims disk space by physically reorganizing the table and its associated indexes, and it can be more resource-intensive compared to regular <code>VACUUM<\/code> operations.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">mvcc=# VACUUM VERBOSE ledger;\r\nINFO:  vacuuming \"mvcc.public.ledger\"\r\nINFO:  finished vacuuming \"mvcc.public.ledger\": index scans: 1\r\npages: 0 removed, 1 remain, 1 scanned (100.00% of total)\r\ntuples: 12 removed, 4 remain, 0 are dead but not yet removable\r\nremovable cutoff: 609251, which was 0 XIDs old when operation ended\r\nindex scan needed: 1 pages from table (100.00% of total) had 12 dead item identifiers removed\r\nindex \"ledger_pkey\": pages: 2 in total, 0 newly deleted, 0 currently deleted, 0 reusable\r\navg read rate: 0.000 MB\/s, avg write rate: 0.000 MB\/s\r\nbuffer usage: 11 hits, 0 misses, 0 dirtied\r\nWAL usage: 4 records, 1 full page images, 8480 bytes\r\nsystem usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s\r\nVACUUM<\/pre>\n<p>However, the dead tuple count is still showing as 12 in the catalog. This is just the system catalog information and we will have to execute the <code>ANALYZE<\/code> command to update table statistics, including information about the distribution of data, column histograms, and correlation between columns. These statistics are used by the query optimizer to generate efficient execution plans<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"905\" height=\"756\" class=\"wp-image-97489\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/07\/word-image-97471-18.png\" \/><\/p>\n<h2>Summary:<\/h2>\n<p>MVCC (Multi-Version Concurrency Control) is a concurrency control mechanism used in PostgreSQL to handle concurrent access to data. It allows multiple transactions to read and write data concurrently while maintaining transaction isolation and ensuring consistency. In this article we learnt MVCC specifics and the building blocks of MVCC in detail. We will dive deep into each section such as <code>VACUUM<\/code> internals, storage internals, index B tree internal and locking internals in the future articles.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Concurrency control is an essential aspect of database systems that deals with multiple concurrent transactions. PostgreSQL employs various techniques to ensure concurrent access to the database while maintaining data consistency using atomicity and isolation of ACID (stands for Atomicity, Consistency, Isolation and Durability &#8211; https:\/\/en.wikipedia.org\/wiki\/ACID) properties. Concurrency Techniques Broadly there are three concurrency techniques available&#8230;&hellip;<\/p>\n","protected":false},"author":342296,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143534],"tags":[159066],"coauthors":[159008],"class_list":["post-97471","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-postgresql-101-webinar-sidebar"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97471","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/342296"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=97471"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97471\/revisions"}],"predecessor-version":[{"id":97587,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97471\/revisions\/97587"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=97471"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=97471"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=97471"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=97471"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}