Technology Sharing | Will queries lock tables in MySQL?

Author: Liu Chen

Net name bisal, with more than ten years of application operation and maintenance work experience, currently mainly engaged in the improvement of database application research and development capabilities, Oracle ACE, has Oracle OCM & OCP, EXIN DevOps Master, SCJP and other international certifications, the first batch of Oracle YEP in China Member, OCMU member, one of the Chinese translators of "DevOps Best Practices", CSDN & ITPub expert blogger, public account "bisal's personal grocery store", long-term insistence on sharing technical articles, multiple online and offline sharing of technical topics .

Source of this article: original submission

* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

---

We know that except for using select ... for update in Oracle, other query statements will not be locked, that is, there is no read lock. Read consistency is solved through multiple versions, which can ensure that the same time is read without locking The data.

Two days ago, a colleague posted an article in the WeChat group, which probably meant that the data was backed up by using insert into select, which caused the select table to be locked, which affected normal use.

The problem is that the insert into select executed in Oracle is normal, and there is no lock table. Is the same statement used in MySQL, and the entire table is locked?

We can verify, what happens when the following statement is executed in MySQL 5.7?

insert into test_1 select * from test_2;

test_1 and test_2 are defined as follows, there are five records in test_1,

​mysql> show create table test_1\G;
*************************** 1. row ***************************
       Table: test_1
Create Table: CREATE TABLE `test_1` (
  `id` int(11) NOT NULL,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.04 sec)
​
mysql> show create table test_2\G;
*************************** 1. row ***************************
       Table: test_2
Create Table: CREATE TABLE `test_2` (
  `id` int(11) NOT NULL,
  `name` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.01 sec)
​
mysql> select * from test_1;
+----+--------+
| id | name   |
+----+--------+
|  1 | test_1 |
|  2 | test_2 |
|  3 | test_3 |
|  4 | test_4 |
|  5 | test_5 |
+----+--------+
5 rows in set (0.01 sec)

By default, the lock information displayed by show engine innodb status is very limited. You can turn on lock monitoring. If you only need to display specific locks in show engine innodb status, you can only turn on innodb_status_output_locks.

The default value of this parameter is OFF, and it can only be turned on at the global level.

​mysql> show variables like 'innodb_status_output_locks';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_status_output_locks | OFF   |
+----------------------------+-------+
1 row in set (0.44 sec)
​
mysql> set global innodb_status_output_locks=on;
Query OK, 0 rows affected (0.02 sec)
​
mysql> show variables like 'innodb_status_output_locks';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_status_output_locks | ON    |
+----------------------------+-------+
1 row in set (0.01 sec)

In session 1, start a transaction and import the line record of test_1's name='test_1' into test_2,

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
​
mysql> insert into test_2 select * from test_1 where name = 'test_1';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

Looking at the lock information, you can see that there are five record locks. Although I only read one row of data from test_1, I actually lock all the records of test_1 and explicitly add an IS intention lock to test_1. , So this operation does affect the concurrent execution of the select table,

mysql> show engine innodb status \G;
...
------------
TRANSACTIONS
------------
Trx id counter 3255
Purge done for trx's n:o < 3254 undo n:o < 0 state: running but idle
History list length 3
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422059634232944, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422059634231120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 3254, ACTIVE 4 sec
3 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 1
MySQL thread id 23, OS thread handle 140584218986240, query id 16201659 localhost root
TABLE LOCK table `bisal`.`test_1` trx id 3254 lock mode IS
RECORD LOCKS space id 44 page no 3 n bits 72 index PRIMARY of table `bisal`.`test_1` trx id 3254 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
​
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000000ca3; asc       ;;
 2: len 7; hex a80000011c0110; asc        ;;
 3: len 6; hex 746573745f31; asc test_1;;
​
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000000ca3; asc       ;;
 2: len 7; hex a80000011c011c; asc        ;;
 3: len 6; hex 746573745f32; asc test_2;;
