Database Locks - Row-Level, Table-Level, Optimistic vs Pessimistic

🎯

Database Locks - Row-Level, Table-Level, Optimistic vs Pessimistic

mediumDBMSAmazonGoogleFlipkart

Imagine multiple users trying to update the same bank account balance simultaneously - how does the database ensure consistency without blocking everyone unnecessarily?

💡 Beginners often confuse locking granularity and concurrency control strategies, thinking all locks behave the same or that optimistic locking means no conflicts can occur. Remember, locking granularity determines how much data is locked at once, affecting concurrency and performance, while concurrency control strategies decide when and how locks are applied to prevent conflicts.

📋

Interview Question

Explain the differences between row-level and table-level locks in databases. Compare optimistic and pessimistic locking strategies, and discuss when each is appropriate.

Lock granularity: row-level vs table-level lockingOptimistic vs pessimistic concurrency controlTrade-offs between concurrency and consistency

💡

Scenario & Trace

ScenarioMultiple users updating different rows in the same table concurrently

With row-level locking, each user acquires a lock only on the specific row they update, allowing others to modify different rows simultaneously. With table-level locking, the entire table is locked for one user’s update, blocking others until the lock is released.

ScenarioA web application where conflicts are rare but must be detected

Optimistic locking allows transactions to proceed without locking resources upfront. Before committing, the system checks if the data was modified by another transaction. If yes, it aborts and retries. This reduces lock contention in low-conflict scenarios.

ScenarioA banking system where data integrity is critical and conflicts are frequent

Pessimistic locking acquires locks before accessing data to prevent conflicts. This ensures no other transaction can modify the locked data until the lock is released, guaranteeing consistency but potentially reducing concurrency.

• What happens if two transactions try to acquire a row-level lock on the same row simultaneously?
• How does optimistic locking handle a conflict detected at commit time?
• What are the consequences of holding a table-level lock for a long-running transaction?

⚠️

Common Mistakes

Confusing optimistic locking with no locking at all

Interviewer thinks candidate does not understand conflict detection

✅ Explain that optimistic locking detects conflicts at commit time using version checks

Assuming table-level locks always improve performance

Interviewer doubts candidate's grasp of concurrency trade-offs

✅ Clarify that table-level locks reduce concurrency and can cause bottlenecks

Believing row-level locks eliminate all conflicts

Interviewer suspects candidate misses deadlock and conflict scenarios

✅ Mention that conflicts and deadlocks can still occur with row-level locks

Not distinguishing when to use optimistic vs pessimistic locking

Interviewer thinks candidate lacks practical insight

✅ Explain optimistic locking suits low-conflict, high-read workloads; pessimistic suits high-conflict or critical data

🧠

Basic Definition - What It Is

💡 This level covers the fundamental concepts you must know to answer basic interview questions confidently. Think of it as the foundation before diving into technical details.

Intuition

Locks control access to data to prevent conflicts; row-level locks are fine-grained, table-level locks are coarse-grained; optimistic locking assumes conflicts are rare, pessimistic locking assumes conflicts are common.

Explanation

Database locks are mechanisms to control concurrent access to data. Row-level locks restrict access to individual rows, allowing high concurrency by letting multiple transactions work on different rows simultaneously. Table-level locks restrict access to the entire table, simplifying management but reducing concurrency. Optimistic locking allows transactions to proceed without locking but checks for conflicts before committing, suitable when conflicts are rare. Pessimistic locking acquires locks before data access to prevent conflicts, suitable when conflicts are frequent or data integrity is critical.

Memory Hook

💡 Think of row-level locks as locking individual seats in a theater, table-level locks as locking the entire theater, optimistic locking as trusting people to not double-book seats but checking at the end, and pessimistic locking as reserving seats upfront.

Interview Questions

❓ What is the difference between row-level and table-level locks?

• Row-level locks lock individual rows, allowing more concurrency
• Table-level locks lock the entire table, reducing concurrency but simpler

⏱

Depth Level

Interview Time30 seconds

Depthbasic

Covers fundamental definitions and differences; sufficient for screening rounds.

Interview Target: Minimum floor - never go below this

Knowing only this helps pass initial screening but is insufficient for deeper technical discussions.

🧠

Mechanism Depth - How It Works

💡 This level explains internal workings and trade-offs expected in product company interviews. Understanding these details helps you answer follow-up questions confidently.

Intuition

Lock granularity affects concurrency and overhead; optimistic locking uses version checks to detect conflicts; pessimistic locking uses explicit locks to prevent conflicts.

Explanation

