Introduction
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
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.
