photo

MDL

Meta Data Lock (MDL) : Metadata Lock.

MDL was introduced primarily to resolve or ensure consistency between DDL operations and DML operations. Prior to the introduction of MDL, the following data inconsistencies occurred due to conflicts between DDL and DML:

  • For example, in the repeatable isolation level, session A modifies the table structure during the two query sessions. Therefore, the two query results are inconsistent and cannot meet the repeatable read requirements
  • For example, when session A executes multiple update statements, another session B changes the table structure and commits it first. As A result, A replication error occurs when the slave reperforms ALTER first and then update.

See this article from the Nuggets for details

MDL read lock sharing, read and write mutually exclusive, write mutually exclusive. Because of the MDL lock conflict problem, there is often a service crash caused by adding fields online. After session A there is session B, and after session B there is session C. Session A acquires the MDL read lock, session B acquires the MDL write lock, and session C acquires the MDL read lock. Because session A is A large transaction, it lasts A very long time. In this case, both sessions B and C are blocked. If session C has the retry timeout function and the traffic is heavy, the database connection pool may be full and service exceptions may occur.

This brings up another topic: online DDL. You can check out the official documentation for MySQL. To sum up, the original copy-replace is broken up into stages so that the blocking MDL only appears in a few stages and lasts for a very short time.

Table locks

The syntax of a table lock is

lock tables ... read/write; . unlocktable;
Copy the code

There’s another way

select * from t lock in share mode
Copy the code

Row locks

A table lock locks the entire table, affecting services. So InnoDB introduced row locking. InnoDB rows are locked by locking index entries.

Another concept is the two-phase lock protocol: In InnoDB transactions, row locks are added when they are needed, but are not released immediately when they are no longer needed, but wait until the end of the transaction. This is the two-phase lock protocol.

Clearance lock

A gap lock locks gaps between data that cannot be inserted by other transactions until the lock is released. This will ensure that there is no magic reading.

Next-Key-Lock

Next-key-lock is formed by row Lock and gap Lock. Its locking rules are summarized as the following rules in the geek time column MySQL Actual Practice 45 by Lin Xiaobin:

Two “principles”, two “optimizations” and one “bug”. Principle 1: The basic unit of locking is next-key lock. I hope you remember that the next-key lock is a front-open and back-closed interval. Principle 2: Only objects accessed during lookup are locked. Optimization 1: equivalent query on index, next-key lock degenerates to row lock when unique index is locked. Optimization 2: the next-key lock degrades to a gap lock when the last value does not meet the equivalence condition. One bug: Range queries on unique indexes access up to the first value that does not meet the criteria.

Intent locks

Intent locks are actually table-level locks. Intent locks are also read/write mutually exclusive, write mutually exclusive, read not mutually exclusive. Take a look at this article

A deadlock

Deadlock is when sessions hold each other’s desired locks and wait for each other.