Database concurrency Transaction Control Four: The lock mechanism of PostgreSQL database

Source: Internet
Author: User
Tags mutex semaphore

Concurrency control is the key technology of DBMS, concurrency control technology is also called synchronization mechanism, and its implementation usually relies on the underlying concurrency control mechanism. The operating system provides a variety of synchronization objects, such as event events, mutex mutex and condition variable Cond, semaphore semaphore, read-write lock RWLock, spin lock spinlock, etc. The database management system itself to achieve the blockade is mainly considered:
Lock semantics Enhanced: OS only provides exclusive lock. In order to improve the concurrency, the database needs at least shared locks and exclusive locks, i.e. read and write locks;
Lock enhancements: The database provides views to monitor the blocking situation and to perform deadlock detection;
Reliability considerations: For example, when a lock process is rolled back at any point, the database system is able to release its own locks;
Portability considerations: The blocking of each platform does not match exactly one by one of the equivalent;
Efficiency considerations, such as the use of spinlock to improve performance on SMP processors;
But eventually, the database system relies on the synchronization mechanism provided by the operating system and hardware platform to implement its own blockade. Spin locks, light locks, and regular locks are available in the PG. Both the spin lock and the light weight lock are used internally by the PG. Regular Lock PG is used internally, and the user can also specify the lock via select * for update.
First, the key structure and variables related to the lock in the core of the PG system.

Spin Lock:
Used for locks or other memory structures, transparent to the user and does not support deadlock detection.
Several related files that implement spin lock in PG are spin.h, SPIN.C, S_lock.h, s_lock.c.
Spin.h defines the interface functions/macros for spin locks.
SPIN.C defines a spin lock that is implemented with a semaphore, which is used if the target platform does not have its own spin lock.
S_lock.h defines the implementation of spin locks related to hardware and operating system platforms.
Spin lock is used directly in a few places, it is mainly as a means of implementing a light-weight lock. Each lwlock is required to have a spin lock.

Light-weight Lock:
The lightweight lock has a read lock (lw_shared) and a write lock (lw_exclusive), transparent to the user, does not support deadlock detection, is used to lock the memory structure, keep the critical section of the process mutually exclusive, shared memory in the allocation of control structure space.

The primary interface functions are defined in the Lwlock.h file. The following is a light-weight lock defined by the domain.
* We have a number of predefined lwlocks, plus a bunch of lwlocks that is
* Dynamically assigned (e.g., for shared buffers). The Lwlock structures
* Live in shared memory (since they contain shared data) and is identified
* By the values of this enumerated type. We abuse the notion of an enum somewhat
* By allowing values not listed in the Enum declaration to be assigned.
* The extra value maxdynamiclwlock is there-keep the compiler from
* Deciding that enum can is represented as char or short ...
* If You remove a lock, please replace it with a placeholder. This retains
* The lock numbering, which is helpful for DTrace and other external
* Debugging scripts.
typedef enum LWLOCKID
/* Individual lock IDs end here */
Firstlockmgrlock = Firstbufmappinglock + num_buffer_partitions,
Firstpredicatelockmgrlock = Firstlockmgrlock + num_lock_partitions,

/* must be last except for Maxdynamiclwlock: */
Numfixedlwlocks = Firstpredicatelockmgrlock + num_predicatelock_partitions,

Maxdynamiclwlock = 1000000000
} Lwlockid;

typedef enum LWLOCKMODE
Lw_wait_until_free/* A special mode used in Pgproc->lwlockmode,
* When waiting-lock to become free. Not
* To be used as lwlockacquire argument */
} Lwlockmode;

What are the light-weight locks in the PG system? You can view the method Createlwlocks ()->numlwlocks () from the Src/backend/storage/lmgr/lwlock.c file, which is allocate shmem space for Lwlocks and initialize the locks.

Numlwlocks (void)
int numlocks;

* Possibly this logic should is spread out among the affected modules,
* The same-to-shmem space estimation is-done. But for now, there
* Is few enough users of lwlocks that we can get away with just keeping
* The knowledge here.

/* Predefined lwlocks */
Numlocks = (int) numfixedlwlocks;

/* BUFMGR.C needs both for each shared buffer */
Numlocks + = 2 * nbuffers;

/* PROC.C needs one for each backend or auxiliary process */
Numlocks + = Maxbackends + Num_auxiliary_procs;

/* CLOG.C needs one per clog buffer */
Numlocks + = Clogshmembuffers ();

/* SUBTRANS.C needs one per subtrans buffer */
Numlocks + = Num_subtrans_buffers;

/* multixact.c needs, SLRU areas */
Numlocks + = num_mxactoffset_buffers + num_mxactmember_buffers;

/* ASYNC.C needs one per async buffer */
Numlocks + = Num_async_buffers;

/* predicate.c needs one per old serializable XID buffer */
Numlocks + = Num_oldserxid_buffers;

* ADD any requested by loadable modules; For backwards-compatibility
* reasons, allocate at least num_user_defined_lwlocks of them even if
* There is no explicit requests.
lock_addin_request_allowed = false;
Numlocks + = Max (lock_addin_request, num_user_defined_lwlocks);

