Lock types (shared, exclusive) in MySQL - Deep Dive

Overview - Lock types (shared, exclusive)

What is it?

Lock types are ways a database controls access to data when multiple users try to read or change it at the same time. Shared locks let many users read the data but prevent changes. Exclusive locks stop others from reading or changing the data while one user is updating it. These locks help keep data correct and avoid conflicts.

Why it matters

Without lock types, many users could change or read data at the same time, causing mistakes like lost updates or wrong reads. This would make databases unreliable and confusing. Lock types solve this by organizing who can do what and when, so data stays safe and consistent even with many users.

Where it fits

Before learning lock types, you should understand basic database concepts like tables, rows, and transactions. After this, you can learn about transaction isolation levels and how databases handle concurrency and performance.

Mental Model

Core Idea

Lock types control who can read or write data at the same time to keep it safe and consistent.

Think of it like...

Imagine a library book that many people want to use. A shared lock is like letting many people read the book quietly in the library, but no one can take it home or write in it. An exclusive lock is like someone checking out the book to take home and write notes, so no one else can use it until they return it.

┌───────────────┐
│   Data Item   │
└──────┬────────┘
       │
  ┌────┴─────┐
  │ Lock Type│
  └────┬─────┘
       │
 ┌─────┴─────┐       ┌───────────────┐
 │ Shared    │       │ Exclusive    │
 │ Lock      │       │ Lock         │
 │ (Read)    │       │ (Write)      │
 └───────────┘       └───────────────┘

Shared lock: many readers allowed, no writers.
Exclusive lock: one writer allowed, no readers.

Build-Up - 7 Steps

1

Foundation

What is a database lock?

Concept: Introduce the basic idea of a lock as a way to control access to data.

A database lock is like a sign on a data item that says who can use it and how. It prevents problems when many users try to read or write the same data at the same time. Locks help keep data safe and correct.

Result

You understand that locks are tools to manage access to data and avoid conflicts.

Understanding locks as access controllers is the first step to grasping how databases keep data consistent.

2

Foundation

Difference between shared and exclusive locks

Concept: Explain the two main lock types and their basic rules.

Shared locks allow many users to read the data but block any changes. Exclusive locks allow one user to change the data and block others from reading or writing until done. This ensures no one reads data while it's being changed.

Result

You can tell when to use shared or exclusive locks based on whether you want to read or write data.

Knowing these two lock types helps you predict how data access is controlled in multi-user environments.

3

Intermediate

How locks work with transactions

🤔Before reading on: do you think locks are held only during the data change or for the whole transaction? Commit to your answer.

Concept: Locks are usually held for the entire transaction to keep data consistent until all changes are done.

When you start a transaction, locks are placed on data you read or write. Shared locks stay while you read, exclusive locks stay while you write. They are released only when you commit or rollback the transaction, so no one sees partial changes.

Result

You understand that locks protect data for the whole transaction, not just a single query.

Knowing locks last for the transaction duration explains why transactions keep data consistent even with many users.

4

Intermediate

Lock compatibility and conflicts

🤔Before reading on: can two exclusive locks exist on the same data at the same time? Commit to yes or no.

Concept: Locks have rules about which types can exist together on the same data without conflict.

Shared locks are compatible with other shared locks, so many readers can coexist. Exclusive locks conflict with all other locks, so only one exclusive lock can exist on data at a time. If a lock conflicts, the new request waits until the existing lock is released.

Result

You can predict when a lock request will wait or proceed based on existing locks.

Understanding lock compatibility helps explain why some queries wait and others run immediately.

5

Intermediate

Lock escalation and granularity

🤔Before reading on: do you think locks are always on single rows or can they cover bigger parts? Commit to your answer.

Concept: Locks can apply to different levels like rows, pages, or tables, and sometimes escalate to bigger scopes.

Databases can lock a single row, a page (group of rows), or a whole table. If many row locks are held, the database might escalate to a table lock to save resources. This can affect concurrency because bigger locks block more users.

Result

You understand how lock size affects performance and concurrency.

Knowing lock granularity and escalation helps you tune database performance and avoid unnecessary blocking.

