---
id: 6fc5e744-e4a7-4552-bcc8-8abdf588393a
---

# Postgresql too many locks acquired
---

This incident type occurs when there are too many locks acquired on a Postgresql database instance. This can cause issues with database performance and functionality. It may be necessary to adjust the max_locks_per_transaction setting in Postgresql to prevent this issue from occurring.

### Parameters
```shell
# Environment Variables

export POSTGRESQL_LOG="PLACEHOLDER"

export NEW_MAX_LOCKS="PLACEHOLDER"

export DATABASE_HOST="PLACEHOLDER"

export DATABASE_PASSWORD="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export DATABASE_USER="PLACEHOLDER"

```

## Debug

### Check the status of the Postgresql service
```shell
systemctl status postgresql
```

### Check the status of the Postgresql server
```shell
ps aux | grep postgres
```

### Check the number of locks acquired per transaction in the Postgresql database
```shell
sudo su postgres -c "psql -c 'SELECT * FROM pg_locks;'"
```

### Check the value of the max_locks_per_transaction setting in the Postgresql configuration file
```shell
sudo cat   /etc/postgresql/main/postgresql.conf | grep max_locks_per_transaction
```

### Check the Postgresql logs for any relevant error messages
```shell
sudo tail -n 100 ${POSTGRESQL_LOG}
```

### A database query is acquiring a large number of locks, causing the lock limit to be exceeded.
```shell
bash

#!/bin/bash



# This script diagnoses the scenario where a database query is acquiring a large number of locks, causing the lock limit to be exceeded.



# Set the database credentials

DB_HOST=${DATABASE_HOST}

DB_NAME=${DATABASE_NAME}

DB_USER=${DATABASE_USER}

DB_PASSWORD=${DATABASE_PASSWORD}



# Connect to the database and run a query to check the number of locks

locks=$(psql -h $DB_HOST -d $DB_NAME -U $DB_USER -c "SELECT COUNT(*) FROM pg_locks")



# Check if the number of locks is above a threshold

if [ $locks -gt 1000 ]

then

  echo "Number of locks is above threshold: $locks"

  echo "Please investigate the queries running on the database to identify the cause of the excessive locks."

else

  echo "Number of locks is within acceptable range: $locks"

fi


```

## Repair

### Set the new value for max_locks_per_transaction
```shell
NEW_MAX_LOCKS=${NEW_MAX_LOCKS}
```

### Update the postgresql.conf file with the new value
```shell
sudo sed -i "s/^max_locks_per_transaction = [0-9]\+$/max_locks_per_transaction = $NEW_MAX_LOCKS/" /etc/postgresql/main/postgresql.conf
```

### Restart the Postgresql service
```shell
sudo systemctl restart postgresql
```


This incident type occurs when there are too many locks acquired on a Postgresql database instance. This can cause issues with database performance and functionality. It may be necessary to adjust the max\_locks\_per\_transaction setting in Postgresql to prevent this issue from occurring.


This incident type refers to a situation where the Tomcat server's JDBC connection pool usage surpasses its configured limits. This can lead to a shortage of available connections for incoming requests, resulting in slow response times or even server crashes. It is important to monitor and manage the JDBC connection pool usage to prevent such incidents from occurring.


Tomcat High JDBC Connection Pool Usage Incident.

This incident type involves a high number of processes being blocked in SQLServer. It may cause delays or errors in database operations and can impact the performance of the system.


High number of processes blocked in SQLServer

This incident type refers to a situation where the Postgresql database has a high rollback rate, which means that a high percentage of transactions are being aborted compared to the committed ones. This can cause issues with data consistency and performance, and may require investigation and resolution by the responsible team. The incident details may include information about the affected database instance, the service or system impacted, the urgency level, and any related alerts or escalations.


Postgresql high rollback rate incident

This incident type refers to a scenario where there is a high rate of statement timeouts in a Postgresql database instance. This can lead to degraded performance and potentially impact the availability of the database. It is important to quickly identify and address the underlying cause of the timeouts to ensure the stability of the system.


Postgresql high rate statement timeout incident.

This incident type occurs when there is a failure in the authentication process for the Postgres user's password. This can happen due to a variety of reasons such as incorrect login credentials, expired passwords, or database configuration issues. It is important to address this type of incident promptly to ensure that the database remains secure and accessible only to authorized users.


Password Authentication Failure for Postgres User

```shell
# Environment Variables

export POSTGRESQL_LOG="PLACEHOLDER"

export NEW_MAX_LOCKS="PLACEHOLDER"

export DATABASE_HOST="PLACEHOLDER"

export DATABASE_PASSWORD="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export DATABASE_USER="PLACEHOLDER"

```


### Check the status of the Postgresql service

```shell
systemctl status postgresql
```

### Check the status of the Postgresql server

```shell
ps aux | grep postgres
```

### Check the number of locks acquired per transaction in the Postgresql database

```shell
sudo su postgres -c "psql -c 'SELECT * FROM pg_locks;'"
```

### Check the value of the max\_locks\_per\_transaction setting in the Postgresql configuration file

```shell
sudo cat   /etc/postgresql/main/postgresql.conf | grep max_locks_per_transaction
```

### Check the Postgresql logs for any relevant error messages

```shell
sudo tail -n 100 ${POSTGRESQL_LOG}
```

### A database query is acquiring a large number of locks, causing the lock limit to be exceeded.

```shell
bash

#!/bin/bash



# This script diagnoses the scenario where a database query is acquiring a large number of locks, causing the lock limit to be exceeded.



# Set the database credentials

DB_HOST=${DATABASE_HOST}

DB_NAME=${DATABASE_NAME}

DB_USER=${DATABASE_USER}

DB_PASSWORD=${DATABASE_PASSWORD}



# Connect to the database and run a query to check the number of locks

locks=$(psql -h $DB_HOST -d $DB_NAME -U $DB_USER -c "SELECT COUNT(*) FROM pg_locks")



# Check if the number of locks is above a threshold

if [ $locks -gt 1000 ]

then

  echo "Number of locks is above threshold: $locks"

  echo "Please investigate the queries running on the database to identify the cause of the excessive locks."

else

  echo "Number of locks is within acceptable range: $locks"

fi


```


### Set the new value for max\_locks\_per\_transaction

```shell
NEW_MAX_LOCKS=${NEW_MAX_LOCKS}
```

### Update the postgresql.conf file with the new value

```shell
sudo sed -i "s/^max_locks_per_transaction = [0-9]\+$/max_locks_per_transaction = $NEW_MAX_LOCKS/" /etc/postgresql/main/postgresql.conf
```

### Restart the Postgresql service

```shell
sudo systemctl restart postgresql
```


Postgresql too many locks acquired

Overview

Parameters

Debug

Check the status of the Postgresql service

Check the status of the Postgresql server

Check the number of locks acquired per transaction in the Postgresql database

Check the value of the max_locks_per_transaction setting in the Postgresql configuration file

Check the Postgresql logs for any relevant error messages

A database query is acquiring a large number of locks, causing the lock limit to be exceeded.

Repair

Set the new value for max_locks_per_transaction

Update the postgresql.conf file with the new value

Restart the Postgresql service

Learn more

Related Runbooks

Tomcat High JDBC Connection Pool Usage Incident.

High number of processes blocked in SQLServer

Postgresql high rollback rate incident

Postgresql high rate statement timeout incident.

Support