Hey request! Are you alive? How to easily handle locks in PostgreSQL

Good time of day! Administration and maintenance of relational databases is often a non-trivial task. Sometimes, queries that worked quickly suddenly start to “slow down” for unknown reasons, the size of the tables grows, and overall database performance decreases.

Often the reason for this behavior is the blocking of various resources that occur in the database, and, accordingly, the growing waiting time for these resources. For example, difficulties begin in situations where two or more queries in different sessions try to simultaneously change the same data in tables or the table structure itself.

To understand this situation, the DBA needs to understand which process is blocking and which process is blocking, as well as be able to cancel or “kill” the blocking process and finally check the result.

In this article I want to touch on the topic of locks in PostgreSQL and talk about tools for working with them. But first, let's try to understand the topic itself.

A bit of theory: educational program about locks


What are database locks? Wikipedia offers the following definition: "Locking (Eng. Lock ) in the database - a mark of the capture of the transaction object in a limited or exclusive access to prevent conflicts and to maintain data integrity."

PostgeSQL maintains data integrity by implementing the MVCC model. MVCC (MultiVersion Concurrency Control) is one of the mechanisms for ensuring parallel access to the database, which consists in providing each user with a so-called “snapshot” of the database. A special “property” of such a snapshot is that the changes made by the user to the database are invisible to other users until the transaction is committed.

PostgreSQL guarantees integrity even for the most stringent transaction isolation level using the innovative SSI isolation level (Serializable Snapshot Isolation, Serializable Snapshot Isolation).

For a better understanding of the topic, you can read an article on Habré and an article in the blog of Alexander Zhuravlev about locks, their work and competitive access in general.

Unexpected situations


Unfortunately, situations arise when implemented mechanisms to ensure data integrity still cannot cope with incoming requests without blocking. This rarely happens, but if a situation arises that some query has blocked the whole table for a long time, then this can lead to trouble.

