InnoDB 锁与隔离级别

共计 2100 个字符，预计需要花费 6 分钟才能阅读完成。

最近在做一个项目中使用到了 MariaDB（Innodb 存储引擎），系统并发性比较高需要不停的接收前端传过来的数据(每天大概 400W)，传过来之后系统会自动的尽快处理计算结果(分批处理，需要更新业务表)。在开发过程中经常出现死锁和锁等待的问题。翻阅了一些资料和动手验证，整理如下：

InnoDB 默认的隔离级别是可重复读，默认行锁。但是它行锁在可重复读的隔离级别下是根语句据索引实现的。如果没有建索引或者因为数据原因查询分析器没有走索引则还是会获取表锁。即使加上相关的 hint 也无效。此外因为隔离级别和锁的实现方式 (索引) 还会导致间隙锁。

客户端 1:
mysql> select @@tx_isolation;
+—————–+
| @@tx_isolation  |
+—————–+
| REPEATABLE-READ |
+—————–+
1 row in set
mysql> start transaction;    – 关闭自动提交 
Query OK, 0 rows affected
mysql> update tt_express_order t set t.cust_code=’ 行锁 ’ where t.order_id=1;      –ID 有索引，行锁
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

客户端 2:
mysql> start transaction;
mysql>  update tt_express_order t set t.cust_code=’ 行锁 2 ′ where t.order_id=2;                – 行锁，与客户端 1 不冲突. 更新成功
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0 
数据库信息:
RECORD LOCKS space id 8 page no 4 n bits 96 index `PRIMARY` of table `ecbil`.`tt_express_order` trx table locks 1 total table locks 1  trx id 148742 lock_mode X locks rec but not gap lock hold time 156 wait time before grant 0
Query OK, 0 rows affected
mysql> update tt_express_order t set t.cust_code=’ 表锁 ’ where t.cust_code=’ 行锁 2 ′;                  –cust_code 没有索引, 需要获取表锁. 但客户端 1 一直未释放记录 1 的行锁，所以 lock_wait;
1205 – Lock wait timeout exceeded; try restarting transaction

这个问题如果不解决，项目上线运行情况肯定很糟糕。因为刚刚接触 mysql 对它的架构还不大了解，所以这个问题还是困扰了我一两天。仔细想想外加动手验证，发现其实就是隔离级别的问题。之前 Oracle 和其他大部分数据默认的是 read-committed 隔离级别，所以不会有这种情况. 但 mysql 默认是可重复读，所以会有上述问题。所以最快速、简单的办法就是降低数据的隔离级别, 改成 Read-Committed.
transaction-isolation = read-committed  – 修改 my.ini 配置
 mysql> select @@tx_isolation;
    +—————-+
    | @@tx_isolation |
    +—————-+
    | READ-COMMITTED |
    +—————-+
    1 row in set

客户端 1：

mysql> start transaction;
Query OK, 0 rows affected
mysql> update tt_express_order t set t.cust_code=’ 表锁 ’ where t.cust_code=’ 行锁 2 ′; 
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

客户端 2:

mysql> start transaction;
Query OK, 0 rows affected
mysql> update tt_express_order t set t.cust_code=’ 第一行记录 ’ where t.cust_code=’ 行锁 ’;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
mysql> commit;
Query OK, 0 rows affected

将隔离级别设置成 Read-Commit 之后，即使不走索引也没有发生表锁 (因为这种隔离级别，没必要锁表以保证数据能被可重复读)。客户端 1 和客户端 2 互补影响, 问题解决.
 
以上测试, 纯属个人设想和验证，如有不正确和遗漏之处请大虾指正。

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-11/137606.htm