What is ACCESS EXCLUSIVE LOCK Mode in PostgreySQL?

Locking is a functionality of a relational database to block various kinds of access performed on the tables based on the lock mode. The session which took the lock retains normal access and the effect is only seen by other users trying to access the locked table in another session. When any such attempt is made, database will block the access in the sense the query will not fail, instead, it will pause until the blocked command is terminated by the user or when the lock is released. 

Database implicitly acquires locks before performing any transaction and the lock is released immediately when the transaction is committed. Explicit locking is a user-defined locking done on a database by the user according to their granted table privileges and this lock overrides implicit locking.

When a user requires an explicit lock on the table, it can be accomplished by using different types of lock modes like ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE. Among these ACCESS EXCLUSIVE lock mode is used for highly secure tables.

In this article, we shall see about ACCESS EXCLUSIVE LOCK mode with some examples.

ACCESS EXCLUSIVE LOCK:

Once this lock is acquired on any table, it is held until the end of the transaction.  This mode guarantees that the holder is the only transaction accessing the table and no other user can access the table in another session. Only an ACCESS EXCLUSIVE lock blocks a SELECT (without FOR UPDATE/SHARE) statement. This lock is acquired automatically by a DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, and REFRESH MATERIALIZED VIEW commands on the table it modifies.

Now let’s create a sample table to illustrate the purpose of this lock mode.

1. Create a table: Create a table named customer with two rows id and name.

CREATE TABLE customer
(
 id INTEGER,
 name VARCHAR(25)
);

2. Insert records: Let’s insert customer id and names into the table,

INSERT INTO customer
VALUES 
(1,'John'),
(2,'Rosy'),
(3,'Nancy'),
(4,'Jin'),
(5,'Pinky');

And check if the records are inserted properly,

3. Start transaction: BEGIN command is used to initiate transaction which is a series of work done in the database like by adding, deleting or updating records. It is the starting point to initiate the transaction without that database won’t be able to recognize the transaction.

BEGIN TRANSACTION;
// DML statements

or 

BEGIN;
// DML statements

4. Lock table: Lock table command is used to obtain the table-level lock and it will wait if any conflicting locks are released. Once the lock is obtained on a table, it will be held until the end of the current transaction. If no mode is specified, then the most restrictive ACCESS EXCLUSIVE lock mode is imposed on the table.

LOCK TABLE customer;
or
LOCK TABLE customer 
IN ACCESS EXCLUSIVE MODE;

Note: Lock mode can be applied on the table only in between the transaction blocks (BEGIN…COMMIT)

5. Access table: Now when the user tries to access the table in another session none of the operations are allowed on the table until the lock is explicitly released (using the COMMIT command) or when the transaction is aborted.

The following logs will be seen in Postgres while trying to access the table,

6. Release lock: COMMIT command refers to saving data permanently after a set of changes. It is used to end the current transaction and only then all other users can see the changes. Before committing the changes, the user can view only the old data and not the edited information within a transaction.

 COMMIT TRANSACTION;

Conflicting Lock Modes:

Two transactions cannot hold locks of conflicting modes on the same table at the same time. This access exclusive lock mode cannot be held by more than one transaction at the same time as it conflicts with all other lock modes. Let’s take this lock in one transaction,

Now trying to take a different lock mode for the same table in another transaction would block the command,