mysql 性能优化(七)锁机制_CodingAnHour的博客-程序员宅基地_mysql锁优化

技术标签: mysql锁  MySql  

1、锁介绍

解决因资源共享,而造成的并发问题。
例如:例如:电商A,B两人同时购买最后一件商品
A:仅快0.0001秒抢到的->加锁->处理业务逻辑->释放锁
B:在B获取到商品时,商品已加锁,等待所释放,商品状态已发生改变,已售空。

  • 分类:
    • 操作类型:
      • 读锁(共享锁):对同一个数据,可以同时进行多个读操作,互不干扰
      • 写锁(互斥锁): 如果当前写操作没有完成(未释放锁),则无法进行其他的读操作和写操作
    • 操作范围:
      • 行锁:一次性对一行加锁,如InnoDB存储引擎,开销大,加锁慢,容易出现死锁,锁的范围较小,不容易发生锁冲突,并发度高(很少发生,脏读、幻读、不可重复读、丢失更新)
      • 表锁:一次性对一个表加锁,如MyISAM存储引擎,开销小,加锁快,无死锁,容易生锁冲突,并发度低
      • 页锁(基本不用)

1.1、MyISAM表锁

  • 查看锁

    1代表加了锁
    show open tables;

  • 增加锁

    locak table 表1 read/write ,表2 read/write…可以加多个

  • 释放锁

    unlocak tables;
    也可以通过事物解锁,ROLLBACK / COMMIT

  • 查看表状态

    SHOW STATUS LIKE ‘table%’;

    • Table_locks_immediate:可以获取到的表锁数
    • Table_locks_waited:需要等待的表锁数,值越大说明锁竞争越大
    • Table_open_cache_hits:表缓存命中的次数
    • Table_open_cache_misses:高速缓存大小
    • Table_open_cache_overflows:超过了table_open_cache的设置,则会进行淘汰,淘汰的值记录到Table_open_cache_overflows

一般建议:Table_locks_immediate / Table_locks_waited > 5000采用InnoDb,否则采用MyISAM

1.1.1、加读锁

例如:会话0给A表增加了读锁

  • 当前会话
    • 如果给A表加了读操作,则当前会话只能对A表进行读操作
  • 其他会话
    • 可以对其他表进行读写操作
    • 可以对A表进行读操作,写操作需要等待会话0释放锁后才会执行

1.1.2、加写锁

例如:会话0给A表增加了写锁

  • 当前会话
    • 可以对当前表进行任何操作;
    • 不能操作其他表
  • 其他会话
    • 需要等待会话0释放锁后,才能进行增删改查操作

mysql表级锁的锁模式

MyISAM

  • 执行查询语句(select)前,会自动给涉及的所有表加读锁。
  • 执行更新操作(DML)前,会自动给涉及的表加写锁。

所以对MyISAM表操作会有一下几种情况

  • 对MyISAM表的读操作(加读锁)
    • 不会阻塞其他进程读(会话)对同一个表的读操作
    • 会阻塞对同一个表的写操作,只有读锁释放后才会执行其他会话写操作。
  • 对MyISAM表的写操作(加写锁)
    • 会阻塞其他进程(会话)对同一个表读写操作,写锁释放后才会执行其他会话读写操作。

2.1、行锁(InnoDB)

如果会话X对某条数据a进行DML操作

  • 其他会话需要等待会话A结束事物后,才能对a数据 进行操作
  • 行锁是通过事物来解锁的
  • 行锁一次锁一行数据,操作不同行数据互不干扰

注意

  • 如果没有索引或索引失效,则行锁转为表锁

通过检查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(等待总时长)

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

2.1.1、间隙锁

行锁的一种特殊情况:值在范围内,但却不存在

例如:一个表a中,ID有1、2、3、4、6、7,这些条数据,但是没有id等于5的

update a set name =‘name’ where id >1 and id< 7

在where中没有id等于5的数据,所以 id =5的数据称之为间隙

mysql会自动给间隙加锁 所以示例中会自动给id=5的数据加间隙锁(行锁)

