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

客官°小女子只卖身不卖艺 2022-10-06 06:55 234阅读 0赞

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

前两天同事在微信群推了一篇文章，《[一条 SQL 引发的事故，同事竟直接被开除！！][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，

![f67e0606d50f20b9de406c1f53b51d39.png][]

该参数的默认值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.
> 
> ![fbfff65ebadad7fae8659e8c30a0c961.png][]
> 
> 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:
> 
> ![8ef6a9c4da3bfe3edc79f686e4cb05ac.png][]

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

再次开启事务，

此时看下锁，这次没对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，

T2时刻，

会话2，

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

小白学习MySQL，

《[小白学习MySQL - 索引键长度限制的问题][MySQL -]》

《[小白学习MySQL - MySQL会不会受到“高水位”的影响？][MySQL - MySQL]》

《[小白学习MySQL - 数据库软件和初始化安装][MySQL - 1]》

《[小白学习MySQL - 闲聊聊][MySQL - 2]》

**近期更新的文章：**

《[误操作怎么办？试试这个神器-Log Miner][-Log Miner]》

《[尝试一下OSWatch][OSWatch]》

《[数据库hang等待链分析利器][hang]》

《[会议交流的一些杂谈][Link 1]》

《[曾经运维生涯中的几个“最”][Link 2]》

**文章分类和索引：**

《[公众号700篇文章分类和索引][700]》

[SQL]: http://mp.weixin.qq.com/s?__biz=MzA4NDY3ODAwNA%3D%3D&chksm=8411e1b5b36668a3e53455ed281f62e1e98ea0c21a76f020678951bc698d15f903c62594409c&idx=1&mid=2651243950&scene=21&sn=4e75610c133896fb7edef148ac3305b3#wechat_redirect
[f67e0606d50f20b9de406c1f53b51d39.png]: /images/20221005/49319c02a88e45d4b94c8d43b1c9f909.png
[fbfff65ebadad7fae8659e8c30a0c961.png]: /images/20221005/ffff0cea113c42cc8f6b1f85824b69e9.png
[8ef6a9c4da3bfe3edc79f686e4cb05ac.png]: /images/20221005/baff6509e67748aabdc63d2f578dadb6.png
[MySQL -]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a466ddf52d701aa8f23065ac5d0ef4e0ff1bf1b6deb53a923e3d3ab007d6d41d92a73088&idx=1&mid=2247491989&scene=21&sn=3979107e0b99ebddc6b6ccfc8f9b901c#wechat_redirect
[MySQL - MySQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a485ddf52d936279d52b0e62463f699412cf886e9b51eaf48114e7561dec55ccc21189be&idx=1&mid=2247491958&scene=21&sn=6917dcc719cab7b5537194eed27b4ed2#wechat_redirect
[MySQL - 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4c8ddf52dde849a70b34f2b62ff16593f81898cca7bc1fd7f0321588823016f0becaa18&idx=1&mid=2247491899&scene=21&sn=7affd185a81761eeaa5c3e143fc6f894#wechat_redirect
[MySQL - 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4dcddf52dcad1708150cc6ceb101423eb5f4326ceff0f9dd53c05103bea76c1c5b7ccbc&idx=1&mid=2247491887&scene=21&sn=91485c182868ef19ad822c3ac9bf2ea9#wechat_redirect
[-Log Miner]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a769ddf52e7fe1c62e13d67af71df7907560c9dcb8f885fc2986afda123202a5ed740254&idx=1&mid=2247492250&scene=21&sn=2ed8a58ec992247a2412411229ccbc06#wechat_redirect
[OSWatch]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a787ddf52e91ce0091e71c200202961a75e452bea9ed2d64cb29a82ecb145cc3a798d2ca&idx=1&mid=2247492212&scene=21&sn=966d6b5ec12307602ff44f65193fb434#wechat_redirect
[hang]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a7a4ddf52eb2590eacab606223bb71dd660be9955ad297a5e44b9314c60266e13c962f34&idx=1&mid=2247492183&scene=21&sn=a80b4e1d743da42ca26aff7d8d022238#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a7a2ddf52eb46af1f228aff9ee58cdb06375aa6e9cc304bcb8ecc9cc1f7f94f1f1b9a44f&idx=1&mid=2247492177&scene=21&sn=18fc61ccb334bac37dc9b091f4b32f7f#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a7f2ddf52ee49775b374898978d2f13624f1e51dcbfecdfdd32925332132e4eaa88cc7bd&idx=1&mid=2247492097&scene=21&sn=f2c338461919594062136eb10a70e9c4#wechat_redirect
[700]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5dfddf52cc949a5b8b316ee905927ff399713b34aec7d806c656e8ada09bfb562c039ca&idx=1&mid=2247491628&scene=21&sn=83bd320bd28da84501fada2e8d515e77#wechat_redirect