Locking ensures the consistency and validity of concurrent data access. There are three types of locks in MySQL:

  • Global lock
  • Table level lock
  • Row locks

Global lock

MySQL global locks close all open tables and lock all tables with global read locks. Its command is:

FLUSH TABLES WITH READ LOCK;
Copy the code

Short for FTWRL, you can use the following command to unlock:

UNLOCK TABLES;
Copy the code

When FTWRL is executed, all tables are read-only and data updates or field updates are blocked.

Usage scenarios

Global locks are typically used for backups of entire libraries (including non-transactional engine tables) (mysqldump or Xtrabackup). This means that the entire library is read-only during the backup process, which can be quite risky. If the data is backed up in the primary database, services cannot modify data. If you are backing up from a secondary repository, this will result in a master-slave delay.

Fortunately, mysqldump contains a single transaction parameter that allows you to create a consistency snapshot in a single transaction and then back up all tables. Therefore, if this parameter is added, data can be modified during backup. But all tables need to be transaction engine tables. So this is one of the reasons why InnoDB storage engine is recommended.

For Xtrabackup, InnoDB and MyISAM can be backed up separately, or –master-data can not be executed to avoid using global locks.

Table level lock

There are two types of table-level locks: table locks and metadata locks.

Table locks

Table lock usage scenarios:

  • The transaction needs to update most or all of the data for a large table. If the default row lock is used, not only the transaction execution efficiency is low, but also other transactions may wait for a long time and lock conflict. In this case, table lock can be considered to improve the transaction execution speed.

  • Transactions involving multiple tables are complex, which may cause deadlocks and result in a large number of transactions being rolled back. Table locks can be considered to avoid deadlocks.

Table locks are divided into table read locks and table write locks. The commands are as follows:

Table read lock:

lock tables table_name read;
/* This thread and other threads can read, this thread writes an error, other threads write wait */
Copy the code

Table write locks:

lock tables table_name  write;
/* This thread can read and write, other threads will block reading and writing */
Copy the code

Metadata lock

The emergence of MDL locks solves the problem that Data inconsistency may occur when transactions and Data Define Language (DDL) are executed in parallel on the same table.

For development, avoid slow queries, commit transactions on time, avoid large transactions, and, of course, for DBAs, avoid DDL operations during peak business hours.

Row locks

The main reasons for InnoDB’s current mainstream are:

  • InnoDB supports transactions: Ideal for scenarios where data consistency is required under concurrent conditions.
  • InnoDB supports row locking: effectively reduces locking due to deletes or updates.

Two phase lock

Two-stage lock principle: The lock operation is divided into two phases: lock adding and unlock, and ensure that the lock adding and unlock phases do not intersect.

The serial number MySQL operation explain Phase lock
1 begin; The transaction start
2 Insert into… ; Insert lock Locking phase
3 Update the table… ; Add lock corresponding to update Locking phase
4 The delete from… ; Add the lock corresponding to delete Locking phase
5 commit; The transaction ends, and the locks added in steps 2, 3, and 4 are released Unlock stage

InnoDB row lock mode

InnoDB implements two types of row locking:

  • Shared lock (S) : an exclusive lock that allows one transaction to read a row, preventing other transactions from acquiring the same data set.
  • Exclusive lock (X) : Allows transactions that acquire exclusive locks to update data, preventing other transactions from acquiring shared read locks and exclusive write locks of the same data set.

InnoDB does not add any locks to normal SELECT statements. Transactions can explicitly add shared or exclusive locks to recordsets:

  • Select * from table_name where… lock in share mode;

  • Select * from table_name where table_name = “select * from table_name where table_name =” For update.

    Add: ** Why use for update? 支那

    Common query statements, such as SELECT * from T16 where b=1, are snapshot reads and do not see data inserted by other transactions.

    Adding an explicit for UPDATE lock to the recordset makes the query read now. Insert, update, and delete operations belong to the current read. Select… For UPDATE is used to allow a normal query to acquire the lock acquired during insert, update, or delete operations.

InnoDB row lock algorithm

InnoDB row lock three algorithms:

  • Record Lock: Locking an index on a single Record.
  • Gap Lock: A Gap Lock that locks the Gap between index entries but not the record itself.
  • Next-key Lock: Gap Lock + Record Lock locks a range, and locks the Record itself.

InnoDB’s row lock implementation features mean that if data is not retrieved by index criteria, InnoDB will lock all records in the table, which works just like a table lock.

Transaction Isolation level

Different transaction isolation levels correspond to different row locks. Before explaining the locking scope of row locks, you must first clarify the isolation level of a transaction.

MySQL has four isolation levels:

  • Read Uncommitted: At this isolation level, all transactions can see the results of other Uncommitted transactions. Dirty reads may occur.
  • Read Committed: A transaction can only see the changes made by Committed transactions. Phantom reads can occur because other instances of the same transaction may have new commits during the processing of that instance.
  • Repeatable Read (RR) : This is MySQL’s default transaction isolation level and ensures that multiple instances of the same transaction will see the same rows when reading data concurrently. Eliminate dirty read, non – repeat read, the default will not appear magic read.
  • Serializable: This is the highest isolation level and solves the phantom read problem by forcing transactions to be ordered so that they cannot conflict with each other.

