乐趣区

关于锁:技术分享-MySQL中查询会锁表

作者:刘晨

网名 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_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)

默认状况下,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 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
​
...

解决方案 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 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     ;;
 ...

解决方案 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: 0

T3 时刻,

会话 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: 0

T3 时刻,

会话 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 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
...

从语义上讲,RC 模式,其实毁坏了 ACID 中的 I,因为两个事务并未做到真正的隔离。而在 RR 模式,尽管两个事务做到了真正的隔离,但理论通过加锁,还是会产生一些问题的,因而隔离级别的抉择,其实还是一种衡量的。

退出移动版