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_INCREMENT columns.
• 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() and RELEASE_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