写点什么

MySQL 锁机制总结

  • 2022 年 9 月 13 日
    北京
  • 本文字数:1255 字

    阅读完需:约 4 分钟

锁是计算机协调多个进程或纯线程并发访问某一资源的机制。在数据库中,除传统的计算资源(CPU、RAM、I/O)的争用以外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂。防止更新丢失,并不能单靠数据库事务控制器来解决,需要应用程序对要更新的数据加必要的锁来解决。

通常事务 T 在度某个数据对象(如表、记录等)操作之前,先向系统发出请求,对其加锁,加锁后事务 T 就对数据库对象有一定的控制,在事务 T 释放它的锁之前,其他事务不能更新此数据对象。

锁分类

从对数据库操作的类型分

读锁(共享锁)

针对同一份数据,多个读操作可以同时进行而不会互相影响

写锁(排它锁)

当前写操作没有完成前,它会阻断其他写锁和读锁

从对数据操作的粒度分,分为表锁和行锁

表锁偏向 MylSAM 存储引擎,开销小,加锁快,无思索,锁定粒度大,发生锁冲突的概率最高,并发度最低。

手动增加表锁

lock table 表名称 read(write),表名称 2 read(write),其他;

查看表上加过的锁

show open tables;

删除表锁

unlock tables;


结论

读锁会阻塞写,但是不会阻塞读。

写锁会把读和写都阻塞。

表锁分析:

查看哪些表被加锁了

show open tables;

如何分析表锁定

可以通过检查 table_lock_waited 和 table_locks_immediate 状态变量来分析系统上的表锁定 show status like ‘table%’;

这里有两个状态变量记录 MySQL 内部表级锁定的情况,两个变量说明如下:

Table_locks_immediate:产生表级锁定的次数,表示可以立即获取锁的查询次数,每立即获取锁值加 1

Table_locks_waited 出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁值加 1),此值高则说明存在着较严重的表级锁争用情况。

行锁分析:

通过检查 InnoDB_row_lock 状态变量来分析系统上的行锁的争夺情况

show status like ‘innodb_row_lock%’;

对各个状态量的说明如下:

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

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

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

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

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

对于这 5 个状态变量,比较重要的主要是:

Innodb_row_lock_time_avg (等待平均时长)

Innodb_row_lock_waits (等待总次数)

Innodb_row_lock_time(等待总时长)

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

优化

1、尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁

2、合理设计索引,尽量缩小锁的范围

3、尽可能减少检索条件,避免间隙锁

4、尽量控制事务大小,减少锁定资源量和时间长度

5、尽可能低级别事务隔离


更多学习资料戳下方

https://qrcode.ceba.ceshiren.com/link?name=article&project_id=qrcode&from=infoQ&timestamp=1662366626&author=xueqi

用户头像

社区:ceshiren.com 2022.08.29 加入

微信公众号:霍格沃兹测试开发 提供性能测试、自动化测试、测试开发等资料、实事更新一线互联网大厂测试岗位内推需求,共享测试行业动态及资讯,更可零距离接触众多业内大佬

评论

发布
暂无评论
MySQL锁机制总结_MySQL_测吧(北京)科技有限公司_InfoQ写作社区