Lock types (shared, exclusive) in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a shared lock in a database?

A shared lock allows multiple transactions to read a resource at the same time but prevents any transaction from modifying it until all shared locks are released.

beginner

What is an exclusive lock in a database?

An exclusive lock allows only one transaction to write or modify a resource, blocking other transactions from reading or writing it until the lock is released.

beginner

Why do databases use shared and exclusive locks?

Databases use shared and exclusive locks to keep data consistent and avoid conflicts when multiple users access or change the same data at the same time.

beginner

Can multiple transactions hold an exclusive lock on the same resource simultaneously?

No, only one transaction can hold an exclusive lock on a resource at a time to prevent conflicts during data modification.

intermediate

What happens if a transaction tries to acquire an exclusive lock while shared locks are held on the same resource?

The transaction must wait until all shared locks are released before it can get the exclusive lock, ensuring no other transaction is reading the resource during modification.

Which lock type allows multiple transactions to read a resource simultaneously?

A Exclusive lock

B Shared lock

C Update lock

D No lock

Shared locks allow multiple transactions to read the same resource at the same time.

What does an exclusive lock prevent?

A Other transactions from reading or writing the locked resource

B Only other transactions from reading the resource

C Only other transactions from writing the resource

D Nothing, it allows all access

Exclusive locks block all other transactions from reading or writing the locked resource.

If a resource has shared locks, what must happen before an exclusive lock can be granted?

A One shared lock must be converted to exclusive

B Exclusive lock can be granted immediately

C All shared locks must be released

D Shared locks are ignored

Exclusive locks require that no shared locks are held on the resource.

Which lock type is typically used when a transaction only needs to read data?

A Shared lock

B Exclusive lock

C Deadlock

D No lock

Shared locks are used to allow safe concurrent reads.

What is the main goal of using locks in a database?

A To delete old data automatically

B To speed up queries

C To allow unlimited simultaneous writes

D To keep data consistent and avoid conflicts

Locks help maintain data consistency and prevent conflicts during concurrent access.

Explain the difference between shared and exclusive locks in a database.

Think about who can read or write when each lock is held.

Describe why databases need to use lock types like shared and exclusive locks.

Consider what happens if many users change data at the same time without locks.