Five, MySQL lock mechanism

5. MySQL lock mechanism

1 Overview

A lock is a mechanism for a computer to coordinate multiple processes or threads to concurrently access a certain resource.

In the database, in addition to contention for computer hardware resources (CPU, RAM, I/O, etc.), data is also a resource shared by many users. How to ensure the consistency and effectiveness of concurrent access data is a problem that all databases must solve. Lock conflicts are also an important factor that affects the performance of concurrent access to the database. Therefore, locks are especially important and more complicated for databases.

For example, when we rush to buy goods on Taobao, when two customers rush to buy a product at the same time, transactions and locks must be used in the back-end database. Through a unified transaction transaction and database lock mechanism for the entire order process, it is guaranteed that the product will not Was oversold.

1.1 Classification of locks

  • Database operation type classification (read/write)
    • Read lock (shared lock): For the same piece of data, multiple read operations can be performed at the same time without affecting each other;
    • Write lock (exclusive lock): before the current operation is completed, other write locks and read locks will be blocked;
  • Granularity of data operations:
    • Table lock: lock the data table
    • Row lock: lock the data row of the operation

Overhead, locking speed, deadlock, granularity, and concurrency performance can only be used to explain which lock is more appropriate based on the characteristics of specific applications.

Here is a recommended blog about MySQL locks. MySQL lock mechanism and locking principle

2. Table lock (biased reading)

2.1 Features

Biased read operation, biased

MYISAM
Storage engine, low overhead, fast locking, no deadlock, large locking granularity, the highest probability of lock conflicts, and the lowest concurrency

2.2 Example

2.2.1 Create a database

use big_data; create table mylock ( id int not null primary key auto_increment, name varchar ( 20 ) default '' ) engine myisam; insert into mylock(name) values ( 'a' ); insert into mylock(name) values ( 'b' ); insert into mylock(name) values ( 'c' ); insert into mylock(name) values ( 'd' ); INSERT INTO mylock (name) values ( 'E' ); duplicated code

View the created database

mysql> use base_crud; Database changed mysql> select * from mylock; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +----+------+ 5 rows in set (0.00 sec) Copy code

Basic operation of database lock

  • Manually add table locks:

    lock table table name 1 read(write), table name 2 read(write), others;

  • View the locked table:

    show open tabels

  • Manually unlock:

    unlock tables

2.2.2 Add a read lock

mylock
Add a read lock to the table and open two session windows at the same time

session1 window

# Add to mylock read lock table MySQL> Lock Table mylock Read ; Query OK, 0 rows affected (0.00 sec) # Inquiry mylock table MySQL> the SELECT * from mylock; +----+------+ | id | name | +----+------+ | 1 | a1 | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +----+------+ 5 rows in set (0.00 sec) # Update table mylock MySQL> Update mylock SET name = 'A' WHERE ID =. 1; ERROR 1099 (HY000): Table'mylock' was locked with a READ lock and can't be updated # Inquiry book table MySQL> the SELECT * from book; ERROR 1100 (HY000): Table'book' was not locked with LOCK TABLES # Update the book table MySQL> Update book SET Card = 10 WHERE BookID =. 1; ERROR 1100 (HY000): Table'book' was not locked with LOCK TABLES # Release lock MySQL> UNLOCK the Table; Query OK, 0 rows affected (0.00 sec) Copy code

session2 window

# Inquiry mylock table MySQL> the SELECT * from mylock; +----+------+ | id | name | +----+------+ | 1 | a1 | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +----+------+ 5 rows in set (0.00 sec) # Inquiry book table MySQL> the SELECT * from book; +--------+------+ | bookid | card | +--------+------+ | 4 | 2 | | 25 | 2 | | 36 | 2 | | 23 | 3 | | 38 | 4 | | 15 | 6 | | 16 | 6 | | 24 | 6 | | 39 | 6 | | 34 | 20 | +--------+------+ 10 rows in set (0.00 sec) # Update the book table MySQL> Update book SET Card = 10 WHERE BookID =. 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 # Update table mylock MySQL> Update mylock SET name = 'A3' WHERE ID =. 1; Query OK, 1 row affected (28.08 sec) Rows matched: 1 Changed: 1 Warnings: 0 Copy code

Since session1 is in

mylock
Adding a read lock to the table causes the queue to be blocked. Session2 can only be
mylock
The table is updated, so the waiting time for the update operation is very long.

Summary of table operations in different sessions after adding a read lock

