What are different types of locks in MySQL?
In MySQL, locks are mechanisms used to control concurrent access to data and ensure data consistency. Different types of locks exist depending on the level of granularity and the storage engine. Here’s a detailed breakdown:
1. Table-level Locks
These locks lock the entire table, preventing other sessions from modifying it until the lock is released.
- Read Lock (
LOCK TABLES ... READ)- Multiple sessions can acquire a read lock simultaneously.
- Prevents write operations but allows reads.
- Useful for consistent reading of a table.
- Write Lock (
LOCK TABLES ... WRITE)- Only one session can acquire a write lock at a time.
- Prevents both reads and writes from other sessions.
- Useful for bulk updates or table maintenance.
Storage Engine: MyISAM primarily uses table-level locks.
2. Row-level Locks
Row-level locks lock only the specific rows being accessed, allowing higher concurrency.
- Shared (S) Lock / Read Lock
- Multiple transactions can read the same row simultaneously.
- Other transactions cannot modify the row until the lock is released.
- Exclusive (X) Lock / Write Lock
- Only one transaction can modify the row.
- Prevents other transactions from reading or writing the locked row.
Storage Engine: InnoDB uses row-level locks with MVCC (Multi-Version Concurrency Control).
3. Intent Locks (InnoDB)
- These are table-level locks that signal an intention to acquire a row-level lock.
- Types:
- IS (Intent Shared): Indicates intention to acquire shared row locks.
- IX (Intent Exclusive): Indicates intention to acquire exclusive row locks.
- Helps prevent deadlocks and optimize concurrency.
4. Auto-increment Locks
- Used when inserting values into tables with
AUTO_INCREMENTcolumns. - Ensures unique auto-increment values when multiple sessions insert concurrently.
- Can be consecutive or interleaved depending on the
innodb_autoinc_lock_mode.
5. Metadata Locks (MDL)
- Automatically acquired by MySQL to protect table definitions during DDL operations.
- Example: Prevents a table from being dropped while a query is running.
- Acquired for operations like
ALTER TABLE,DROP TABLE,CREATE INDEX.
6. Advisory Locks (User-level Locks)
- Application-controlled locks using
GET_LOCK()andRELEASE_LOCK(). - Useful for custom synchronization at the application level.
- Example:
SELECT GET_LOCK('my_lock', 10); -- Acquire lock with 10s timeout SELECT RELEASE_LOCK('my_lock'); -- Release lock
Summary Table
| Lock Type | Granularity | Storage Engine | Purpose |
|---|---|---|---|
| Table-level | Table | MyISAM, InnoDB | Lock whole table for read/write |
| Row-level | Row | InnoDB | Lock specific rows for transactions |
| Intent Lock | Table (signals) | InnoDB | Indicate intention for row locks |
| Auto-increment Lock | Table | InnoDB | Ensure unique AUTO_INCREMENT values |
| Metadata Lock (MDL) | Table/DDL | All | Protect table schema during DDL |
| Advisory Lock | Application-defined | All | User-controlled synchronization |
No comments yet! You be the first to comment.