When working with PostgreSQL, understanding how locks work on tables is crucial. To ensure data consistency and integrity during concurrent transactions, PostgreSQL uses various locking mechanisms. In this blog, we’ll dive into the types of table-level locks available in PostgreSQL, what each lock represents, and how they impact database operations.
What is a Lock in PostgreSQL?
A lock is a mechanism that controls concurrent access to database objects, such as tables, rows, or pages. Locks ensure that multiple transactions don’t interfere with each other in a way that corrupts data or causes inconsistent results.
PostgreSQL uses multi-granularity locking, meaning it locks at different levels (table-level, row-level) depending on the operation.
Table-Level Locks in PostgreSQL
PostgreSQL provides several lock modes that can be acquired on tables. You can also explicitly acquire a lock with the command:
LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ];
Where lockmode can be one of the following:
* ACCESS SHARE
* ROW SHARE
* ROW EXCLUSIVE
* SHARE UPDATE EXCLUSIVE
* SHARE
* SHARE ROW EXCLUSIVE
* EXCLUSIVE
* ACCESS EXCLUSIVE
Each lock mode has different characteristics and use cases, which we will explain below.
1. ACCESS SHARE Lock
* Purpose: Acquired by SELECT queries.
* Effect: Allows other transactions to read the table concurrently.
* Blocks: Schema-altering commands (like ALTER TABLE) and exclusive locks.
* Example: Simply running SELECT * FROM table; acquires this lock.
BEGIN;
select * from table;
SELECT
pg_class.relname AS table_name,
pg_locks.locktype,
pg_locks.mode,
pg_locks.granted,
pg_stat_activity.pid,
pg_stat_activity.usename,
pg_stat_activity.state,
pg_stat_activity.query,
pg_stat_activity.query_start
FROM pg_locks
JOIN pg_class ON pg_class.oid = pg_locks.relation
JOIN pg_stat_activity ON pg_stat_activity.pid = pg_locks.pid
WHERE pg_class.relname = 'table';
Detailed Explanation:
The ACCESS SHARE lock is the most common and lightweight lock in PostgreSQL. It ensures that many sessions can read from the same table at the same time without blocking each other. This is why SELECT queries do not interfere with each other.
However, this lock blocks operations that change the table's structure, such as ALTER TABLE, DROP TABLE, or other DDL commands. Those commands need stronger locks and will wait until all ACCESS SHARE locks are released.
This lock ensures consistency during read operations while preventing conflicting structural changes.
2. ROW SHARE Lock
* Purpose: Acquired by SELECT FOR UPDATE or SELECT FOR SHARE.
* Effect: Locks rows to prevent concurrent modifications.
* Blocks: Exclusive locks but allows concurrent reads and writes elsewhere.
* Example:
BEGIN;
SELECT * FROM my_table WHERE id = 1 FOR UPDATE;
-- This locks the row with id=1 to prevent others from updating it.
Detailed Explanation:
The ROW SHARE lock is a row-level locking mechanism that prevents other transactions from modifying the same rows you have locked for update or share. While it locks the specific rows for updates, it does not prevent other rows from being accessed or modified concurrently.
This lock prevents race conditions where two transactions try to update the same row simultaneously, which would cause inconsistencies.
It allows high concurrency while preserving correctness for row-level modifications.
3. ROW EXCLUSIVE Lock
* Purpose: Acquired during INSERT, UPDATE, or DELETE operations.
* Effect: Allows concurrent reads and writes but blocks some schema modifications.
* Blocks: Certain DDL commands like ALTER TABLE.
Example:
BEGIN;
INSERT INTO my_table (col1) VALUES ('value');
-- This acquires a ROW EXCLUSIVE lock on my_table.
Detailed Explanation:
The ROW EXCLUSIVE lock is obtained whenever data is modified. It prevents other transactions from acquiring conflicting schema locks but does not block other concurrent writes or reads.
This lock allows multiple transactions to modify the table concurrently but ensures the table’s structure remains stable during modifications.
It's the standard lock mode when performing data-changing queries and is crucial for maintaining concurrency while protecting data integrity.
4. SHARE UPDATE EXCLUSIVE Lock
* Purpose: Used by maintenance commands like VACUUM and ANALYZE.
* Effect: Prevents concurrent schema modifications but allows reads and writes.
* Blocks: Schema changes like ALTER TABLE.
Example:
VACUUM my_table;
-- This acquires a SHARE UPDATE EXCLUSIVE lock.
Detailed Explanation:
This lock mode is used primarily for internal maintenance operations that scan or update the table without making conflicting structural changes.
For instance, the VACUUM process cleans up dead rows but does not block regular DML (data manipulation language) operations like INSERT or UPDATE.
The SHARE UPDATE EXCLUSIVE lock protects the table's structure during such maintenance to avoid race conditions with schema modifications.
5. SHARE Lock
* Purpose: Acquired during commands like CREATE INDEX.
* Effect: Prevents concurrent writes but allows reads.
* Blocks: Data modifications such as INSERT, UPDATE, and DELETE.
Example:
CREATE INDEX idx_col1 ON my_table (col1);
-- This acquires a SHARE lock to block writes while the index is created.
Detailed Explanation:
When PostgreSQL creates an index, it needs the table data to remain stable to build a consistent index.
This SHARE lock allows users to continue reading data but blocks any data modifications to ensure the index accurately reflects the table’s content.
Writes will wait until the index creation completes, but SELECT queries are not affected.
6. SHARE ROW EXCLUSIVE Lock
* Purpose: Used internally by commands like ANALYZE.
* Effect: Blocks concurrent writes and schema modifications.
* Example: Usually acquired internally by PostgreSQL.
Detailed Explanation:
This lock is stronger than the ROW EXCLUSIVE lock and prevents most concurrent modifications and schema changes.
It is primarily used by internal operations to maintain consistency during processes that need stable data but do not block all reads.
It is rarely directly acquired by users but is crucial in system operations.
7. EXCLUSIVE Lock
* Purpose: Acquired during schema changes like ALTER TABLE.
* Effect: Blocks most concurrent writes and schema changes but allows reads.
Example:
BEGIN;
ALTER TABLE my_table ADD COLUMN new_col INT;
-- This acquires an EXCLUSIVE lock.
Detailed Explanation:
When altering the structure of a table, PostgreSQL needs to prevent concurrent writes and schema modifications to avoid conflicts.
However, reads (SELECTs) are still allowed to proceed, so the database can maintain availability during many schema changes.
Other write operations or DDL statements that conflict with the EXCLUSIVE lock must wait until the lock is released.
8. ACCESS EXCLUSIVE Lock
* Purpose: The most restrictive lock mode.
* Effect: Blocks all other operations, including reads, writes, and other locks.
* Used by: DROP TABLE, TRUNCATE, and some ALTER TABLE commands.
Example:
DROP TABLE my_table;
-- This acquires an ACCESS EXCLUSIVE lock.
Detailed Explanation:
This is the strongest lock in PostgreSQL and guarantees exclusive access to the table.
It ensures that no other transactions can read, write, or lock the table while it is held.
Commands like DROP TABLE or TRUNCATE require this lock because they fundamentally change or remove the table.
While an ACCESS EXCLUSIVE lock is held, all other transactions trying to access the table will be blocked, potentially leading to significant wait times if the lock is held for a long.
PostgreSQL System Tables Related to Locks
PostgreSQL provides several system catalog views that help monitor and manage locks:
* pg_locks
This view displays information about the current locks held by active processes in the database. It includes details such as the lock mode, the relation (table) locked, and the process ID holding the lock.
* pg_stat_activity
Shows information about the currently active queries and sessions, including those holding locks. You can join this with pg_locks to identify which query holds a specific lock.
* pg_class
Contains metadata about tables and indexes. The oid column in this table is used in pg_locks to identify the locked table.
By querying these system tables together, you can get detailed insights into locks currently held in your PostgreSQL database.
Useful Queries to Check Current Locks on Specific Tables
1. List all locks on a specific table:
SELECT
pg_class.relname AS table_name,
pg_locks.locktype,
pg_locks.mode,
pg_locks.granted,
pg_stat_activity.usename,
pg_stat_activity.query,
pg_stat_activity.query_start
FROM pg_locks
JOIN pg_class ON pg_locks.relation = pg_class.oid
JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid
WHERE pg_class.relname = 'your_table_name';
Replace 'your_table_name' with the name of the table you want to inspect.
2. Check all current locks with their lock modes:
SELECT DISTINCT mode FROM pg_locks WHERE relation IS NOT NULL;
3. Find blocking locks and the queries they block:
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_query,
blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity ON blocked_locks.pid = blocked_activity.pid
JOIN pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND
blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_stat_activity blocking_activity ON blocking_locks.pid = blocking_activity.pid
WHERE NOT blocked_locks.granted;
This query helps identify which queries are blocked and which queries are causing the block.
Locks are fundamental to PostgreSQL’s ability to handle multiple concurrent transactions while maintaining data consistency and integrity. Each lock mode serves a specific purpose, balancing concurrency and safety depending on the type of operation being performed.
By understanding these locks, PostgreSQL users and administrators can better diagnose performance issues, avoid deadlocks, and optimize their database workloads.
Moreover, leveraging PostgreSQL’s system catalogs like `pg_locks` and `pg_stat_activity` empowers you to monitor live lock activity and troubleshoot locking conflicts effectively.
Mastering locks in PostgreSQL is a crucial step toward becoming proficient in database management and ensuring your applications run smoothly under concurrent workloads.
Delve into the strategies for enhancing your PostgreSQL database's responsiveness. This article, "How to Improve PostgreSQL Performance Using pg_prewarm & Caching Techniques," provides practical guidance on leveraging pg_prewarm and various caching methods to ensure faster query execution and a more efficient database.