---
id: 8b46afd4-fc98-49cc-bd4e-a3595773c90c
---

# Postgresql Deadlocks Incident
---

This incident type refers to the occurrence of deadlocks in a PostgreSQL database. A deadlock happens when two or more transactions are waiting for each other to release locks on resources, resulting in a situation where none of the transactions can proceed. This can cause significant disruption to the database and the applications that rely on it, as it prevents any further progress until the deadlock is resolved.

### Parameters
```shell
# Environment Variables

export HOSTNAME="PLACEHOLDER"

export USERNAME="PLACEHOLDER"

export DATABASE_PASSWORD="PLACEHOLDER"

export NEW_MAX_CONNECTIONS="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export DATABASE_USER="PLACEHOLDER"
```

## Debug

### Connect to the Postgresql instance
```shell
sudo -u postgres psql -h ${HOSTNAME} -d ${DATABASE_NAME} -U ${USERNAME}
```

### Show details of current locks
```shell
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 blocking_statement 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;
```

### Show queries currently running on the instance
```shell
SELECT pid,query,state FROM pg_stat_activit WHERE state = 'active';

```

### Show details of current transactions
```shell
SELECT * FROM pg_stat_activity WHERE backend_xid IN (SELECT transactionid FROM pg_locks WHERE locktype = 'transactionid');
```

### Dump the Postgresql logs
```shell
tail -n 100 /var/log/postgresql/postgresql-main.log
```

## Repair

### Stop the Postgresql service
```shell
sudo systemctl stop postgresql
```

### Wait for the service to stop
```shell
while sudo systemctl is-active --quiet postgresql; do sleep 1; done
```

### Start the Postgresql service
```shell
sudo systemctl start postgresql
```

### Wait for the service to start
```shell
while ! sudo systemctl is-active --quiet postgresql; do sleep 1; done
```

### Next Step
```shell
echo "Database server restarted successfully."
```

### Increase the number of connections allowed to the database to reduce contention.
```shell
bash

#!/bin/bash

# Set variables

DATABASE_USER=${DATABASE_USER}

DATABASE_NAME=${DATABASE_NAME}

DATABASE_PASSWORD=${DATABASE_PASSWORD}

NEW_MAX_CONNECTIONS=${NEW_MAX_CONNECTIONS}

# Increase the max connections

psql -U $DATABASE_USER -d $DATABASE_NAME -c "ALTER SYSTEM SET max_connections = $NEW_MAX_CONNECTIONS;"

psql -U $DATABASE_USER -d $DATABASE_NAME -c "SELECT pg_reload_conf();"


```
### Identify the deadlocked queries and kill.
```shell
#Identify the deadlocked queries

SELECT pid,query,state,locktype,mode,granted FROM pg_locks JOIN pg_stat_activity ON pg_locks.pid=pg_stat_activity.pid WHERE  pg_stat_activity.wait_event_type = 'deadlock';

#Terminate the query with the specified 

SELECT pg_terminate_backend(<pid>);

```

This incident type refers to the occurrence of deadlocks in a PostgreSQL database. A deadlock happens when two or more transactions are waiting for each other to release locks on resources, resulting in a situation where none of the transactions can proceed. This can cause significant disruption to the database and the applications that rely on it, as it prevents any further progress until the deadlock is resolved.


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.

A SQL Server deadlock incident occurs when two or more processes are blocked and waiting on each other to release a resource. This results in a situation where none of the processes can continue executing, causing a system slowdown or complete system failure. The incident requires immediate attention from a software engineer to identify and resolve the issue.


SQL Server Deadlock Incident

This incident type refers to an issue with Redis replication, which means that there is a problem with the synchronization of data between Redis instances. This issue could impact the availability and performance of the system and may require immediate attention to restore the replication and ensure data consistency. The incident could be caused by various factors, such as network problems, hardware failures, or configuration issues. The incident must be investigated and resolved as soon as possible to avoid any data loss or downtime.


Redis replication broken incident.

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.


Postgresql too many locks acquired

This incident type refers to situations where a Postgresql table has not been vacuumed automatically, causing potential performance issues. Vacuuming is a process that frees up space and improves performance by removing dead rows and updating statistics. Failure to vacuum a table can result in degraded database performance and slow query execution times. This incident type requires immediate attention to prevent further performance degradation and optimize database performance.


Postgresql table not auto vacuumed incident

```shell
# Environment Variables

export HOSTNAME="PLACEHOLDER"

export USERNAME="PLACEHOLDER"

export DATABASE_PASSWORD="PLACEHOLDER"

export NEW_MAX_CONNECTIONS="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export DATABASE_USER="PLACEHOLDER"
```


### Connect to the Postgresql instance

```shell
sudo -u postgres psql -h ${HOSTNAME} -d ${DATABASE_NAME} -U ${USERNAME}
```

### Show details of current locks

```shell
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 blocking_statement 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;
```

### Show queries currently running on the instance

```shell
SELECT pid,query,state FROM pg_stat_activit WHERE state = 'active';

```

### Show details of current transactions

```shell
SELECT * FROM pg_stat_activity WHERE backend_xid IN (SELECT transactionid FROM pg_locks WHERE locktype = 'transactionid');
```

### Dump the Postgresql logs

```shell
tail -n 100 /var/log/postgresql/postgresql-main.log
```


### Stop the Postgresql service

```shell
sudo systemctl stop postgresql
```

### Wait for the service to stop

```shell
while sudo systemctl is-active --quiet postgresql; do sleep 1; done
```

### Start the Postgresql service

```shell
sudo systemctl start postgresql
```

### Wait for the service to start

```shell
while ! sudo systemctl is-active --quiet postgresql; do sleep 1; done
```

### Next Step

```shell
echo "Database server restarted successfully."
```

### Increase the number of connections allowed to the database to reduce contention.

```shell
bash

#!/bin/bash

# Set variables

DATABASE_USER=${DATABASE_USER}

DATABASE_NAME=${DATABASE_NAME}

DATABASE_PASSWORD=${DATABASE_PASSWORD}

NEW_MAX_CONNECTIONS=${NEW_MAX_CONNECTIONS}

# Increase the max connections

psql -U $DATABASE_USER -d $DATABASE_NAME -c "ALTER SYSTEM SET max_connections = $NEW_MAX_CONNECTIONS;"

psql -U $DATABASE_USER -d $DATABASE_NAME -c "SELECT pg_reload_conf();"


```

### Identify the deadlocked queries and kill.

```shell
#Identify the deadlocked queries

SELECT pid,query,state,locktype,mode,granted FROM pg_locks JOIN pg_stat_activity ON pg_locks.pid=pg_stat_activity.pid WHERE  pg_stat_activity.wait_event_type = 'deadlock';

#Terminate the query with the specified 

SELECT pg_terminate_backend(<pid>);

```


Postgresql Deadlocks Incident

Overview

Parameters

Debug

Connect to the Postgresql instance

Show details of current locks

Show queries currently running on the instance

Show details of current transactions

Dump the Postgresql logs

Repair

Stop the Postgresql service

Wait for the service to stop

Start the Postgresql service

Wait for the service to start

Next Step

Increase the number of connections allowed to the database to reduce contention.

Identify the deadlocked queries and kill.

Learn more

Related Runbooks

Tomcat High JDBC Connection Pool Usage Incident.

SQL Server Deadlock Incident

Redis replication broken incident.

Postgresql too many locks acquired

Support