MySQL数据库学习- 6 | 行锁功过：怎么减少行锁对性能的影响？

  • 锁的类型
  • 行锁
  • 两阶段锁
  • 死锁和死锁检测
  • 参考资料
  • 写在后面


锁的类型

环境： MySQL 5.7.24, for linux-glibc2.12 (x86_64)

数据库锁设计的初衷是 处理并发 问题。

  • 作为多用户共享的资源，当出现并发访问时，数据库需要合理的控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。
  • 根据加锁的范围， MySQL 里的锁大致可以分成全局锁、表级锁和行锁三类。

行锁

行锁是针对数据库表中行记录的锁。

MySQL 的行锁是在存储引擎层1实现的。

  • InnoDB引擎支持行锁，MyISAM引擎不支持行锁。
  • 不支持行锁意味着并发控制只能使用表锁，在同一张表上任何时刻只能有一个更新在执行，会影响业务并发度。

两阶段锁

两阶段锁协议：InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。

举个例子，下面的操作步骤，事务B的update语句执行时会是什么样子？

-- CREATE
mysql> create table T(id int NOT NULL primary key, k int) engine=InnoDB;
-- INSERT
insert into T(id, k) values(1, 1),(2, 2);

步骤

事务A

事务B

1

begin;

2

update t set k=k+1 where id=1;

3

update t set k=k+1 where id=2;

4

begin;

5

update t set k=k+2 where id=1;

6

commit;

实际上，事务B的update语句会被阻塞，直到事务A的commit执行后，事务B才能继续执行。
因为事务A持有的2个记录的行锁，都是在commit时才释放。

如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

死锁和死锁检测

并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，会导致这几个线程都进入无限等待的状态，称为死锁。

步骤

事务A

事务B

备注

1

begin;

begin;

启动事务

2

update t set k=k+1 where id=1;

A对id=1加行锁

3

update t set k=k+1 where id=2;

B对id=2加行锁

4

update t set k=k+1 where id=2;

A等待B释放id=2行锁

5

update t set k=k+1 where id=1;

B等待A释放id=1行锁

事务A和事务B在互相等待对方的资源释放，进入了死锁状态。出现死锁后，有两种策略：

  • 直接进入等待，直到超时。超时时间通过参数innodb_lock_wait_timeout设置，默认为50秒。
  • 发起死锁检测，发现死锁后主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。通过参数innodb_deadlock_detect设置为on开启死锁检测。

正常情况下，采用的是主动死锁检测策略。死锁检测的额外负担就是需要耗费大量的CPU资源（出现场景：热点行更新时会导致性能问题）。

如果业务设计确保不会出现死锁时，可以临时把死锁检测关闭。

数据库服务端做控制并发度。如果有中间件，可考虑在中间件实现。比如，对同一行同一时间最多只有10个线程在更新。基本思路是对于相同行的更新，在进入引擎之前排队。

通过业务设计，将一行改成逻辑上的多行来减少锁冲突。

参考资料

《高性能MySQL》
《MySQL实战45讲》 作者：丁奇

写在后面

之前学习了大神丁奇的《MySQL实战45讲》，目前在看《高性能高MySQL》，也想自己整理一下MySQL知识点，发现力不从心，也发现大神之所以是大神，那是因为真的牛。

推荐大家还是去学习丁奇的《MySQL实战45讲》，条理清晰，循序渐进，深入浅出，通俗易懂。而且每一讲后面都有高质量的留言评论， 从中能获益良多。感谢！

  • 如有 错误之处 还请多多指正。希望能给您带来帮助。
  1. MySQL基础架构 ↩︎