MySQL高级学习之行锁

MySQL高级学习之行锁

1.概述

行锁特点 ：偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

InnoDB 与 MyISAM 的最大不同有两点：一是支持事务；二是 采用了行级锁。

2.InnoDB的行锁模式

  • 共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

  • 排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获

    取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；

对于普通SELECT语句，InnoDB不会加任何锁；

3.行锁演示

3.1建表语句

create table test_innodb_lock(
	id int(11),
	name varchar(16),
	sex varchar(1)
)engine = innodb default charset=utf8;

insert into test_innodb_lock values(1,'100','1');
insert into test_innodb_lock values(3,'3','1');
insert into test_innodb_lock values(4,'400','0');
insert into test_innodb_lock values(5,'500','1');
insert into test_innodb_lock values(6,'600','0');
insert into test_innodb_lock values(7,'700','0');
insert into test_innodb_lock values(8,'800','1');
insert into test_innodb_lock values(9,'900','1');
insert into test_innodb_lock values(1,'200','0');

create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);

3.2 基本演示

会话A 会话B 描述
image-20200918121558361 image-20200918121525504 会话AB：关闭自动提交功能
image-20200918121815201 image-20200918121901342 会话AB：都可以正常查询数据
image-20200918122049669 image-20200918122106340 会话AB：都查询id为3的数据
image-20200918122748665 image-20200918122836288 会话A：更新id为3的数据，但是不提交 会话B：更新id为3的数据，处于等待状态
image-20200918123125454 image-20200918123306951 会话A：提交事务 会话B：解除阻塞状态，更新成功，但未提交
以上都是操作同一行的数据，现在来演示 不同行的数据。
image-20200918123646268 image-20200918123712408 会话A：更新id为1的数据。正常获取到行锁，更新成功。 会话B：由于与会话A操作的不是同一行数据，获取当前行锁，更新成功

4.无索引或索引失效会导致行锁升级为表锁

如果不通过索引条件检索数据，或者索引失效，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。

会话A：

image-20200918135224868

会话B：

image-20200918135115567

从上图可以看出，我们操作的是两个不同行的数据，但是会话B却发生了阻塞。这是因为sex字段定义为varchar类型，而我们在会话A中传

递的是int类型，发生了自动转换导致索引失效，进而是行锁升级为表锁，所以会话B会阻塞。

5.间隙锁

间隙锁定义

当我们用范围条件，而不是使用相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据进行加锁； 对于键值在条件范围内但并不存在的记录，叫做 "间隙（GAP）" ， InnoDB也会对这个 "间隙" 加锁，这种锁机制就是所谓的 间隙锁（Next-Key锁） 。

间隙锁危害

因为在Query执行过程中通过范围查找的话，会锁定整个范围内的所有索引键值，即使这个索引不存在。间隙锁有一个比较致命的弱点，就是当锁定一个范围键值后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定值范围内的任何数据。在某些场景下这个可能会对性能造成很大的危害。

间隙锁演示

会话A 会话B 描述
image-20200918140510815 image-20200918140452197 会话A：进行范围更新。会话B：发生阻塞
image-20200918140635181 image-20200918140717415 会话A：提交事务。会话B：更新成功

注意：表中没有id为2的数据

6.如何锁定某一行

利用begin，for update语句

6.1会话A对id为5这一行数据进行锁定

image-20200918141317184

6.2 会话B对id为5这一行的数据进行修改

image-20200918141554205

发生阻塞

6.3会话Acommit

image-20200918141649184

6.4 会话B更新成功

image-20200918141712480

7. InnoDB 行锁争用情况

show  status like 'innodb_row_lock%';

image-20200918141826701


Innodb_row_lock_current_waits: 当前正在等待锁定的数量

Innodb_row_lock_time: 从系统启动到现在锁定总时间长度

Innodb_row_lock_time_avg:每次等待所花平均时长

Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间

Innodb_row_lock_waits: 系统启动后到现在总共等待的次数

当等待的次数很高，而且每次等待的时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

8.总结

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些，但是在整体并发处理能力方面要远远由于MyISAM的表锁的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势。

但是，InnoDB的行级锁同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

优化建议：

  • 尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁。
  • 合理设计索引，尽量缩小锁的范围
  • 尽可能减少索引条件，及索引范围，避免间隙锁
  • 尽量控制事务大小，减少锁定资源量和时间长度
  • 尽可使用低级别事务隔离（但是需要业务层面满足需求）

posted @ 2020-09-18 14:26  毕竟是曾经  阅读(166)  评论(0)  编辑  收藏  举报