return numlocks;

Simple pre-and post-related procedures can be found in my blog "What's going on during PostgreSQL startup seven: Initializing shared memory and signal one: initializing Shmemindex and Signals"

Regular Lock (lock):
We mainly look at conventional locks. Regular locks are database-specific locking mechanisms, providing a rich lock mode that can automatically deadlock detection and deadlock. Implementations are much more complex than the first two.

Look at the several structures and variables associated with regular locks, see src/include/storage/lock.h:
LockMethod: There are two types of blocking methods, as follows:
/* These identify the known lock methods */
The former is the internal locking system, the addition of the process of unlocking the user is transparent, for example, when users do select, the system automatically add Accesssharelock lock to prevent the modification of the table structure and other concurrent operations. The latter is the user lock, that is, the user locks the lock statement, the lock requires the user to explicitly unlock or the user has a transaction failure to automatically unlock.

Lockmode: Lock mode, there are 8 kinds of total. Nolock not count.
* These is the valid values of type Lockmode for all the standard lock
* Methods (both DEFAULT and USER).

/* NoLock is isn't a lock mode, but a flag value meaning "Don t get a lock" */
#define NOLOCK 0

#define ROWSHARELOCK 2/* SELECT for update/for SHARE */
#define SHARELOCK 5/* CREATE INDEX (without concurrently) */
#define EXCLUSIVELOCK 7/* Blocks ROW share/select ... For UPDATE */
#define ACCESSEXCLUSIVELOCK 8 */ALTER table, DROP table, VACUUM full, and unqualified LOCK tabl E/

Locktagtype: Lock Object Kind, also has user lock andAdvisory Lock (This new added trasaction type in pg9.1, originally only session type)
* Locktag is the key information needed-a LOCK item in the
* Lock Hashtable. A Locktag value uniquely identifies a lockable object.
* The Locktagtype enum defines the different kinds of objects we can lock.
* We can handle up to different locktagtypes.
typedef enum LOCKTAGTYPE
Locktag_relation,/* Whole RELATION */
/* ID info for a relation is DB OID + REL oid; DB OID = 0 if shared */
Locktag_relation_extend,/* The right to EXTEND a RELATION */
/* Same ID info as RELATION */
Locktag_page,/* One PAGE of a relation */
/* ID info for a page is RELATION info + blocknumber */
Locktag_tuple,/* One physical TUPLE */
/* ID info for a tuple is PAGE info + offsetnumber */
Locktag_transaction,/* TRANSACTION (for waiting to XACT done) */
/* ID info for a transaction are its TransactionID */
Locktag_virtualtransaction,/* Virtual transaction (ditto) */
/* ID info for a virtual transaction are its Virtualtransactionid */
Locktag_object,/* non-relation database OBJECT */
/* ID info for a object is DB oid + CLASS OID + object OID + SUBID */

* Note:object ID have same representation as in Pg_depend and
* Pg_description, but notice is constraining SUBID to + bits.
* Also, we use the DB OID = 0 for shared objects such as tablespaces.
Locktag_userlock,/* reserved for old contrib/userlock code */
locktag_advisory/* Advisory User Locks */
} Locktagtype;

#define Locktag_last_type Locktag_advisory

Locktag: The identity of the locked object:


* The Locktag struct is defined with malice aforethought to fit into 16
* bytes with no padding. Note that this would need adjustment if we were
* To widen OIDs, Blocknumber, or TransactionID to more than.
* We include Lockmethodid in the Locktag so, a single hash table in
* Shared memory can store locks of different lockmethods.
typedef struct LOCKTAG
UInt32 Locktag_field1; /* A 32-bit ID field: Used to identify the database dbid */
UInt32 locktag_field2; /* A 32-bit ID field: Used to identify the relationship Relid */
UInt32 locktag_field3; /* A 32-bit ID field: Used to identify page Blocknum */
UInt16 Locktag_field4; /* A 16-bit ID field: Used to identify line offset Offnum */
Uint8 Locktag_type; /* See enum Locktagtype */
Uint8 Locktag_lockmethodid; /* LockMethod Indicator */
} Locktag;

As noted in Locktag's comments, the conventional lock in the PG system is through the hash table in the shared memory: Regular lock manager-"lock hash" management, interested can refer to my blog What happens during PostgreSQL startup seven: Initializing shared memory and signals eight: initializing the regular lock manager in Shmem

The general lock mechanism can refer to the official PG Manual, chapters and contents are shown below
13.3. Explicit Locking
This section is divided into: table lock, row lock, page lock, deadlock, Advisory lock (how does this name translate well??? Advice lock, Announcement lock, consultation lock??? )。

The following outline of the content is:
Table Locks,
Row locks,
Page locks,
Advisory lock (how to translate this name??? Advice lock, Announcement lock, consultation lock??? )、
View the lock.

Feel some tired, lazy, do not want to write, see the situation on the Internet to find some colleagues comrades friends of the article, put here to classify, if there is a need to stay next time according to my thoughts and organize the organization, this time first to complete the topic.

Let's get here first.


Reprint please specify the source:

Database concurrency Transaction control Four: The lock mechanism of PostgreSQL database

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.