PostgreSQL Locks and Table Lock Examples

Introduction

When you’re working with database tables in PostgreSQL, you’ll need to prevent situations where multiple users or sessions are updating the same data at the same time. Fortunately, the solution to this issue is simple: PostgreSQL locks can be used to control this kind of concurrent access to database tables. In this article, we’ll take a closer look at PostgreSQL locks and review an example of a table lock in action.

Prerequisites

Before we delve any deeper into the topic of PostgreSQL locks, there are a few basic prerequisites that must be in place:

• PostgreSQL server needs to be properly installed and configured, and the service needs to be running in the background.

If you’re running Linux or Windows on your machine, you can download PostgreSQL here.

• You can start PostgreSQL server on a Linux machine using the following command:

• If you’re not sure whether the service is running, use the following command:

• The output of this command will look something like this:

• To start, stop and restart PostgreSQL server on a Windows machine, there’s a somewhat different set of instructions:

1. First, open Control Panel
2. Next, open Administrative Tools
3. Then, open Services
4. Locate the PostgreSQL Server service
5. Finally, start, stop or restart the service

What are PostgreSQL Locks?

PostgreSQL locks, also known as “write locks” or “exclusive locks”, work by preventing users from changing either a row or an entire PostgreSQL table. When rows have been changed by the DELETE or UPDATE operations, they will be exclusively locked until the transaction is complete. This will prohibit other users from performing any changes on the same rows until the transaction in question is either rolled back or committed. This locking mechanism only comes into play when users are trying to modify the same rows– if users are modifying different rows, they won’t need to wait.

Although some degree of locking occurs automatically within databases, there are situations where the locking process must be done manually. We can perform manual locking with the PostgreSQL LOCK command. Using this command also allows you to indicate the scope and lock type for the transaction.

NOTE: There is no equivalent command for unlocking a PostgreSQL table; locks are automatically released at the end of a transaction.

PostgreSQL LOCK Command Syntax

Let’s look at the basic syntax for the PostgreSQL LOCK command:

Another version of the syntax is shown below:

Let’s take a look at each part of this command’s syntax so that we can understand it better:

• The name represents the table name that we want to lock. By specifying the ONLY clause right after the TABLE clause, we indicate that only the specified table name will only be locked; if we omit this clause, PostgreSQL will lock the table and all of its descendants.

• The lockmode will specify any locks that might conflict with this lock. The most restrictive mode is ACCESS EXCLUSIVE, and it acts as the default if no lock mode is specified.

• The NOWAIT clause tells PostgreSQL that the LOCK TABLE command shouldn’t wait for the release of any conflicting locks. If the lock can’t be acquired immediately, the transaction will be cancelled.

What are PostgreSQL Deadlocks?

When multiple transactions are waiting for each other’s respective operations to finish, the situation results in what is known as a “deadlock”. PostgreSQL can catch occurrences of deadlocks and can end them using a ROLLBACK. Planning your applications so that they lock objects in the same order can help you avoid deadlock situations.

What are PostgreSQL Advisory Locks?

PostgreSQL allows the creation of locks with application-specific definitions. These are known as advisory locks. It’s important to keep in mind that the database system doesn’t enforce the use of advisory locks, so the application is responsible for using them correctly.

The example shown below illustrates the typical function of the PostgreSQL advisory lock, which is to mimic the “pessimistic locking” strategies commonly seen in flat-file management systems.

In this example, we begin by getting a demo table named team, which contains the records shown below:

We can use the following commands to lock the table team within the testdatabase database using the mode ACCESS EXCLUSIVE. Keep in mind that the LOCK statement will only take effect in transaction mode:

The above statement results in the notification “LOCK TABLE”.

This message confirms that the team table is now locked for the entire duration of the transaction. We need to perform a COMMIT or a ROLLBACK to finish the transaction.

Conclusion

It’s clear that controlling concurrent access to rows and tables is important for maintaining data integrity. PostgreSQL locking offers an easy way to exercise this control and ensure that only one user is modifying a row or table at a time. In this article, we explained how PostgreSQL locks work and looked at an example that demonstrates a typical usage of the LOCK TABLE command. With this example and the information provided in this tutorial, you’ll be equipped to enable table locking in your own PostgreSQL database environment.