PostgreSQL Lock

The lock is used to prevent the user from modifying the row or table. The table can be modified using the update and Alter commands and is then automatically locked exclusively for the duration of the transaction.
The lock is used to prevent other users from changing the row until the transaction is either committed or rolled back.SELECT queries never have to wait.When two users want to modify the same row then one user must wait for the other users, and if two users modify different rows then no wait is required.
Locking occurs automatically by the database but in some cases locking is controlled manually.LOCK command is used in manual locking.

Syntax LOCK command:

The basic syntax SERIAL dataype is as follows :-
name:-Name is the name of a table to lock. If only specified, only that table is locked, if not, its descendant tables (if any are closed) are locked.
lock_mode:-If no lock mode is specified, the most restrictive mode, ACCESS EXCLUSIVE, is used. Possible values are: ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE.

LOCK [ TABLE ]
name
IN
lock_mode

DeadLocks

A deadlock occurs when there are two transactions waiting to complete their operations. But in PostgreSQL, it can detect DeadLocks and terminate them with a ROLLBACK. If you are creating any application than make sure design in this way, that they will lock objects in the same order.

Example :

Consider the COMPANY table to be created-

lfcdb=# select * from COMPANY;
id | name     | age | address    | salary
----+-------+-----+------------+--------
1 | Prateek  |  22 | Mysore     |  20000
2 | Vaibhav  |  20 | Udaipur    |  15528
3 | Yash     |  24 | Mumbai     |  22000
4 | Jugal    |  23 | Delhi      |  25000
5 | Prasun   |  19 | Kolkata    |  30000
-----------------------------------------
\\Now, locks the COMPANY table within the testdb database in ACCESS EXCLUSIVE mode.
BEGIN;
LOCK TABLE company1 IN ACCESS EXCLUSIVE MODE;
------------------------------------------
\\PostgreSQL statement will produce the following result −
LOCK TABLE

Visit :

PostgreSQL Triggers

Discussion