Table operationsCurrent sessionOther session
Read a table with a read lockYESYES
Read a table without a read lockNOYES
Update/insert table with read lockNOBlocking wait
Update/insert table without read lockNOYES
Delete the table with read lock operationNOBlocking wait
Delete the table without read lockNOYES

2.2.3 Add write lock

mylock
Add a write lock to the table and open two session windows at the same time

session1 window

# 1.1 Add a write lock to the mylock table mysql> lock table mylock write; Query OK, 0 rows affected (0.00 sec) # 1.2 Query mylock table mysql> select * from mylock; +----+------+ | id | name | +----+------+ | 1 | a3 | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +----+------+ 5 rows in set (0.01 sec) # 1.2 Query book table mysql> select * from book; ERROR 1100 (HY000): Table'book' was not locked with LOCK TABLES # 2.1 Update mylock table mysql> update mylock set name = 'b1' where id = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 2.2 Query the mylock table after the update mysql> select * from mylock; +----+------+ | id | name | +----+------+ | 1 | a3 | | 2 | b1 | | 3 | c | | 4 | d | | 5 | e | +----+------+ 5 rows in set (0.00 sec) # 3.1 Update the book table mysql> update book set card = 22 where bookid = 1; ERROR 1100 (HY000): Table'book' was not locked with LOCK TABLES # 3.2 Release write lock mysql> unlock table; Query OK, 0 rows affected (0.00 sec) Copy code

session2 window

# 1.1 Query mylock table mysql> select * from mylock; +----+------+ | id | name | +----+------+ | 1 | a3 | | 2 | b1 | | 3 | c3 | | 4 | d | | 5 | e | +----+------+ 5 rows in set (12.18 sec) # 1.2 Query book table mysql> select * from book; +--------+------+ | bookid | card | +--------+------+ | 4 | 2 | | 25 | 2 | | 36 | 2 | | 23 | 3 | | 38 | 4 | | 15 | 6 | | 16 | 6 | | 24 | 6 | | 39 | 6 | | 34 | 20 | +--------+------+ 10 rows in set (0.00 sec) # 1.3 Update mylock table mysql> update mylock set name = 'c3' where id = 3; Query OK, 1 row affected (10.42 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 1.4 Update the book table mysql> update book set card = 2222 where bookid = 4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 1.5 Query the book table after the update mysql> select * from book; +--------+------+ | bookid | card | +--------+------+ | 25 | 2 | | 36 | 2 | | 23 | 3 | | 38 | 4 | | 15 | 6 | | 16 | 6 | | 24 | 6 | | 39 | 6 | | 34 | 20 | | 4 | 2222 | +--------+------+ 10 rows in set (0.00 sec) Copy code

Since session1 is in

mylock
Adding a write lock to the table causes the queue to be blocked. Only when session1 releases the read lock, session2 can
mylock
Table query, update, insert, delete operations, blocking waiting time is very long.

Summary of table operations in different sessions after adding a write lock

Table operationsCurrent sessionOther session
Read the write-locked tableYESBlocking wait
Read the table without write lockNOYES
Update/insert table with write lockYESBlocking wait
Update/insert table without write lockNOYES
Delete the write-locked tableYESBlocking wait
Delete the table without write lockNOYES

2.2.4 Example summary

MYISAM
Executing query
SELECT
Before, it will automatically add read locks to all the tables involved ; before performing the addition, deletion, and modification operations, it will automatically add write locks to all the tables involved .

MySQL table-level lock has two modes:

  • Table shared read lock (Table Read Lock)
  • Table Exclusive Write Lock (Table Write Lock)

To use

MyISAM
The following situations will occur when the table of the file storage engine is operated:

  • Correct
    MyISAM
    Table read operations (add read locks) will not block other processes' read requests for the same table, but will block write requests for the same table. When the read lock is released, the write operation of other processes will be executed;
  • Correct
    MyISAM
    Table write operations (add write lock) will block other processes from reading and writing the same table. Only after the write lock is released, can the read and write operations of other processes be performed;
  • In short, read locks will block write operations, but not read operations. The write lock will block both read and write operations;

2.3 Table lock analysis

View those tables locked in the database:

show open tables;

1: Indicates lock

0: means no lock

Analysis table lock

Use the command:

show status like'table%'

mysql> show status like'table %' ; +-----------------------+--------+ | Variable_name | Value | +-----------------------+--------+ | Table_locks_immediate | 510836 | | Table_locks_waited | 5 | +-----------------------+--------+ 2 rows in set (0.00 sec) Copy code

