The Dev World – Sergio Lema

Have you ever faced a database deadlocks in Postgres? In this article, I will explain why and when deadlocks occur, how to detect them, and how to solve them.

For that, I will tell you a story about the Deadlock, A Journey Through PostgreSQL’s Conundrums.

Unlocking the Mystery of Deadlocks: A Journey Through PostgreSQL’s Conundrums

Once upon a time, in the vast kingdom of Datastoria, there was a bustling city named Postgresville. In Postgresville, data flowed like a river, and databases thrived in harmony. However, beneath the surface, a silent battle raged, the battle of deadlocks.

Our story begins with two valiant knights of Postgresville: Sir Application and Lady Database. Sir Application, a noble and ambitious coder, was tasked with managing the kingdom’s treasure, while Lady Database, the guardian of data integrity, protected the vault where the treasure was stored.

Chapter 1: The Quest for Gold

One sunny morning, Sir Application rode into the kingdom with a mission to retrieve a sack of gold coins from the vault. He approached the entrance but found it locked. Lady Database was already inside, updating the treasure records.

Seeing the opportunity, Sir Application knocked on the door and requested to enter. Lady Database, not wanting to let the knight wait, opened the door, and both entered the vault. Sir Application wanted to deduct the gold from the records, while Lady Database wanted to update the record to show the gold’s new location.

Chapter 2: The Standoff

Here, our story takes a twist. Both Sir Application and Lady Database reached for their quills and inkpots simultaneously, intending to make the necessary changes. Unbeknownst to them, they had inadvertently created a deadlock.

Sir Application had locked a row in the “treasure” table for the purpose of updating it, while Lady Database had locked a different row to update the same table. Each was waiting for the other to finish, but neither would yield.

This deadlock left them both stuck, unable to complete their tasks and exit the vault.

Chapter 3: The Deadlock Resolution

As the minutes passed, the kingdom’s treasurer, Master Admin, grew concerned. He had heard of such deadlocks before and knew they could paralyze the kingdom’s operations. Master Admin decided to intervene and resolve the deadlock.

In PostgreSQL, deadlock detection works like a diligent scribe. The database constantly monitors transactions, and when it detects a circular dependency of locks, it intervenes. The database selected one of the knights (Sir Application in this case) as the “victim” to be rolled back, allowing the other knight (Lady Database) to proceed.

Chapter 4: Preventing Future Deadlocks

After this ordeal, Sir Application and Lady Database realized the importance of preventing such deadlocks. They agreed to follow some strategies to minimize the risk:

1. Lock Ordering: They decided to always acquire locks in a predefined order, ensuring that they both followed the same sequence.
2. Reducing Lock Time: They committed to keeping their transactions as short as possible to minimize the chances of locking resources for extended periods.
3. Use of Advisory Locks: In situations where they needed synchronization without actual database locks, they would employ advisory locks, which PostgreSQL offers for this purpose.
4. Database Design: They acknowledged the significance of a well-designed database schema and indexes, as a poor design could increase the likelihood of deadlocks.

Chapter 5: The Ongoing Battle

The battle against deadlocks in Postgresville never truly ends. Sir Application and Lady Database continued to monitor their interactions, fine-tuning their strategies to minimize deadlocks and maintain the kingdom’s prosperity.

In the kingdom of Datastoria, where data was the lifeblood of the land, they understood that deadlocks were but a minor inconvenience on the path to greater knowledge and prosperity. With their newfound wisdom, they ensured that data in Postgresville would always flow freely and securely.

And so, dear readers, the tale of Postgresville’s deadlocks comes to an end. Remember, in the world of databases, deadlocks are challenges that can be overcome with the right knowledge and strategies, ensuring the kingdom’s data remains safe and accessible for generations to come.

Conclusion

Sometimes, the Deadlocks are not detected automatically. That’s why a request is available to see the blocked and the blocking request:

  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 current_statement_in_blocking_process
   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;

You can find more information about the previous request in the following link.