Deadlock concept and prevention in SQL - Mini Project: Build & Apply

Understanding Deadlock Concept and Prevention in SQL

📖 Scenario: You are managing a small bank database where two transactions try to update account balances at the same time. This can cause a deadlock, where each transaction waits for the other to finish, and both get stuck.We will create a simple table and simulate how deadlocks can happen and how to prevent them by controlling the order of operations.

🎯 Goal: Build a simple SQL setup to understand deadlocks and learn how to prevent them by ordering updates consistently.

📋 What You'll Learn

Create a table called accounts with columns account_id (integer) and balance (integer).

Insert two rows into accounts with account_id 1 and 2, and balances 1000 and 1500 respectively.

Write two SQL transactions that update balances of accounts 1 and 2 in different orders to simulate a deadlock.

Modify the transactions to update accounts in the same order to prevent deadlock.

💡 Why This Matters

🌍 Real World

Deadlocks can happen in any system where multiple users or processes try to change data at the same time, like banking, booking systems, or inventory management.

💼 Career

Understanding deadlocks and how to prevent them is important for database administrators and developers to keep applications running smoothly without freezing or errors.

1

Create the accounts table and insert initial data

Create a table called accounts with columns account_id as INTEGER and balance as INTEGER. Then insert two rows: one with account_id 1 and balance 1000, and another with account_id 2 and balance 1500.

SQL

-- Create the accounts table and insert two rows
-- Your code here

Need a hint?

Use CREATE TABLE to make the table and INSERT INTO to add rows.

2

Write two transactions updating accounts in different orders

Write two SQL transactions named Transaction1 and Transaction2. Transaction1 should update the balance of account_id 1 by subtracting 100, then update account_id 2 by adding 100. Transaction2 should update account_id 2 by subtracting 200, then update account_id 1 by adding 200. Use BEGIN TRANSACTION and COMMIT for each transaction.

SQL

CREATE TABLE accounts (
    account_id INTEGER PRIMARY KEY,
    balance INTEGER
);

INSERT INTO accounts (account_id, balance) VALUES (1, 1000);
INSERT INTO accounts (account_id, balance) VALUES (2, 1500);

-- Write Transaction1 and Transaction2 below
-- Your code here

Need a hint?

Use BEGIN TRANSACTION and COMMIT to group updates. Notice the different order of updates in each transaction.

3

Explain how deadlock can happen with these transactions

Add a comment in SQL explaining that these two transactions can cause a deadlock because each transaction locks one account and waits for the other account locked by the other transaction.

SQL

CREATE TABLE accounts (
    account_id INTEGER PRIMARY KEY,
    balance INTEGER
);

INSERT INTO accounts (account_id, balance) VALUES (1, 1000);
INSERT INTO accounts (account_id, balance) VALUES (2, 1500);

BEGIN TRANSACTION Transaction1;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

BEGIN TRANSACTION Transaction2;
UPDATE accounts SET balance = balance - 200 WHERE account_id = 2;
UPDATE accounts SET balance = balance + 200 WHERE account_id = 1;
COMMIT;

-- Add a comment explaining deadlock below
-- Your code here

Need a hint?

Write a comment starting with -- explaining the deadlock situation in simple words.

4

Prevent deadlock by ordering updates consistently

Modify both Transaction1 and Transaction2 so that they update account_id 1 first, then account_id 2. This consistent order prevents deadlocks. Use BEGIN TRANSACTION and COMMIT for each transaction.

SQL

CREATE TABLE accounts (
    account_id INTEGER PRIMARY KEY,
    balance INTEGER
);

INSERT INTO accounts (account_id, balance) VALUES (1, 1000);
INSERT INTO accounts (account_id, balance) VALUES (2, 1500);

-- Modify Transaction1 and Transaction2 below to update account 1 first, then account 2
-- Your code here

Need a hint?

Change Transaction2 to update account_id 1 first, then account_id 2, matching Transaction1.