1.7 MySQL Locks

1. What Is a Lock

• Database Locks.md

2. Lock Implementation

• trx information: indicates which transaction generated this lock structure.
• is_waiting: indicates whether the current transaction is waiting.

3. Lock Classification

• Based on locking scope, MySQL locks can roughly be divided into three categories: global locks, table-level locks, and row locks.

3.1. Global Lock

3.1.1. What It Is

• Flush tables with read lock locks the entire database instance.
• The entire database becomes read-only. Other threads are blocked when executing the following statements: data update statements (insert/delete/update), data definition statements (including creating tables and changing table structures), and commit statements for update transactions.

3.1.2. Why It Is Needed

• Full-database logical backup.

3.1.2.1. MVCC RR vs Global Lock

• RR can obtain a consistent view, so a global lock is actually unnecessary in that case, but RR is specific to the InnoDB storage engine.

3.2. Table-Level Locks

3.2.1. Table Locks

3.2.1.1. What They Are

• If thread A executes lock tables t1 read, t2 write, then thread A can read t1, read and write t2, but cannot write t1; at the same time, other threads cannot write t1 or read/write t2.

3.2.1.2. Why They Are Needed

• Before finer-grained locks appeared, table locks were the most commonly used way to handle concurrency.

3.2.2. Metadata Locks

3.2.2.1. What They Are

• When performing insert/delete/update/query operations on a table, an MDL read lock is added; when changing the table structure, an MDL write lock is added.

3.2.2.2. Why They Are Needed

• For example, if the table structure changes while data is being queried, the data would become incorrect.

3.2.2.3. Online DDL

• To avoid deadlock, add WAIT when changing the table structure.

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ... 

Does MySQL ALTER TABLE Lock the Table When Adding a Column? - Juejin A MySQL DDL Caused a Table Lock - CSDN

• Internal document: MySQL Online DDL (anonymized)
• Internal document: MySQL Online DDL Details (anonymized) MySQL Tool gh-ost Principle Analysis - MoTianLun Six Methods for Batch Updating Data in MySQL - Juejin GitHub - github/gh-ost: GitHub’s Online Schema-migration Tool for MySQL

3.2.2.3.1. gh-ost

4. MyISAM

• Supports only table-level locks.
• Because it does not support transactions, locks are session-level.
  • If session A performs a select, it needs a table-level S lock. At this time, if session B performs an update, it needs a table-level X lock. Since another session already holds an S lock, it can only block.

4.1. Write-Lock Experiment

• session1

# Add write lock
lock table mylock write;
# Can query the locked table
select * from mylock;

# Cannot query other tables
select * from ttest;

# Can modify the locked table
update mylock set name = '111' where id = 1;

unlock tables ;

• session2

# Querying the table write-locked by session1 blocks
select * from mylock;

# Can query other tables
select * from ttest;

# Modifying the table write-locked by session1 blocks
update mylock set name = '222' where id = 1;

4.2. Read-Lock Experiment

• session1

# Add read lock
lock table mylock read;
# Can query the locked table
select * from mylock;
# Cannot change a table with a read lock
update mylock set name = 'qqq' where id = 1;
# Cannot query other tables
select * from ttest;
# session2's write can continue only after unlocking
unlock tables ;

• session2

# Can query a table locked by another session
select * from mylock;
# Modification blocks
update mylock set name = 'qqq' where id = 1;
# Can query other tables
select * from ttest;

5. InnoDB

• Supports table-level locks and row-level locks.
• Because it supports transactions, locks are transaction-level.
• Table-level S and X locks are not very useful here.
  • When transaction A executes DDL and transaction B executes DML, B is blocked. This uses metadata locks rather than table-level locks.
• Table-level IS and IX locks are similarly not very useful by themselves.
  • IS and IX exist to serve table-level S and X locks. Since the S and X table locks above are of limited practical use here, the derived IS and IX locks are also of limited practical use by themselves.
• Table-level AUTO-INC lock:
  • used for auto-increment columns.
• Row locks:
  • Record Locks:
    • Locks on a single row record.
    • Divided into S locks and X locks.
    • If transaction A adds an S lock to a record, transaction B can continue to add an S lock, but cannot add an X lock.
    • If transaction A adds an X lock to a record, transaction B can add neither an S lock nor an X lock.
  • Gap Locks:
    • Gap locks lock a range but do not include the record itself.
    • MySQL solves the phantom-read problem under the Repeatable Read level through Gap Locks.
    • For example, if the existing primary-key range is [1,20], adding a Gap Lock to the Supremum record means records cannot be inserted before the Supremum record (not including the Supremum record), that is, in [21,+∞).
    • Gap Locks are used to prevent phantom-related inserts and do not conflict with other gap locks.
  • Next-Key Locks:
    • record + gap: lock a range including the record itself.
    • When you want to lock a record and also prevent other transactions from inserting a new record into the gap before it, that is, lock [A,B].
    • Gap Locks + Record Locks.

5.1. Row-Lock Experiment

• session1

# First disable autocommit
# Query
select * from ttest;
# Update the row with id=1; a row lock will be added
update ttest set status = 'never' where id = 1;
# Query again and it is already updated
select * from ttest;

• session2

# session1 has already updated it, but it is not visible here because of the transaction isolation level
select * from ttest;
# If I try to update the same row as session1, it blocks. This is a row lock
update ttest set status = 'never' where id = 1;

5.2. Index Invalidation Causing Many Records to Be Locked

If a varchar indexed column is compared after implicit type conversion, the index may become unusable. InnoDB may then scan and lock many records or ranges; this is not a generic row-lock-to-table-lock escalation.

• session1

# title has an index and is varchar, but the WHERE value is accidentally written as an integer, which may make the index unusable and lock many records/ranges
update tb_item set title = 'zzzzz', sell_point = 'zzz' where title = 121;

5.3. Gap-Lock Experiment

A gap lock: IDs are not contiguous. One transaction updates and locks according to a range, for example 1<=id<=6; another transaction inserts data that happens to be within this range, for example id=2, so it blocks.

5.4. Full-Table Update

An UPDATE Without an Index Locks the Whole Table! - Huawei Cloud Does a MySQL UPDATE Lock Rows or the Table? - CSDN