Hey, a request! Are you alive? How easy it is to handle locking in PostgreSQL

Good time of day! Administration and maintenance of relational databases is often not a trivial task. Sometimes the queries worked quickly, suddenly begin to "slow down" for unknown reasons, the size of the tables grows and the overall performance of the database is reduced.

Often the cause of such behavior are emerging in the base of the lock different resources, and therefore, growing the waiting time of those resources. For example, the difficulties begin in situations where two or more request in different sessions are trying to simultaneously change the same data in the tables or the structure of the table.

To understand the current situation, the DBA will need to understand what process is blocking and what is the process is blocked, and also have the opportunity to cancel or "kill" the blocking process and at the end to check the result.

In this article I want to touch on the topic of locks in PostgreSQL and tell about the tools for working with them. But first try to understand the topic.

the

a Bit of theory: an educational program about locks

What is a lock in the database? Wikipedia offers the following definition:“Lock (eng. lock) in a DBMS — mark of the seizure of the object of transaction in a limited or exclusive access, to prevent collisions and maintain the integrity of the data.”

PostgeSQL supports data integrity by implementing the MVCC model. MVCC (MultiVersion Concurrency Control) is one of the mechanisms to provide concurrent access to the database, which is to provide each user a so-called "snapshot" of the database. A special "feature" of this picture is that the changes in the database visible to other users until the transaction commits.

PostgreSQL guarantees the integrity even of the strictest transaction isolation level, using an innovative isolation level SSI (Serializable Snapshot Isolation Serializable snapshot isolation).

For greater understanding of the topic, you can read article on habré and article blog Alexander Zhuravlev about locks, their work and competitive access at all.

the

Emergency

Unfortunately, there are situations when implemented mechanisms to ensure the integrity of data still can not cope with the incoming queries without the occurrence of blocking. This rarely happens, but if there is a situation that some query has locked a table for a long time, it can lead to trouble.

