Author: Liu Chen
The network name bisal has more than ten years of experience in application operation and maintenance. At present, it is mainly engaged in the improvement of database application R & D capability. Oracle ACE has international certifications such as Oracle OCM & OCP, EXIN DevOps Master and SCJP. It is one of the first batch of Oracle YEP members in China, OCMU members, one of the Chinese translators of DevOps best practices, and expert blogger of CSDN & itpub, The official account, "bisal's personal grocery store", has long insisted on sharing technical articles, sharing online and offline technology topics.
Source: original contribution
*It is produced by aikesheng open source community. The original content cannot be used without authorization. For reprint, please contact Xiaobian and indicate the source.
We know that in Oracle, in addition to using select... for update, other query statements will not have locks, that is, there is no read lock. The read consistency is solved through multiple versions, which can ensure that the data at the same time can be read without locking.
Two days ago, my colleague pushed an article on wechat group, which roughly means that the data was backed up by using insert into select, which led to the locking of the select table, which affected the normal use.
The problem is that the insert into select executed in Oracle is very normal, and there will be no locking table. Is it possible that the same statement used in MySQL will lock the whole table?
We can verify. What happens when the following statements are executed in MySQL 5.7?
insert into test_1 select * from test_2;
test_1 and test_2 is defined as follows: test_1 there are five records,
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 in show engine innodb status is very limited, and lock monitoring can be enabled. If you only need to display specific locks in show engine innodb status, you can only open innodb_status_output_locks，
The default value of this parameter is OFF and 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 test_ name of 1 = test_1 'import this line of records 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 lock s, although I only start from test_1 reads a row of data, but actually tests_ All records of 1 are locked, and test IS explicitly checked_ 1 adds an IS intent lock, 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 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
Start 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
Look at the lock now. It's not right this time_ 1 add any lock, just for 'test'_ 1 'this line of records is added with a lock mode s locks gap before rec, which is 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 the table is locked is related to the isolation level. First, take a look at the isolation level of the database. ISO and ANSI SQL standards set standards for four transaction isolation levels, including:,
Read Uncommitted
Read Committed
Repeatable Read
Serializable
However, not all database vendors follow these standards. For example, Oracle does not support RU and RR, while MySQL supports all levels. The default isolation level of Oracle is RC, and that of MySQL is RR.
P.S.
https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html
Under RR and RC, the InnoDB engine provides consistent non locking reading, that is, it reads the row data at the current time through multi version control. In terms of technical implementation, MySQL and Oracle are very similar. They both implement MVCC (Multi Version Concurrency Control) through rollback segments. Each row may have multiple versions, that is, multiple snapshot data, to avoid locking the reading, Improve read concurrency.
Compare RR and RC. The biggest difference is that they have different definitions of snapshot data. In RR mode, the row snapshot data at the beginning of the transaction is read, and in RC mode, the latest snapshot data of the row is read. Let's see what this means through experiments.
In 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
At T3,
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 with id=5 is changed in the transaction of session 2, the record value with id=5 in the transaction of session 1 is the same as that at the beginning of the transaction.
Change to RC mode and simulate the operation of the following two transactions.
This operation is performed in both sessions,
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
At T3,
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 row snapshot data at the beginning of the transaction is read in RR mode, and the latest snapshot data of the row is read in RC mode.
If the isolation level is RC, execute 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
Now look at the lock information and you can see test_ There is no lock on 2, so there will be no RR 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, RC mode actually destroys the I in ACID, because the two transactions are not really isolated. In RR mode, although the two transactions are truly isolated, there will still be some problems through locking. Therefore, the selection of isolation level is actually a trade-off.