Overview

PostgreSQL is using an engine called Multiversion concurrency control or (MVCC for short). Such way of operating is used by other Databases such as: Oracle and MySQL (InnoDB). In a nutshell, this engine allows you to store different versions of the data at the same time in the shared buffers, depending on the consistency needs which your query must fulfill.

In PostgreSQL, we have several types of locks:

• Access Share: Taken when person performs select.
• Row Share: Taken when Select for UPDATE / for SHARE is issued.
• Row Exclusive: Taken when DML Operations (Update / Insert / Delete) is issued.
• Share Update Exclusive: This lock is imposed when one of the following: CREATE INDEX CONCURRENTLY, ANALYZE/ALTER TABLE (SOME FORMS), VALIDATE and VACUUM (but not FULL).
• Share: When Index is created.
• Share Row Exlusive: When CREATE TRIGGER and some forms of Alter table
• EXCLUSIVE & ACCESS EXCLUSIVE: The most restrict ones by far. They are taken upon DROP TABLE request.

Below you can see also, conflict matrix for all of the above Lock modes: