作者：刘晨
网名 bisal ，具备十年以上的利用运维工作教训，目前次要从事数据库利用研发能力晋升方面的工作，Oracle ACE ，领有 Oracle OCM & OCP、EXIN DevOps Master 、SCJP 等国内认证，国内首批 Oracle YEP 成员，OCMU 成员，《DevOps 最佳实际》中文译者之一，CSDN & ITPub 专家博主，公众号"bisal的集体杂货铺"，长期保持分享技术文章，屡次在线上和线下分享技术主题。
本文起源：原创投稿
*爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。
咱们晓得，Oracle 中除了应用 select ... for update ，其余查问语句不会呈现锁，即没有读锁，读一致性通过多版本解决的，能够保障在不加锁的状况下，读到同一时间的数据。
前两天共事在微信群推了一篇文章，大略意思就是通过应用 insert into select 做了数据的备份，导致了 select 的表锁住，进而影响了失常的应用。
问题来了，Oracle 中执行的 insert into select 很失常，不会呈现锁表，难道雷同的语句用在了 MySQL ，就会锁住整张表？
咱们能进行验证，MySQL 5.7 中执行如下语句，会呈现什么景象？
insert into test_1 select * from test_2;test_1 和 test_2 定义如下，test_1 存在五条记录，
mysql> show create table test_1\G;*************************** 1. row *************************** Table: test_1Create Table: CREATE TABLE `test_1` ( `id` int(11) NOT NULL, `name` varchar(10) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb41 row in set (0.04 sec)mysql> show create table test_2\G;*************************** 1. row *************************** Table: test_2Create Table: CREATE TABLE `test_2` ( `id` int(11) NOT NULL, `name` varchar(10) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb41 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)默认状况下，show engine innodb status 显示的锁信息很无限，能够开启锁监控，如果仅须要在 show engine innodb status 显示具体的锁，能够仅关上 innodb_status_output_locks，
该参数的默认值 OFF ，而且只能够在全局层面关上，
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)在会话 1 中，开启一个事务，将 test_1 的 name='test_1' 这行记录导入 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查看锁的信息，能够看到，有五个 record lock ，尽管我只从 test_1 读取一行数据，但实际上对 test_1 的所有记录都加了锁，而且显式对 test_1 加了一个 IS 的意向锁，因而这种操作，的确影响了 select 表的并发执行，
mysql> show engine innodb status \G;...------------TRANSACTIONS------------Trx id counter 3255Purge done for trx's n:o < 3254 undo n:o < 0 state: running but idleHistory list length 3LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 422059634232944, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 422059634231120, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 3254, ACTIVE 4 sec3 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 1MySQL thread id 23, OS thread handle 140584218986240, query id 16201659 localhost rootTABLE LOCK table `bisal`.`test_1` trx id 3254 lock mode ISRECORD LOCKS space id 44 page no 3 n bits 72 index PRIMARY of table `bisal`.`test_1` trx id 3254 lock mode SRecord 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...解决方案 1 ，创立索引
咱们为列 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再次开启事务，
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此时看下锁，这次没对 test_1 加任何的锁，只是对'test_1'这行记录加了共享锁(lock mode S locks gap before rec)，其实是加到了索引上，
mysql> show engine innodb status \G;...------------TRANSACTIONS------------Trx id counter 3268Purge done for trx's n:o < 3268 undo n:o < 0 state: running but idleHistory list length 4LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 422059634232944, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 422059634231120, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 3263, ACTIVE 3 sec4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 23, OS thread handle 140584218986240, query id 16201664 localhost rootTABLE LOCK table `bisal`.`test_1` trx id 3263 lock mode ISRECORD 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 SRecord 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 IXRECORD 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 recRecord 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 ;; ...解决方案2：更改隔离级别
在创立索引前，之所以会呈现锁表的状况，和隔离级别是相干的，首先看下数据库的隔离级别。ISO 和 ANSI SQL 规范制订了 4 种事务隔离级别的规范，包含如下，
Read Uncommitted
Read Committed
Repeatable Read
Serializable
然而不是所有的数据库厂商都遵循这些规范，例如 Oracle 不反对 RU 和 RR ，MySQL 则反对所有级别。Oracle 默认隔离级别是 RC ，MySQL 默认隔离级别是 RR 。
P.S.
https://dev.mysql.com/doc/ref...
RR 和 RC 下，InnoDB 引擎都提供了一致性的非锁定读，即通过多版本控制的形式来读取以后时刻的行数据，从技术实现上，MySQL 和 Oracle 是很相像的，都是通过回滚段来实现的 MVCC (Multi Version Concurrency Control)，每行都可能有多个版本，即多个快照数据，防止对读加锁，进步读的并发。
比拟一下 RR 和 RC ，最大的区别是两者对快照数据的定义不同，RR 模式下读取的是事务开始时的行快照数据，RC 模式下读取的则是该行最新的一份快照数据，咱们通过试验，来看下这是什么意思。
如果是 RR 模式，模仿如下两个事务的操作。
T1 时刻，
会话 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 时刻，
会话 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: 0T3 时刻，
会话 1 ，
mysql> select * from test_1 where id=5;+----+--------+| id | name |+----+--------+| 5 | test_5 |+----+--------+1 row in set (0.01 sec)T4 时刻，
会话 2 ，
mysql> commit;Query OK, 0 rows affected (0.01 sec)T5 时刻，
会话 1 ，
mysql> select * from test_1 where id=5;+----+--------+| id | name |+----+--------+| 5 | test_5 |+----+--------+1 row in set (0.01 sec)能够看到，无论在会话 2 的事务中 id=5 的记录如何改变，会话 1 的事务中，id=5 的记录值，都和事务开始时的值雷同。
更改为 RC 模式，模仿如下两个事务的操作。
在两个会话中，都执行这个操作，
mysql> set session transaction_isolation='read-committed';Query OK, 0 rows affected (0.00 sec)T1 时刻，
会话 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 时刻，
会话 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: 0T3 时刻，
会话 1 ，
mysql> select * from test_1 where id=5;+----+--------+| id | name |+----+--------+| 5 | test_5 |+----+--------+1 row in set (0.00 sec)T4 时刻，
会话 2 ，
mysql> commit;Query OK, 0 rows affected (0.01 sec)T5 时刻，
会话 1 ，
mysql> select * from test_1 where id=5;+----+--------+| id | name |+----+--------+| 5 | test_6 |+----+--------+1 row in set (0.00 sec)能够看到，在会话 2 的事务中改变 id=5 的值，在会话1的事务中失去了体现。
因而，RR 模式下读取的是事务开始时的行快照数据，RC 模式下读取的则是该行最新的一份快照数据。
如果隔离级别是 RC ，执行如上 insert into select 操作，
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此时看下锁信息，能看到 test_2 上是没有任何锁，因而不会呈现 RR 会锁定 test_2 的状况，
mysql> show engine innodb status \G;...------------TRANSACTIONS------------Trx id counter 3269Purge done for trx's n:o < 3268 undo n:o < 0 state: running but idleHistory list length 0LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 422059634232944, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 422059634231120, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 3268, ACTIVE 108 sec1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1MySQL thread id 23, OS thread handle 140584218986240, query id 16201671 localhost rootTABLE LOCK table `bisal`.`test_2` trx id 3268 lock mode IX...从语义上讲，RC 模式，其实毁坏了 ACID 中的 I ，因为两个事务并未做到真正的隔离。而在 RR 模式，尽管两个事务做到了真正的隔离，但理论通过加锁，还是会产生一些问题的，因而隔离级别的抉择，其实还是一种衡量的。