Managing Deadlock In Databases
Tags: database, beginners, productivity
Published: 2024-02-11 15:58:31

It is common knowledge that databases are meant for storing data and it is the Database Management System (DBMS) that is in charge of controlling how data is stored, retrieved, and edited. You have probably written a program that accessed data from a database. What are the chances there wouldn't be any conflicts when trying to access this data? 

## What is deadlock?
Deadlock is a common concept when dealing with operating systems but not necessarily limited to operating systems. Imagine you are hungry and go to the kitchen to get food. Turns out your sibling is already by the pot and wants to serve themselves. But there is a problem, you are holding the only clean plate available but wouldn't be able to get food unless your sibling is done. Your sibling needs a plate in order to serve themselves food but you wouldn't give up your plate. Neither will your sibling leave the pot unless they get a plate. You need the pot to get food but your sibling is with the pot. Your sibling needs a plate to get food but you are with the plate. This is a "deadlock" kind of scenario. Deadlock is a situation where a computer process waits for a resource which is being assigned or used by another process. 

In databases, a deadlock occurs when multiple transactions are waiting for the release of resources which leads a situation where neither can proceed. Deadlocks are not very frequent in databases but aren't inevitable. In the context of a database, a **lock** is a mechanism used to control access to a resource, such as a data row, table, or page. Locks are employed to ensure the consistency and integrity of the database by preventing multiple transactions from simultaneously modifying the same resource. When multiple transactions are involved, and they are waiting for each other to release locks, it can lead to a deadlock.

Here's a simple example to illustrate a deadlock scenario:
- Transaction A holds a lock on Resource X and is waiting for Resource Y.
- Transaction B holds a lock on Resource Y and is waiting for Resource X.


## How to manage deadlocks in databases

- **Deadlock Prevention:** Like they say _"Prevention is better than cure"_. In order for this to happen, there should be an order in which transactions are being carried out. One transaction should wait for the other to release the lock before it can begin. There should be a hierarchy of acquiring locks ensuring these locks are requested in a consistent order. In addition, there should be a time limit for all transactions to acquire locks.This is ideal for small databases

- **Deadlock Detection:** Here, resources are allocated in such a  way that the deadlock never happens. The DBMS makes sure to assess the operations making sure there's no room for any deadlock else the transaction won't be executed. It involves periodically checking for deadlocks and this is perfect for very large databases.

- **Deadlock Resolution:** Once a deadlock is identified, there are 2 things that should be done - **abort & rollback** and **wait-die & wound-wait**. With _abort & rollback_, one or more transactions that are involved in a deadlock are aborted in order to release their locks whereas in _wait-die & wound-wait_, transactions are prioritized based on their timestamps. Younger transactions requesting locks held by older transactions are aborted meanwhile older transactions with locks held by younger transactions are forced to wait.