innodb锁机制再探

AsiaYe

发布于 2019-11-06 16:15:11

4040

发布于 2019-11-06 16:15:11

文章被收录于专栏：DBA随笔DBA随笔

innodb锁机制再探昨天我们已经简单说了锁的概念，今天主要是看一些例子，来加深对于锁的印象，首先我们来看在MySQL中获得读取锁的SQL语法：

在SELECT 的读取锁定主要分为两种方式：
共享锁：　　SELECT ... LOCK IN SHARE MODE　
排它锁：　　SELECT ... FOR UPDATE

当我们使用如上所述的语法的时候，这两种方式在事务(Transaction) 进行当中SELECT 到同一个数据表时，都必须等待其它事务数据被提交(Commit)后才会执行。

没有索引的表模拟锁等待

我们首先创建一个表，这个表里面只有id和name两个字段，而且我们并没有在id字段创建索引，下面我们来看测试的过程：

会话1：

mysql> create table t3(id int,name varchar()) engine=innodb;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t3 values(,'1'),(,'2'),(,'3'),(,'4');
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> set autocommit=;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t3 where id= for update;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
+------+------+
1 row in set (0.00 sec)

#此处停止了大概半分钟来观察另外一个会话

在停止的这段时间，我们打开另外一个连接会话，然后重新获取同一个表的锁，我们可以看到下面的结果：

会话2：

可以看到，当我们敲完回车之后，并没有出现想要的结果，而是出现了锁等待。也就是说，这个时候，当前会话必须等待上一个会话释放了相关的排它锁之后，才能重新给这个数据加自己的排它锁。此时我们不进行任何操作，会话2上出现了下面的输出结果：

mysql> select * from t3 where id=;
+------+------+
| id   | name |
+------+------+
|    2 | 2    |
+------+------+
1 row in set (0.00 sec)

mysql> select * from t3 where id= for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

显示锁等待超时，证明在等待的过程中一直没有拿到相应记录的排它锁。

另外一点值得注意的是，当会话1中我们仅仅对id=1的表获取了读取的排它锁，而会话2中是对id=2的记录获取排它锁，那么为什么依旧会产生冲突？这个结果想必大家也知道了，是因为我们这个表没有索引，所以会话1在进行获取排它锁的时候，是锁定了整个表的所有记录。

此时我们重新发起会话2的排它锁要求，稍等10s左右，在会话1窗口敲个commit命令，可以看到：

会话1：

mysql> set autocommit=;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t3 where id= for update;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
+------+------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

会话2：

mysql> select * from t3 where id= for update;
+------+------+
| id   | name |
+------+------+
|    2 | 2    |
+------+------+
1 row in set (8.44 sec)

可以发现，我们在会话1上敲了commit之后，会话2立即返回了相应的结果，等待时间是8.44s，也就证实了确实是由于会话1占用排它锁导致会话2锁等待的。

有一个索引情况下的表等待

在上面的基础上，我们给这个表t3的id列添加一个索引，然后再去看这个过程：

会话1：

mysql> alter table t3 add index idx_t(id);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> set autocommit=;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t3 where id= for update;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
+------+------+
1 row in set (0.02 sec)

#等待大约10s左右

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

此时会话2上我们可以看到如下结果：

mysql> select * from t3 where id= for update;
+------+------+
| id   | name |
+------+------+
|    2 | 2    |
+------+------+
1 row in set (0.00 sec)

mysql> select * from t3 where id= for update;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
+------+------+
1 row in set (13.40 sec)

可以看到，当我们想要获取id=2的记录的排它锁的时候，并没有产生阻塞，而当我们想要获取id=1的记录的时候，依旧产生了阻塞，由于我们在会话1上面敲了commit命令，所以会话2最终执行了，但是执行时间是13.40s，可以说相当长了。

上面的例子说明了我们在使用不同的索引键（也就是id=1和id=2）的时候，锁之间不会产生等待，那么如果我们使用相同的索引键呢？下面我们进行一个测试：

