Introduction to row-level locking

MySQL row-level locking is implemented by the storage engine, using the storage engine to lock index entries to achieve.

  • InnoDB row-level locks are divided into three types according to the lock scope:
- Record Locks: Locks a Record in an index. Id =1 - Gap Locks: either Locks the value in the middle of the index record, the value before the first index record, or the value after the last index record. - Next-key Locks: a combination of record Locks on the index record and the Gap Locks before the index recordCopy the code
  • InnoDB row-level locks are functionally divided into two types: RR
- Shared lock (S) : allows one transaction to read a row, preventing other transactions from acquiring the exclusive lock of the same data set. - Exclusive lock (X) : allows a transaction to update the data, preventing other transactions from acquiring the shared read lock (not read) and exclusive write lock of the same data setCopy the code

For UPDATE, DELETE, and INSERT statements, InnoDB automatically assigns an exclusive lock (X) to the data set involved; InnoDB does not add any locks to normal SELECT statements. Transactions can be displayed with shared or exclusive locks on recordsets.

  • Manually add a share (S) :
SELECT * FROM table_name WHERE. LOCKIN SHARE MODE
Copy the code
  • Manually add exclusive lock (X) :
SELECT * FROM table_name WHERE.FOR UPDATE
Copy the code
  • InnoDB also implements table-level locking, also known as intentional locking, which is used internally by mysql without user intervention.
- Intentional shared lock (IS) : a transaction intends to lock a row and must acquire the IS lock on that table before it can lock a row. - Intentional exclusive lock (IX) : a transaction intends to lock a row and must acquire the IX lock on that table before it can lock a rowCopy the code
  • Intent locks and row locks can coexist. The main purpose of intent locks is to improve the performance of [full table update data]. Otherwise, when updating data in the full table, you need to check whether the table has row locks on some records
A Shared lock (S) Exclusive lock (X) Intended Shared Lock (IS) Intentional Exclusive Lock (IX)
A Shared lock (S) Compatible with conflict Compatible with conflict
Exclusive lock (X) conflict conflict conflict conflict
Intended Shared Lock (IS) Compatible with conflict Compatible with Compatible with
Intentional Exclusive Lock (IX) conflict conflict Compatible with Compatible with

  • InnoDB locks rows by locking index entries, so InnoDB uses row locks only for data retrieved by index criteria. Otherwise, InnoDB uses table locks!

  • Innodb row-level lock contention status

 show status like 'innodb_row_lock%';
Copy the code

-innodb_ROW_LOCK_CURRENT_WAITS: Number of current_waits currently being held; - Innodb_row_lock_time: specifies the total lock time from system startup to now. - Innodb_row_lock_time_avg: indicates the average time spent on each wait. - Innodb_row_lock_time_max: the time spent waiting for the most common time since system startup; - Innodb_row_lock_waits: Total number of waits since system startup;Copy the code

For these 5 state variables, the most important ones are:

- Innodb_row_lock_time_avg (average wait time) - Innodb_row_lock_waits (total wait times) - Innodb_row_lock_time (total wait timeCopy the code

Especially when the number of waits is very high and the waiting time is not small, we need to analyze why there are so many waits in the system, and then start to specify the optimization plan according to the analysis results

Two phase lock

One of the principles of traditional RDBMS Locking is 2PL (two-phase Locking). The lock operation is divided into two phases: lock and unlock, and the lock and unlock phases do not intersect. Let’s take a look at the implementation of 2PL in MySQL again.

2PL splits locking/unlocking into two completely disjoint phases.

Lock stage: lock only, lock not release Unlock stage: lock only, lock not releaseCopy the code

Clearance lock

There are two cases of gap lock: 1. Prevent data from being inserted into the gap; 2. Prevent existing data from being updated to the data in the gap