Dirty reading and phantom reading:

  • Dirty read: Reads uncommitted transactions.
  • Phantom read: a transaction re-reads previously retrieved data under the same query criteria, only to find that other transactions have inserted data that meets their query criteria.

Row locking at different transaction isolation levels

RC Isolation level

  • Query by non-index field: because there is no index, so can only go to the cluster index, the full table scan, all records on the cluster index, are locked. If a record cannot be quickly filtered through the index, the storage layer will tell all records to return after locking, and then the server layer will filter out the data that does not meet the conditions, and the records that do not meet the conditions will be locked. So lock all the records.

    Summary: In the absence of indexes, InnoDB’s current read locks all records. So you should pay special attention to this feature in your work, otherwise you can have a lot of lock conflicts.

  • Query by unique index:

    Select * from t16 where a=1 for update; (later called SQL2) select the index with column A for conditional filtering. After finding the record with column A =1, add X lock to the index with column A =1 on the unique index. At the same time, return to the cluster index based on the read ID column, and then add X lock to the cluster index with column ID =1.

    Summary: If the query condition is unique index, then SQL needs to lock on the unique index that meets the condition, and locks on the corresponding cluster index.

  • Query by non-unique index

    Summary: If the query criteria are non-unique indexes, then SQL needs to lock all non-unique indexes that meet the criteria, and locks their corresponding cluster indexes.

  • RC isolation level illusion

    At the RC isolation level, only the steps that satisfy c=3 are locked, and no subsequent positions (or gaps) are locked, resulting in write statements from other sessions executing and committing normally.

    Since a row lock can only hold the current row, the newly inserted record is the gap before the locked record. Therefore, to solve the illusionary problem, InnoDB configures a Gap Lock at RR isolation level.

RR Isolation Level

  • Non-unique index query at RR isolation level:

    Similar to the figure for the RC isolation level, but with one big difference: RR isolation level has a GAP lock.

    As shown in the figure above, we first need to consider where we can insert new items that satisfy the condition c=4:

    • The index of the B+ tree is ordered, so it is not possible to insert the index before [2,2].
    • [4,3] can be inserted between [2,2] and [4,4].
    • [4,5] can be inserted between [4,4] and [4,6].
    • After [4,6], many values can be inserted: [4,n](where n>6);

    To ensure that no new records that meet the condition c=4 are inserted into these intervals, the MySQL RR isolation level selects a GAP lock to lock these intervals.

  • Query non-index fields in RR isolation level:

    As shown in the figure, all records have an X lock. In addition, each GAP has a GAP lock. Select * from t17 where b=1 for update; Until commit, any locked SQL, except for unlocked snapshot reads, will wait, which would be a terrible thing if this were an online business table.

    Summary: Conditional current reads of non-indexed fields in RR isolation will not only lock every record with X, but also lock every GAP with GAP. Again, the importance of conditional fields with indexes.

  • Whether the GAP lock is used for the current read of the unique index in RR isolation

    The purpose of the GAP lock is to prevent the same transaction from being read twice. If you can ensure that the index field is unique, then in fact, an equivalent query, at most return one record, and the same index record value, must not be added, so there is no GAP lock.

    Therefore, a current read conditional on a unique index will not have a GAP lock. Therefore, the current read-lock status of the unique index at the RR isolation level is the same as that of the unique index at the RC isolation level.

A deadlock

A deadlock is a vicious cycle in which two or more transactions occupy the same resource and request to lock the resource occupied by the other.

There are two ways to resolve deadlocks in InnoDB:

  1. The innodb_deadlock_detect parameter is set to ON to enable this logic. If the innodb_deadlock_detect parameter is set to on, an error is returned.
  2. When the query time reaches the lock wait timeout, the lock request is abandoned. This timeout is controlled by innodb_lock_WAIT_timeout. The default is 50 seconds.

Generally, the first policy is recommended for online services, because the lock wait time of the second policy is 50 seconds, which is unacceptable for online services with high concurrency. However, the first strategy also has the additional CPU overhead of deadlock detection.

Deadlock condition occurs

This section talks about deadlocks. Several deadlock situations are listed through specific experiments:

  • Different threads concurrently access multiple rows of the same table, resulting in deadlocks if the data is not accessed in sequence.
  • When multiple tables are accessed concurrently by different threads, out-of-order access results in deadlocks.
  • At the RR isolation level, a gap lock caused a deadlock. Procedure

Reduces the probability of deadlocks

So how can you reduce the probability of deadlocks? Here are some lessons:

  1. Update SQL where conditions as far as possible with index;
  2. Update data based on primary or unique key;
  3. Reduce range updates, especially on non-primary key, non-unique indexes;
  4. Lock in the same order, as far as possible to lock all needed lines at once;
  5. Example Change the RR isolation level to the RC isolation level.

The way deadlock is analyzed

In InnoDB, you can use the SHOW InnoDB STATUS command to view information about the last deadlock. We can try using this command to obtain some deadlock information, as follows:

show engine innodb status
Copy the code