Parameter Description:

  • Table_locks_immediate
    : The number of times the table-level lock is generated, which means the number of lock queries that can be obtained immediately, and the value of the lock is increased by 1 every time the lock is obtained immediately;
  • Table_locks_waited
    : The number of waits for table-level lock contention (the number of times that the lock cannot be acquired immediately, the lock value is increased by 1 for each wait). A high value indicates that there is a serious table-level lock contention;

MyISAM
The read-write lock scheduling is write first, so it is not suitable for writing to the main table engine. Because after locking, other threads can't do any operations, a large number of updates will make it difficult for queries to obtain locks, which will cause query blocking.

3. Row lock (biased to write)

3.1 Features

Bias

InnoDB
The storage engine is expensive and slow to lock; there will be deadlock problems, the granularity of the lock is small, the probability of lock conflicts is the lowest, and the concurrency is the highest.

InnoDB
Engine and
MyISAM
The biggest difference in engines:
InnoDB
Support transaction (transaction) and row-level locks.

3.2 Affairs related

3.2.1 Transactions and their ACID attributes

A transaction is a logical processing unit composed of a set of SQL statements. A transaction has the following four attributes, usually referred to as the ACID attribute of the transaction

Atomicity : A transaction is an atomic operation. Any modification of data is executed or not executed at all;

Consistency (Consistent) : At the beginning and completion of a transaction, the data must be in a consistent state. This means that all relevant data rules must be applied to the modification of the transaction to maintain the integrity of the data; at the end of the transaction, all internal data structures (such as: B-tree index or doubly linked list) must also be correct;

Isolation (Isolation) : The database system provides a certain isolation mechanism to ensure that transactions are not affected by external concurrent operations to be executed in an **"independent environment"**. This means that the intermediate state of the transaction is invisible to the outside, and vice versa;

Durable : After the transaction is completed, the modification of the data is permanent and can be maintained even if there is a system failure;

3.2.2 Transaction problems caused by concurrency

Lost Update

When two or more transactions select the same row and update the current row based on the initially selected value, since each transaction does not know the existence of other transactions, the last update overwrites the update made by other transactions, resulting in a lost update The problem.

Time sliceTransaction ATransaction B
T1Start transaction
T2Start transaction
T3Check account balance 300 yuan
T4Check account balance 300 yuan
T5The account recharges 200 yuan and the balance is 500 yuan
T6Commit transaction
T7100 yuan consumption, 200 yuan balance
T8Undo transaction
T9The balance is 300 yuan

Transaction A commits the transaction last, causing the transaction commit of transaction B to be overwritten, causing the data updated by transaction B to be lost

Dirty Reads

Transaction A is making changes to a record. Before this transaction is committed, the data of this record is in a pending state (may be submitted or rolled back); at this time, transaction B also reads the pending state data, and Further processing of the data will result in data dependency. This phenomenon is called **"dirty read"**.

In short, transaction B reads the modified but not committed data of transaction A, and performs operations on the basis of this data. At this time, transaction A rolls back, and the data read by transaction B is invalid and does not meet the consistency requirements.

Non-Repeatable Reads

A transaction reads the same record one after another, but the data is modified or deleted by other transactions between the two reads of the transaction. The data read twice is inconsistent or cannot be read. This phenomenon is called **"unable" Repeat "**.

In short, transaction A reads the modified data submitted by transaction B, which does not meet isolation.

Phantom Reads

A transaction reads the previously retrieved data under the same query conditions, but finds that other transactions have inserted new data that meets its query conditions. This phenomenon is called **"phantom reading"**

In short, transaction A reads the new data submitted by transaction B, which does not meet isolation.

Comparison of phantom reading and dirty reading:

  • Dirty read is the modification of data in transaction B;
  • The phantom read is the new data in transaction B;

Comparison of phantom reading and non-repeatable reading:

  • The key point of non-repeatable reading is to modify: the same condition, two readings will find that the value is different;
  • The key point of the phantom reading is to add or delete: the same condition, the number of records obtained from the two readings is different;

3.3.3 Transaction isolation level

DEFAULT
:

The default level, the isolation level is determined by the DBA default setting, which belongs to one of the following:

READ_UNCOMMITTED
:

That is, a transaction can read the data of another uncommitted transaction.

Dirty reads, non-repeatable reads, and phantom reads will appear (the isolation level is the lowest, but the concurrency is high)

The lowest level can only guarantee that the physically damaged data will not be read;

READ_COMMITTED
:

