Lock types (shared, exclusive) in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Shared Locks

Which statement best describes a shared lock in MySQL?

A It allows multiple transactions to write to the data simultaneously but prevents reading.

B It allows only one transaction to read or write the data at a time.

C It prevents all transactions from reading or writing the data until the lock is released.

D It allows multiple transactions to read the same data simultaneously but prevents any transaction from writing to it.

💡 Hint

Think about what happens when many people want to read a book but no one should change it while reading.

A shared lock allows many transactions to read the data at the same time but stops any transaction from changing it until all shared locks are released.

🧠 Conceptual

intermediate

Exclusive Lock Behavior

What is the main effect of an exclusive lock on a database row in MySQL?

A It prevents other transactions from reading or writing the locked row until the lock is released.

B It allows other transactions to read but not write the locked row.

C It allows multiple transactions to write to the row simultaneously.

D It only prevents other transactions from writing but allows reading.

💡 Hint

Imagine locking a diary so no one else can even peek or write until you finish.

An exclusive lock blocks all other transactions from reading or writing the locked data until the lock is released.

❓ query_result

advanced

Result of Concurrent Shared Locks

Consider two transactions that both request a shared lock on the same row simultaneously. What will be the result?

A Both transactions are blocked and cannot proceed until an exclusive lock is released.

B Only the first transaction acquires the shared lock; the second waits until the first releases it.

C Both transactions acquire the shared lock and can read the row simultaneously.

D Both transactions acquire exclusive locks, causing a deadlock.

💡 Hint

Think about multiple people reading the same book at the same time.

Shared locks allow multiple transactions to read the same data concurrently without blocking each other.

❓ query_result

advanced

Effect of Exclusive Lock on Concurrent Shared Lock Request

Transaction A holds an exclusive lock on a row. Transaction B requests a shared lock on the same row. What happens to Transaction B?

A Transaction B waits until Transaction A releases the exclusive lock before acquiring the shared lock.

B Transaction B immediately acquires the shared lock and reads the row.

C Transaction B causes Transaction A to release the exclusive lock automatically.

D Transaction B acquires an exclusive lock instead of a shared lock.

💡 Hint

Think about waiting your turn when someone else is writing in a book.

Shared locks cannot be granted while an exclusive lock is held; the requesting transaction must wait.

❓ schema

expert

Designing Locking Strategy for High Concurrency

You have a table orders where many transactions read order details, but only a few update them. Which locking strategy optimizes concurrency while ensuring data integrity?

A Use exclusive locks for all operations to ensure maximum data safety.

B Use shared locks for reads and exclusive locks only for updates to minimize blocking.

C Use no locks and rely on application-level checks to handle conflicts.

D Use shared locks for updates and exclusive locks for reads.

💡 Hint

Consider how to let many people read without waiting but still protect updates.

Shared locks allow many readers simultaneously, while exclusive locks block others only during updates, balancing concurrency and integrity.

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