如果有where 则加锁的范围就是where后面的范围,不是实际的值

  • 总结:
    InnoDB默认采用行锁;
    缺点:比表锁性能损耗大
    有点:并发能力强,效率高
    建议:高并发采用InnoDB,否则采用MyISAM
  • 分析

    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:等待锁的总时长,从系统启动到现在一共等待的次数

2.1.2、查询数据加锁

for update 行级锁,又叫排它锁,一旦用户对某个行施加了行级加锁,则该用户可以查询也可以更新被加锁的数据行,其它用户只能查询但不能更新被加锁的数据行.如果其它用户想更新该表中的数据行,则也必须对该表施加行级锁.即使多个用户对一个表均使用了共享更新,但也不允许两个事务同时对一个表进行更新,真正对表进行更新时,是以独占方式锁表,一直到提交或复原该事务为止。行锁永远是独占方式锁。

只有当出现如下之一的条件,才会释放共享更新锁:

  1. 执行提交(COMMIT)语句
  2. 退出数据库(LOG OFF)
  3. 程序停止运行

select * from tableName where id =1 for update;

FOR UPDATE仅适用于InnoDB,且必须在事务处理模块(BEGIN/COMMIT)中才能生效。

什么时候需要使用for update?

借助for update语句,我们可以在应用程序的层面手工实现数据加锁保护操作。就是那些需要业务层面数据独占时,可以考虑使用for update。

场景上,比如火车票订票,在屏幕上显示有票,而真正进行出票时,需要重新确定一下这个数据没有被其他客户端修改。所以,在这个确认过程中,可以使用for update。

2.1.3 update where 无索引字段更新或索引范围大

例如 一个用户表,学历字段qualification 是非索引字段

update user set  qualification ="本科以下" where qualification = "大专"

在执行更新时会全表锁,会导致有其他业务在使用时表中数据时产生Lock wait timeout exceeded; try restarting transaction 异常

或者是索引,但是索引覆盖范围太大,在业务请求量大时也会引起间隙锁

update user_table set qualification ="本科以下" where qualification = "大专" and ....
  • 总结
    • 尽量使用主键更新数据
    • 特殊场景一定要控制好索引覆盖范围

3、锁优化建议

  • 尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁
  • 合理设计索引,尽量缩小锁的范围
  • 尽可能减少检索条件范围,避免间隙锁
  • 尽量控制事务大小,减少锁定资源量和时间长度,涉及事务加锁的sql尽量放在事务最后执行
  • 尽可能低级别事务隔离
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/Extraordinarylife/article/details/118380334

智能推荐

CRF和HMM_baihaisheng的博客-程序员宅基地_crf hmm

CRF和HMM比较CRF是生成模型,HMM是判别模型HMM模型中存在两个假设:一是输出观察值之间严格独立(观察独立性假设),二是状态的转移过程中当前状态只与前一状态有关(齐次马尔科夫假设)CRF是在给定需要标记的观察序列的条件下,计算整个标记序列的联合概率分布,而不是在给定当前状态条件下,定义下一个状态的状态分布。缺点:训练代价大、复杂度高条件随机域模型相比较改进的隐马尔可夫模型可以更好更多的利用待识别文本中所提供的上下文信息以得更好的实验结果HMM:隐马尔可夫 模型,它一般以文本序列数据输入,

JDBC将连接数据库的信息配置到配置文件中_千万不要熬夜啊哈哈~的博客-程序员宅基地

jdbc.properties文件:driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3366/bjpowernodeuser=rootpassword=123package 连接数据库的信息配置到配置文件;import java.sql.Connection;import java.sql.DriverManager...

Windows下构建robotium+jenkins+TMTS可持续集成自动化测试_紫葡藤的博客-程序员宅基地

与时俱进,见此文:http://blog.csdn.net/hunterno4/article/details/43603959前言TMTS是淘宝的自动化测试构架,优缺点都较为明显优点:最主要的就是已经实现出错截屏并提供日志缺点:比较小众化,遇到问题也无人解答自动化测试终究是要能够持续集成才能有更大的意义的,利用robotium+jenkins可以实现集成测试,但此时要

Prophecy Book - 为 TensorFlowJS 做的 Notebook 小工具_weixin_33940102的博客-程序员宅基地

