CodeBlanks Logo Icon CodeBlanks
Login Register

Deadlock

Learn the mutual-wait gridlock.

Deadlock

A deadlock is two operations waiting for each other's locks. An unsolvable wait.

-- İşlem 1: A'yı kilitle, B'yi bekle
-- İşlem 2: B'yi kilitle, A'yı bekle → deadlock

Mutual waiting.

Deadlock — details

The database detects a deadlock and sacrifices one operation. The other continues.

The most effective way to reduce deadlocks is to lock tables and rows in the same order in every transaction and keep transactions short.

Practice Deadlock

Write a query that locks resources in the same order to prevent deadlocks.

Starter code

query.sql
-- prevent deadlock
SELECT * FROM accounts WHERE id = 1

Sample solution

query.sql
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

Expected output

id  balance
1   500

Questions and answers

Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.

1 Deadlock: match the terms

Term Meaning
deadlock deadlock
mutual waiting
sacrifice one op
detect automatic

2 Deadlock

Question 
____ → mutual waiting
Answer 
Deadlock → mutual waiting

Explanation: Deadlock is the right answer.

3 What is a deadlock?

  • Two transactions waiting on each other Correct answer
  • An automatic commit
  • A fast lock
  • An index problem

Explanation: A deadlock is two operations waiting on each other.

All lessons in Advanced

1 CTE

  1. What is a CTE?
  2. Using WITH
  3. Multiple CTEs
  4. CTE vs subquery
  5. CTE with JOIN
  6. Summary - CTE

2 Recursive CTE

  1. What is a recursive CTE?
  2. Anchor and recursive parts
  3. Walk a hierarchy
  4. Tracking depth
  5. Stopping recursion
  6. Summary - Recursive CTE

3 Window functions

  1. What is a window function?
  2. ROW_NUMBER
  3. RANK and DENSE_RANK
  4. LAG and LEAD
  5. PARTITION BY
  6. Summary - Window functions

4 Project: Analytics report

  1. Analytics design
  2. CTE summary
  3. Top users with ROW_NUMBER
  4. Growth with LAG
  5. Final report

5 Pivot and unpivot

  1. What is pivot?
  2. Pivot with CASE
  3. FILTER clause
  4. Unpivot
  5. Dynamic pivot
  6. Summary - Pivot

6 Materialized view

  1. What is a materialized view?
  2. CREATE
  3. REFRESH
  4. View vs materialized view
  5. Index on materialized views
  6. Summary - Materialized view

7 Stored procedure

  1. What is a procedure?
  2. CREATE PROCEDURE
  3. Parameters
  4. CALL
  5. Control flow
  6. Summary - Stored procedure

8 Function

  1. What is a function?
  2. CREATE FUNCTION
  3. RETURN
  4. Calling a function
  5. Function vs procedure
  6. Summary - Function

9 Trigger

  1. What is a trigger?
  2. BEFORE and AFTER
  3. INSERT trigger
  4. UPDATE trigger
  5. Write an audit trigger
  6. Summary - Trigger

10 Transaction isolation

  1. What is isolation?
  2. READ UNCOMMITTED
  3. READ COMMITTED
  4. REPEATABLE READ
  5. SERIALIZABLE
  6. Summary - Isolation

11 Locking

  1. What is a lock?
  2. Shared and exclusive locks
  3. Deadlock
  4. SELECT FOR UPDATE
  5. Lock timeout
  6. Summary - Locking

12 Query optimization

  1. What is EXPLAIN?
  2. Seq scan vs index scan
  3. Cost
  4. Sargable
  5. N+1 problem
  6. Summary - Optimization

13 Partitioning

  1. What is partitioning?
  2. Range partition
  3. List partition
  4. Hash partition
  5. Partition pruning
  6. Summary - Partitioning

14 Advanced final

  1. Hierarchy report
  2. Window analysis
  3. Procedure and trigger
  4. Optimization