Postgres Locks

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

PostgreSQL provides various lock modes that control access to database tables. These modes can be used in various situations where multiversion concurrency control, or MVCC, does not produce the desired results. Some PostgreSQL commands automatically set locks to ensure referenced tables aren’t modified or dropped during command execution. Postgres locks, such as Write Locks or Exclusive locks, perform a similar function by preventing users from altering a table or a row in the table. While many of the LOCK functions happen automatically in PostgreSQL, any of these locks can also be acquired explicitly by executing the LOCK command. This tutorial provide explanations and examples of the functions and uses of Postgres Locks..

Prerequisite

  • PostgreSQL must be properly installed and configured on the local operating system. The most current version of Postgres can be downloaded here PostgreSQL.

  • A basic working knowledge in SQL is required to follow the examples in this tutorial.

What is Postgres Locks

When a DELETE or an UPDATE operation is preformed the affected rows are exclusively locked automatically throughout the entire transaction process. This is designed to prevents other processes from altering the table row during the transaction cycle. This results in the changes either being committed to the database or being rolled back, meaning canceled, for some reason.

It is important to note that waiting time only occurs during the lock process when the same row is being modified. This is not an issue when different rows are being modified or the SELECT operation is being executed.

Following is the basic syntax of the LOCK command:

1
2
3
4
LOCK TABLE
<table_name>
IN
mode_of_lock

Here is a breakdown of the syntax:

  • table_name – This is the name of the table that is targeted to lock. If the ONLY clause before the table name is specified, then just the specified table will be locked. If it is not specified, then the named table and its descendants will all be locked.

  • mode_of_lock – This specifies what mode will be used against the target table. If no mode is defined, then the most restrictive mode is used by default, this being is the _ACCESS EXCLUSIVE* mode.

Creating Sample Dataset

This section will explain how to create the sample dataset that will be used in this tutorial.

  • Begin by logging in to the Postgres shell.

  • Now execute the following command to create the database:

1
create database employeedb
  • Create tables for the database with the following structure:
1
2
3
4
5
6
7
8
CREATE TABLE IF NOT EXISTS employee (
    id SERIAL PRIMARY KEY,
    emp_id CHARACTER VARYING(100),
    emp_name CHARACTER VARYING(100),
    emp_age INTEGER,
    emp_gender CHARACTER VARYING(100),
    emp_department CHARACTER VARYING(100)
);

Now insert the sample database as follows:

1
2
3
4
5
6
7
INSERT INTO employee (id,emp_id, emp_name, emp_age, emp_gender,emp_department)
VALUES
   (1,'MN023','Rod Hendricks',43, 'male', 'finance'),
   (2,'MN069','Willie Tulliao',33, 'male', 'training'),
   (3,'MN073','Kelvin Victa',32, 'male', 'ict'),
   (4,'MN045','Raymond Forma',36, 'male', 'training'),
   (5,'MN011','Romulo Despi',40, 'male', 'ict');

The new “employee” table should resemble this:

1
2
3
4
5
6
7
 id | emp_id |    emp_name    | emp_age | emp_gender | emp_department
----+--------+----------------+---------+------------+----------------
  1 | MN023  | Rod Hendricks  |      43 | male       | finance
  2 | MN069  | Willie Tulliao |      33 | male       | training
  3 | MN073  | Kelvin Victa   |      32 | male       | ict
  4 | MN045  | Raymond Forma  |      36 | male       | training
  5 | MN011  | Romulo Despi   |      40 | male       | ict

Postgres LOCK example

With the table now ready to be used in an example, execute the following Postgres table LOCK command:

1
LOCK TABLE employee IN ACCESS EXCLUSIVE MODE;

The above lock mode instructs Postgres to lock the employee table until the end of the transaction. This will result in the transaction either being committed to the database or rolled back.

The above command should produce the following results:

1
2
3
4
employeedb=# BEGIN;
BEGIN
employeedb=# LOCK TABLE employee IN ACCESS EXCLUSIVE MODE;
LOCK TABLE

NOTE: The above command should be executed in a transaction block with the BEGIN command executed prior to the LOCK TABLE command. Otherwise, an exception will be thrown that will resembles the following:

1
ERROR:  LOCK TABLE can only be used in transaction blocks

Conclusion

This tutorial explained the functions and uses of Postgres Locks. The tutorial specifically provided an overview of the Postgres Locks function, the syntax structure of the basic LOCK command and a breakdown of the syntax. The article then explained how to create the sample dataset and tables used in this tutorial and how to add data to the sample database. The tutorial then provide a working example of the Postgres LOCK function. Remember that the affected rows are exclusively locked automatically throughout the entire transaction process whenever a DELETE or an UPDATE operation is preformed.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.