1. An overview of the

A lock is a mechanism by which a computer coordinates the concurrent access of multiple processes or threads to a resource.

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

For example, when we buy a commodity on Taobao, when two customers buy a commodity at the same time, transaction and lock must be used in the background database. Through the unified transaction delivery and database lock mechanism for the whole ordering process, to ensure that the commodity will not be oversold.

1.1 Classification of locks

  • Database operation Type classification (read/write)
    • Read lock (shared lock) : Multiple read operations can be performed simultaneously for the same data without affecting each other.
    • Write lock (exclusive lock) : Blocks other write locks and read locks until the current operation is complete.
  • Granularity of operations on data:
    • Table lock: Locks data tables
    • Row lock: Locks the data rows of an operation

Overhead, locking speed, deadlocks, granularity, and concurrency performance only depend on the characteristics of the specific application.

MySQL lock mechanism and lock principle

2. Table lock (biased read)

2.1 the characteristics of

Biased read operation, biased to MYISAM storage engine, low overhead, fast lock, no deadlock, large lock granularity, the highest probability of lock conflict, the lowest concurrency

2.2 the sample

2.2.1 Establishing 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');
Copy the 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 the code

Basic operations for database locks

  • Add table lock manually:

    Lock table name 1 read(write), table name 2 read(write), other;

  • View locked tables:

    show open tabels

  • Manual unlock:

    unlock tables

2.2.2 Adding a Read lock

Add read lock to myLock table and open two session Windows simultaneously

Session1 window

#Add read lock to myLock table
mysql> lock table mylock read;
Query OK, 0 rows affected (0.00 sec)

#Query mylock table
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a1   |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.00 sec)

#Update mylock table
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

#Query book table
mysql> 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 the lock
mysql> unlock table;
Query OK, 0 rows affected (0.00 sec)

Copy the code

Session2 window

#Query mylock table
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a1   |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.00 sec)

#Query book table
mysql> select * from book;+--------+------+ | bookid | card | +--------+------+ | 4 | 2 | | 25 | 2 | | 36 | 2 | | 23 | 3 | | 38 | 4 | | 15 | 6 | | 16 6 | | | | | 6 24 39 6 | | | | | | 20 + 34 -- -- -- -- -- -- -- - + -- -- -- -- -- - + 10 rows in the set (0.00 SEC)
#Update the book table
mysql> update book set card = 10 where bookid = 1;Query OK, 1 row affected (0.01sec) Rows matched: 1 Changed: 1 Warnings: 0
#Update mylock table
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 the code

Session2 can update mylock only when session1 releases the read lock, so it takes a long time to update mylock.

Summary of table operations performed by different sessions after read lock

Table operation The current session Other session
Read a table with a read lock YES YES
Read a table without a read lock NO YES
Update/insert table with read lock NO Block waiting for
Update/insert tables without read locks NO YES
Delete table with read lock NO Block waiting for
Delete a table without a read lock NO YES

2.2.3 Adding a write Lock

Add write lock to myLock table and open two session Windows simultaneously

Session1 window

#Alter table myLock add write lock to mylock
mysql> lock table mylock write;
Query OK, 0 rows affected (0.00 sec)

#1.2 Querying 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 Querying the 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 mylock table after 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 Releasing the write lock
mysql> unlock table;
Query OK, 0 rows affected (0.00 sec)
Copy the code

Session2 window

#1.1 Querying 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 Querying the Book Table
mysql> select * from book;+--------+------+ | bookid | card | +--------+------+ | 4 | 2 | | 25 | 2 | | 36 | 2 | | 23 | 3 | | 38 | 4 | | 15 | 6 | | 16 6 | | | | | 6 24 39 6 | | | | | | 20 + 34 -- -- -- -- -- -- -- - + -- -- -- -- -- - + 10 rows in the 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 Updating 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 update
mysql> select * from book;+--------+------+ | bookid | card | +--------+------+ | 25 | 2 | | 36 | 2 | | 23 | 3 | | 38 | 4 | | 15 | 6 | | 16 | 6 | 24 6 | | | | | | 6 39 34 20 | | | | | 2222 | + -- -- -- -- -- -- -- - + -- -- -- -- -- - + 10 rows in the set (0.00 SEC)Copy the code

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Summary of table operations in different sessions after write lock

