MySQL事务分析和锁机制分析

最新推荐文章于 2023-05-26 16:04:21 发布
最新推荐文章于 2023-05-26 16:04:21 发布
阅读量217 收藏
点赞数
分类专栏： 数据库 MySQL 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/chuaded/article/details/122451063
版权

MySQL事务分析和锁机制分析

事务

事务控制语句

-- 显示开启事务
START TRANSACTION | BEGIN
-- 提交事务
COMMIT
-- 回滚事务
ROLLBACK
-- 创建一个保存点,类似于C中的label
SAVEPOINT identifier
-- 删除一个保存点
RELEASE SAVEPOINT identifiter
-- 事务回滚到保存点
ROLLBACK TO [SAVEPOINT] identifier

目的

事务将数据库从一种一致性状态转换为另一种一致性状态

组成

事务可由一条非常简单的SQL语句组成,页可以由一组复杂的SQL语句组成

特征

  • 事务是访问和更新数据库的一个程序执行单元
  • 单条语句是隐含的事务
  • 多条语句需要主动开启事务
  • 通过设置set autocommit=0禁止事务自动提交,之后需要commit才能提交事务

概念

redo日志

redo日志用来实现事务的持久性，事务提交时，必须将该事务的所有日志写入到日子文件进行持久化

  • redo log顺序写，记录的是对每个页的修改
  • 只有发生宕机的时候，才会拿redo log进行恢复

undo日志

undo日志用来帮助事务回滚以及MVCC的功能，存储再共享表空间中

  • 实现rollback，记录事务每一步的具体操作，当rollback时，会执行相反的动作
  • 实现MVCC功能，记录行的版本信息

MVCC多版本并发控制Multi version concurrency control

  • 目的：用来实现一致性的非锁定读；非锁定读是指不需要等待，访问到行上读到加了X锁的行数据，就去读一个快照
  • 每一次事务提交都会产生一个快照，历史数据
  • 为什么读取快照数据不需要上锁？因为是由事务会对快照数据进行修改

ACID特性

原子性（A）

事务具有原子性，通过undo log来实现回滚操作

持久性（D）

把操作进行持久化，即使操作过程中宕机，通过redo log实现

一致性（C）

一致性指事务将数据库从一种一致性状态转换为下一种一致性状态，事务执行前后，数据库完整性约束没有被破坏,一个事务单元需要提交后才能够被其他事务可
见

  • 一致性由原子性、隔离性以及持久性共同来维护。

隔离性（I）

事务的隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离，并发事务之间不会相互影响。

  • 设置不同的隔离级别，破坏一致性，能够提高性能。级别越高并发性能约低，级别越低并发性能越高
  • 通过MVCC和锁来实现
  • 表、页、行三种粒度加锁，操作哪在哪加锁

隔离性的实现

隔离级别

隔离级别针对的主要是读?
innodb默认隔离级别是可重复读repeatable read

  • read uncommitted读未提交
    读不加锁,写加排它锁
  • read committed读已提交
    支持MVCC,也就是提供一致性非锁定读
    读取操作读取历史快照的最新版本数据(最新提交的数据)
  • repeatable read可重复读
    支持MVCC
    读取操作读取事务开始时的版本数据
  • serializable串行化
    给读加了共享锁,所有事务都是串行化执行.
-- 设置隔离级别
SET [GLOBAL　｜ SESSION] TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 或者采用下面的方式设置隔离级别
SET @@tx_isolation='REPEATABLE READ';
SET @@global.tx_isolation = 'REPEATABLE READ';  
transaction_isolation
-- 查看全局隔离级别
SELECT @@global.tx_isolation;
-- 查看当前会话隔离级别
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;
-- 手动加共享锁
SELECT ... LOCK IN SHARE MODE;
-- 手动加排它锁
SELECT ... FOR UPDATE;
-- 查看当前的锁信息
SELECT * FROM information_schema.innodb_locks;
-- 新版注意点
-- tx_isolation变为transaction_isolation
SELECT @@SESSION .transaction_isolation;
SELECT @@GLOBAL .transaction_isolation;
SELECT @@transaction_isolation;
-- nformation_schema.innodb_locks变为PERFORMANCE_SCHEMA .data_lock_waits;
SELECT * FROM PERFORMANCE_SCHEMA .data_locks;
SELECT * FROM PERFORMANCE_SCHEMA .data_lock_waits;