That is, a transaction can only read data after another transaction is submitted to solve the dirty read problem.

There will be non-repeatable read, phantom read problems (lock the row being read, suitable for most systems, Oracle default level)

Statement level

REPEATABLE_READ
:

That is, when the data is started to be read (the transaction is opened), the modification operation is no longer allowed to solve the problem of non-repeatable reading.

There will be phantom reading problems (lock all rows read, MYSQL default level)

Transaction level

SERALZABLE
:

It is the highest transaction isolation level. Under this level, transactions are executed serially in order to avoid dirty reads, non-repeatable reads, and phantom reads.

However, this transaction isolation level is inefficient and consumes database performance, so it is generally not used. (Lock the entire table)

The highest level, transaction level;

The transaction isolation level is increased from top to bottom. The higher the isolation level, the better the integrity and consistency of the data can be guaranteed. However, the consumption of database performance increases successively, and the efficiency of concurrent execution decreases successively.

Transaction isolation essentially makes transactions "serial" to a certain extent , which is actually contradictory to concurrency. At the same time, different applications have different levels of read consistency and transaction isolation. Some applications may be insensitive to "non-repeatable reads" and "phantom reads"**, and are more concerned about the concurrent processing capabilities of data.

View the transaction isolation level of the current database:

show variables like'tx_isolation'

The default isolation level for most databases is

Read Commited
,such as
SqlServer
,
Oracle

The default isolation level for a few databases is:

Repeatable Read
such as:
MySQL InnoDB

The relationship between the isolation level of the transaction and the problems that arise in concurrency

Dirty readsnon-repeatable readsphantom reads
READ_UNCOMMITTEDYYY
READ_COMMITTEDNYY
REPEATABLE_READNNY
SERALZABLENNN

3.3 Example

3.3.1 Table building statement

- Create a database CREATE TABLE test_innodb_lock (a INT ( 11 ), b VARCHAR ( 16 )) ENGINE = INNODB; INSERT INTO test_innodb_lock VALUES ( 1 , 'b2' ); INSERT INTO test_innodb_lock VALUES ( 3 , '3' ); INSERT INTO test_innodb_lock VALUES ( 4 , '4000' ); INSERT INTO test_innodb_lock VALUES ( 5, '5000' ); INSERT INTO test_innodb_lock VALUES ( 6 , '6000' ); INSERT INTO test_innodb_lock VALUES ( 7 , '7000' ); INSERT INTO test_innodb_lock VALUES ( 8 , '8000' ); INSERT INTO test_innodb, VALUES ( 9 , ' 7000' ); 9000' ); INSERT INTO test_innodb_lock VALUES ( 1 , 'b1'); - Create index CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a); CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b); - Turn off automatic submission SET autocommit = 0 ; Copy code

View the created database

mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | 3 | | 4 | 4000 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec) Copy code

3.3.2 Row lock example

session 1 window

# 1.1 Update data mysql> update test_innodb_lock set b = 'b3' where a = 3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 1.2 Query data mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | b3 | | 4 | 4000 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec) # 1.3 Submit query mysql> commit; Query OK, 0 rows affected (0.01 sec) # 2.1 Update row data mysql> update test_innodb_lock set b = '3000' where a=4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 2.2 Query after update mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | b3 | | 4 | 3000 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec) # 2.3 Submit mysql after update > commit; # 2.4 Query after submit mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | b3 | | 4 | 4000 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec) # 2.5 session 2 Submit after submitting mysql> commit; Query OK, 0 rows affected (0.00 sec) # 2.6 Query after submission mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | b3 | | 4 | b4 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec) # 3.1 Update a=5 mysql> update test_innodb_lock set b = '5005' where a=5; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 3.2 Commit the transaction mysql> commit; Query OK, 0 rows affected (0.01 sec) # 3.3 Query after submission mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | b3 | | 4 | b4 | | 5 | 5005 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9009 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec) Copy code

session 2 window

# 1.1 session 1 unsubmitted session 2 query mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | 3 | | 4 | 4000 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec) # 1.2 After session 1 is submitted, session 2 is submitted mysql> commit; Query OK, 0 rows affected (0.00 sec) # 1.3 After both session 1 and session 2 are submitted, query mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | b3 | | 4 | 4000 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec) # 2.1 Update row data, session 1 has not yet been submitted, and there will be blocking at this time. Session 2 can only be updated after session 1 is submitted mysql> update test_innodb_lock set b = 'b4' where a=4; Query OK, 1 row affected (15.21 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 2.2 session 1 Submit the transaction after submitting mysql> commit; Query OK, 0 rows affected (0.01 sec) # 2.3 Query data mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | b3 | | 4 | b4 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec) # 3.1 Update a=9 mysql> update test_innodb_lock set b = '9009' where a=9; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 3.2 Commit the transaction mysql> commit; Query OK, 0 rows affected (0.00 sec) # 3.3 Query after submission mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | b3 | | 4 | b4 | | 5 | 5005 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9009 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec) Copy code

