The locking mechanism in MySQL is the core component that ensures transaction isolation and concurrency control. It is mainly divided into the following types, and is classified as follows according to scope of action and scenarios:
1. Classification by lock particle size
1. Table-Level Locking
characteristic: Lock the entire table, with small overhead but low concurrency.
Common Types：
- Table Shared Lock (LOCK TABLES … READ): Allow other transactions to read tables, but write operations are prohibited.
- Table Exclusive Lock (LOCK TABLES … WRITE): Prohibit other transactions to read and write tables.
Intention Locks：
- Intent Sharing Lock (IS): The transaction intends to add a shared lock on the line.
- Intention Exclusive Lock (IX): The transaction intends to add exclusive locks to the row.
- effect: As a table-level lock, it is an identification of compatibility judgment with row-level locks, optimized lock collision detection.
2. Row-Level Locking
characteristic: Only lock data rows, with high overhead but high concurrency (InnoDB supports default).
Common Types：
- Record Locks: Lock a single record in the index.
- Gap Locks: Lock the "gap" between index records to prevent insertion of new data (solve phantom reading).
- Next-Key Locks: A combination of record lock + gap lock, locking the record itself and the previous gap (InnoDB default lock mode).
2. Classification by lock mode
1. Shared Lock (S lock)
- characteristic: Allow other transactions to read data, but modification is prohibited.
-
Use scenarios：
SELECT ... LOCK IN SHARE MODE。
2. Exclusive Lock (X lock)
- characteristic: Prohibit other transactions from reading and writing data.
-
Use scenarios：
SELECT ... FOR UPDATEOr automaticallyINSERT/UPDATE/DELETEtrigger.
3. Special scene lock
1. AUTO-INC Locks
-
characteristic:against
AUTO_INCREMENTcolumn to ensure that self-value increases are unique and continuous. - Behavior: Holding briefly when the insert statement is executed may become a concurrency bottleneck.
2. Insert Intention Locks
- characteristic: When a transaction attempts to insert data into a locked gap, set an insertion intention lock to indicate that the gap is released.
- effect: Avoid insertion conflicts and improve concurrent insertion efficiency.
3. Metadata Locks (MDL)
characteristic: Implicit locking to manage concurrent access to table structures (such as DDL operations).
Behavior：
- When modifying the table structure (
ALTER TABLE), automatically request metadata exclusive lock. - If other transactions hold the metadata lock of the table, they need to wait for release.
4. Storage engine differences
-
InnoDB: Supports row-level lock, gap lock, and key lock, and the default isolation level is
REPEATABLE READ。 - MyISAM: Only table-level locks are supported, no row-level locks are used, and the concurrency performance is low.
5. Relationship between lock behavior and isolation level
| Isolation level | Lock type | Features |
|---|---|---|
| Read not submitted | No gap lock, only record lock | Allow dirty reading, with minimal competition for locks |
| Read Submitted | Record Lock (MVCC) | Avoid dirty reading through snapshot reading, but may be phantom reading |
| Repeatable (default) | Next-Key Locks | Default lock mode, prevents phantom reading, but may increase lock competition |
| Serialization | Forced table-level lock or gap lock | Fully serial execution with lowest performance |
VI. Use suggestions
- Optimize index: Rationally designing indexes can reduce the lock range (such as unique indexes avoid gap locks).
- Control transaction granularity: Avoid holding locks for a long time and reduce lock competition.
-
Monitor lock status:pass
SHOW ENGINE INNODB STATUSorINFORMATION_SCHEMA.INNODB_LOCKSAnalyze lock conflicts. - Isolation level selection: Weigh consistency and concurrency performance based on business needs (such as read commit + index optimization).
By understanding lock types and behavior, MySQL concurrency performance can be effectively optimized to avoid deadlocks and performance bottlenecks.
Summarize
The above is personal experience. I hope you can give you a reference and I hope you can support me more.