For example, if you start a long-processed query to a table with 1000 records, to which 100 UPDATE queries occur per second, then in 5-6 hours the size of the table will increase to 1.8 million records, respectively, the physical size of the table will also increase (since the database stores all versions rows until a long transaction completes its work.

Let's consider this situation in more detail.

Blocking Example


Suppose we have a pgsqlblocks_testing table in some database and it has a rule_pgsqlblocks_testing rule . We emulate a “long” query for it for 10 minutes, for example, using the pgAdmin SQL editor:

SELECT * FROM public.pgsqlblocks_testing LIMIT 1000; SELECT pg_sleep(600);

Pid of process 16728

Open another editor and execute another request to delete the rule:

DROP RULE rule_pgsqlblocks_testing ON public.pgsqlblocks_testing;

Pid of process 16726

And here DROP RULE is blocked by SELECT request. MVCC in this case could not do without explicitly locking the pgsqlblocks_testing table .

Lock Tools


How do we view the existing locks? You can write the query yourself for the pg_locks lock table and pg_stat_activity view, or use the tool built into pgAdmin.

Server Status in pgAdmin


pgAdmin is a fairly convenient and simple software for working with the PostgreSQL database. At the moment, the current versions are pgAdmin III and released only at the end of September pgAdmin IV.

pgAdmin III


Displaying information about locks and active processes in pgAdmin III requires the adminpack extension in the database. After installing this extension, the desired window opens through the Tools - Server Status menu.

In this window we see a table with processes and a table with existing locks in the database. In order not to get lost among a large number of processes, we can adjust the colors of processes depending on their status: active, blocked, inactive or “slow”.


In the table, each blocking and blocking process is represented by separate lines, and there is no way to quickly determine who is blocking whom. To solve this problem, we will have to compare different lines with each other in an attempt to find lines combined by the common value of the relation column and the excellent values ​​of the granted column.

There are two buttons in the window for canceling or terminating the selected process. After terminating any of the processes, you need to update the window and match the lines again to evaluate the result.

So, pgAdmin III can be used as a tool for working with locks, but it has a couple of minuses: it requires preliminary configuration of the database and shows locks in a flat form (without a tree view of blocking-blocked processes), which complicates the search for problem processes and the assessment of their termination. This makes it not the most convenient tool for our tasks.

pgAdmin IV


After installing and running pgAdmin IV, we can see the existing locks in the same form as in pgAdmin III.


But ... that's all we can do here. PgAdmin IV lost the toolbar for actions on processes, and we can no longer cancel or terminate processes from this view, which makes pgAdmin IV an inconvenient tool for working with locks.

Database Queries


The network has many different query implementations for viewing blocked and blocking queries in the database.

The very first result in the search engine for the query “pg_locks monitoring” gives a link with the query variant:



Request 1:

SELECT blocked_locks.pid     AS blocked_pid,
         blocked_activity.usename  AS blocked_user,
         blocking_locks.pid     AS blocking_pid,
         blocking_activity.usename AS blocking_user,
         blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.GRANTED;

We open the editor and enter a query to get information about the locks:



It looks quite complicated, but the result is pleasant to the eyes. In general, the PostgreSQL community has created and supports quite a lot of resources that help and facilitate the search for information for ordinary DBAs. For example, the same wiki wiki.postgresql.org.

So, we see who is blocking who and whom. There are still options for such requests, where you can display information about how long the process has been waiting in line, and so on.

The second link (from the official, by the way, documentation) offers a very simple request:

Request 2:

SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa
    ON pl.pid = psa.pid;

The meaning of all these variations is essentially the same query: displaying information about locks. We received the necessary information, but the answer does not lie directly on the surface. Especially if there are a lot of queries to the database. Sit and figure out who blocks whom and why! Not everyone can build a graph of blocked resources in their heads!



In addition, we need to destroy or stop the blocking process. And yes, this will have to be done manually, through another request indicating the pid of the process -

select pg_backend_pid(16728);

or

select pg_terminate_backend(16728);

To check the result, run Request 1 or again SELECT * FROM pg_catalog.pg_stat_activity WHERE pid=16728;.

Everything is simple and convenient with pgSqlBlocks!


I want to show you another tool and share why it is so convenient - pgSqlBlocks. The pgSqlBlocks tool was written by us for ourselves, and was created specifically to facilitate the solution of lock problems in PostgreSQL, which we have been using for more than a year.

This is what the pgSqlBlocks window looks like in the case of our two-process example (here they have pid 29981 (SELECT) and 28710 (DROP RULE)).



In the left part of the window there is a list of databases that displays information about the status of connecting to the database (connected, disconnected, updating information, connection error, there are locks in the database).

The main part of the application is occupied by the tree of processes that are currently in the selected database. Blocked processes have an icon of a closed gray lock and are descendants of blocking processes whose icon is a red lock. The icon for ordinary processes is a green dot.

This representation of processes allows us to easily navigate in them, to receive information about blocking and waiting processes, as well as about their relationship to each other. For clarity, you can hide the usual (not blocked and not blocking) processes.


We can clearly see that a process with pid 29981 with a long SELECT request blocks the process with pid 28710.

If necessary, you can send a cancel or kill signal to any process. For example, if you destroy a blocked process 28710, then the information in the process tree will be updated immediately and we will see the result - a process 29981 with a long SELECT request does not block anyone else. Fast and convenient.

Another of the small and pleasant features of the application can be noted:

- Saving the history of locks to a file and downloading back to the application. A kind of snapshot of all locks at the time of saving, which allows you to view and analyze at any convenient time what locks were in the database;
- The tray icon changes if at least one of the connected databases has a lock;
- Notifications in the tray when locks appear;
- Customizable auto-update process list.

How to install pgSqlBlocks and how is it convenient compared to the options described above?

Installation and setup


JRE 8 must be preinstalled in the system.

Go to pgcodekeeper.ru/pgsqlblocks and select the latest current version of the program. The folder will contain 4 jar files. Choose the one that suits the OS and the bit depth of your system. Download, run and voila!

This is all you need to run the application. Everything works out of the box.

To start working with the application, you should fill out the list with databases. To add a new database, click on the database icon with the "+" sign above the database list and fill in the necessary data in the dialog that appears. It is better to store the password in a pgpass file.


Tested on versions 9.2-9.6 PostgreSQL.

Additionally, you can configure the frequency of updating information from the database, the need to show idle processes, a list of displayed columns.

Conclusion


The problem of blocking queries in the database can be very serious and lead to a noticeable slowdown in the database and the exhaustion of disk space. Therefore, it is important to have a convenient and fast tool for detecting locks and taking (sometimes) operational actions.

Such a tool for us is pgSqlBlocks, an application that allows you to easily navigate among processes and receive information about blocking and pending requests.

Its advantages include the visibility of the information provided, as well as the convenience of performing typical tasks - viewing information about processes, finding problems among the list of processes, canceling or terminating a process, and evaluating the result. In addition, a nice opportunity is to save the lock history to a file for further analysis of the situation. All this makes your work with locks in the PostgreSQL database quick and convenient.

PS: the inspiration for creating this application was the MSSQL Blocks utility . But it is designed specifically for working with the MSSQL database. For PostgreSQL, there were no analogues.

→ Download the latest version for your OS here .

Also popular now: