One of the most valuable capabilities of Postgres is its support for concurrent ACID transactions. ACID is an acronym for Atomicity, Consistency, Isolation, and Durability. These database transaction properties help verify data validity. It is important to understand that locking can be a common occurrence in active Postgres databases. This is because Postgres uses these levels of locking as one of the ways to safely implement these (concurrent ACID) transactions. Managing peak performance includes being aware of queries that might be taking too long to run. In some cases, these commands might be waiting for a lock. Foglight can help you identify and resolve these situations.
Foglight for Postgres provides several out-of-the-box visual alarms (pre-configured and configurable). One of them is called ‘Potential Deadlock Issue’. This alarm fires if there are any ungranted locks (Locks Waiting) older than the deadlock timeout setting. Foglight is pulling this information from the Postgres view ‘pg_locks’.
(*As of Foglight v. 5.9.3.10), the ‘Potential Deadlock Issue’ alarm can be found/configured/managed here:
Navigate to the instance(s) you’d like to manage by checking the box(es) to the left of the instance name(s). With appropriate Foglight privileges, the ‘Configure Alarms’ button will be enabled. Select it.
(Click on the images to enlarge)
Select the alarm from the list on the left side.
Select the blue link to enable/disable.
Select the ‘Enhance Alarm’ button to view/modify the severity level and associated message.
Back on the Foglight database home screen, click on the instance name to access the Overview page.
From the Overview page, navigate to lock details be either selecting the ‘Locks Waiting’ spinner or the Locks page option at the top.
If there are Locks Waiting, it will look something like this…
The data displayed includes the Process ID (PID), the User, the Query that performed it, the Query Start Time, and if the lock has been granted or not (true/false). If it is deadlocked, one (or more) have not been granted.
To resolve the issue, note the Process ID (PID) and select the Connections page option.
Find the corresponding PID and on the far right of the Connections page, select the Admin icon.
**Note: This requires that the user is configured with the Postgres Administrator privilege.
From here, you can highlight the query and Cancel it or Terminate the connection.
For more information, visit Foglight database monitoring to download a free trial.
