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.