Locking and Deadlocks in SQL
💡 Concept Name
Locking and Deadlocks in SQL – Mechanisms to manage concurrent access to data while ensuring data consistency and preventing conflicts.
📘 Quick Intro
Locks ensure that only one transaction can access or modify data at a time to maintain data integrity. Deadlocks occur when two or more transactions are waiting on each other’s locks, resulting in a cycle that halts progress. SQL databases use lock managers to detect and resolve deadlocks automatically. Understanding and managing locks is key to writing efficient, concurrent-safe queries.
🧠 Analogy / Short Story
Imagine two people trying to cross a narrow bridge from opposite sides. If they both move at the same time and stop midway, they block each other—this is a deadlock. Locks in SQL are like traffic signals that let one person pass while others wait, preventing collisions. If signals are ignored or poorly timed, traffic jams (deadlocks) can occur, needing intervention or timeout to clear up.
🔧 Technical Explanation
- 🔐 **Locks** prevent conflicting access by placing control on rows, pages, or tables.
- ⛓️ **Deadlock** is a situation where two transactions hold locks the other needs and neither can proceed.
- 📦 **Types of locks**: shared (read) and exclusive (write), plus variations at row, page, and table levels.
- ⏱️ Most SQL engines have a deadlock detector that cancels one transaction to break the cycle.
- 🛡️ Best practices include acquiring locks in consistent order, using timeouts, and keeping transactions short.
🎯 Purpose & Use Case
- ✅ Ensure consistency when multiple users read/write to the same data.
- ✅ Prevent dirty reads and race conditions in financial or critical systems.
- ✅ Detect and resolve deadlocks to maintain system responsiveness.
- ✅ Use lock hints or transaction isolation levels for fine-grained control.
💻 Real Code Example
-- Example: Transaction with row-level lock
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
-- Simulate deadlock
-- Another session:
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
-- Both sessions now wait on each other's locked row
-- One will be rolled back by the DB engine
❓ Interview Q&A
Q1: What is a lock in SQL?
A: A lock is a mechanism to control access to data during a transaction to ensure consistency and avoid conflicts.
Q2: What is a deadlock?
A: A deadlock occurs when two or more transactions block each other by holding and waiting for locks in a circular fashion.
Q3: How do databases resolve deadlocks?
A: By detecting the cycle and terminating one of the involved transactions.
Q4: What types of locks exist in SQL?
A: Shared locks for reading, exclusive locks for writing, and variations like row-level or table-level locks.
Q5: How can deadlocks be prevented?
A: By acquiring resources in a consistent order and minimizing transaction duration.
Q6: What role does isolation level play in locking?
A: Higher isolation levels introduce more locks but reduce concurrency issues like dirty reads.
Q7: Can a SELECT statement cause a lock?
A: Yes, especially under certain isolation levels or when using WITH (UPDLOCK) hints.
Q8: What is a lock timeout?
A: The time a transaction waits before aborting if it cannot acquire the needed lock.
Q9: How does SQL Server handle deadlocks?
A: It detects them automatically and kills the least costly transaction.
Q10: What's a good practice for avoiding locks in reporting queries?
A: Use NOLOCK or read-committed snapshot isolation when consistency is not critical.
📝 MCQs
Q1. What does a lock do in SQL?
- Deletes data
- Backs up rows
- Controls access to data during transactions
- Cleans up memory
Q2. What causes a deadlock?
- Syntax errors
- Missing tables
- Transactions waiting on each other’s locks
- Slow indexes
Q3. What type of lock is used for updates?
- Shared lock
- Snapshot lock
- Exclusive lock
- No lock
Q4. How is a deadlock resolved?
- DB restarts
- DB ignores it
- User aborts it
- DB kills one transaction
Q5. Which lock allows multiple readers?
- Exclusive lock
- Shared lock
- Blocked lock
- Paged lock
Q6. What prevents dirty reads?
- WHERE clause
- Joins
- Locks or higher isolation level
- ORDER BY
Q7. Which SQL clause starts a transaction?
- START TX
- INIT TRAN
- BEGIN TRANSACTION
- LOCK START
Q8. What’s a good strategy to avoid deadlocks?
- Skip locks
- Use INNER JOIN
- Acquire locks in same order
- Add more indexes
Q9. What is a lock timeout?
- Time to unlock data
- Time to index data
- How long to wait before aborting
- Backup interval
Q10. Which lock is the most restrictive?
- Shared lock
- Intent lock
- Exclusive lock
- Read lock
💡 Bonus Insight
While locks protect data integrity, overusing them can degrade performance. Use explicit transactions for critical operations, and avoid unnecessary locking for read-only queries. Monitoring tools can help detect frequent deadlocks and long-held locks. It's all about balancing safety and performance.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!