Table operation The current session Other session
Read a table with a write lock YES Block waiting for
Read tables without write locks NO YES
Update/insert table with write lock YES Block waiting for
Update/insert tables without write locks NO YES
Delete table with write lock YES Block waiting for
Delete a table without a write lock NO YES

2.2.4 Summary of examples

MYISAM will automatically lock all tables involved before executing the SELECT statement. Write locks are automatically added to all tables involved before the add, delete, or modify operation is performed.

MySQL table level lock has two modes:

  • Table Read Lock
  • Table Write Lock Table Write Lock

Operating on a table using the MyISAM file storage engine produces the following:

  • rightMyISAMRead operations (read locks) on a table do not block other processes’ read requests on the same table, but block write requests on the same table. Write operations of other processes are performed only after the read lock is released.
  • rightMyISAMWrite operations (add a write lock) on a table block other processes’ read and write operations on the same table. Only after the write lock is released, other processes can perform read and write operations on the same table.
  • In short, read locks block writes, but not reads. Write locks block both read and write operations.

2.3 Table lock analysis

Show open tables;

1: locks

0: no lock is added

Analysis table locking

Run the show status like ‘table%’ command.

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

Parameter Description:

  • Table_locks_immediate: number of times that table level locks are generated. It is the number of times that lock queries can be obtained immediately.
  • Table_locks_waited: Indicates the number of wait times due to table-level lock contention (the number of wait times for which the lock cannot be obtained immediately, and the value of each wait is increased by 1). If the value is high, serious table-level lock contention exists.

MyISAM’s read-write lock scheduling is write-first, so it is not suitable for an engine that writes to a master table. Because no other thread can do anything with the lock, a large number of updates can make it difficult for the query to acquire the lock, causing the query to block.

3. Row locking (biased to write)

3.1 the characteristics of

Prefer InnoDB storage engine, high overhead, slow lock; Deadlock problem will occur, lock granularity is small, the probability of lock conflict is the lowest, concurrency is the highest.

The biggest difference between InnoDB and MyISAM is that InnoDB supports transaction and row-level locking.

3.2 Transaction correlation

3.2.1 Transactions and their ACID properties

A transaction is a logical processing unit consisting of a set of SQL statements, and a transaction has the following four properties, often referred to simply as the ACID property of the transaction

Atomicity: A transaction is an atomic operation in which changes to data are either all or none performed;

Consistent: Data must be Consistent at the start and completion of a transaction. This means that all relevant data rules must be applied to transaction modifications to preserve data integrity; At the end of the transaction, all internal data structures (such as B-tree indexes or bidirectional linked lists) must also be correct;

Isolation: Database systems provide some kind of Isolation mechanism to ensure that transactions are not affected by external concurrent operations ** “isolated environment”. This means that the intermediate state of a transaction is not visible to the outside world and vice versa;

Durable: Data modification after transaction completion is permanent and can be maintained even in case of system failure.

3.2.2 Transaction problems caused by concurrency

Lost Update

When two or more transactions select the same row and move forward based on the value originally selected, the problem of lost updates occurs because each transaction is unaware of the existence of the other transactions and the last update overwrites the update made by the other transactions.

Time slice A transaction Transaction B
T1 Start the transaction
T2 Start the transaction
T3 Query the account balance of 300 yuan
T4 Query the account balance of 300 yuan
T5 The account was topped up with 200 yuan and the balance was 500 yuan
T6 Commit the transaction
T7 I spent $100 and had a balance of $200
T8 Cancel the transaction
T9 The balance of 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 modifying A record whose data is in the pending state (either committed or rolled back) until the transaction is committed. At this point, transaction B also reads the pending data and performs further processing on the data. This phenomenon is called ** “dirty read” **.

In short, transaction B reads data that transaction A has modified but not committed, and acts on that data. In this case, transaction A rolls back, and transaction B reads invalid data, which does not meet the consistency requirements.

Non-repeatable Reads

A transaction reads the same record consecutively, but the data is modified or deleted by other transactions between the two reads, and the two reads are inconsistent or unreadable. This phenomenon is called ** “non-repeatable read” **.