3.3.3 Upgrade of index invalid row lock to table lock

session 1 window

# 1.1 Query current data mysql> select * from test_innodb_lock; +------+-------+ | a | b | +------+-------+ | 1 | 10010 | | 3 | b3 | | 4 | b4 | | 5 | 5005 | | 6 | 6000 | | 7 | 7007 | | 8 | 8008 | | 9 | 10010 | | 1 | 10010 | +------+-------+ 9 rows in set (0.00 sec) # 1.2 Update the row data where b=10010 is located mysql> update test_innodb_lock set a=20 where b=10010; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 # 1.3 Commit the transaction mysql> commit; Query OK, 0 rows affected (0.02 sec) # 1.4 Query after submission mysql> select * from test_innodb_lock; +------+-------+ | a | b | +------+-------+ | 20 | 10010 | | 3 | 3000 | | 4 | b4 | | 5 | 5005 | | 6 | 6000 | | 7 | 7007 | | 8 | 8008 | | 20 | 10010 | | 20 | 10010 | +------+-------+ 9 rows in set (0.00 sec) Copy code

session 2 window

# 1.1 Query current data mysql> select * from test_innodb_lock; +------+-------+ | a | b | +------+-------+ | 1 | 10010 | | 3 | b3 | | 4 | b4 | | 5 | 5005 | | 6 | 6000 | | 7 | 7007 | | 8 | 8008 | | 9 | 10010 | | 1 | 10010 | +------+-------+ 9 rows in set (0.00 sec) # 1.2 Session 1 was not submitted after the update. The row lock became invalid due to the index failure of session 1 and became a table lock. Session 2 was blocked when updating different rows mysql> update test_innodb_lock set b= '3000' where a=3; Query OK, 1 row affected (9.93 sec) Rows matched: 1 Changed: 1 Warnings: 0 # session 1 Submit after submission mysql> commit; Query OK, 0 rows affected (0.01 sec) # After submitting a query MySQL> the SELECT * from test_innodb_lock; +------+-------+ | a | b | +------+-------+ | 20 | 10010 | | 3 | 3000 | | 4 | b4 | | 5 | 5005 | | 6 | 6000 | | 7 | 7007 | | 8 | 8008 | | 20 | 10010 | | 20 | 10010 | +------+-------+ 9 rows in set (0.00 sec) Copy code

Summary of row lock examples:

  • The current session does not submit the updated data, and other sessions cannot see the updated data;
  • The current session updates a row of data without submitting it, and other sessions will block when updating the same row of data;
  • When the current session and other sessions update different data rows, there will be no blocking;
  • Index failure will cause row locks to become table locks;

3.3.4 Gap lock

When we use range queries instead of equal conditions to retrieve data and request shared locks or exclusive locks, InnoDB will lock the index entries of existing data that meet the conditions; for records whose key values are within the condition range but do not exist, Called **"Gap" , InnoDB will also lock the "Gap" . This lock mechanism is called "Gap Lock (Next-Key Lock)"**.

The hazards of gap lock:

If you search through the range during Query execution, all index key values in the entire range will be locked, even if this value does not exist.

One of the fatal weaknesses of the gap lock is that when a range value is locked, even some non-existent key values will be locked, which will cause any data within the locked key value range to be unable to be inserted when locked. In some scenarios, performance may be greatly affected.

session 1 window