锁

锁机制用于管理对共享资源的并发访问，用来实现事务的隔离级别。

锁类型

MySQL事务采用的是粒度锁，针对表（B+树）、页（B+树叶子节点）、行（B+树叶子节点中的某一行）三种粒度锁
共享锁和排它锁都是行级别锁；
意向共享锁和意向排它锁都是表级别的锁；

共享锁（S）

事务读操作加的锁，给一行加读锁
read uncommitted: 没有加共享锁,没有mvcc
read committed: 没有加共享锁,由mvcc,读最新提交版本
repeatable read: 没有加共享锁,有mvcc,读事务开始前的版本
serializable: 自动加共享锁

排他锁（X）Exclusive lock

事务删除或更新加的锁，对某一行加锁,事务提交或事务回滚后释放锁.

意向锁

意义: 表明了某个事务持有了锁或准备去持有锁
目的: 为了协调行锁和表锁的关系,支持多粒度(行锁和表锁)的锁并存.
例子: 事务A想修改table中的row数据,需要给row加上行级别的排它锁,同时会给table表加上意向排它锁;此时如果事务B想要修改table中的rowb数据,也需要给table加一个表级的意向排他锁,此时事务B就会被阻塞

  • 意向共享锁（IS）Intentional sharing lock
    事务在请求S锁前,需要先获得IS锁
  • 意向排他锁（IX）Intentional exclusive lock
    事务在请求X锁前,需要先获得IX锁

q1 为什么意向锁是表级锁呢？
当我们需要加一个排他锁时，需要根据意向锁去判断表中有没有数据行被锁定（行锁）；
（1）如果意向锁是行锁，则需要遍历每一行数据去确认；
（2）如果意向锁是表锁，则只需要判断一次即可知道有没数据行被锁定，提升性能。

q2：意向锁怎么支持表锁和行锁并存？
（1）首先明确并存的概念是指数据库同时支持表、行锁，而不是任何情况都支持一个表中同时有一个事务A持有行锁、又有一个事务B持有表锁，因为表一旦被上了一个表级的写锁，肯定不能再上一个行级的锁。
（2）如果事务A对某一行上锁，其他事务就不可能修改这一行。这与“事务B锁住整个表就能修改表中的任意一行”形成了冲突。所以，没有意向锁的时候，让行锁与表锁共存，就会带来很多问题。于是有了意向锁的出现，如q1的答案中，数据库不需要在检查每一行数据是否有锁，而是直接判断一次意向锁是否存在即可，能提升很多性能

自增锁（AI）AUTO-INC Lock

是一种特殊的表级锁，发生在AUTO_INCREMENT约束下的插入操作，在完成对自增长插入的sql语句后立即释放。

锁SXISIXAI
S兼容冲突兼容冲突冲突
S兼容冲突兼容冲突冲突
X冲突冲突冲突冲突冲突
IS兼容冲突兼容兼容兼容
IX冲突冲突兼容兼容兼容
AI冲突冲突兼容兼容冲突
  • innodb支持的是行级别的锁，mysim支持的是表级别的锁
  • 意向锁相互兼容，并不会阻塞除全表扫描外的任何请求
  • IS锁只对X锁冲突，或X锁与其他锁都冲突
  • 加S锁需要在所在表和行加意向锁IS，再在所在行加S锁
  • 加X锁需要在所在表和行加意向锁IX，再在所在行加X锁

锁算法

记录锁Record Lock

当个记录行的锁

间隙锁Gap Lock

全开区间，锁定一个范围，但不包含记录本身，Repeatable read级别以上支持
如果 REPEATABLE READ 修改 innodb_locks_unsafe_for_binlog = 0 ，那么隔离级别相当于退化为 READ COMMITTED；

记录锁+间隙锁Next-Key Lock

左开右闭区间，锁定一个范围，并锁住记录本身

插入意向锁insert-intention lock

