Location>code7788 >text

Global lock, table lock, row lock

Popularity：274 ℃/2025-02-27 16:34:34

mysql lock

Global lock

A global lock is to lock the entire database instance. A typical use scenario for global lock is to do a full library logical backup.

-- Add global lock
     Command: Flush tableswith read lock (FTWRL)
-- Release the global lock
     Command: unlock tables-- Set global read-onlyset global readonly=true
-- Set global read and writeset global readonly=false
The difference between setting a full library read-only and adding a global lock
1. During execution, the global lock will automatically release the lock due to abnormal client disconnection, and setting the entire library read-only will not.

Table lock

A typical use scenario for table locks is to perform read-write separation to invalidate read-write separation.

-- Add table lock
     Command: lock tables read/write table_name;
-- Release the table lock
     Command: unlock tables;

Metadata lock (MDL lock)

When adding, deleting, modifying and checking a table, add an MDL read lock; when changing the table structure, add an MDL write lock.

A typical scenario where adding fields to a small table causes the entire library to hang up:

  1. Thread A is conducting a long query on the table, holding the MDL read lock
  2. Thread B needs to add a field to the table and needs to obtain the MDL write lock, so it is blocked.
  3. All subsequent threads that want to access the table must first acquire the MDL lock, and all are blocked
  4. If the table is a hotspot table, a large number of threads are blocked, resulting in the database connection pool being filled
  5. In the end, the entire database is unavailable

Solution:

  1. Before modifying the table structure, confirm whether there are long transactions that are executing
  2. Set the timeout time of the DDL operation and give up after the timeout:
ALTER TABLE table_name ADD COLUMN column_name VARCHAR(50), ALGORITHM=INPLACE, LOCK=NONE;
  1. Use tools like pt-osc or gh-ost to change the online table structure
Use single-transaction instead of global locks

For transaction-enabled storage engines (such as InnoDB), you can use the single-transaction parameter to do backups to avoid using global locks:

mysqldump --single-transaction -uroot -p database_name >
Issues of single-transaction backup in master-slave architecture

Problems that may be encountered when using single-transaction for backup in master-slave architecture:

  1. If the main library executes DDL during the backup process:

    • If the backup is not copied to this DDL yet, the backup is normal
    • If the DDL has been executed from the library:
      • Backup will report an error interrupt
      • Because DDL will cause MDL write locks, conflict with MDL read locks of backup transactions

  2. Solution:

    • When backing up, it is best to make sure that the main library does not perform DDL operations
    • Or use other backup solutions, such as:
      • Use the --lock-all-tables parameter of mysqldump
      • Using the logical backup tool Percona XtraBackup
      • Use global lock FTWRL
 

Global locks are mainly used in the logical backup process. For libraries that are all InnoDB engines, I suggest you choose to use the --single-transaction parameter, which will be more application-friendly. Table locks are usually used when the database engine does not support row locks.

Lock

It is achieved by locking index records.

 In innodb, line locks are added when needed, but they are not released immediately after not needed, but they have to wait until the transaction is over.  This is the two-stage lock protocol.

 Two-stage lock protocol:
 1. Locking phase: Transactions can obtain locks supported by engines other than InnoDB, and InnoDB only supports row locks.
 2. Unlocking phase: All added locks will be released only after the transaction is completed.
Deadlock processing strategy:
1. Wait until timeout.
 2. Initiate deadlock detection and actively roll back one transaction in the deadlock chain to allow other transactions to continue execution.  Set the parameter innodb_deadlock_detect to on to enable this logic.
<Previous Next>