6

Advanced

Deadlocks and lock waits

🤔Before reading on: do you think deadlocks happen because of a single lock or multiple locks? Commit to your answer.

Concept: Deadlocks happen when two or more transactions wait forever for locks held by each other.

If Transaction A holds a lock that Transaction B wants, and Transaction B holds a lock that Transaction A wants, both wait forever. Databases detect deadlocks and abort one transaction to break the cycle. Lock waits happen when a transaction waits for a lock to be released.

Result

You can recognize deadlocks and understand why they happen.

Understanding deadlocks is key to designing transactions that avoid waiting forever and improve system reliability.

7

Expert

Locking internals in MySQL InnoDB

🤔Before reading on: do you think MySQL uses the same lock types for all storage engines? Commit to yes or no.

Concept: MySQL InnoDB uses row-level locking with shared and exclusive locks, plus intention locks for multi-level locking.

InnoDB uses shared locks for reads and exclusive locks for writes at the row level. It also uses intention locks at the table level to signal which rows are locked. This helps manage locks efficiently and avoid conflicts. Other engines like MyISAM use table-level locks only.

Result

You understand how MySQL implements locks internally and why InnoDB is better for concurrency.

Knowing InnoDB's locking details explains why it supports high concurrency and how to optimize transactions.

Under the Hood

When a transaction requests a lock, the database checks existing locks on the data. If compatible, it grants the lock immediately. If not, the transaction waits in a queue. Locks are tracked in lock tables with information about type, owner, and scope. When the transaction ends, locks are released. InnoDB uses multi-version concurrency control (MVCC) to reduce locking for reads by using snapshots.

Why designed this way?

Locks were designed to prevent data corruption and inconsistent reads in multi-user environments. Early databases used coarse locks (whole tables) which limited concurrency. Row-level locks and intention locks were introduced to allow more users to work simultaneously without conflicts. MVCC was added to improve read performance by avoiding locks when possible.

┌───────────────┐
│ Transaction A │
└──────┬────────┘
       │ requests lock
       ▼
┌───────────────┐
│ Lock Manager  │
│ - Checks     │
│   compatibility│
│ - Queues wait │
└──────┬────────┘
       │ grants or waits
       ▼
┌───────────────┐
│ Data Storage  │
│ - Row locks  │
│ - Intention locks│
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Can shared locks block other shared locks? Commit to yes or no.

Common Belief: Shared locks block all other locks, including other shared locks.

Reality: Shared locks allow multiple shared locks at the same time; they only block exclusive locks.

Why it matters: Believing shared locks block each other leads to thinking reads are always slow or blocked, which is not true and can cause unnecessary complexity.

Quick: Do exclusive locks allow other transactions to read the locked data? Commit to yes or no.

Common Belief: Exclusive locks only block writes but allow reads by others.

Reality: Exclusive locks block both reads and writes by others until released.

Why it matters: Misunderstanding this can cause data inconsistencies if reads happen during writes, breaking data correctness.

Quick: Do locks always happen automatically without developer control? Commit to yes or no.

Common Belief: Developers cannot control locks; the database handles everything invisibly.

Reality: Developers can control locks using SQL commands like SELECT ... FOR UPDATE or LOCK TABLES to manage concurrency explicitly.

Why it matters: Not knowing this limits the ability to optimize performance and avoid deadlocks in complex applications.

Quick: Can deadlocks be completely avoided by using only shared locks? Commit to yes or no.

Common Belief: Using only shared locks means no deadlocks can happen.

Reality: Deadlocks require conflicting locks, so shared locks alone do not cause deadlocks, but real applications need exclusive locks for writes, where deadlocks can occur.

Why it matters: Thinking deadlocks never happen with shared locks can lead to ignoring deadlock detection and recovery strategies.

Expert Zone

1

InnoDB uses intention locks to signal at the table level which rows are locked, improving lock management efficiency.

2

Lock waits can cause performance bottlenecks; understanding lock wait time and deadlock detection is crucial for tuning.

3

MVCC reduces the need for shared locks on reads by providing consistent snapshots, but exclusive locks are still needed for writes.