insert操作时产生，在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会产生锁等待。
1-7 插入3和4，没有则只能加gap lock将1-7全锁住，这样子4就没办法插入了
插入意向锁能够使3和4同时插入

  • 提升间隙插入的并发性能
锁GAP（持有）Insert Intention（持有）Record（持有）Next-key（持有）
GAP（请求）兼容兼容兼容兼容
Insert Intention（请求）冲突兼容兼容冲突
Record（请求）兼容兼容冲突冲突
Next-key（请求）兼容兼容冲突冲突

分析死锁就是根据这个来的
死锁：再在请求insert intention但是已经被gap持有了

一个事务已经获取了插入意向锁，对其他事务是没有任何影响的；
一个事务想要获取插入意向锁，如果有其他事务已经加了 gap lock 或 Next-key lock 则会阻塞；
这个是重点，死锁之源；

锁的对象

重点考虑INNODB在read committed和repeatable read中的情况
无索引-全局扫描-表级别的锁
命中就是行锁，未命中就加gap锁
为什么没有命中会有间隙锁？避免幻读问题
可重复读加了一个gap锁
用优化器看是怎么索引的，再去分析锁

  • 对read commited和repeatable read，分别讨论以下的情况
    • 聚集索引，查询命中
    • 聚集索引，查询未命中
    • 辅助唯一索引，查询命中
    • 辅助唯一索引，查询未命中
    • 辅助非唯一索引，查询命中
    • 辅助非唯一索引，查询未命中
    • 无索引
    • 聚集索引，范围查询
    • 辅助索引，范围查询
    • 修改索引值

并发读异常

脏读

事务B比事务先运行,事务A读到事务B未提交的数据，读到修改的数据
解决方法：提升隔离级别>=read committed

seqsession Asession B
1SET @@tx_isolation=‘READ UNCOMMITTED’;SET @@tx_isolation=‘READ UNCOMMITTED’;
2BEGIN;
3UPDATE account_t SET money = money - 100 WHERE name = ‘A’;
4BEGIN;
5SELECT money FROM account_t WHERE name = ‘A’;
6SELECT money FROM account_t WHERE name = ‘B’;
7UPDATE account_t SET money = money - 100 WHERE name = ‘B’;
8COMMITCOMMIT

不可重复读

事务AB同时进行, 事务A对数据进行修改,事务A先提交, 事务B对数据进行查询,查到了事务A修改完的提交数据
解决方法：提升隔离级别>=repeatedable read

seqsession Asession B
1SET @@tx_isolation=‘READ COMMITTED’;SET @@tx_isolation=‘READ COMMITTED’;
2BEGIN;BEGIN;
3SELECT money FROM account_t WHERE name = ‘A’;
4UPDATE account_t SET money = money - 100 WHERE name = ‘A’;
5COMMIT;SELECT money FROM account_t WHERE name = ‘A’;
6COMMIT;

幻读

幻读是针对一个范围的数据。
幻读是两次读取同一个范围内的记录两次结果集不一样
结果集不一样：可能不同个数，也可能相同个数内容不一样

seqsession Asession B
1SET @@tx_isolation=‘REPEATABLE READ’;SET @@tx_isolation=‘REPEATABLE READ’;
2BEGIN;BEGIN;
3SELECT * FROM account_t WHERE id >= 2;
4INSERT INTO account_t(id,name,money) VALUES (4,‘D’,1000);
5COMMIT;SELECT * FROM account_t WHERE id >= 2;
6COMMIT;

丢失更新

两个事务都是写，提交覆盖和回滚覆盖
回滚覆盖在INNODB中不可能产生

seqsession Asession B
1SET @@tx_isolation=‘REPEATABLE READ’;SET @@tx_isolation=‘REPEATABLE READ’;
2BEGIN;BEGIN;
3SELECT money FROM account_t WHERE name = ‘A’;
4SELECT money FROM account_t WHERE name = ‘A’;
5UPDATE account_t SET money = 1100 WHERE name = ‘A’;
6COMMIT;
7UPDATE account_t SET money = 900 WHERE name = ‘A’;
8COMMIT;

隔离级别下并发读异常

