TABLE LEVEL LOCK AND ROW LEVEL LOCK

1. Table-level lock
2. Row-level lock

TABLE LEVEL LOCK:
A table-level lock is a type of lock that is applied to an entire table. It means that when a transaction acquires a table-level lock on a particular table, it can control access to the entire table, preventing other transactions from acquiring certain types of locks on the same table simultaneously. There are two main types of table- level locks in PostgreSQL:
Share Lock (SHARE MODE):
A share lock on a table allows multiple transactions to acquire the lock simultaneously for reading purposes. Transactions holding a share lock can read the table but cannot acquire an exclusive lock on the same table until the share lock is released.
Example: 
  LOCK TABLE table_name IN SHARE MODE;
Exclusive Lock (EXCLUSIVE MODE):
An exclusive lock on a table prevents any other transactions from acquiring any lock on the same table. Transactions holding an exclusive lock have exclusive access to the table for both reading and writing.
Example: 
  LOCK TABLE table_name IN EXCLUSIVE MODE;
It's important to note that table-level locks can potentially lead to performance issues and contention in a multi-user environment, as they restrict access to the entire table. In many cases, it's recommended to use row-level locks or other isolation mechanisms instead of table-level locks to minimize contention and allow for concurrent access to different parts of the table.
ROW LEVEL LOCK :
A row-level locks are a mechanism for controlling access to individual rows within a table. These locks are used to ensure the consistency of data in a multi-user environment by preventing conflicts when multiple transactions try to access or modify the same rows simultaneously. PostgreSQL provides two main types of row-level locks:
FOR UPDATE:
The FOR UPDATE lock is used when a transaction wants to update or delete specific rows.
It prevents other transactions from acquiring FOR UPDATE or FOR SHARE locks on the same rows until the locking transaction is committed or rolled back.
Example: 
  SELECT * FROM table_name WHERE condition FOR UPDATE;
FOR SHARE:
The FOR SHARE lock is used when a transaction wants to read specific rows without the intention of modifying them.
Multiple transactions can acquire FOR SHARE locks on the same rows simultaneously, but it prevents any of them from acquiring a conflicting FOR UPDATE lock until the locks are released.
Example: 
  SELECT * FROM table_name WHERE condition FOR SHARE;
These locks can be applied to individual
These locks can be applied to individual rows based on the specified conditions in the WHERE clause. The FOR UPDATE and FOR SHARE clauses can be used with various SELECT statements, including simple SELECT queries and more complex queries involving joins.
It's important to note that row-level locks are automatically acquired by PostgreSQL in certain situations, even if you don't explicitly use the FOR UPDATE or FOR SHARE clauses. For example, when you perform an UPDATE, PostgreSQL implicitly acquires a FOR UPDATE lock on the affected rows.
When using row-level locks, it's crucial to release the locks appropriately by committing or rolling back the transaction. Failing to release locks can lead to contention and may result in deadlocks.

(Query Blocking and Deadlocks)