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

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

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

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

Hint

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

Practice

1. What is a deadlock in a database system?

easy

A. A backup process that locks the entire database

B. A syntax error in SQL queries causing failure

C. A method to speed up query execution

D. A situation where two or more transactions wait indefinitely for each other to release locks

Solution

1. Step 1: Understand transaction locking

   Transactions lock resources to maintain data integrity during operations.

2. Step 2: Identify deadlock condition

   Deadlock occurs when transactions wait on each other's locked resources indefinitely, causing a standstill.

3. Final Answer:

   A situation where two or more transactions wait indefinitely for each other to release locks -> Option D

4. Quick Check:

   Deadlock = Transactions waiting forever [OK]

Hint: Deadlock means waiting forever for each other's locked resource [OK]

Common Mistakes:

• Confusing deadlock with syntax errors
• Thinking deadlock speeds up queries
• Assuming deadlock is a backup process

2. Which SQL statement is best to prevent deadlocks by controlling transaction order?

easy

A. Run transactions without COMMIT statements

B. Use SELECT * without WHERE clause

C. Always access tables in the same order in all transactions

D. Use DROP TABLE before INSERT

Solution

1. Step 1: Understand deadlock prevention strategy

   Accessing resources in a consistent order prevents circular waiting among transactions.

2. Step 2: Evaluate options

   Only accessing tables in the same order avoids deadlocks; other options cause errors or do not help.

3. Final Answer:

   Always access tables in the same order in all transactions -> Option C

4. Quick Check:

   Consistent access order = Deadlock prevention [OK]

Hint: Access tables in same order to avoid deadlocks [OK]

Common Mistakes:

• Ignoring transaction commit timing
• Using SELECT * without filters causes performance issues, not deadlocks
• Dropping tables is unrelated to deadlock prevention

3. Consider two transactions:
Transaction 1: LOCK table A; LOCK table B;
Transaction 2: LOCK table B; LOCK table A;
What is the likely outcome?

medium

A. Deadlock occurs because each waits for the other's locked table

B. Transaction 2 commits immediately

C. Transaction 1 fails due to syntax error

D. Both transactions complete successfully without waiting

Solution

1. Step 1: Analyze lock order in transactions

   Transaction 1 locks table A then B; Transaction 2 locks table B then A, creating circular wait.

2. Step 2: Identify deadlock condition

   Each transaction waits for the other's locked resource, causing deadlock.

3. Final Answer:

   Deadlock occurs because each waits for the other's locked table -> Option A

4. Quick Check:

   Opposite lock order = Deadlock [OK]

Hint: Opposite lock order in transactions causes deadlock [OK]

Common Mistakes:

• Assuming transactions run without waiting
• Confusing deadlock with syntax errors
• Thinking one transaction commits immediately

4. You have a deadlock error in your SQL transactions. Which fix will most likely resolve it?

medium

A. Remove all COMMIT statements

B. Change transactions to lock tables in the same order

C. Use SELECT without WHERE clause to avoid locks

D. Increase transaction isolation level to SERIALIZABLE

Solution

1. Step 1: Identify cause of deadlock

   Deadlocks often happen due to inconsistent lock order in transactions.

2. Step 2: Apply fix by ordering locks

   Changing transactions to lock tables in the same order prevents circular waiting and deadlocks.

3. Final Answer:

   Change transactions to lock tables in the same order -> Option B

4. Quick Check:

   Consistent lock order fixes deadlocks [OK]

Hint: Fix deadlocks by ordering locks consistently [OK]

Common Mistakes:

• Removing COMMIT causes longer locks, worsening deadlocks
• SELECT without WHERE does not prevent locks
• Higher isolation can increase deadlocks

5. You want to prevent deadlocks in a system where multiple transactions update two tables, Orders and Customers. Which approach is best?

hard

A. Ensure all transactions update Customers first, then Orders, and keep transactions short

B. Allow transactions to update tables in any order but increase isolation level to SERIALIZABLE

C. Use long transactions with many updates to reduce lock frequency

D. Disable locking to speed up updates

Solution

1. Step 1: Understand deadlock prevention principles

   Accessing resources in the same order and keeping transactions short reduces deadlocks.

2. Step 2: Evaluate options for best practice

   Ensure all transactions update Customers first, then Orders, and keep transactions short enforces consistent order and short transactions, preventing deadlocks effectively.

3. Step 3: Analyze other options

   Allow transactions to update tables in any order but increase isolation level to SERIALIZABLE may increase deadlocks; Use long transactions with many updates to reduce lock frequency lengthens locks; Disable locking to speed up updates is unsafe and invalid.

4. Final Answer:

   Ensure all transactions update Customers first, then Orders, and keep transactions short -> Option A

5. Quick Check:

   Consistent order + short transactions = Deadlock prevention [OK]

Hint: Use same update order and short transactions to prevent deadlocks [OK]

Common Mistakes:

• Increasing isolation level can cause more deadlocks
• Long transactions hold locks longer, increasing deadlock risk
• Disabling locking breaks data integrity