乱了很久终于还是签了三方,虽然工资不多还是算是有结果了。现在在学校上课无所事事,所以想着自己部门是做数据技术的,来做个东西玩AI好了(并不承认本人感染了AI)这个工具目前还是demo雏型,bug和功能需求,希望大家都能踊跃的提到github上面!因为要tfjs使用方便,请使用最新Chrome浏览器Github地址:github.com/mxz96102/pr…线上demo地址: pboo...

Android 第二章 本地文件的读写_liuhenghui5201的博客-程序员宅基地_android读写本地文件

 读写的第一种方式: 使用最初始的IO方式读写到应用包目录下面package com.example.login;import java.io.BufferedReader;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.Inpu...

Navicat导出数据库结构为PDF_weixin_30634661的博客-程序员宅基地

1、选中需要导出的数据表,右键选择打印表2、点击左上角打印,选择标红的打印机,点击确定,然后键入文件名,确定之后会生成后缀为xps的文件3、然后打开这个网址(https://xpstopdf.com/zh/)或百度 (“xps转pdf”),将生成的xps上传,转为pdf,下载即可转载于:https://www.cnblogs.com/yulongcode/...

随便推点

Typora软件快捷键_Struggle_Hard_Z的博客-程序员宅基地

Typora软件使用下载安装下载网址: https://www.typora.io/ # 根据不同的系统下载不同的版本下载.exe结尾的启动程序文件,下载完成后直接双击安装即可注意:软件的安装目录目录尽量不要使用C盘:会影响电脑性能 尤其是开机速度Typora基本使用Typora软件编写文档采用MarkDown格式,目前使用最为频繁的一种格式。 Typora该软件生成的文档后缀名是.md结尾格式编写标题(总共六级) 方式1: 几个警号就是几级标题 方式2: ct

cocos 链接PHP服务器,cocos2d-x3.0之请求网络(php服务器)_weixin_40001805的博客-程序员宅基地

HelloWorldScene.h#ifndef __HELLOWORLD_SCENE_H__#define __HELLOWORLD_SCENE_H__#include "cocos2d.h"#include "networkHttpClient.h"#include "cocos-ext.h"class HelloWorld : public cocos2d::Layer{public: ...

卧槽,又来一个 Java 神器!!_公众号-老炮说Java的博客-程序员宅基地

最近又赶上8月跳槽的高峰期(金九银十,招聘旺季),好多读者都问我要有没有最新面试题,找华为朋友整理一份内部资料《第6版:互联网大厂面试题》并分类4份PDF,累计 926 页!整个资...

操作系统发展史以及区别_weixin_45837954的博客-程序员宅基地

操作系统发展历史二进制编码ASSIC  每一个字符统一都需要8个bit来存储计算机容量1位 = 1bit8bit = 1byte = 1字节 1024bytes = 1kbytes =1KB 1024个字符,小文档 ,几百k可以表示一张图片1024KB = 1Million Bytes = 1MB = 1兆 , 几万字的文档, 大图片1024MB = 1Gigabytes ,...

北大计算机python教程_清华北大计算机系 联合推出的Java+Python1010集视频学习教程曝光..._牛学霸秒懂训练营的博客-程序员宅基地

Java 和 Python 双方都有各自适合和发展的领域,所以别人常问我学习什么语言好,或者让我在两种语言进行比较好坏。其实编程语言只有适不适合你个人去学,并不存在好坏,每种语言的存在即是合理的,你想从事什么工作就去学习什么语言。当然在2019年的今天,无论是 Java 还是 Python,学出来之后找工作都是很容易的,而且小编为大家准备的教程(免费)每天拿出2-3个小时自学就可以,学的时间长了,...

CMPT1.ConfigMgr Prerequisites Tool简介_weixin_33719619的博客-程序员宅基地

ConfigMgr Prerequisites Tool 使用指南系列-1:ConfigMgr Prerequisites Tool简介Lander Zhang 专注外企按需IT基础架构运维服务,IT Helpdesk 实战培训践行者博客:https://blog.51cto.com/lander IT Helpdesk实战培训视频课程:https://edu.51cto.com/lectur...

推荐文章

热门文章

相关标签