<commit_before>---
layout: post
title:  "Find transaction that are locked (and who to blame for!)"
author: <NAME>
tags:
- PostgreSQL
permalink: /:year/:month/:day/:title.html
---
Have you ever wonder about what transaction (and user) is blocking another transaction? In other words, who to blame for keeping the database locked in any part? Here's a short and dirty way to dig a little into the problem.

# Find transaction that are locked (and who to blame for!)

PostgreSQL is great at looking in the sense it does its best effort to not lock at all! Nevertheless, in order to keep things coherent, even PostgreSQL requires locks to be acquired at diffent times and with different levels.

PostgreSQL provides a rich set of *statistical views* to introspect a running database, including single transaction activity and locks. Therefore, combining these information togethere there's a chance to find out **if and who is blocking due to not performing a lock release**.

The idea proposed in this article is quite simple and represents a **poor man way to find out locks information**. The example presented here is a live example I produced while preparing some teaching material.

The path to discover what and who is blocking is done thru three main steps:
1. **find out if there are blocked transactions**, this is done querying the activity statistic view having a glance at its state and/or the time it is running since (long running transactions could be possibly waiting for a lock to acquire);
2. **find out what blocked transaction are waiting for** and on the other hand, **what lock is not acquired**, and this is extracted by the lock statistical view, so that it is possible to see the lock not acquired and, by mean, the transaction that have acquired it;
3. **decide what to do** this is not an easy task. When the blocking transaction has been found, is it worth terminating or should it be kept running for another period of time in the case it volountary releases the lock? The choice strongly depends on the execution context of the database and transaction itself.
<commit_msg>Fixed a typo.<commit_after>---
layout: post
title:  "Find transactions that are locked (and who to blame for!)"
author: <NAME>
tags:
- PostgreSQL
permalink: /:year/:month/:day/:title.html
---
Have you ever wonder about what transaction (and user) is blocking another transaction? In other words, who to blame for keeping the database locked in any part? Here's a short and dirty way to dig a little into the problem.

# Find transactions that are locked (and who to blame for!)

PostgreSQL is great at looking in the sense it does its best effort to not lock at all! Nevertheless, in order to keep things coherent, even PostgreSQL requires locks to be acquired at diffent times and with different levels.

PostgreSQL provides a rich set of *statistical views* to introspect a running database, including single transaction activity and locks. Therefore, combining these information togethere there's a chance to find out **if and who is blocking due to not performing a lock release**.

The idea proposed in this article is quite simple and represents a **poor man way to find out locks information**. The example presented here is a live example I produced while preparing some teaching material.

The path to discover what and who is blocking is done thru three main steps:
1. **find out if there are blocked transactions**, this is done querying the activity statistic view having a glance at its state and/or the time it is running since (long running transactions could be possibly waiting for a lock to acquire);
2. **find out what blocked transaction are waiting for** and on the other hand, **what lock is not acquired**, and this is extracted by the lock statistical view, so that it is possible to see the lock not acquired and, by mean, the transaction that have acquired it;
3. **decide what to do** this is not an easy task. When the blocking transaction has been found, is it worth terminating or should it be kept running for another period of time in the case it volountary releases the lock? The choice strongly depends on the execution context of the database and transaction itself.

{'fraction_non_alphanumeric': 0.04688243693479296, 'fraction_numerical': 0.0014278914802475012, 'mean_word_length': 4.530263157894737, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4727987', 'n_tokens_mistral': 956, 'n_tokens_neox': 947, 'n_words': 692}