LOCKS in PostgreSQL

Lock is basically a situation which occurs on a table for a particular time due to conflicting locks which are raised by the other statement which perform some action on the table. It is used to control simultaneous access to the specified table.

• DeadLocks
• Advisory Locks

LOCK [ TABLE ]

name

IN

lock_mode

Types of Lock modes 

• Table-Level Locks and
• Row-Level Locks

Table-Level Locks

PostgreSQL provides various lock modes to control concurrent access to data in tables. It can be thought of as metadata locks and prevents concurrent users from making schema changes simultaneously or while records within the table are being changed.

It can be achieved manually using the LOCK TABLE and UNLOCK TABLE commands.

Types of Table-Level Locks

• AcessShareLock
• RowShareLock
• RowExclusiveLock
• ShareLock
• ShareRowExclusiveLock
• ExclusiveLock
• AccessExclusiveLock

Row-Level Locks

PostgreSQL upgrades row-level locks to page-level locks internally in a few places like serializable snapshot predicate tracking, buffer management, etc. It should be changed to cause concurrent UPDATEs to fail with a serialization error.

PostgreSQL doesn't remember any information about modified rows in memory, so it has no limit to the number of rows locked at one time.

Types of Row-Level Locks

• Exclusive lock
• Share-Lock

How to accomplish Row-Level Locks

To accomplished Row-Level Locks we use SELECT .... FOR UPDATE or SELECT .... FOR SHARE.

Deadlocks

Dead lock is a situation where two (or more) transactions each hold locks that the other wants.

Lets discuss a situation to understand the Deadlocks. Let suppose process ABC1 is holding lock on object XYZ1 and waiting to lock on object XYZ2. Process ABC2 is holding lock on object XYZ2 and waiting to lock on object XYZ1. Now here the two processes are now in what's called 'deadlock;' each is trying to obtain a lock on something owned by the other.

You can avoid the situation of deadlocks by making sure all your transactions must be acquired locks on the same objects in the same order, and if multiple lock modes are involved for a single object, then transactions should always acquire the most restrictive mode first.

Advisory Locks

It provides a convenient way to obtain a lock condition which is application enforced, and will not block writes to the table.

Lets take an example to understand the advisory locks. Imagine you have a scheduled task that sends information off to another 3rd party service. We can use PostgreSQL Advisory Locks to guarantee that the program cannot cause any unexpected behavior if run multiple times concurrently.

Lock mode of advisory locks are:

• postgresql.alock.ShareLock
• postgresql.alock.ExclusiveLock

Here's a free e-book on PostgreSQL: PostgreSQL 9.5.1 Documentation