​
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000000ca3; asc       ;;
 2: len 7; hex a80000011c0128; asc       (;;
 3: len 6; hex 746573745f33; asc test_3;;
​
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 000000000ca3; asc       ;;
 2: len 7; hex a80000011c0134; asc       4;;
 3: len 6; hex 746573745f34; asc test_4;;
​
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000000ca3; asc       ;;
 2: len 7; hex a80000011c0140; asc       @;;
 3: len 6; hex 746573745f35; asc test_5;;
​
TABLE LOCK table `bisal`.`test_2` trx id 3254 lock mode IX
​
...

Solution 1, create an index

We create an index for the column name,

mysql> alter table test_1 add index idx_test_1_01 (name);
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

Open the transaction again,

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
​
mysql> insert into test_2 select * from test_1 where name = 'test_1';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

Looking at the lock at this time, no lock was added to test_1 this time, but a shared lock (lock mode S locks gap before rec) was added to the row of'test_1', which was actually added to the index.

​mysql> show engine innodb status \G;
...
------------
TRANSACTIONS
------------
Trx id counter 3268
Purge done for trx's n:o < 3268 undo n:o < 0 state: running but idle
History list length 4
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422059634232944, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422059634231120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 3263, ACTIVE 3 sec
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 23, OS thread handle 140584218986240, query id 16201664 localhost root
TABLE LOCK table `bisal`.`test_1` trx id 3263 lock mode IS
RECORD LOCKS space id 44 page no 4 n bits 72 index idx_test_1_01 of table `bisal`.`test_1` trx id 3263 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 6; hex 746573745f31; asc test_1;;
 1: len 4; hex 80000001; asc     ;;
​
TABLE LOCK table `bisal`.`test_2` trx id 3263 lock mode IX
RECORD LOCKS space id 44 page no 4 n bits 72 index idx_test_1_01 of table `bisal`.`test_1` trx id 3263 lock mode S locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 6; hex 746573745f32; asc test_2;;
 1: len 4; hex 80000002; asc     ;;
 ...

Solution 2: Change the isolation level

Before creating an index, the reason why there is a lock table is related to the isolation level. First, let's look at the isolation level of the database. ISO and ANSI SQL standards have formulated 4 standards for transaction isolation levels, including the following,
Read Uncommitted
Read Committed
Repeatable Read
Serializable
However, not all database vendors follow these standards. For example, Oracle does not support RU and RR, and MySQL supports all levels. The default isolation level for Oracle is RC, and the default isolation level for MySQL is RR.
P.S. 
https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

Under both RR and RC, the InnoDB engine provides consistent non-locking read, that is, reads the row data at the current moment through multi-version control. In terms of technical implementation, MySQL and Oracle are very similar. In MVCC (Multi Version Concurrency Control) implemented by rolling segments, each row may have multiple versions, that is, multiple snapshot data, avoiding read locks and improving read concurrency.

Comparing RR and RC, the biggest difference is that they have different definitions of snapshot data. RR mode reads the row snapshot data at the beginning of the transaction, and RC mode reads the latest snapshot data of the row. , Let’s see what this means through experiments.

If it is RR mode, simulate the operation of the following two transactions.

T1 moment,

Session 1,

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
​
mysql> select * from test_1 where id=5;
+----+--------+
| id | name   |
+----+--------+
|  5 | test_5 |
+----+--------+
1 row in set (0.01 sec)

T2 moment,

Session 2,

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
​
mysql> update test_1 set name='test_6' where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

T3 moment,

Session 1,

mysql> select * from test_1 where id=5;
+----+--------+
| id | name   |
+----+--------+
|  5 | test_5 |
+----+--------+
1 row in set (0.01 sec)

T4 moment,

Session 2,

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

T5 moment,

Session 1,

mysql> select * from test_1 where id=5;
+----+--------+
| id | name   |
+----+--------+
|  5 | test_5 |
+----+--------+
1 row in set (0.01 sec)

It can be seen that no matter how the record of id=5 is changed in the transaction of session 2, the record value of id=5 in the transaction of session 1 is the same as the value at the beginning of the transaction.

Change to RC mode to simulate the operation of the following two transactions.

In both sessions, perform this operation,

mysql> set session transaction_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)

T1 moment,

Session 1,

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
​
mysql> select * from test_1 where id=5;
+----+--------+
| id | name   |
+----+--------+
|  5 | test_5 |
+----+--------+
1 row in set (0.00 sec)

T2 moment,

Session 2,

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
​
mysql> update test_1 set name='test_6' where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

T3 moment,

Session 1,

mysql> select * from test_1 where id=5;
+----+--------+
| id | name   |
+----+--------+
|  5 | test_5 |
+----+--------+
1 row in set (0.00 sec)

T4 moment,

Session 2,

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

T5 moment,

Session 1,

mysql> select * from test_1 where id=5;
+----+--------+
| id | name   |
+----+--------+
|  5 | test_6 |
+----+--------+
1 row in set (0.00 sec)

It can be seen that changing the value of id=5 in the transaction of session 2 is reflected in the transaction of session 1.

Therefore, the RR mode reads the row snapshot data at the beginning of the transaction, and the RC mode reads the latest snapshot data of the row.

If the isolation level is RC, perform the above insert into select operation,

mysql> show variables like '%transaction_isolation%';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)
​
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
​
mysql> insert into test_2 select * from test_1 where name = 'test_1';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

Looking at the lock information at this time, you can see that there is no lock on test_2, so there will be no situation where RR will lock test_2.

mysql> show engine innodb status \G;
...
------------
TRANSACTIONS
------------
Trx id counter 3269
Purge done for trx's n:o < 3268 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422059634232944, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422059634231120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 3268, ACTIVE 108 sec
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 23, OS thread handle 140584218986240, query id 16201671 localhost root
TABLE LOCK table `bisal`.`test_2` trx id 3268 lock mode IX
...

Semantically speaking, the RC mode actually destroys the I in ACID, because the two transactions are not truly isolated. In the RR mode, although the two transactions are truly isolated, some problems will still occur through locking. Therefore, the choice of isolation level is actually a trade-off.