When NOT to use

Exclusive locks should be minimized in high-concurrency systems to avoid blocking. Alternatives include optimistic concurrency control or using MVCC features to reduce locking. For read-heavy workloads, snapshot isolation or read-committed isolation levels can reduce locking overhead.

Production Patterns

In production, developers use SELECT ... FOR UPDATE to lock rows before updating, avoiding lost updates. Deadlock detection and retry logic are implemented in applications. Lock monitoring tools help identify bottlenecks. Partitioning tables can reduce lock contention by limiting lock scope.

Connections

Transaction Isolation Levels

Lock types are the building blocks that enforce different isolation levels by controlling data visibility and concurrency.

Understanding lock types clarifies how isolation levels like READ COMMITTED or SERIALIZABLE control data consistency and concurrency.

Operating System File Locks

Both database locks and OS file locks control access to shared resources to prevent conflicts.

Knowing OS file locks helps understand the general principle of locking as a concurrency control mechanism across systems.

Traffic Lights in Road Systems

Lock types are like traffic lights controlling when cars (transactions) can go or stop to avoid crashes (data conflicts).

This cross-domain connection shows how controlling access in time and order prevents collisions, whether in traffic or data.

Common Pitfalls

#1 Trying to read data while holding an exclusive lock unnecessarily.

Wrong approach:

START TRANSACTION;
SELECT * FROM orders WHERE id=1 FOR UPDATE;
-- reading data but no update needed
COMMIT;

Correct approach:

START TRANSACTION;
SELECT * FROM orders WHERE id=1;
COMMIT;

Root cause:

Misunderstanding that FOR UPDATE locks exclusively even when only reading, causing unnecessary blocking.

#2 Not handling deadlocks in application code.

Wrong approach:

BEGIN;
UPDATE accounts SET balance=balance-100 WHERE id=1;
UPDATE accounts SET balance=balance+100 WHERE id=2;
COMMIT;

Correct approach:

BEGIN;
UPDATE accounts SET balance=balance-100 WHERE id=1;
UPDATE accounts SET balance=balance+100 WHERE id=2;
COMMIT;
-- with retry logic on deadlock error

Root cause:

Ignoring that deadlocks can happen and not coding retries leads to failed transactions and poor user experience.

#3 Locking entire tables when only a few rows need locking.

Wrong approach:

LOCK TABLES orders WRITE;
-- update one order
UNLOCK TABLES;

Correct approach:

START TRANSACTION;
SELECT * FROM orders WHERE id=123 FOR UPDATE;
-- update order
COMMIT;

Root cause:

Using coarse locks reduces concurrency and slows down other users unnecessarily.

Key Takeaways

Lock types are essential tools that control who can read or write data at the same time to keep databases consistent.

Shared locks allow many readers but block writers, while exclusive locks allow one writer and block everyone else.

Locks usually last for the whole transaction to protect data until changes are complete.

Understanding lock compatibility and conflicts helps explain why some queries wait and others run immediately.

Advanced locking mechanisms like intention locks and MVCC improve concurrency and performance in modern databases like MySQL InnoDB.

Practice

1. What is the main purpose of a shared lock in MySQL?

easy

A. Allow multiple users to read data simultaneously without conflicts

B. Prevent any user from reading data

C. Allow only one user to write data at a time

D. Delete data safely from the database

Solution

1. Step 1: Understand shared lock behavior

   A shared lock allows many users to read the same data at the same time safely.

2. Step 2: Compare with other lock types

   Exclusive locks prevent others from reading or writing, but shared locks only prevent writing.

3. Final Answer:

   Allow multiple users to read data simultaneously without conflicts -> Option A

4. Quick Check:

   Shared lock = multiple readers allowed [OK]

Hint: Shared locks allow many readers, exclusive locks block others [OK]

Common Mistakes:

• Confusing shared lock with exclusive lock
• Thinking shared locks block reading
• Assuming shared locks allow writing

2. Which of the following is the correct syntax to acquire a shared lock on a table in MySQL?

easy

A. LOCK TABLES table_name WRITE;

B. LOCK TABLES table_name SHARE;

