What is ACCESS EXCLUSIVE lock?

An ACCESS EXCLUSIVE lock in PostgreSQL is the most restrictive type of lock. It prevents all other transactions from accessing the locked table in any way, including reading or writing. This lock is typically acquired during operations that modify the structure of a table, such as:

• ALTER TABLE
• DROP TABLE
• TRUNCATE TABLE
• REINDEX

Characteristics of ACCESS EXCLUSIVE Lock

1. Exclusive Access: No other transactions can read or write to the table while it is locked with an ACCESS EXCLUSIVE lock.
2. Blocking: Any other transaction that tries to access the locked table will be blocked until the lock is released.
3. Use Cases: It is used for operations that fundamentally change the table's structure or data.

Example

When you execute a command like DROP TABLE, PostgreSQL will acquire an ACCESS EXCLUSIVE lock on that table:

DROP TABLE my_table;

During this operation, any other attempts to read from or write to my_table will be blocked until the DROP TABLE command completes and the lock is released.

Conclusion

ACCESS EXCLUSIVE locks are essential for ensuring data integrity during critical operations that require exclusive access to a table. However, they can lead to contention and blocking if not managed carefully, especially in a multi-user environment.