In focus

LOCKS in PostgreSQL

In this article you will learn about locks in PostgreSQL. PostgreSQL is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards-compliance.

Abhishek Sinha Apr 05, 2016


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.

Types of Locks
  • DeadLocks
  • Advisory Locks
Basic syntax of Lock statement


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.


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

deadlocks postgresql