Row-level locking involves acquiring locks on specific rows, which allows multiple transactions to work concurrently on different rows but requires more overhead to manage many locks. Table-level locking locks the entire table, simplifying lock management but reducing concurrency and increasing wait times. Optimistic locking uses a version number or timestamp to detect if data was modified since it was read; if a conflict is detected at commit, the transaction aborts and retries. Pessimistic locking acquires locks before data access, blocking other transactions from accessing the locked data until the lock is released, preventing conflicts but potentially causing deadlocks or reduced throughput. Choosing between these depends on workload characteristics: high contention favors pessimistic locking and coarser locks; low contention favors optimistic locking and finer locks.

Memory Hook

💡 Imagine a busy restaurant: row-level locking is like reserving individual tables, table-level locking is closing the entire restaurant for a private event, optimistic locking is trusting guests to not double-book but checking at payment, and pessimistic locking is assigning tables upfront and not allowing changes.

Interview Questions

❓ How does optimistic locking detect conflicts?

• By checking a version number or timestamp before commit
• If data changed since read, transaction aborts and retries

❓ What are the trade-offs between row-level and table-level locks?

• Row-level locks increase concurrency but add overhead
• Table-level locks reduce overhead but decrease concurrency

⏱

Depth Level

Interview Time2-3 minutes

Depthintermediate

Demonstrates understanding of internal mechanisms and trade-offs; suitable for on-site interviews.

Interview Target: Target level for FAANG on-sites

Mastering this level distinguishes you from most candidates.

📊

Explanation Depth Levels

💡 Choose depth based on interview stage and company expectations. Start simple and add details as needed.

Level	Interview Time	Suitable For	Risk
Basic Definition	30s	Screening call	Too shallow for on-site interviews
Mechanism Depth	2-3 minutes	On-site interviews at FAANG and top companies	Requires good understanding; insufficient detail may lose points

💼

Interview Strategy

💡 Use this guide to structure your explanation clearly and confidently before every mock or real interview. It helps ensure you cover all important points logically.

How to Present

Start with a clear definition of row-level and table-level locksGive a real-world analogy or example to illustrate lock granularityExplain optimistic vs pessimistic locking with their mechanisms and use casesDiscuss edge cases like conflicts, deadlocks, and performance impacts

Time Allocation

Definition: 30s → Example: 1min → Mechanism: 2min → Edge cases: 30s. Total ~4min

What the Interviewer Tests

Interviewer checks your understanding of concurrency control, ability to compare trade-offs, and knowledge of practical scenarios.

Common Follow-ups

• What happens if two transactions try to lock the same row simultaneously? → One waits or deadlock may occur
• When is optimistic locking preferred over pessimistic locking? → Low conflict, high read scenarios

💡 These are common curveballs that test your depth and practical understanding.

🔍

Pattern Recognition

When to Use

Asked when discussing database concurrency, transaction management, or performance tuning.

Signature Phrases

'Explain the difference between row-level and table-level locks''Compare optimistic and pessimistic locking''What happens when two transactions try to update the same row?'

NOT This Pattern When

Similar Problems

Practice

1. You need to optimize a database query that frequently performs exact-match lookups on a large, unordered dataset. Which index type is most appropriate to use?

easy

A. B+ tree index, because it supports efficient range queries.

B. Hash index, because it provides O(1) average-time equality lookups.

C. B+ tree index, because it provides faster equality lookups than hash indexes.

D. Hash index, because it supports ordered traversal for range queries.

Solution

1. Step 1: Identify the query type

   The query performs exact-match lookups, which hash indexes optimize efficiently.

2. Step 2: Understand hash index strengths

   Hash indexes provide average O(1) time for equality lookups but do not support ordered traversal.

3. Step 3: Understand B+ tree strengths

   B+ trees support ordered traversal and range queries but equality lookups are O(log n), slower than hash indexes.

4. Step 4: Evaluate distractors

   B+ tree index, because it supports efficient range queries. is incorrect because range queries are not the focus here. B+ tree index, because it provides faster equality lookups than hash indexes. wrongly claims B+ trees are faster for equality lookups. Hash index, because it supports ordered traversal for range queries. incorrectly states hash indexes support ordered traversal.

5. Final Answer:

   Option B -> Option B

6. Quick Check:

   Exact-match queries -> hash index [OK]

Hint: Exact-match -> hash index; range -> B+ tree

Common Mistakes:

• Confusing hash indexes as supporting range queries
• Assuming B+ trees are always faster for equality lookups
• Believing hash indexes maintain order

2. Trace the sequence of lock acquisitions and releases when two transactions T1 and T2 both request shared locks on the same data item, followed by T1 requesting an exclusive lock on that item. What happens step-by-step?

easy

A. T1 acquires exclusive lock first; T2 waits for T1 to release before acquiring shared lock

B. T1 and T2 acquire shared locks simultaneously; T1 upgrades to exclusive lock immediately without waiting

C. T1 acquires shared lock; T2 waits until T1 releases its lock before acquiring shared lock

D. T1 and T2 acquire shared locks; T1 must wait to upgrade to exclusive lock until T2 releases its shared lock