隔离级别回滚覆盖脏读不可重复读幻读提交覆盖
READ UNCOMMITTED不存在存在存在存在存在
READ COMMITTED不存在不存在存在存在存在
REPEATABLE READ不存在不存在不存在存在（手动加锁）存在（手动加锁）
SERIALIZABLE不存在不存在不存在不存在不存在

并发死锁

两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种相互等待的现象。
MySQL中采用wait-fro-graph（等待图-采用非递归深度优化的图算法实现）的方式来进行死锁的检验；

相反顺序加锁，造成死锁

不同表的加锁顺序相反或相同表不同行的加锁顺序相反造成死锁。

锁冲突造成死锁

在innodb的repeatable read级别下，最常见的时插入意向锁和gap锁冲突造成死锁

查看死锁

-- 开启标准监控 
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB; 
-- 关闭标准监控 
DROP TABLE innodb_monitor; 
-- 开启锁监控 
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB; 
-- 关闭锁监控 
DROP TABLE innodb_lock_monitor

-- 开启标准监控 
set GLOBAL innodb_status_output=ON; 
-- 关闭标准监控 
set GLOBAL innodb_status_output=OFF; 
-- 开启锁监控 
set GLOBAL innodb_status_output_locks=ON; 
-- 关闭锁监控 
set GLOBAL innodb_status_output_locks=OFF; 
-- 将死锁信息记录在错误日志中 
set GLOBAL innodb_print_all_deadlocks=ON;

-- 查看事务 
select * from information_schema.INNODB_TRX; 
-- 查看锁 
select * from information_schema.INNODB_LOCKS; 
-- 查看锁等待 
select * from information_schema.INNODB_LOCK_WAITS;

死锁解决

  • 顺序相反型，调整执行顺序
  • 锁冲突型，更换语句或者降低隔离级别

如何避免死锁

尽可能以相同顺序来访问索引记录和表；
如果能确定幻读和不可重复读对应用影响不大，考虑将隔离级别降低为RC；
添加合理的索引，不走索引将会为每一行记录加锁，死锁概率非常大；
尽量在一个事务中锁定所需要的所有资源，减小死锁概率；
避免大事务，将大事务分拆成多个小事务；大事务占用资源多，耗时长，冲突概率变高；
避免同一时间点运行多个对同一表进行读写的概率；