In short, transaction A reads the modified data that transaction B has already committed, which does not comply with isolation.

Phantom Reads

A transaction that re-reads previously retrieved data under the same query criteria only to find that other transactions insert new data that meets its query criteria is called ** “phantom read” **

In short, transaction A reads the new data submitted by transaction B and does not comply with isolation.

Phantom and dirty read comparison:

  • A dirty read is a data modification in transaction B;
  • Phantom read is new data in transaction B;

Phantom and unrepeatable read comparison:

  • The emphasis of non-repeatable read is to modify: the same condition, two read values are not the same;
  • The key of illusory reading is to add or delete: under the same condition, the number of records obtained by two reads is not the same;

3.3.3 Transaction isolation level

1. The DEFAULT:

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

(2) READ_UNCOMMITTED:

A transaction can read data from another uncommitted transaction.

Dirty reads, unrepeatable reads, phantom reads (lowest isolation level, high concurrency)

At the lowest level, only physically corrupted data can be read.

(3) READ_COMMITTED) :

A transaction cannot read data until another transaction commits, thus solving the dirty read problem.

Unrepeatable read, phantom read (lock row being read, Oracle default level, on most systems)

Statement level;

(4) REPEATABLE_READ:

When the data is read (transaction start), no modification operation is allowed to solve the problem of unrepeatable read.

Phantom read problem (lock all rows read, MYSQL default level)

Transaction level;

(5) SERALZABLE:

Is the highest transaction isolation level at which transactions are sequentially executed to avoid dirty reads, unrepeatable reads, and phantom reads.

However, this transaction isolation level is inefficient and costly to database performance, and is generally not used. (Lock table)

Highest level, transaction level;

The transaction isolation level increases from top to bottom. The higher the isolation level, the more data integrity and consistency are ensured. However, the consumption of database performance increases and the concurrent execution efficiency decreases.

Transaction isolation essentially makes transactions “serial” to a degree that is actually incompatible with concurrency. At the same time, different applications have different levels of read consistency and transaction isolation, and some applications may be insensitive to “unrepeatable reads” and “phantom reads” and more concerned with concurrent processing of data.

Check 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 and Oracle

For a few databases, the default isolation level is Repeatable Read. For example, MySQL InnoDB

The relationship between the isolation level of a transaction and the concurrent problems

Dirty reads non-repeatable reads phantom reads
READ_UNCOMMITTED Y Y Y
READ_COMMITTED N Y Y
REPEATABLE_READ N N Y
SERALZABLE N N N

3.3 the sample

3.3.1 Building a predicate sentence

