MySQL性能优化（三）事务与锁详解

于 2023-04-09 21:32:36 发布
阅读量1k 收藏 2
点赞数
分类专栏： MySQL 数据库 文章标签： mysql 性能优化 数据库
本文为博主Huathy原创文章，遵循 CC 4.0 BY-NC-SA 版权协议。
未经许可不得转载！否则将依法追究相关责任。
本文链接：https://blog.csdn.net/qq_40366738/article/details/130048329
版权

文章目录

  1. 事务的特性与事务并发造成的问题
  2. 事务读一致性问题的解决方案
  3. MVCC的原理
  4. 锁的分类，行锁的原理、行锁的算法
  5. 死锁的原因，如何避免

本文数据库版本：

select VERSION();
-- 5.7.41
show variables like '%engine%';
-- InnoDB
show global variables like '%tx_isolation%';
-- tx_isolation	REPEATABLE-READ  可重复读

什么是数据库事务？

事务是数据库管理系统执行过程的一个逻辑单元，由一个有限的数据库操作序列构成。并不是所有的存储引擎都支持事务。在MySQL中支持事务的存储引擎包括InnoDB和NDB。

事务的四大特性：ACID

  • Atomic：原子性（逻辑单位不可再分。要么全部成功，要么全部失败。InnoDB是通过undolog来实现的。）
  • Consistency：一致性（数据库完整性的约束没有破坏）
  • Isolation：隔离性（事务与其他事务之间相互隔离）
  • Durability：持久性（事务提交的结果是持久的。崩溃恢复crash-safe。redolog+双写缓冲。）

事务的开启与结束

案例表结构与数据

