mysql innodb 注意事项_MySQL InnoDB 锁事

本文首发于微信公众号 《andyqian》, 关注第一时间查看好文

前言

在数据库中，通常通过锁来解决并发下数据一致性问题，从而避免数据产生脏乱。在保证数据一致性问题的前提下，通过锁范围又分为不同种类，在 MySQL 中，存储引擎就支持不同类型锁。如： MyISAM 只支持表锁。InnoDB 支持：行锁，表锁，Gap 锁等等。今天就来聊聊 MySQL InnoDB 的 " 锁事" 。

锁类型

既然提到了锁，锁类型是逃不开的话题，在 InnoDB 中，行锁分为共享锁(Share) 和 排他 (Exclusive) 锁。其详细概念如下：共享(Share)锁 ：简称 (S) 锁， 持有该锁的事务允许读取行数据。

排他(Exclusive)锁：简称 (X)锁 持有该锁的事务允许 update 或 delete 行数据。

备注:此处的行锁不能简单的理解为锁定一行数据，而是 行数据。行数据包括：单行，多行，理解这区别，对后面理解(Record 锁，Gap 锁)会有莫大的帮助。

兼容关系：

S 锁X 锁S 锁兼容不兼容X 锁不兼容不兼容

文字解析：

当事务 T1 在行 t 上持有 S (共享) 锁时，事务 T2 对行 t 上 请求持有锁的结果如下：当事务 T2 请求获取 S (共享) 锁时，将立即授予，此时 事务 T1，T2 同时持有 S (共享) 锁。

当事务 T2 请求获取 X (排他) 锁时，此时T2 将会处于锁等待状态，等待事务T1 释放 S (共享)锁后再进行获取，如果T2 锁

例子： 事务1持有S 锁, 事务2 请求持有S 锁。事务1

begin;

mysql> select * from t_base_info where oid = 1 lock in share mode;

+-----+----------+---------------------+---------------------+

| oid | name | create_time | updated_time |

+-----+----------+---------------------+---------------------+

| 1 | andyqian | 2020-03-21 14:34:08 | 2020-03-21 14:34:08 |

+-----+----------+---------------------+---------------------+

1 row in set (0.00 sec)事务2

begin;

mysql> select * from t_base_info where oid = 1 lock in share mode;

+-----+----------+---------------------+---------------------+

| oid | name | create_time | updated_time |

+-----+----------+---------------------+---------------------+

| 1 | andyqian | 2020-03-21 14:34:08 | 2020-03-21 14:34:08 |

+-----+----------+---------------------+---------------------+

1 row in set (0.00 sec)

例子： 事务1持有S 锁, 事务2 请求持有X 锁。事务1

begin;

mysql> select * from t_base_info where oid = 1 lock in share mode;

+-----+----------+---------------------+---------------------+

| oid | name | create_time | updated_time |

+-----+----------+---------------------+---------------------+

| 1 | andyqian | 2020-03-21 14:34:08 | 2020-03-21 14:34:08 |

+-----+----------+---------------------+---------------------+

1 row in set (0.00 sec)事务2

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_base_info where oid = 1 for update;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

以上例子也佐证了上面的理论。

常见锁

在 InnoDB 存储引擎， REPEATABLE READ (可重复读) 隔离级别下，为我们提供了多种锁，接下来我们一探究竟。(下述所有例子：在 Innodb，REPEATABLE READ (可重复读)隔离级别 运行，请确保一致)

1. 表锁

锁定范围：整表。

特点：获取锁效率高，有效避免死锁 (破坏了死锁的竞争条件)。但严重影响性能，并发性低，在生产系统上，表锁简直属于灾难。

事务1

begin;

lock table t_base_info read;

事务2:

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_base_info(name,create_time,updated_time)values("name",now(),now());

结果：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

备注：表锁的超时时间不受：innodb_lock_wait_timeout 限制，而是受：lock_wait_timeout 限制。

mysql> show variables like "lock_wait_timeout";

+-------------------+----------+

| Variable_name | Value |

+-------------------+----------+

| lock_wait_timeout | 31536000 |

+-------------------+----------+

1 row in set (0.00 sec)

单位为秒，默认为31536000 秒，365 天。

可通过下述命令进行修改：

set global lock_wait_timeout = 10;

2. Record 锁

锁定范围：索引记录，如果表中没有索引记录，则会自动创建一个隐藏的聚簇索引。

特点：只锁定单条索引记录，获取锁效率稍低，可能会产生死锁， 但并发高，性能好。

适用隔离级别： REPEATABLE READ (可重复读)，READ COMMITTED (提交读)，READ UNCOMMITTED (未提交读)。

3. Gap 锁

锁定范围：顾名思义，锁定的是一个范围。

特点：Gap 锁: 锁定一个范围，锁定后该范围不支持新增，其他事物在该范围中insert，delete 需要lock wait 直至释放。

适用隔离级别： REPEATABLE READ (可重复读)。

备注：在 READ UNCOMMITTED (未提交读)，READ COMMITTED (提交读) 隔离级别下无效。

例子：

事务1

begin;

select * from t_base_info where oid < 8 for update;

事务2:

begin;

mysql> insert into t_base_info(oid,name,create_time,updated_time)

values(4,"name4",now(),now());

结果：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

4. Next-Key 锁

锁定范围：顾名思义，锁定的是一个范围。

特点：Next-Lock：Recod 锁 + Gap 锁的集合。其中锁定的范围是：当前记录和范围。

适用隔离级别：REPEATABLE READ (可重复读)

注意事项：该锁在 READ UNCOMMITTED (未提交读)，READ COMMITTED (提交读) 隔离级别下无效。

事务1

begin;

select * from t_base_info where oid < 8 for update;

事务2:

begin;

mysql> update t_base_info set name = "andyqian008" where oid = 8;

结果：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

锁超时

在高并发下，热点数据的锁竞争是非常常见的。在 MySQL 中采用超时机制，当获取锁时间超过 innodb_lock_wait_timeout 后，会提示获取锁失败。如下所示：锁获取超时后，提示信息：

> 1205 - Lock wait timeout exceeded; try restarting transaction

> Time: 30.89s查看锁超时时间, (默认为 50 秒)。

show variables like "innodb_lock_wait_timeout";修改全局锁超时时间。

set global innodb_lock_wait_timeout = 30;修改当前会话锁超时时间

set session innodb_lock_wait_timeout = 20;

备注：修改 global 超时时，当前 session 超时时间不变，其他 session 改变。

修改 session 超时时，当前 session 超时时间改变，其他 session 不改变。

在 Navicat 等客户端工具中，一个 Query 为一个 session 。

小结

上述是我对 MySQL InnoDB 锁以及常见锁的理解，如有误之处，还请多多指出。谈到这里，我觉得这和 Java 中 JUC 包提供的职责是相同的，其设计思路也有相通之处。此时有人要问了：Java 中不是还有CAS 无锁方案吗？数据库中有类似的概念及实现吗？你还别不信， 在 InnoDB 存储引擎中的 MVCC 与 Java 中 CAS 的就像极了，我们下篇就来详细讲讲。

相关阅读: