This document is an opensql technical document based on postgresql.
 

Overview

In PostgreSQL, locks are synchronization mechanisms used to maintain data consistency between multiple sessions.
They are used to ensure data consistency and prevent conflicts when multiple sessions access data simultaneously.

Method

Types of Locks

These are the types of locks applied up to the table level.

ACCESS SHARE

• Conflicts with ACCESS EXCLUSIVE lock
• Lock acquired when executing read-only SELECT statements

ROW SHARE

• Conflicts with EXCLUSIVE, ACCESS EXCLUSIVE locks
• Lock acquired when executing SELECT FOR UPDATE, SELECT FOR SHARE statements
• When performing these, ACCESS SHARE lock is acquired on other referenced tables

ROW EXCLUSIVE

• Conflicts with SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE locks
• Lock acquired when executing statements that modify data such as UPDATE, DELETE, INSERT

SHARE UPDATE EXCLUSIVE

• Conflicts with ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE locks
• Lock acquired when executing VACUUM (excluding FULL), ANALYZE, CREATE INDEX CONCURRENTLY, CREATE STATISTICS, ALTER TABLE VALIDATE, ALTER TABLE type statements

SHARE

• Conflicts with ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE locks, and also prevents simultaneous data modification at the table level
• Lock acquired when executing CREATE INDEX statements

SHARE ROW EXCLUSIVE

• Conflicts with ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE locks; similar to SHARE, it prevents simultaneous data modification, but due to Exclusive, only a single session can acquire this lock
• Lock acquired when executing CREATE COLLATION, CREATE TRIGGER, and other ALTER TABLE type statements

EXCLUSIVE

• Conflicts with ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE locks; this is the only type of lock that allows simultaneous ACCESS SHARE
• Acquired when executing REFRESH MATERIALIZED VIEW CONCURRENTLY statements

ACCESS EXCLUSIVE

• Conflicts with all types of locks (the only lock type that can also block SELECT statements)
  Therefore, if this lock occurs, you can expect that the transaction holding this lock is in progress
• Acquired when executing DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, REFRESH MATERIALIZED VIEW statements

Dead Lock 

A deadlock is a situation where two or more transactions are waiting for resources held by each other, resulting in all transactions waiting indefinitely for resources held by the other party.

To simulate a deadlock in PostgreSQL, you can use two or more sessions and follow these steps:

Deadlock Simulation Test Example 

Test data is created using benchmarksql.

1. Set up two sessions
   • Start two transactions in different sessions

-- Session 1 
BEGIN ;
UPDATE public.bmsql_stock
SET s_order_cnt = s_order_cnt + 1,
WHERE s_quantity = 100;

-- Session 2 
BEGIN ;
UPDATE public.bmsql_stock
SET s_order_cnt = s_order_cnt + 1,
WHERE s_quantity = 101;

2. Set up transactions to wait for each other's resources
   • To make both transactions wait for resources held by each other, execute the following queries

-- Session 1
UPDATE public.bmsql_stock
SET s_order_cnt = s_order_cnt + 1,
WHERE s_quantity = 101;

-- Session 2
UPDATE public.bmsql_stock
SET s_order_cnt = s_order_cnt + 1,
WHERE s_quantity = 100;

3. Attempt to commit the transactions
   • Each transaction tries to update the data it wants to change and attempts to commit

-- Session 1
COMMIT;

-- Session 2
COMMIT;

4. Check for deadlock occurrence
   • In this scenario, both sessions will end up waiting for each other, resulting in a deadlock.
   • PostgreSQL detects deadlocks and rolls back one of the transactions, so adjust and proceed with the test accordingly.