正向，决断
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL系统表information_schema.INNODB_TRX详解及查看当前运行事务
oschina_41731918的博客
11-23 9992
在日常管理数据库的过程中，有时需要查询MySQL数据库是否正在有正在执行的事务，便于排查业务问题。MySQL的系统库表有数据维护对应的信息，就在。表，包含事务中是否存在锁，事务开启时间，事务执行的语句等等。
mysql SELECT * FROM information_schema.innodb_trx;
热门推荐
David_jiahuan的博客
06-12 1万+
SELECT * FROM information_schema.innodb_trx命令是用来查看当前运行的所以事务： 在执行结果中可以看到是否有表锁等待或者死锁，如果有死锁发生，可以通过下面的命令来杀掉当前运行的事务： KILL 165667 ; KILL 后面的数字指的是trx_mysql_thread_id值。 ...
information_schema.INNODB_TRX表详解
CaptHua的博客
08-18 8975
information_schema.INNODB_TRX表；MySQL查看事务；事务与锁查看。
MySQL学习：事务的隔离
小朱小朱绝不认输的博客
09-01 2812
简单来说，事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在 MySQL 中，事务支持是在引擎层实现的。MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务。比如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一。 一、事务 数据库事务（Database Transaction）： 一组具有ACID属性的SQL命令组成的单个逻辑工作单元，称为数据库事务。 事务是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割
mysql 查询正在进行的事务
stephen
06-08 1万+
SELECT * FROM information_schema.innodb_trx ；
MySQL锁机制与用法分析
01-19
本文实例讲述了MySQL锁机制与用法。分享给大家供大家参考，具体如下： MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁；BDB存储引擎采用的是...
深入分析MSSQL数据库中事务隔离级别和锁机制
12-15
锁机制 NOLOCK和READPAST的区别。 1. 开启一个事务执行插入数据的操作。 BEGIN TRAN t INSERT INTO Customer SELECT 'a','a' 2. 执行一条查询语句。 SELECT * FROM Customer WITH (NOLOCK) 结果中显示”a”和...
mysql共享锁与排他锁用法实例分析
01-19
mysql锁机制分为表级锁和行级锁，本文就和大家分享一下我对mysql中行级锁中的共享锁与排他锁进行分享交流。 共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，...
MySQL：锁机制.pdf
04-19
主要包括锁机制、表锁、行锁、间隙锁、页锁，具体的应用案例分析、案例总结、行锁分析、表锁分析以及优化建议
SQL Server数据库事务锁的机制分析
02-11
深入介绍了SQL Server的锁的机制，锁与事务隔离等级的关系及影响，分析了死锁的案例和解决方案。提出了锁的性能分析方法，分析并发性阻塞的问题和应用技巧。
information_schema(INNODB_TRX | INNODB_LOCKs | INNODB_LOCK_waits)
尼古拉斯.张飞的博客
11-21 2153
这三条语句 分别 查 事务 、锁、锁等待： SELECT * FROM information_schema.INNODB_TRX; -- 命令是用来查看当前运行的所有事务： SELECT * FROM information_schema.INNODB_LOCKs; -- 命令是用来查看当前出现的锁； SELECT * FROM information_schema.INNODB_L...
SELECT * FROM information_schema.innodb_trx
没有伞的孩子只能快跑，如若不跑，只有一个结果：超过你的人回头鄙视你
10-27 1837
SELECT * FROM information_schema.innodb_trx命令是用来查看当前运行的所以事务： 在执行结果中可以看到是否有表锁等待或者死锁，如果有死锁发生，可以通过下面的命令来杀掉当前运行的事务： KILL165667; KILL 后面的数字指的是trx_mysql_thread_id值。 ...
mysql事务锁表进程排查
Brucexiaocai的博客
05-09 277
-- 查找没提交的事物 SELECT * FROM information_schema.INNODB_TRX ORDER BY trx_started ASC; -- 查找指定事务进程的执行sql SELECT a.sql_text,c.id,d.trx_started FROM performance_schema.events_statements_current a JOIN performance_schema.threads b ON a.thread_id = b.t...
mysql问题分析、解决
有梦想的咸鱼的博客
03-21 828
查看正在进行中的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; 杀死进程id(就是上面命令查到的trx_mysql_thread_id列) kill 线程id 查看当前锁定的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 查看当前等锁的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 查看超过60s的长事务 s.
Mysql查询正在运行的事务以及杀掉它
落叶翩翩的CSDN博客
04-26 6937
查询 正在执行的事务：SELECT * FROM information_schema.INNODB_TRX根据这个事务的线程ID（trx_mysql_thread_id）:可以使用mysql命令：kill  线程id       杀掉线程到information_schema库下面，查看下面这个表： innodb_trx ## 当前运行的所有事务 innodb_locks ## 当前出现的锁 i...
mysql SELECT * FROM information_schema.innodb_trx；
Jack_2085
03-29 1086
mysql SELECT * FROM information_schema.innodb_trx; https://blog.csdn.net/David_jiahuan/article/details/91558816
MySQL解锁方式与Oracle解锁方式
qq_27929003的博客
02-11 357
MySQL解锁方式与Oracle解锁方式
如何查看mysql里面的锁(详细)
李逍遥
05-26 4999
如何查看mysql里面的锁(详细)
数据库事务和锁（三）——INNODB_LOCKS, INNODB_LOCK_WAITS, INNODB_TRX表的简单介绍
蜗牛^_^的博客
09-02 1万+
INNODB_LOCKS, INNODB_LOCK_WAITS, INNODB_TRX是MYSQL中事务和锁相关的表。通常我们遇到事务超时或锁相关问题时，直接运行下面SQL语句即可进行简单检查： --查看事务 select * from information_schema.INNODB_TRX; --查看锁 select * from information_schema.INNODB_...
MySQL事务管理与并发控制的实验分析
最新发布
06-09
MySQL提供了两种锁机制：共享锁和排它锁。共享锁用于读取数据，排它锁用于修改数据。 在实验分析中，我们可以通过模拟多个用户同时访问数据库来测试MySQL的并发控制能力。我们可以使用多个线程同时访问数据库，并且...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值