For example, if you run a long process query to the table from 1000 records to which the second occurs 100 UPDATE queries, for 5-6 hours table size will increase to 1.8 million records, respectively, the physical table size also increases (since the database stores all versions of the rows until the long transaction completes its work.

Consider this situation in more detail.

the

Example with emerging lock

Suppose that in a certain database we have a table pgsqlblocks_testing and she has the rule rule_pgsqlblocks_testing. Emulate him for a “long” request to 10 minutes, for example, using the SQL editor of pgAdmin:

the

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

Pid 16728

Open another editor and run another query to delete a rule:

the

DROP RULE rule_pgsqlblocks_testing ON public.pgsqlblocks_testing;

Pid 16726

And here DROP RULE is blocked by the SELECT query. MVCC in this case could not dispense with an explicit lock table pgsqlblocks_testing.

the

Tools for working with locks

How do we see the existing lock? It is possible to write a query for that table locks pg_locks and pg_stat_activity view or use the built in pgAdmin tool.

the

server Status in pgAdmin

pgAdmin is a rather convenient and simple to work with PostgreSQL database. At the moment the latest versions are pgAdmin III and released only in late September pgAdmin IV.

pgAdmin III

Display information about active locks and the processes in pgAdmin III requires an extension adminpack in the database. After installing this extension we need you open the window via menu Tools — server Status.

In this window we see a table with the processes and the table with the existing locks in the database. Not to get lost among a large number of processes, we can customize the colors of the processes depending on their status: active, blocked, dormant, or "slow".

In the table, each blocking and blocked process is represented in separate rows, and there is no way to quickly determine who blocks whom. To solve this problem, we have to compare different rows with each other in the attempt to find rows with common column value relation and excellent values for column granted.

For cancellation or termination of a selected process in the window there are two buttons. After termination of any of the processes need to update the window, and again to compare a string to evaluate the result.

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

pgAdmin IV

After installing and running pgAdmin IV we can view existing locks in the same form as it was in the pgAdmin III.

But... that's all we can do here. In pgAdmin IV lost the toolbar for actions on processes, and we can no longer cancel or terminate the processes of this kind that makes pgAdmin IV inconvenient tool to operate the lock.

the

database Requests

In the network there are many different implementations of the queries to view the blocked and blocking queries to the database.

The first result in a search engine request “pg_locks monitoring” produces a link with a variant of the query:



Query 1:

the

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.the 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;

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



Looks quite difficult, but the result is pleasing to the eye. In General, the PostgreSQL community has created and supports a lot of resources which help and facilitate the search of information ordinary DBAs. For example, the same wiki wiki.postgresql.org

So you can see who is and who is blocking. There are options such queries, where you can display information about how long a process waits for its turn, and so on.

The second link (from the official, among other things, documentation) offers a very very simple query:

Request 2:

the

SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa

The meaning of all these variations of essentially the same request: show information about locks. The desired information we received, but the answer does not lie directly on the surface. Particularly if the database is a lot of queries. Sit down and deal with it, and why someone who blocks! To build a graph of resources locked in my head, not everyone can!
 

 

 

 
Besides, we have to destroy or stop the blocking process. And Yes, it have to manually, via another query, indicating the pid of the process 
 

 
the 
select pg_backend_pid(16728);
 
or
 

 
the 
select pg_terminate_backend(16728);
 
To check the result, then run Query 1 or SELECT * FROM pg_catalog.pg_stat_activity WHERE pid=16728;.
 

 
the 
and it works great with pgSqlBlocks!
 
I want to show you another tool and share why it's so convenient — pgSqlBlocks. Tool pgSqlBlocks written to himself and created precisely in order to facilitate the solution of problems with locks in PostgreSQL, which we use for more than a year.
 

 
Here is the window pgSqlBlocks in the case of our example with two processes (here, they are 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 the DB connection (connected, disconnected, updating information, connection error, there is a lock in the database).
 

 
The main part of the application takes a tree of processes that is currently selected in the database. The blocked processes have the icon of a closed lock and gray are descendants of blocking processes, whose icon is a red castle. Icon is the green dot. 
 

 
Such a representation of processes allows us to easily navigate them, to obtain information about blocking and waiting processes and their relation to each other. For greater clarity, to hide a normal (not blocked or blocking) processes.
 

 

 
Clearly see that the process with pid 29981 with a long SELECT query blocks the process with pid 28710.
 

 
If necessary, you can send a signal to the abolition or destruction of any process. For example, if you destroy the blocked process 28710, the information in the process tree update and we will see the result — the process 29981 with a long SELECT query no longer blocks. Quickly and conveniently.
 

 
Still, from the small and pleasant features of the app include:
 

 
— Keep a history of the locks to a  file  and load back into the app. A sort of snapshot of all locks at the moment of saving, which allows at any moment to view and analyze what was blocking in the database;
 
— Tray icon changes if at least one of the connected database appeared lock;
 
Notifications in the tray when the locks;
 
— Customizable auto-refresh the list of processes.
 

 
How to install pgSqlBlocks and how it is convenient for comparison with the above options?
 

 
the 
Installation and configuration
 
The system must be preinstalled JRE 8.
 

 
Go to pgcodekeeper.ru/pgsqlblocks and select the latest version of the program. In the folder will be 4 jar. Choose the one that fits the OS and bitness of Your system. Download, run and voila!
 

 
It's everything you need to run the application. Everything works out of the box.
 

 
To start working with the application should fill the list with databases. To add a new database click the database icon with the "+" icon above the list of your database and fill in the required details in the dialog that appears. The password best stored in the pgpass file.
 

 

 
Tested on 9.2-9.6 PostgreSQL.
 

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

the 
Conclusion
 
The problem of occurrence of blocking queries in database can be very serious and lead to significant slowing down of the database and the exhaustion of disk space. Therefore, it is important to have a convenient and fast tool for the detection of blocking and making (sometimes) operational action.
 

 
This tool is pgSqlBlocks is an application that allows you to easily navigate among the processes and to obtain information about blocking and waiting queries.
 

 
The advantages of it can be attributed to the clarity of the information provided and ease of performing common tasks — view information about the processes, look for problems among the list of processes, cancellation or termination of the process and evaluation result. Also, a good option is to preserve the history of locks in a file for later analysis of the situation. All this makes your work with locks in a PostgreSQL database quick and easy.
 

 
PS: the inspiration for the creation of this application was a utility MSSQL Blocks. But it is designed to work with database MSSQL. For PostgreSQL its analogues was not. 
 

 
→ Download the latest version for your OS, you can here.

Article based on information from habrahabr.ru