What are locks in PostgreSQL?

What are locks used for?

There are many database fallacies that happen if a row is manipulated by multiple users simultaneously. That is if multiple users perform read and write operations on data.

To prevent this, Locks is introduced.

Working

Locks locks a row while it is being manipulated, and the other user waits for the manipulation.

During a transaction, the row which has been updated or deleted is locked. It remains locked until the transaction has ended.

Different lock modes

These modes specify the locks the current lock will conflict with.

• ACCESS EXCLUSIVE (this is used when no mode of lock is specified).
• ACCESS SHARE(it does not allow DELETE, ALTER or vacuum commands to execute)
• ROW EXCLUSIVE (it is used whenever a row is deleted or updated).
• SHARE ROW EXCLUSIVE (this is used if we want to hold one exclusive row in one session).
• EXCLUSIVE(it is used to prevent concurrent changes in data)
• SHARE UPDATE EXCLUSIVE (it prevents any concurrent updates and is not acquired automatically)
• SHARE (this is used to ensure the table’s stability during a transaction).
• ROW SHARE(this is acquired by the writers writing an exclusive row).

Syntax of locks

BEGIN WORK
LOCK tablename
IN
lockMode
COMMIT WORK

Code

BEGIN WORK
LOCK movies
IN 
ACCESS EXCLUSIVE
COMMIT WORK

Output

LOCK TABLE

Explanation

The example above shows that the movie table is locked until the running transaction is committed or rolled back.

If we use SET TRANSACTION instead of LOCK TABLE, it will be compatible with SQL.