InnoDB 锁与隔离级别

rgw2qe

最近在做一个项目中使用到了MariaDB（Innodb存储引擎），系统并发性比较高需要不停的接收前端传过来的数据(每天大概400W)，传过来之后系统会自动的尽快处理计算结果(分批处理，需要更新业务表)。在开发过程中经常出现死锁和锁等待的问题。翻阅了一些资料和动手验证，整理如下：  InnoDB默认的隔离级别是可重复读，默认行锁。但是它行锁在可重复读的隔离级别下是根语句据索引实现的。如果没有建索引或者因为数据原因查询分析器没有走索引则还是会获取表锁。即使加上相关的hint也无效。此外因为隔离级别和锁的实现方式(索引)还会导致间隙锁。
   客户端1:

1 2 3 4 5 6 7 8 9 10 11 12 13

客户端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

1 2 3 4 5 6 7 8 9 10

客户端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.

1 2 3 4 5 6 7 8

transaction-isolation = read-committed  --修改my.ini配置 mysql> select @@tx_isolation;     +----------------+     | @@tx_isolation |     +----------------+     | READ-COMMITTED |     +----------------+     1 row in set

1

客户端1：

1 2 3 4 5

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

1

客户端2:

1 2 3 4 5 6 7

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互补影响,问题解决.

  以上测试,纯属个人设想和验证，如有不正确和遗漏之处请大虾指正。