小白学习MySQL - 查询会锁表？

bisal

发布于 2021-09-06 15:51:25

2.2K0

发布于 2021-09-06 15:51:25

文章被收录于专栏：bisal的个人杂货铺bisal的个人杂货铺

我们知道，Oracle中除了使用select ... for update，其他查询语句不会出现锁，即没有读锁，读一致性通过多版本解决的，可以保证在不加锁的情况下读到正确的数据。

前两天同事在微信群推了一篇文章，《一条 SQL 引发的事故，同事竟直接被开除！！》，大概意思就是MySQL中通过使用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


...

关于意向锁，官方文档有如下的介绍，

Intention Locks Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table. There are two types of intention locks: 1. An intention shared lock (IS) indicates that a transaction intends to set a shared lock on individual rows in a table. 2. An intention exclusive lock (IX) indicates that a transaction intends to set an exclusive lock on individual rows in a table. For example, SELECT … LOCK IN SHARE MODE sets an IS lock, and SELECT … FOR UPDATE sets an IX lock. The intention locking protocol is as follows: Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table. Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table. Table-level lock type compatibility is summarized in the following matrix.

A lock is granted to a requesting transaction if it is compatible with existing locks, but not if it conflicts with existing locks. A transaction waits until the conflicting existing lock is released. If a lock request conflicts with an existing lock and cannot be granted because it would cause deadlock, an error occurs. Intention locks do not block anything except full table requests (for example, LOCK TABLES … WRITE). The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table. Transaction data for an intention lock appears similar to the following in SHOW ENGINE INNODB STATUS and InnoDB monitor output: TABLE LOCK table `test`.`t` trx id 10080 lock mode IX

关于record lock，官方文档有如下的介绍，

Record Locks A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10. Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking. See Section 14.6.2.1, “Clustered and Secondary Indexes”. Transaction data for a record lock appears similar to the following in SHOW ENGINE INNODB STATUS and InnoDB monitor output:

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

这里又涉及到了一个Gap Lock，官方文档有如下的介绍，

Gap Locks A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked. A gap might span a single index value, multiple index values, or even be empty. Gap locks are part of the tradeoff between performance and concurrency, and are used in some transaction isolation levels and not others. Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.) For example, if the id column has a unique index, the following statement uses only an index-record lock for the row having id value 100 and it does not matter whether other sessions insert rows in the preceding gap: SELECT * FROM child WHERE id = 100; If id is not indexed or has a nonunique index, the statement does lock the preceding gap. It is also worth noting here that conflicting locks can be held on a gap by different transactions. For example, transaction A can hold a shared gap lock (gap S-lock) on a gap while transaction B holds an exclusive gap lock (gap X-lock) on the same gap. The reason conflicting gap locks are allowed is that if a record is purged from an index, the gap locks held on the record by different transactions must be merged. Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function. Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog system variable (which is now deprecated). Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking. There are also other effects of using the READ COMMITTED isolation level or enabling innodb_locks_unsafe_for_binlog. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. For UPDATE statements, InnoDB does a “semi-consistent” read, such that it returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE.

解决方案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/refman/5.7/en/innodb-transaction-isolation-levels.html

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模式，虽然两个事务做到了真正的隔离，但实际通过加锁，还是会产生一些问题的，因此隔离级别的选择，其实还是一种权衡的。

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2021-06-11 ，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库