会话1

mysql> select * from t3 ;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
|    2 | 2    |
|    3 | 3    |
|    4 | 4    |
+------+------+
4 rows in set (0.00 sec)

mysql> insert into t3 values (,'0');
Query OK, 1 row affected (0.14 sec)

mysql> select * from t3 where id= and name='0' for update;
+------+------+
| id   | name |
+------+------+
|    1 | 0    |
+------+------+
1 row in set (0.00 sec)

#此处等待10s左右

mysql> commit;
Query OK, 0 rows affected (0.02 sec)

首先我们给表t3中插入一条新的记录，它的索引键是1，name值是0，此时我们在会话1上面获取排它锁，再在会话2上面获取相同索引键不同name值的一条记录，我们发下依旧产生了等待，如下图：

会话2

mysql> select * from t3 where id= and name='1' for update;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
+------+------+
1 row in set (8.34 sec)

也是产生了锁等待，等待了8s左右才执行成功。

两个字段都有索引的情况

上述内容我们再重新进阶，给name字段也添加一个索引，进行如下变化：

mysql> alter table t3 add index index_n(name);
Query OK, 0 rows affected (0.60 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | MUL | NULL    |       |
| name  | varchar(10) | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from t3 order by name;
+------+------+
| id   | name |
+------+------+
|    1 | 0    |
|    1 | 1    |
|    2 | 2    |
|    3 | 3    |
|    4 | 4    |
+------+------+
5 rows in set (0.00 sec)

mysql> select * from t3 where id= for update;
+------+------+
| id   | name |
+------+------+
|    3 | 3    |
+------+------+
1 row in set (0.00 sec)

#此处等待大概10s左右

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

如上所示，我们先给name字段加上索引，然后锁定id=3的一行数据，在回话2上进行测试，结果如下：

mysql> select * from t3 where name='2' for update;
+------+------+
| id   | name |
+------+------+
|    2 | 2    |
+------+------+
1 row in set (0.00 sec)

mysql> select * from t3 where name='3' for update;
+------+------+
| id   | name |
+------+------+
|    3 | 3    |
+------+------+
1 row in set (10.00 sec)

我们可以看到，在会话2上选择name='2'这条记录的时候，没有产生锁等待，而选择name='3'的时候，产生了锁等待，因为name='3'的那条记录对应的是id=3的记录，而id=3的记录在会话1中被锁定，所以产生了等待。

再看最后一种情况，当我们过滤条件改为name=3而不是name='3'的时候，又会发生什么变化呢？来看会话1：

mysql> select * from t3 where id= for update;
+------+------+
| id   | name |
+------+------+
|    3 | 3    |
+------+------+
1 row in set (0.00 sec)

#等待大概10s

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

我们依旧锁定id=3的这行值，然后再会话2上用name=3这个条件去过滤，可以看到如下结果：

会话2

mysql> select * from t3 where name= for update;
+------+------+
| id   | name |
+------+------+
|    3 | 3    |
+------+------+
1 row in set (12.10 sec)

mysql> explain select * from t3 where name= for update;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t3    | NULL       | ALL  | index_n       | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set,  warnings (0.00 sec)

mysql> explain select * from t3 where name='3' for update;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t3    | NULL       | ref  | index_n       | index_n | 13      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set,  warning (0.00 sec)

依旧产生了锁等待，这次锁等待的原因是当我们使用name=3作为过滤条件的时候，由于name的类型是varchar类型，mysql会自动进行类型转换，将int类型的数值转换为varchar类型，但是在转换的过程中，我们的SQL不再使用索引，所以走了全表扫描，而id=3的记录此时是被锁定的，所以产生了锁等待。从下面两个SQL的执行计划中我们也可以看出，name=3的时候，执行计划中的key值是null，而name='3'的时候，使用index_n这个索引。

有无索引的情况大概介绍到这里，关于共享锁，大家可以自己试试，关于锁，后面会专门再写几篇，彻底搞定它。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2018-12-26，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server