Definition of PostgreSQL Lock Table
PostgreSQL lock table is defined as a lock table for access from the user, we can lock the table from read access or write access. A lock is very useful and important in PostgreSQL to prevent the user for modifying a single row or all tables. We can lock the table by using access share, row share, row exclusive, share, share update exclusive, exclusive, share row exclusive, and access exclusive mode in PostgreSQL. Using the lock command we need to specify the table name and the name of the mode which was we have applied on the table.
How to Lock Table in PostgreSQL?
Below is the way that defined how to lock the table in PostgreSQL. Below syntax shows how to lock the table.
Syntax:
Lock table name_of_table IN [Mode of locking] [NOWAIT]
- In the above example, the lock table is defined as a command used to lock the table by which mode we have used at the time of the locking table in PostgreSQL. After applying a lock on the table, it’s not accessible for read or write operations.
- The name of the table is defined as the table name on which we have applying lock.
- Mode of locking is defined as the mode which was used while locking a table in PostgreSQL. If we have not used any lock mode then the default mode of access exclusive is used in PostgreSQL.
- Now it is defined as do not wait for any lock to be released from the table. If the lock is not acquired then the transaction will directly be aborted without waiting in PostgreSQL.
- We can acquire lock explicitly by using the lock command. Below is the mode of lock in PostgreSQL.
Mode of Locks
- Access share
- Row share
- Row exclusive
- Share update exclusive
- Share
- Share row exclusive
- Exclusive
- Access exclusive
- The above mode of lock will contain its specified function to lock the table in PostgreSQL are as follows.
1. Access Share
- This lock in PostgreSQL conflicts with the only mode of access exclusive. Select command in PostgreSQL will acquire these locks on a specified table.
- This is defined as we can only reads data from the table we cannot modify the table after acquiring these locks on table.
2. Row Share
- This lock in PostgreSQL conflicts with the mode of access exclusive and exclusive. The select for share and select for update statement will acquire these types of lock on the table.
3. Row Exclusive
- This lock in PostgreSQL conflicts with the mode of share row exclusive, share, access exclusive, and exclusive.
- The update deletes and insert will acquire the locks on the table. Basically, these locks are acquired on the lock which modifies the statement in PostgreSQL.
4. Share Update Exclusive
- This lock in PostgreSQL conflicts with the mode of share row exclusive, share, access exclusive, share update exclusive, and exclusive.
- It will acquire command of vacuum, index creation, alter table, and validate command in PostgreSQL.
5. Share
- This lock in PostgreSQL conflicts with the mode of share row exclusive, share, access exclusive, share update exclusive, share, and exclusive.
- This lock mode will acquire locks from the create index command in PostgreSQL.
6. Share Row Exclusive
- This lock in PostgreSQL conflicts with the mode of share row exclusive, share, access exclusive, share update exclusive, share row exclusive, share, and exclusive.
- This mode is not automatically acquired on the table we have applied the same by using the lock command.
7. Exclusive
- This lock in PostgreSQL conflicts with the mode of share row exclusive, share, access exclusive, share update exclusive, share row exclusive, share, and exclusive.
- This lock is acquired by using the refresh materialized view.
8. Access Exclusive
- This lock in PostgreSQL conflicts with the mode of share row exclusive, share, access exclusive, share update exclusive, share row exclusive, share, access exclusive, and exclusive.
- While using this lock the only the user can access the table who applying a lock on the table.
Examples of PostgreSQL Lock Table
Below is the example of lock table in PostgreSQL.
1. Lock Table by Using Access Share Lock Mode
- The below example shows that the lock table by using access share lock mode.
begin;
lock table stud1 IN ACCESS SHARE MODE;
select * from stud1;
2. Lock Table by Using Row Share Lock Mode
- The below example shows that lock table by using row share lock mode.
begin;
lock table stud1 IN ROW SHARE MODE;
3. Lock Table by Using Rowexclusive Lockmode
- Below example shows that lock table by using row exclusive lock mode.
begin;
lock table stud1 IN ROW EXCLUSIVE MODE;
4. Lock Table by Using Share Updateexclusive Lock Mode
- Below example shows that lock table by using share update exclusive lock mode.
begin;
lock table stud1 IN SHARE UPDATE EXCLUSIVE MODE;
5. Lock Table by Using Share Lock Mode
- Below example shows that lock table by using share lock mode.
begin;
lock table stud1 IN SHARE MODE;
6. Lock Table by Using Share Row Exclusive Lock Mode
- Below example shows that lock table by using share row exclusive lock mode.
begin;
lock table stud1 IN SHARE ROW EXCLUSIVEMODE;
7. Lock Table by Using Exclusive Lock Mode
- Below example shows that lock table by using exclusive lock mode.
begin;
lock table stud1 IN EXCLUSIVE MODE;
8. Lock Table by Using Access Exclusive Lock Mode
- Below example shows that lock table by using access exclusive lock mode.
begin;
lock table stud1 IN ACCESS EXCLUSIVE MODE;
Recommended Articles
This is a guide to PostgreSQL Lock Table. Here we also discuss the definition and how to lock table in postgresql along with different examples and its code implementation. You may also have a look at the following articles to learn more –
