This section describes table locking

MySQL SQL Layer MySQL table level lock

  • Table locks
  • Meta Data Lock (MDL)

MySQL implements table level locking contention state variables:

 show status like 'table%';
Copy the code

Table_locks_imm DIate: indicates the number of table locks. Table_locks_waited: number of waits caused by table level lock contention;Copy the code

Table lock is introduced

  • Table locks take two forms:
Table Read Lock Table Write LockCopy the code
  • Manually add table locks
lock tableTable name Read (write) indicates the table name2Read (write), other;Copy the code
  • Check the table lock status
show open tables;
Copy the code
  • Delete table locks
unlock tables;
Copy the code

Introduction to metadata locks

DML does not need to be used explicitly and is added automatically when accessing a table. The purpose of DML is to ensure correct read and write. If a query is iterating through a table and another thread changes the table structure and deletes a column during execution, the query thread will get results that do not match the table structure.

Therefore, DML was introduced in MySQL 5.5 to add DML read locks when adding, deleting, modifying, or querying a table. Add DML write locks when you want to make structural changes to a table.

  • Read locks are not mutually exclusive, so you can have multiple threads adding, deleting, or modifying a table at the same time.
  • Read/write locks and write locks are mutually exclusive to ensure the security of changing the table structure. Therefore, if two threads are adding fields to a table at the same time, one of them will wait for the other to finish before starting to execute

The MDL lock in a transaction is claimed at the beginning of a statement execution, but is not released immediately after the statement completes, but after the entire transaction commits.