QUERY BLOCKING AND DEAD LOCK

A deadlock in PostgreSQL (or any database system) occurs when two or more transactions are blocked indefinitely, each waiting for the other to release a lock. In other words, each transaction holds a resource that the other transaction needs and vice versa, resulting in a circular dependency.
PostgreSQL uses a two-phase locking protocol to manage locks: transactions first acquire locks and then release them. Deadlocks can occur when the transactions are not careful about the order in which they acquire locks, leading to a situation where each transaction is waiting for a resource held by the other.
Here's a simplified example to illustrate how a deadlock can occur:
    Transaction A acquires a lock on resource X.
    Transaction B acquires a lock on resource Y.
    Transaction A attempts to acquire a lock on resource Y (waits for B to release it).
    Transaction B attempts to acquire a lock on resource X (waits for A to release it).
    Now, both transactions are waiting for a resource that the other transaction holds, creating a deadlock.
PostgreSQL has mechanisms to detect and resolve deadlocks, but it's also essential for application developers and database administrators to design transactions carefully to minimize the likelihood of deadlocks. Here are some tips to avoid deadlocks:
1.Lock Ordering:
Enforce a consistent order in which locks are acquired. If all transactions follow the same order when acquiring locks, deadlocks are less likely to occur.
2.Lock Timeout:
Set reasonable lock timeouts for transactions. If a transaction cannot acquire a lock within a specified time, it can be programmed to release its locks and retry later.
3.Transaction Design:
Break transactions into smaller, more manageable units to reduce the duration of locks. This reduces the chance of collisions between transactions.
4.Use Explicit Locks Wisely:
If using explicit locks (e.g., FOR UPDATE), ensure that they are acquired and released appropriately within the transaction.

VIEWS FOR DETECTING LOCKS :
The view pg_locks provides access to information about the locks held by open transactions within the database server. 
    postgres => \d pg_locks
    View "pg_catalog.pg_locks"
    Column | Type | Collation | Nullable | Default

    --------------------+----------+-----------+----------+---------
    locktype | text | | |
    database | oid | | |
    relation | oid | | |
    page | integer | | |
    tuple | smallint | | |
    virtualxid | text | | |
    transactionid | xid | | |
    classid | oid | | |
    objid | oid | | |
    objsubid | smallint | | |
    virtualtransaction | text | | |
    pid | integer | | |
    mode | text | | |
    granted | boolean | | |
    fastpath | boolean | | |

    postgres=# select locktype,database,relation,pid,mode from pg_locks ;

    postgres=# select pg_terminate_backend(pid);
    pg_terminate_backend

    ----------------------
    t
    (1 row)


(Lock Queue, Lock Management Options)