Solution

1. Step 1: Both transactions request shared locks

   Shared locks are compatible, so T1 and T2 both acquire shared locks simultaneously.

2. Step 2: T1 requests exclusive lock (lock upgrade)

   Exclusive lock is incompatible with shared locks held by others, so T1 must wait until T2 releases its shared lock.

3. Step 3: T1 waits; T2 continues holding shared lock

   T1 cannot upgrade immediately; it must wait for T2 to release.

4. Final Answer:

   Option D -> Option D

5. Quick Check:

   Lock compatibility rules prevent immediate exclusive lock upgrade when others hold shared locks.

Hint: Exclusive lock upgrade waits for all shared locks to release [OK]

Common Mistakes:

• Assuming lock upgrade is immediate despite other shared locks
• Believing shared locks are exclusive
• Ignoring lock compatibility rules

3. Why is it generally discouraged to directly implement many-to-many (N:M) relationships in a relational database without an associative (junction) table?

medium

A. Because direct N:M relationships cause data redundancy and complicate enforcing referential integrity.

B. Because relational databases do not support multiple foreign keys in a single table.

C. Because N:M relationships require storing duplicate primary keys in both tables.

D. Because N:M relationships can only be represented using triggers.

Solution

1. Step 1: Understand N:M relationship representation

   Direct N:M relationships are not supported as foreign keys in relational tables.

2. Step 2: Identify issues with direct implementation

   Trying to store multiple foreign keys in one table leads to redundancy and integrity problems.

3. Step 3: Use associative table to resolve

   Associative (junction) tables break N:M into two 1:N relationships, maintaining normalization and integrity.

4. Step 4: Evaluate other options

   Because relational databases do not support multiple foreign keys in a single table is false; tables can have multiple foreign keys. Because N:M relationships require storing duplicate primary keys in both tables is incorrect; primary keys are not duplicated but referenced. Because N:M relationships can only be represented using triggers is false; triggers are not required for N:M representation.

5. Final Answer:

   Option A -> Option A

6. Quick Check:

   Associative tables prevent redundancy and maintain referential integrity in N:M relationships.

Hint: N:M relationships require a junction table to avoid redundancy and maintain integrity.

Common Mistakes:

• Believing multiple foreign keys in one table can represent N:M directly
• Confusing primary key duplication with foreign key referencing

4. Suppose you want to rank products within categories but now allow products to appear multiple times with different sale_dates, and you want to rank each sale instance instead of aggregated totals. Which modification to the SQL query is correct?

hard

A. Use DENSE_RANK() instead of RANK() to avoid gaps in ranking when sales_amounts tie.

B. Keep GROUP BY product_id and category, but add sale_date to GROUP BY to rank each sale instance.

C. Use HAVING clause to filter sale_date before aggregation to rank recent sales only.

D. Remove GROUP BY and aggregate; apply RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) directly on sales table.

Solution

1. Step 1: Understand requirement change

   Ranking each sale instance means no aggregation; each row is ranked by sales_amount within category.

2. Step 2: Adjust query accordingly

   Remove GROUP BY and SUM; apply RANK() window function directly on sales table partitioned by category ordered by sales_amount DESC.

3. Final Answer:

   Option D -> Option D

4. Quick Check:

   Ranking individual sales requires no aggregation [OK]

Hint: Ranking individual rows means no aggregation, direct window function use [OK]

Common Mistakes:

• Trying to aggregate then rank individual rows
• Misusing HAVING to filter non-aggregated columns

5. Suppose you want to extend the SQL query execution simulation to handle queries where LIMIT is applied without an ORDER BY clause. Which of the following statements about the behavior of LIMIT in this case is correct, and what adjustment should be made to the simulation to reflect real SQL behavior?

hard

A. LIMIT cannot be used without ORDER BY; simulation should raise an error if ORDER BY is missing.

B. LIMIT returns an arbitrary subset of rows without ORDER BY; simulation should note LIMIT applies after FROM and WHERE but before ORDER BY.

C. LIMIT always returns the first N rows sorted by default; no change needed in simulation.

D. LIMIT returns arbitrary rows without ORDER BY; simulation should reflect that LIMIT applies last but does not guarantee order.

Solution

1. Step 1: Understand LIMIT behavior without ORDER BY

   Without ORDER BY, LIMIT returns an arbitrary subset of rows, not sorted, so results may be inconsistent.

2. Step 2: Adjust simulation to reflect logical execution

   Simulation should keep LIMIT as the last clause executed but clarify it does not guarantee sorted output without ORDER BY.

3. Final Answer:

   Option D -> Option D

4. Quick Check:

   LIMIT applies last but unordered without ORDER BY [OK]

Hint: LIMIT without ORDER BY returns arbitrary rows [OK]

Common Mistakes:

• Assuming LIMIT sorts rows by default