CREATE TABLE `student` (
  `id` int(16) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `sno` varchar(16) DEFAULT NULL COMMENT '学号',
  `sname` varchar(64) DEFAULT NULL COMMENT '姓名',
  `company` varchar(128) DEFAULT NULL COMMENT '公司',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `student` (`id`, `sno`, `sname`, `company`) VALUES (1, '1001', 'Huathy', 'Huathy');
INSERT INTO `student` (`id`, `sno`, `sname`, `company`) VALUES (2, '1002', 'hx', 'Huathy');
INSERT INTO `student` (`id`, `sno`, `sname`, `company`) VALUES (3, '1003', 'Dy', 'Huathy');

案例

UPDATE student SET sname = 'WHX' where id = 2;
-- 增删改语句默认开启事务 autocommit	ON
show variables like '%autocommit%'
-- 开启事务;
begin;	
update student set sname = 'whx2' where id = 2;
-- 这里还没有提交，新开一个窗口则会查询出不同的结果。
select * from student where id = 2;
rollback;		-- 回滚事务，连接断开也会自动回滚
commit;			-- 提交事务
-- 一个事务持有的锁会在事务结束的时候释放

事务并发的三大问题：

事务并发的三大问题，其实都是数据库读一致性的问题。必须由数据库提供一定的事务隔离机制来解决。

脏读（一个事务读取到了其他事务未提交的数据）

image.png

不可重复读（一个事务读取到其他事务已提交的数据造成读不一致）

image.png

幻读（一个事务读取到了其他事务新增的数据造成读不一致）

image.png
区别：只有插入数据导致的前后两次读不一致，才称为幻读。

事务隔离级别 —— SQL92标准

隔离级别解决问题存在问题
Read UnCommitted（RU 未提交读）事务未提交的数据对其他事务也是可见的，会出现幻读。
Read Committed（RC 已提交读）解决脏读问题一个事务开启，只能看到已提交的事务所作的修改。
会出现不可重复读。
Repeatable Read（RR 可重复读）解决不可重复读问题在同一个事务中多次读取同样的数据结果是一样的。
这种未定义解决幻读问题。
Serializable（可序列化）解决所有问题最高的事务隔离级别，强行事务串行执行

image.png

事务隔离级别的修改

-- 设置事务隔离级别为 未提交读
set global transaction isolation level read uncommitted;
-- 设置事务隔离级别为 已提交读
set global transaction isolation level read committed;
-- 设置事务隔离级别为 可重复读
set global transaction isolation level repeatable read;
-- 设置事务隔离级别为 可序列化
set global transaction isolation level serializable;

事务隔离级别的解决方案

  1. 在数据读取前，对其加锁，阻止其他事务对数据进行修改(LBCC)。Lock Based Concurrency Control。
  2. 生成一个数据请求时间点的一致性数据快照(Snapshot)，并利用这个快照来提供一定级别(语句级或事务级)的一致性读取(MVCC)。Multi Version Concurrency Control。

多版本并发控制（MVCC）核心思想

效果：建立一个快照，同一个事务，无论多少次查询的数据都是相同的。
规则：

  • 一个事务能看到的数据版本：
    1. 第一次查询之前已经提交的事务修改
    2. 本事务的修改
  • 一个事务不能看到的数据版本：
    1. 在本事务第一次查询之后创建的事务（事务ID比当前事务ID大）
    2. 活跃的（未提交的）事务的修改
案例

image.png

--  创建表
DROP TABLE IF EXISTS `mvcctest`;
CREATE TABLE `mvcctest`  (
  `id` int(11) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- 插入数据
truncate table mvcctest;
begin;
INSERT INTO `demo`.`mvcctest`(`id`, `name`) VALUES (1, 'whx');
INSERT INTO `demo`.`mvcctest`(`id`, `name`) VALUES (2, 'Huathy');
commit;

image.png

MVCC 实现原理：Read View（一致性视图）存储内容

image.png

ReadView判断规则
  1. 从数据的最早版本开始判断（undo log）
  2. 数据版本的trx_id = creator_trx_id，本事务修改，可以访问。
  3. 数据版本的trx_id < min_trx_id(未提交事务的最小ID)，说明该版本在生成Read View前已经提交，可以访问。
  4. 数据版本的trx_id > max_trx_id（下一个事务ID），该版本是生成Read View后才开启的事务，不能访问。
  5. 数据版本的trx_id在min_trx_id和max_trx_id之间，看看是否在m_ids（活跃事务ID）中，如果在则不可以。如果不在则可以。
  6. 如果当前版本不可见，就找undo log链中的下一个版本。
RC(已提交读)与RR(可重复读)中ReadView的区别

RR的ReadView是事务第一次查询的时候就创建的。
RC的ReadView是事务每次查询的时候都会重新创建。

锁🔒

MyIsam支持表锁，InnoDB支持行锁

表锁与行锁的区别

锁粒度：表锁 > 行锁
加锁效率：表锁 > 行锁
冲突概率：表锁 > 行锁
并发性能：表锁 < 行锁

表锁的锁定与解锁

lock tables xxx read;
lock tables xxx write;;
unlock tables;

InnoDB锁类型（Lock Mode）

  • 共享锁（行锁）：Shared Lock
  • 排他锁（行锁）：Exclusive Lock
  • 意向共享锁（表锁）：Intention Shared Lock
  • 意向排他锁（表锁）：Intention Exclusive Lock
共享锁：

也成为读锁，S锁。共享锁就是多个事务对于同一个数据可以共享同一把锁。都能访问到数据，但是只读不能改。
加锁释锁方式：

select * from student where id = 1 lock in shared mode;
commit / rollback;
排他锁：

也成为写锁，X锁。排他锁不能与其他锁并存，如果一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的锁（共享锁、排他锁）。只有该获取了排他锁的事务是可以对数据行进行读写的。
加锁释锁方式：

-- 自动方式：
-- delete / update / insert 默认加上X锁
-- 手动方式：
select * from student where id=1 for update;
commit / rollback;
意向锁：意向共享锁（IS） / 意向排他锁（IX）

意向锁是由数据库引擎自己维护的，用户无法手动操作意向锁。

  1. 意向共享锁（Intention Shred Lock）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁之前必须取得该表的IS锁。
  2. 意向排他锁（Intention Exclusive Lock）：表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

问题：为什么需要（表级别的）意向锁？类似于一个锁标志。
一个事务能够成功给一张表加上表锁的前提是没有任何一个事务已经锁定了这个表的任意一行数据。

锁的作用：

为了解决并发资源竞争的问题。

InnoDB的行锁到底锁的是什么？锁住的是索引。

案例

  1. t1表：不使用索引，锁表
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t1` (`id`, `name`) VALUES (1, '1');
INSERT INTO `t1` (`id`, `name`) VALUES (2, '2');
INSERT INTO `t1` (`id`, `name`) VALUES (3, '3');
INSERT INTO `t1` (`id`, `name`) VALUES (4, '4');

-- Session 1
begin;	
SELECT * FROM t1 WHERE id =1 FOR UPDATE;
  
-- Session 2
select * from t1 where id=3 for update;
INSERT INTO `t1` (`id`, `name`) VALUES (5, '5');

行锁锁住的是索引。但是t1表中没有建立主键索引，InnoDB使用了隐藏的_rowId。而我们这里的select语句并没有使用索引来访问，导致全表扫描。所以锁住了所有行。也就是锁表了。这也是我们加锁必须带上精确条件的原因。

  1. 使用主键索引t2
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t2` (`id`, `name`) VALUES (1, '1');
INSERT INTO `t2` (`id`, `name`) VALUES (4, '4');
INSERT INTO `t2` (`id`, `name`) VALUES (7, '7');
INSERT INTO `t2` (`id`, `name`) VALUES (10, '10');
-- Session 1
begin;	
SELECT * FROM t2 WHERE id =1 FOR UPDATE;
-- Session 2
select * from t2 where id=1 for update;	
select * from t2 where id=4 for update;
ROLLBACK;
  1. 使用唯一索引t3
DROP TABLE IF EXISTS `t3`;
CREATE TABLE `t3` (
  `id` int(11) ,
  `name` varchar(255) ,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t3` (`id`, `name`) VALUES (1, '1');
INSERT INTO `t3` (`id`, `name`) VALUES (4, '4');
INSERT INTO `t3` (`id`, `name`) VALUES (7, '7');
INSERT INTO `t3` (`id`, `name`) VALUES (10, '10');
-- Session 1
begin;
select * from t3 where name= '4' for update;
-- Session 2
select * from t3 where name = '4' for update;
select * from t3 where id = 4 for update;

select * from t3 where id = 1 for update;

image.png

行锁的算法：锁住什么范围？

区间划分

image.png

记录锁（Record Lock）：锁定记录

image.png

间隙锁（Gap Lock）：锁定范围

image.png
GapLock只在RR（可重复读）中存在。用来阻塞插入。解决幻读问题。

-- Session 1
begin;  
select * from t2 where name= '4' for update;
-- Session 2
INSERT INTO t2 VALUES (5, '5');
INSERT INTO t2 VALUES (6, '6');

临键锁（Next-key Lock）：锁定范围加记录

Next-key Lock = GapLock + RecordLock
image.png

事务隔离级别的实现：

image.png

事务隔离级别的选择：RC（已提交读）和RR（可重复读）的主要区别

  1. RR的间隙锁会导致锁定范围的扩大
  2. 条件列未使用到索引，RR锁表，RC锁行
  3. RC的"半一致性"（semi-consistent）读可以增加update操作的并发性。

死锁的条件

互斥、不可剥夺、形成等待环路

开启标准监控和锁监控

# 开启标准监控和锁监控
set global innodb_status_output=on;
set global innodb_status_output_locks=on;
# 查看这条语句的加锁情况
show engine innodb status;

死锁的避免

  1. 顺序访问
  2. 数据排序
  3. 申请足够级别的锁
  4. 避免没有where条件（不命中索引）的操作
  5. 大事务分解成小事务
  6. 使用等值查询而不是范围查询
Huathy-雨落江南，浮生若梦
关注
专栏目录
深入理解MySQL锁机制与性能优化：详解记录锁、间隙锁、临键锁及慢SQL查询分析
搬砖的小熊猫的博客
07-24 1465
Read View 保存了当前事务开启时所有活跃（还没有提交）的事务列表，换个角度，可以理解为 Read View 保存了不应该让这个事务看到的其他的事务 ID 列表。第三种情况，当我们使用了范围查询，不仅仅命中了 Record 记录，还包含了 Gap 间隙，在这种情况下我们使用的就是临键锁，它是 MySQL 里面默认的行锁算法，相当于记录锁加上间隙锁。越靠上，表示查询效率越高。第一种情况，当我们对于唯一性的索引（包括唯一索引和主键索引）使用等值查询，精准匹配到一条记录的时候，这个时候使用的就是记录锁。
在事务中更新大量数据造成的锁等待超时和慢查询
qq_23204557的博客
10-15 869
根据公司SRE给出的慢查询记录： 我在Kibana中查到了相关的600多条日志： 根本原因在于某sql update，where id in的列表过长（多达几百），range过大的互斥锁不好申请（估计读已提交级别，update … where id in (…)采用的是间隙锁，查过一些资料，未能100%确认）。它所在的事务一直申请不到锁，就超时（默认50秒）报错了： com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock
mysql update 事务_mysql同一个事务中update，insert导致死锁问题分析解决
weixin_42501161的博客
01-18 1323
现象：在一个repeatable read级别的事务中采用先update再insert的方式，当这段代码并发执行时造成死锁；示例如下： 如表student中只有一条记录 ，该记录的code 字段值为1，且该表在code上建立索引。transaction A：updatestudentsetyn=1wherecode='2';insertintostudent(code,…)...
mysql 事务 for update_Mysql 锁表 for update （引擎/事务）
weixin_28727321的博客
01-19 389
因为之前用过oracle，知道利用select * for update 可以锁表。所以很自然就想到在mysql中能不能适应for update来锁表呢。学习参考如下由于InnoDB预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL才会执行Row lock (只锁住被选取的资料例) ，否则MySQL将会执行Table Lock (将整个资料表单给锁住)。举个例子:假设有个表...
Mysql 事务中Update 会锁表吗？
LoveSummer
10-12 4505
Mysql 事务中Update 会锁表吗？ 两种情况： 1.带索引 2.不带索引 前提介绍： 方式：采用命令行的方式来模拟 1.mysq由于默认是开启自动提交事务，所以首先得查看自己当前的数据库是否开启了自动提交事务。 命令：select @@autocommit; 结果如下： +--------------+ | @@autocommit | +--------------+ | 0 | +--------------+ 如果是1，那么运行命令：set autocommit = 0;设置
事务-update操作一点观察
一颗学徒
08-03 723
如果数据库的隔离级别是读已提交（Read Committed），这是很多数据库默认的隔离级别。在这种情况下：如果事务要更新一条记录，而这条记录恰好被另一个运行中但未提交事务更改（被锁定或删除），则当前事务会阻塞，等待直到另一个事务提交或回滚后，再继续处理（First Updater Win Rule）。如果另一个事务回滚了，那么当前事务可以继续执行，更新这条记录。如果另一个事务提交了，要分两种情形。第一种，要是这条记录被删除了，那么忽略这条记录；第二种，这条记录被更新了，需要重新判断这条记录是否满足谓词条件
MySQL InnoDB之事务与锁详解
09-11
MySQL的InnoDB存储引擎在处理数据操作时，广泛使用事务和锁来确保数据的完整性和安全性。事务是数据库操作的基本单元，它确保一系列操作要么全部完成，要么全部不完成，从而保证了数据的一致性。InnoDB事务支持四大...
MySQL性能优化详解.docx
最新发布
03-26
### MySQL性能优化详解 #### 一、MySQL性能优化的重要性及背景 在Web应用程序体系结构中，数据持久层（通常是关系数据库）是系统的核心部分之一，对整体系统的性能有着至关重要的影响。MySQL作为目前使用最广泛的...
MySQL架构执行与SQL性能优化 MySQL高并发详解 MySQL数据库优化训练营四期课程
06-17
课程内容进行了精华的浓缩，有四大内容主旨，MySQL架构与执行流程，MySQL索引原理详解，MySQL事务原理与事务并发，MySQL性能优化总结与MySQL配置优化。课程安排的学习的教程与对应的学习课件，详细的学习笔以及课程...
两个事务Update导致的死锁问题
Just_do_it_HZF的博客
04-17 2594
引言 近来做省一级计算机一级考试系统的时候，学生端进行大批量判分的时候，出现了这样的问题（事务(进程 ID 262)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。）： 这个就是我们在代码中写了大批量的update语句，用trace Profiler ，我们对死锁追踪是这样的： 分析： 我们来分析一下上面的图，上面为DeakLock graph，图中左右两边的椭圆形相当于一个处理节点（Process Node），当鼠标移动到上面的时候，可以看到内部执行的代码.
理解完这些基本上能解决面试中MySql的事务问题
Ccww_的博客
10-16 2035
越努力，越幸运， 本文已收藏在GitHub中JavaCommunity, 里面有面试分享、源码分析系列文章，欢迎收藏，点赞 https://github.com/Ccww-lx/JavaCommunity 前言 在面试中，基本上都会问到关于数据库的事务问题，如果啥都不会或者只回答到表面的上知识点的话，那面试基本上是没戏了，为了能顺利通过面试，那MySql的事务问题就需要了解，所以就根据网上的资料总结一版Mysql事务的知识点，巩固一下事务的知识。 事务 事务是指逻辑上的一组操作，要么都执行，要么都不执行.
记录高并发事务中Update导致MySQL死锁的问题
weixin_42260698的博客
09-25 2526
背景：后台批量发券的接口，在运营导入要发券的excel后，后台开始批量发券 问题代码： @Transactional(rollbackFor = Exception.class) public boolean sendCouponToMoreUser(Date date, CouponToMoreUserMsg couponMsg) { //1.查询优惠券的基础信息（select 优惠券表） //2.更新优惠券基础信息中的剩余数量 （update 优惠券表） //3.发券 ...
update慢如何处理？
elvis的Oracle Lab
07-10 4877
update慢: 1.表的pctfree参数设置? 2.执行计划用索引还是全表扫? 3.SQL语句写法问题? 4.update慢还是commit慢? 5.更新多少条数据? 6.表是否频繁update造成碎片过多? 7.考虑使用merge? 8.应用问题? 9.数据库参数设置?
技术干货 | Update更新慢、死锁等问题的排查思路分享
GreatDB的博客
08-04 2744
内容提纲 一、简介 二、Update 生命周期 2.1 连接器 2.2 分析器 2.3 优化器 2.4 执行器 2.4.1 事务执行阶段 2.4.2 事务提交阶段 2.4.3 假设事务 ROLLBACK 三、影响事务提交延迟的几种情况 四、Update 更新慢的排查思路 五、常见问题 一、简介 在开始排错Update之前，我们需要知道 Update 在 MySQL 中的生命周期是什么，MySQL 如何执行一个事务的。理解了如何执行，才知道如何去排查故障。 二、Update 生命周期 Server 层阶段 2
sql如何进行脱敏操作_《深入精通Mysql（六）》系列之如何通过慢查询日志进行SQL分析和优化...
weixin_39821874的博客
11-20 267
深入精通Mysql系列其他文章推荐：《深入精通Mysql（一）》系列之Mysql整体架构和sql执行过程《深入精通Mysql（二）》深入底层剖析Mysql索引（面试必问）《深入精通Mysql（三）》深入底层剖析Mysql各种锁机制（面试必问）《深入精通Mysql（四）》MySQL 事务机制，中高级开发面试必问！《深入精通Mysql（五）》实战：Mysql实现主从复制《深入精通Mysql（六）》系列...
MySQL安装教程+navicat安装教程图解
Huathy的博客
09-09 6712
这里写自定义目录标题欢迎使用Markdown编辑器新的改变功能快捷键合理的创建标题，有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能，丰富你的文章UML 图表FLowchart流程图导出与导入导出导入 @ 欢迎使用...
SQL server基础
Huathy的博客
03-10 5547
SQL server基础1. SQL语言的分类2. SQL server库&表操作与约束2.1 库操作:2.1.1 创建数据库：2.1.2 修改数据库:2.1.3 删除数据库：2.2 表操作:2.2.1 SQL server常用数据类型：2.2.2 创建表:2.2.3 修改表:2.3 约束4. 数据的操作4.1 增:4.2 删:4.3 改:4.4 查: 1. SQL语言的分类 DDL 数...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Huathy-雨落江南，浮生若梦

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值