Chapter 12. Concurrency Control

Chapter 12. Concurrency Control

Table of Contents

12.1. Introduction

12.2. Transaction Isolation

12.2.1. Read Committed Isolation Level

12.2.2. Serializable Isolation Level

12.3. Explicit Locking

12.3.1. Table-Level Locks

12.3.2. Row-Level Locks

12.3.3. Deadlocks

12.4. Data Consistency Checks at the Application Level

12.5. Locking and Indexes

This chapter describes the behavior of the PostgreSQL database system when two or more sessions try to access the same data at the same time. The goals in that situation are to allow efficient access for all sessions while maintaining strict data integrity. Every developer of database applications should be familiar with the topics covered in this chapter.

12.1. Introduction

Unlike traditional database systems which use locks for concurrency control,

PostgreSQL

maintains data consistency by using a multiversion model (Multiversion Concurrency Control, MVCC). This means that while querying a database each transaction sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data. This protects the transaction from viewing inconsistent data that could be caused by (other) concurrent transaction updates on the same data rows, providing transaction isolation for each database session.

The main advantage to using the MVCC model of concurrency control rather than locking is that in MVCC locks acquired for querying (reading) data do not conflict with locks acquired for writing data, and so reading never blocks writing and writing never blocks reading.

Table- and row-level locking facilities are also available in PostgreSQL for applications that cannot adapt easily to MVCC behavior. However, proper use of MVCC will generally provide better performance than locks.