C. LOCK TABLES table_name EXCLUSIVE;

D. LOCK TABLES table_name READ;

Solution

1. Step 1: Recall MySQL lock syntax

   In MySQL, to acquire a shared lock, you use LOCK TABLES table_name READ;.

2. Step 2: Eliminate incorrect options

   WRITE is for exclusive locks, EXCLUSIVE and SHARE are not valid MySQL lock keywords.

3. Final Answer:

   LOCK TABLES table_name READ; -> Option D

4. Quick Check:

   Shared lock syntax uses READ keyword [OK]

Hint: Use READ for shared lock, WRITE for exclusive lock [OK]

Common Mistakes:

• Using WRITE instead of READ for shared lock
• Using unsupported keywords like EXCLUSIVE or SHARE
• Confusing lock syntax with SQL SELECT statements

3. Consider two transactions in MySQL: Transaction A acquires a shared lock on a row, and Transaction B tries to acquire an exclusive lock on the same row. What happens?

medium

A. Transaction B immediately acquires the exclusive lock

B. Transaction B waits until Transaction A releases the shared lock

C. Transaction A releases its shared lock automatically

D. Both transactions acquire locks simultaneously

Solution

1. Step 1: Understand lock compatibility

   Shared locks allow multiple readers but block exclusive locks until released.

2. Step 2: Analyze the scenario

   Transaction B must wait for Transaction A to release the shared lock before getting exclusive lock.

3. Final Answer:

   Transaction B waits until Transaction A releases the shared lock -> Option B

4. Quick Check:

   Exclusive lock waits for shared locks to release [OK]

Hint: Exclusive locks wait if shared locks exist on data [OK]

Common Mistakes:

• Assuming exclusive lock can override shared lock immediately
• Thinking shared lock releases automatically
• Believing both locks can coexist on same data

4. You wrote this MySQL code to lock a table for writing but it causes an error:

LOCK TABLES my_table SHARE;

What is the error and how to fix it?

medium

A. Error: Missing semicolon. Fix: Add semicolon at end.

B. Error: Table does not exist. Fix: Create the table first.

C. Error: Invalid lock type 'SHARE'. Fix: Use 'WRITE' instead of 'SHARE'.

D. Error: Cannot lock table twice. Fix: Unlock before locking again.

Solution

1. Step 1: Identify invalid lock keyword

   MySQL does not support 'SHARE' as a lock type; it uses 'READ' or 'WRITE'.

2. Step 2: Correct the lock type

   To lock for writing, use LOCK TABLES my_table WRITE; instead.

3. Final Answer:

   Error: Invalid lock type 'SHARE'. Fix: Use 'WRITE' instead of 'SHARE'. -> Option C

4. Quick Check:

   Use WRITE for exclusive lock, SHARE is invalid [OK]

Hint: Use READ or WRITE only; SHARE is not valid in MySQL [OK]

Common Mistakes:

• Using unsupported lock keywords
• Ignoring error messages about syntax
• Assuming SHARE is synonym for READ

5. You want to ensure that multiple users can read data from a table simultaneously, but only one user can update it at a time. Which locking strategy should you use in MySQL?

hard

A. Use shared locks (READ) for reading and exclusive locks (WRITE) for updating

B. Use exclusive locks (WRITE) for both reading and updating

C. Use shared locks (READ) for updating and exclusive locks (WRITE) for reading

D. Do not use any locks; rely on automatic commit

Solution

1. Step 1: Understand shared and exclusive lock roles

   Shared locks allow multiple readers; exclusive locks allow one writer.

2. Step 2: Apply correct locks for reading and writing

   Use shared locks for reading so many users can read safely, and exclusive locks for updating to prevent conflicts.

3. Final Answer:

   Use shared locks (READ) for reading and exclusive locks (WRITE) for updating -> Option A

4. Quick Check:

   Shared for read, exclusive for write ensures safe concurrency [OK]

Hint: Shared locks for reading, exclusive locks for writing [OK]

Common Mistakes:

• Using exclusive locks for reading blocks all users
• Using shared locks for writing causes conflicts
• Ignoring locks and risking data corruption