# 1.1 Update 1 <a <6 line b is a Test MySQL> Update test_innodb_lock SET b = 'Test' WHERE A>. 1 and A <. 6; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 # 1.2 Commit the transaction mysql> commit; Query OK, 0 rows affected (0.00 sec) # 1.3 Query after submission mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | test | | 4 | test | | 5 | test | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec) # 2.1 After session 2 is submitted, submit mysql> commit; Query OK, 0 rows affected (0.00 sec) # 2.2 Query after submission mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | test | | 4 | test | | 5 | test | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | | 2 | 2000 | +------+------+ 10 rows in set (0.00 sec) # 3. Lock a row # 3.1 Mark the starting point mysql> begin; Query OK, 0 rows affected (0.00 sec) # 3.2 Query lock mysql> select * from test_innodb_lock where a=8 for update; +------+------+ | a | b | +------+------+ | 8 | 8000 | +------+------+ 1 row in set (0.00 sec) # 3.3 Commit the transaction mysql> commit; Query OK, 0 rows affected (0.00 sec) # 3.4 Session 2 Submit after submitting mysql> commit; Query OK, 0 rows affected (0.00 sec) # 3.5 Query after submission mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | test | | 4 | test | | 5 | test | | 6 | 6000 | | 7 | 7000 | | 8 | xxxx | | 9 | 9000 | | 1 | b1 | | 2 | 2000 | +------+------+ 10 rows in set (0.00 sec) Copy code

session 2 window

# 1.1 Session 1 updates the range data, causing a gap lock to block. After session 1 is submitted, execute mysql> insert into test_innodb_lock values(2, '2000' ); Query OK, 1 row affected (7.61 sec) # 1.2 After session 1 is submitted, submit mysql> commit; Query OK, 0 rows affected (0.01 sec) # 1.3 Query after submission mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | test | | 4 | test | | 5 | test | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | | 2 | 2000 | +------+------+ 10 rows in set (0.00 sec) # 3. Lock a row # 3.1 Update a row, because the row is locked during the session 1 query, the execution is blocked. Execute only after session 1 is submitted mysql> update test_innodb_lock set b = 'xxxx' where a=8; Query OK, 1 row affected (7.90 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 3.2 Commit the transaction mysql> commit; Query OK, 0 rows affected (0.00 sec) # 3.3 Query after submission mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | test | | 4 | test | | 5 | test | | 6 | 6000 | | 7 | 7000 | | 8 | xxxx | | 9 | 9000 | | 1 | b1 | | 2 | 2000 | +------+------+ 10 rows in set (0.00 sec) Copy code

Interview question: How to lock rows?

select xxxx... for update
After a row is locked, other operations will be blocked until the session that locks the row is submitted.

3.3.5 Summary

InnoDB
Because the storage engine implements row-level locking, although the performance loss caused by the implementation of the locking mechanism is more than that of the table-level lock, it is far better than the overall concurrent processing capacity
MyISAM
The table level is locked. When the system concurrency is relatively high,
InnoDB
Overall performance and
MyISAM
Compared with this, there will be obvious advantages.

InnoDB
The row-level lock also has problems. When we use it improperly, it may cause
InnoDB
Overall performance ratio
MyISAM
worse.

3.4 Row lock analysis

pass from checking

InnoDB_row_lock
State variables to analyze row lock contention on the system

View command

show status like'%innodb_row_lock%'

mysql> show status like'innodb_row_lock%' ; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 91944 | | Innodb_row_lock_time_avg | 18388 | | Innodb_row_lock_time_max | 51296 | | Innodb_row_lock_waits | 5 | +-------------------------------+-------+ 5 rows in set (0.00 sec) Copy code

State variable description:

Innodb_row_lock_current_waits
: The number currently waiting to be locked;

Innodb_row_lock_time
: The total time since the system is started to the current lock;

Innodb_row_lock_time_avg
: Average waiting time each time;

Innodb_row_lock_time_max
The time taken from the system startup to the longest waiting time;

Innodb_row_lock_waits
: The total number of waiting times since the system was started up to now;

The more important of the five state variables:

Innodb_row_lock_time_avg
: Average waiting time each time;

Innodb_row_lock_waits
: The total number of waiting times since the system was started up to now;

Innodb_row_lock_time
: The total time since the system is started to the current lock;

Especially when the current waiting times are high and the waiting time is long each time, it is necessary to analyze the reasons for multiple waiting in the system and specify an optimization plan based on the analysis results.

3.5 Optimization suggestions

As far as possible, all data retrieval is completed through the index, to avoid the upgrade of non-index row locks to table locks;

Design the index reasonably to minimize the scope of the lock;

Reduce retrieval conditions as much as possible to avoid gap locks;

Try to control the size of the transaction, reduce the amount of locked resources and the length of time;

Use low-level transaction isolation as much as possible;

4. Page Lock

Page-level locks are a type of lock that has a locking granularity between row-level locks and table-level locks in MySQL. Table-level locking is fast, but there are many conflicts, and row-level conflicts are few, but the speed is slow. Therefore, a compromised page level is adopted, and a set of adjacent records is locked at a time. BDB supports page-level locking.