1. Background
Under normal circumstances, PostgreSQL can be used happily as long as it is connected. However, in some special scenarios, such as pressure measurement or some undescribable exceptions, database connection abnormalities will occur, such as the number of links is full and cannot be released. At this time, we will guess whether the database is deadlocked? Or has the link number reached its maximum?
2. Common SQL statements
2.1. Query the maximum number of connections set by PostgreSQL
-- Query the maximum number of connections SHOW max_connections;
2.2. Query the number of connections currently in use
-- Query the current number of connections SELECT COUNT(*) FROM pg_stat_activity;
In view of the above situation, I have listed several commonly used SQL statements to troubleshoot "deadlock" problem.
2.3. Query the current number of connections to all database users and users
-- Query the current number of connections to all database users and users（Group statistics by username） SELECT usename, COUNT(*) FROM pg_stat_activity GROUP BY usename ORDER BY count DESC; -- Query the current number of connections for a specific database user(your_db_user_nameReplace with your own database name) SELECT COUNT(*) FROM pg_stat_activity WHERE usename = 'your_db_user_name';
2.4. Query whether there is a lock table record
-- Shows a lock not granted，That is, the waiting lock SELECT * FROM pg_locks WHERE granted = false; -- The equivalent is as followsSQL SELECT * FROM pg_locks WHERE granted = 'f';
Note: In PostgreSQL, values of Boolean types can be represented by 'true', 'false', 't', or 'f'.
- 'true' or 't' means true;
- 'false' or 'f' means false.
So when you see 't' in the query result, it is equivalent to 'true', both of which means that the process has been granted a lock. Similarly, 'f' and 'false' are equivalent, both indicating that the process is still waiting for the lock.
2.5. Query detailed lock information
-- Query more detailed lock information，What statements are included waiting for lock
SELECT ,
::regclass,
,
,
,
,
,
a.query_start,
age(now(), a.query_start) AS "age",
FROM pg_stat_activity a
JOIN pg_locks l ON =
WHERE NOT ;Each line represents a waiting lock. Most columns are self-explanatory, but here we need to mention the age column: when a transaction is waiting for a lock but does not progress, it will show how long the process has not responded.
2.6. How to unlock?
2.6.1. Position and terminate the matter of blocking the game
In general, the most direct way to unlock is to terminate the process or transaction that causes the table to be locked. First, you need to determine the transaction holding the lock, you can use the following statement:
-- Determine the transaction holding the lock SELECT * FROM pg_stat_activity WHERE waiting = 't';
2.6.1.1 What is the difference between pg_locks and pg_stat_activity?
The contents of these two view queries are actually different. The specific view to choose depends on the information you need to query.
SELECT * FROM pg_stat_activity;
The pg_stat_activity view contains information about all active processes connected to the database server.
This view is usually used to view running queries and other session information. This view is a very good starting point if you need to diagnose problems blocking a specified process. In this query, the waiting='t' situation refers to the process waiting to acquire a lock held by other processes.
SELECT * FROM pg_locks WHERE granted = false;
- The pg_locks view shows all active locks and processes waiting for locks. granted = false means that the lock has not been granted, that is, a process is waiting to acquire the lock, which may be because other processes hold the lock and fail to obtain it immediately.
- In general, if you need to view the query that generates the lock, it may be more useful to use pg_stat_activity. If you want to see which locks are waiting, pg_locks will be more useful. The use of the two views should be selected according to actual needs and scenarios.
2.6.2. After finding the pid, press the pid to terminate the process
-- according toPIDTerminate the process SELECT pg_terminate_backend(PID);
The PID needs to be replaced with the actual process ID.
If the transaction that causes the lock table cannot be directly terminated, it is necessary to find and fix the program logic error that causes the blocking of the game.
If it is a long-term obstacle, more in-depth measures such as database performance optimization or hardware upgrades may be considered.
Terminating a transaction may lose data or make the data state inconsistent. In most cases, it is best to find out why the table lock occurs and then fix the cause of the problem rather than simply termination of the transaction directly.
2.6.2.1 What is the difference between pg_cancel_backend and pg_terminate_backend?
The pg_cancel_backend and pg_terminate_backend functions are similar to the SIGINT and SIGTERM signals in UNIX.
pg_cancel_backend(PID): This function will send a request to cancel the current query in the backend. A PID is the PID of the process you want to cancel the query. This effect is similar to UNIX's SIGINT signal, and the query will be cancelled as safely as possible, such as rolling back to the start state at a safe point.
pg_terminate_backend(PID): This function interrupts a connection and any activity on that connection. The PID is the PID of the backend process you want to terminate. This function sends a terminate (BSIGTERM) signal to the specified backend, causing the backend process to exit immediately.
To sum up, pg_cancel_backend attempts to cancel the query executed by the backend but keeps the connection, while pg_terminate_backend directly disconnects the specified connection.
The above is the detailed content of the commonly used SQL statements for PostgreSQL troubleshooting connection lock problems. For more information about PostgreSQL troubleshooting connection lock statements, please pay attention to my other related articles!