Lock types (shared, exclusive) in MySQL - Step-by-Step Execution

Concept Flow - Lock types (shared, exclusive)

Transaction requests lock

↓

Check lock type requested

↓

Shared lock?

↓

Check existing

↓

locks compatibility

↓

Grant shared lock

↓

Transaction proceeds

↓

Transaction releases lock

A transaction requests a lock type (shared or exclusive). The system checks compatibility with existing locks and grants the lock if allowed, then the transaction proceeds and later releases the lock.

Execution Sample

MySQL

BEGIN;
SELECT * FROM accounts WHERE id=1 LOCK IN SHARE MODE;
UPDATE accounts SET balance=balance+100 WHERE id=1;
COMMIT;

A transaction starts, acquires a shared lock for reading, then updates by upgrading to an exclusive lock, and commits.

Execution Table

Step	Action	Lock Requested	Existing Locks	Lock Granted?	Reason/Result
1	BEGIN transaction	None	None	None	Transaction started, no locks yet
2	SELECT with LOCK IN SHARE MODE	Shared	None	Yes	No conflicting locks, shared lock granted
3	UPDATE row	Exclusive	Shared (self)	Yes	Lock upgraded to exclusive (same transaction)
4	UPDATE row (after acquiring exclusive lock)	Exclusive	Exclusive (self)	Yes	Exclusive lock already held, update allowed
5	COMMIT transaction	Release all	Exclusive	Released	Locks released, transaction ends

💡 Transaction ends after commit, all locks released

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Lock Held	None	Shared	Exclusive	Exclusive	None
Transaction State	Not started	Active	Active	Active	Committed

Key Moments - 2 Insights

Why can the exclusive lock be granted immediately after holding a shared lock?

Because the transaction already holds a shared lock on the row (see step 3 in execution_table), MySQL allows automatic upgrade to exclusive lock for the same transaction.

What happens if another transaction holds an exclusive lock when a shared lock is requested?

The shared lock request will be blocked until the exclusive lock is released, because exclusive locks are not compatible with shared locks (see step 2 logic in concept_flow).

Visual Quiz - 3 Questions

Look at the execution_table, what lock is held after step 2?

A Shared lock

B Exclusive lock

C No lock

D Both shared and exclusive locks

💡 Hint

Check the 'Lock Granted?' and 'Lock Held' columns for step 2 in execution_table and variable_tracker

At which step does the transaction finally get the exclusive lock?

A Step 2

B Step 4

C Step 3

D Step 5

💡 Hint

Look at the 'Lock Granted?' column in execution_table for when exclusive lock is granted

If the transaction did not release the shared lock before requesting exclusive lock, what would happen?

A Exclusive lock request is denied or blocked

B Shared lock is upgraded automatically

C Exclusive lock granted immediately

D Transaction deadlocks

💡 Hint

Refer to step 3 in execution_table and key_moments about lock upgrade behavior

Concept Snapshot

Lock types in MySQL:
- Shared lock (S): allows multiple transactions to read but not write.
- Exclusive lock (X): allows one transaction to write, blocks others.
- Shared and exclusive locks are incompatible.
- Transactions must release or upgrade locks properly.
- Locks are held until transaction commits or rolls back.

Full Transcript

This visual execution shows how MySQL handles shared and exclusive locks during a transaction. The transaction begins without locks, then requests a shared lock for reading. The shared lock is granted because no conflicting locks exist. When the transaction tries to update the row, it needs an exclusive lock. MySQL automatically upgrades the shared lock to exclusive because it's the same transaction. The update succeeds. Finally, the transaction commits and releases all locks. This demonstrates lock compatibility and upgrade behavior within a transaction.