Create 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_lock VALUES(9.'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 the 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 | 9 | 8000 | | 9000 | | 1 b1 | | + -- -- -- -- -- - + -- -- -- -- -- - + 9 rows in the set (0.00 SEC)Copy the code

3.3.2 Row Lock example

The session 1 window

#1.1 Updating 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 Querying Data
mysql> select * from test_innodb_lock;+------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | b3 | | 4 | 4000 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 9 | | 8000 | | 9000 | | 1 b1 | | + -- -- -- -- -- - + -- -- -- -- -- - + 9 rows in the set (0.00 SEC)#1.3 Submitting a Query
mysql> commit;Query OK, 0 rows affected (0.01sec)
#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 9 | | 8000 | | 9000 | | 1 b1 | | + -- -- -- -- -- - + -- -- -- -- -- - + 9 rows in the set (0.00 SEC)#2.3 Submit after update
mysql> commit;
#2.4 Query after submission
mysql> select * from test_innodb_lock;+------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | b3 | | 4 | 4000 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 9 | | 8000 | | 9000 | | 1 b1 | | + -- -- -- -- -- - + -- -- -- -- -- - + 9 rows in the set (0.00 SEC)#2.5 Session 2 Submitted After submission
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 | 9 | 8000 | | 9000 | | 1 b1 | | + -- -- -- -- -- - + -- -- -- -- -- - + 9 rows in the 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 Committing a Transaction
mysql> commit;Query OK, 0 rows affected (0.01sec)#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 | 9 | 8000 | | 9009 | | 1 b1 | | + -- -- -- -- -- - + -- -- -- -- -- - + 9 rows in the set (0.00 SEC)Copy the code

The session 2 window

#1.1 Session 1 The query session 2 is not submitted
mysql> select * from test_innodb_lock;+------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | 3 | | 4 | 4000 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 9 | 8000 | | 9000 | | 1 b1 | | + -- -- -- -- -- - + -- -- -- -- -- - + 9 rows in the set (0.00 SEC)#1.2 Session 1 Is submitted after Session 2 is submitted
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
#1.3 Query session 1 and Session 2
mysql> select * from test_innodb_lock;+------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | b3 | | 4 | 4000 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 9 | | 8000 | | 9000 | | 1 b1 | | + -- -- -- -- -- - + -- -- -- -- -- - + 9 rows in the set (0.00 SEC)
#2.1 Update row data. Session 1 has not been committed, so session 2 can be updated only after session 1 is committed
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 Commit after the transaction is committed
mysql> commit;Query OK, 0 rows affected (0.01sec)#2.3 Querying Data
mysql> select * from test_innodb_lock;+------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | b3 | | 4 | b4 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 9 | 8000 | | 9000 | | 1 b1 | | + -- -- -- -- -- - + -- -- -- -- -- - + 9 rows in the 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 Committing a 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 | 9 | 8000 | | 9009 | | 1 b1 | | + -- -- -- -- -- - + -- -- -- -- -- - + 9 rows in the set (0.00 SEC)Copy the code

3.3.3 Index Failure Row lock Upgraded to table lock

The session 1 window

#1.1 Querying Current Data
mysql> select * from test_innodb_lock;+------+-------+ | a | b | +------+-------+ | 1 | 10010 | | 3 | b3 | | 4 | b4 | | 5 | 5005 | | 6 | 6000 | | 7 | 7007 | | 9 8 | 8008 | | | 10010 | | 10010 | 1 | + -- -- -- -- -- - + -- -- -- -- -- - + 9 rows in the set (0.00 SEC)#1.2 Update the data in line B =10010
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 transaction
mysql> commit;Query OK, 0 rows affected (0.02sec)#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 | | | | | | 10010 | | | 10010 | + -- -- -- -- -- - + -- -- -- -- -- -- - + 9 rows in the set (0.00 SEC)Copy the code

The session 2 window

#1.1 Querying Current Data
mysql> select * from test_innodb_lock;+------+-------+ | a | b | +------+-------+ | 1 | 10010 | | 3 | b3 | | 4 | b4 | | 5 | 5005 | | 6 | 6000 | | 7 | 7007 | | 9 8 | 8008 | | | 10010 | | 10010 | 1 | + -- -- -- -- -- - + -- -- -- -- -- - + 9 rows in the set (0.00 SEC)#1.2 Session 1 is not committed after update. Because session 1 index fails, the row lock becomes invalid and becomes a table lock. Session 2 is 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 is committed after it is committed
mysql> commit;Query OK, 0 rows affected (0.01sec)#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 | | | | | | 10010 | | | 10010 | + -- -- -- -- -- - + -- -- -- -- -- -- - + 9 rows in the set (0.00 SEC)Copy the code

Summary of row lock example:

  • If the current session does not submit the updated data, other sessions cannot see the updated data.
  • The current session fails to submit an update row of data, and other sessions block when updating the same row of data.
  • No blocking occurs when the current session and other sessions update different rows;
  • Index failure causes a row lock to become a table lock;

We do clearance lock

When we use a range query rather than an equality condition to retrieve data and request a shared or exclusive lock, InnoDB locks the index entry of existing data that meets the condition. For records whose Key values are within the condition range but do not exist, it is called “GAP”. InnoDB also locks “GAP”. This locking mechanism is called “next-key locking” **.

Harm of clearance lock:

A range lookup during Query execution locks all index keys in the entire range, even if the value does not exist.

An Achilles heel of gap locking is that when a range is locked, even nonexistent keys can be locked, making it impossible to insert any data within the range at the time of locking. This can have a significant impact on performance in some scenarios.

The session 1 window

#1.1 Update 1 < a < 6 line B istest
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 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 | | 9 8 | 8000 | | | 9000 | | 1 b1 | | + -- -- -- -- -- - + -- -- -- -- -- - + 9 rows in the set (0.00 SEC)
#2.1 Session 2 Submit after submission
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 | | 9 8 | 8000 | | | 9000 | | | 1 b1 | | 2 | 2000 | + -- -- -- -- -- - + -- -- -- -- -- - + 10 rows in the set (0.00 SEC)
#3. Lock a row
#3.1 Type the starting point
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
#3.2 Querying Lock information
mysql> select * from test_innodb_lock where a=8 for update;+ -- -- -- -- -- - + -- -- -- -- -- -- + | a | b | + -- -- -- -- -- - + -- -- -- -- -- - + 8 | | 8000 | + -- -- -- -- -- - + -- -- -- -- -- - + 1 row in the set (0.00 SEC)#3.3 Committing transactions
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
#3.4 Session 2 Submit after submission
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 | | 9 8 | XXXX | | | 9000 | | 1 b1 | | | | 2000 | + -- -- -- -- -- - + -- -- -- -- -- - + 10 rows in the set (0.00 SEC)Copy the code

The session 2 window

#1.1 Session 1 Updates the range data, causing clearance lock and blocking. Session 1 is submitted and executed
mysql> insert into test_innodb_lock values(2,'2000');
Query OK, 1 row affected (7.61 sec)
#1.2 Session 1 Submit after submission
mysql> commit;Query OK, 0 rows affected (0.01sec)#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 | | 9 8 | 8000 | | | 9000 | | | 1 b1 | | 2 | 2000 | + -- -- -- -- -- - + -- -- -- -- -- - + 10 rows in the set (0.00 SEC)
#3. Lock a row
#3.1 Update a row. The execution is blocked because session 1 locks the row during query. Session 1 can be executed only after it is committed
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 Committing a 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 | | 9 8 | XXXX | | | 9000 | | 1 b1 | | | | 2000 | + -- -- -- -- -- - + -- -- -- -- -- - + 10 rows in the set (0.00 SEC)Copy the code

Interview question: How do I lock in lines?

select xxxx… After a row is locked for UPDATE, all other operations are blocked until the session that locked the row is committed.

3.3.5 summary

InnoDB storage engine implements row-level locking, which causes more performance loss than table-level locking, but is far superior to MyISAM’s table-level locking in terms of overall concurrent processing capability. When the system concurrency is high, InnoDB’s overall performance has a significant advantage over MyISAM.

InnoDB’s row-level locking is also problematic, and when used incorrectly, InnoDB’s overall performance can be worse than MyISAM’s.

3.4 Row lock analysis

Analyze row lock contention on the system by examining the InnoDB_row_lock state variable

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 the code

Description of state variables:

Innodb_row_lock_current_waits: Number of locks currently being waited for;

Innodb_row_lock_time: total lock time since system startup;

Innodb_row_lock_time_avg: average wait time;

Innodb_row_lock_time_max: The time spent waiting for the longest time since system startup;

Innodb_row_lock_waits: Total number of waits since system startup;

The more important of the five state variables:

Innodb_row_lock_time_avg: average wait time;

Innodb_row_lock_waits: Total number of waits since system startup;

Innodb_row_lock_time: total lock time since system startup;

Especially when the current waiting times are very high and each waiting time is very long, it is necessary to analyze the causes of multiple waits in the system and specify optimization plans according to the analysis results.

3.5 Optimization Suggestions

(1) As far as possible, all data retrieval is done through the index, to avoid the upgrade of non-index row lock to table lock;

② Reasonable design index, as far as possible to reduce the scope of lock;

③ Reduce retrieval conditions as much as possible to avoid clearance locking;

(4) try to control the transaction size, reduce the amount of locked resources and the length of time;

⑤ Use low-level transaction isolation whenever possible;

4. Page locks

Page-level locking is a kind of lock whose granularity is between row-level locking and table-level locking in MySQL. Table level locking is fast but has many conflicts, while row level locking is slow but has few conflicts. So a compromise page level is taken, locking adjacent sets of records at a time. BDB supports page-level locking.