SQL Lab — Deadlock Investigation & Resolution (Hard)

🖥️

SQL Terminal

📚

Reference

📌 OBJECTIVES — NO HINTS

○Read InnoDB deadlock log (SHOW ENGINE INNODB STATUS)

○Identify conflicting transactions and lock types

○Reproduce the deadlock with two concurrent sessions

○Identify the lock ordering problem

○Restructure transaction to use consistent lock order

○Add NOWAIT / SKIP LOCKED for queue processing

○Verify zero deadlocks over 100 test runs

0 / 7 complete

SQL TERMINAL — DEADLOCK INVESTIGATION & RESOLUTION (HARD)

MySQL 8.0 — Lab environment loaded Deadlock Investigation & Resolution (Hard) — Hard tier · No syntax hints provided ⚠ This lab tests independent problem-solving. Reference window available. ──────────────────────────────

mysql>

COMMAND REFERENCE

AVAILABLE COMMANDS

SHOW ENGINE INNODB STATUS\G
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
BEGIN; SELECT * FROM accounts WHERE id=1 FOR UPDATE; UPDATE accounts SET balance=balance-500 WHERE id=2;
BEGIN; SELECT * FROM accounts WHERE id IN (1,2) ORDER BY id FOR UPDATE;
SELECT * FROM jobs WHERE status='pending' LIMIT 10 FOR UPDATE SKIP LOCKED;

Type keywords from the command list. The terminal will recognise your intent even if the exact syntax differs.

MISSION BRIEFING

DEADLOCK FORENSICS — VAULT BANKING · HARD ⭐⭐⭐

SCENARIO

Vault Banking's transaction processing system is experiencing deadlocks every few minutes during peak load. Customer transfers are failing silently. You must read the InnoDB deadlock log, identify the conflicting transactions, understand the lock ordering problem, and restructure the transaction logic to eliminate the deadlock.

OBJECTIVES

Read InnoDB deadlock log (SHOW ENGINE INNODB STATUS)
Identify conflicting transactions and lock types
Reproduce the deadlock with two concurrent sessions
Identify the lock ordering problem
Restructure transaction to use consistent lock order
Add NOWAIT / SKIP LOCKED for queue processing
Verify zero deadlocks over 100 test runs

HARD MODE RULES

No step-by-step guidance. Use the Reference window for command syntax only. You must determine the approach, the correct columns and order, and verify your own results.

Display Mode