MySQL事务与锁（五）

summer_45

已于 2022-02-26 17:38:12 修改

阅读量428

点赞数

分类专栏：数据库文章标签： mysql 数据库 sql

于 2022-02-26 15:11:46 首次发布

本文链接：https://blog.csdn.net/summer_45/article/details/123150347

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

本文深入探讨了MySQL的事务特性，包括ACID属性和四种事务隔离级别，分析了不同隔离级别下可能出现的问题。接着，介绍了锁的概念，如乐观锁与悲观锁、读锁与写锁，以及行锁、表锁和间隙锁的使用场景和影响。此外，还讨论了InnoDB的锁升级现象和如何通过监控锁状态进行优化。

摘要由CSDN通过智能技术生成

数据库笔记

事务与锁

事务

定义

事务是由一组SQL语句组成的逻辑处理单元，具有ACID四个属性

属性

A 原子性： 事务内的所有操作一致
C 一致性：事务内的操作==导致的数据修改要一致==
I 隔离性：事务处理过程中的**中间状态对别的外部是不可见**的；外部的状态修改也对事务不可见的。
D 持久性：事务完成后，对==数据的修改是永久性的。==

并发事务处理带来的问题

脏写 —— 最后的更新覆盖了由其他事务所做的更新
脏读 —— 事务A读到了事务B已经修改但未提交的数据【不满足隔离性】
不可重读 —— 事务A内部相同的查询SQL结果不一致【不满足隔离性】
幻读 —— 事务A读取了事务B提交的新增数据【不满足隔离性】

事务隔离级别

定义

数据库有不同的隔离级别，默认的隔离级别是 可重复读

查看当前数据库的事务隔离级别:
```
show variables like 'tx_isolation';
```
设置事务隔离级别：
```
set tx_isolation='REPEATABLE-READ';
```

分类

读未提交
读已提交
可重复读
可串行化

读未提交

set tx_isolation='read-uncommitted';

流程：

客户端A与客户端B各设置为 读未提交
客户端B**更新表account，**但不提交事务
客户端A查询表account，读到B修改的数据
如果此时，B回滚，A读到了脏数据

可能遇到的问题：

脏读、不可重读、幻读

读已提交

set tx_isolation = 'read-committed';

流程：

客户端A与客户端B各设置为 读已提交
客户端B**更新表account，**提交事务
客户端A查询表account，读到B修改的数据

可能遇到的问题：

不可重读、幻读

可重复读

set tx_isolation = 'repeatable-read';

通过undo日志文件进行版本控制，实现隔离性

流程：

客户端A与客户端B各设置为 可重复读
客户端B**往表account新增一条id为100的数据，**提交事务
客户端A查询表account，可以到id为100的数据

可能遇到的问题：

幻读

串行化

set tx_isolation = 'serializable';

通过加锁来实现串行化

流程：

第一种情况
1. 客户端A与客户端B各设置为 可串行化
2. 客户端A执行查询id为1的语句【加上行锁、读锁、间隙锁】
3. 客户端B此时更新id为1的语句会阻塞等待，更新id为2的语句可以正常执行
第二种情况
1. 客户端A与客户端B各设置为 可串行化
2. 客户端B此时插入id为1的语句【加上行锁、写锁】
3. 客户端A执行查询id为1的语句会 阻塞等待

锁详解

定义：

锁就是用来控制 临界区资源 给谁用

下面的锁分类，数据库实现的是 行锁和表锁

锁分类

性能 —— 乐观锁 和 悲观锁
操作类型 —— 读锁和写锁
锁粒度 —— 行锁和表锁和 间隙锁

各种分类的锁可以交叉，不一定互斥

乐观锁

定义：

各线程用版本号对比来实现争取 临界区资源

悲观锁

定义：

各线程通过争抢锁来争取 临界区资源

读锁

定义：

共享锁 S

针对同一份数据，如果 **线程A拿到读锁 **

多个线程读操作可以同时进行，除了线程A其他线程写操作会堵塞。

是一种悲观锁

写锁

定义：

排他锁 X

针对同一份数据，如果 **线程A拿到写锁 **

只有线程A能对数据进行读写， 其他线程的读写操作均会堵塞。

行锁

定义：

每次操作锁住一行数据，粒度较小。

分类：

行锁+读锁 —— 锁住该行数据，其他线程对该行的写操作被阻塞
```
select * from test_innodb_lock where a = 2 lock in share mode;
```
行锁+写锁 —— 锁住该行数据，其他线程对该行的读写操作均被阻塞
```
select * from test_innodb_lock where a = 2 for update;
```

开销：

因为行锁要定位到某一个行，所以它加锁慢，开销会变大，且会出现死锁。

锁升级：

InnoDB的行锁是针对索引加的锁，而不是记录加的锁。如果对非索引字段进行更新或者索引失效，行锁可能会升级为表锁。

行锁分析：

# 查询InnoDB行锁的争夺情况
show status like 'innodb_row_lock%';

Innodb_row_lock_time_avg （等待平均时长）
Innodb_row_lock_waits （等待总次数）
Innodb_row_lock_time（等待总时长）

根据情况进行优化

表锁

定义：

每次操作锁住一行数据，粒度较大，适用整表数据迁移。

分类：

表锁+读锁 —— 锁住该表数据，其他线程对该表的写操作被阻塞
```
lock table 表名称 read;
```
表锁+写锁 —— 锁住该表数据，其他线程对该表的读写操作均被阻塞
```
lock table 表名称 write;
```

间隙锁

定义：

间隙锁，就是锁住两个行之间的间隙【区间】只在可重复读才生效

例子：

在这里插入图片描述

现有区间 id为 (3,10) (10,20) (20,+∞)

在session1中执行

# 更新语句
update account set name = 'zhuge' where id > 8 and id <18;
## MySQL会加上间隙锁 锁住 (8,18)
## 因为8落在原来(3,10)的区间，18落在(10,20)的区间，所以锁住的区间为 (3,20]

根据间隙锁，锁住 (3,20]

给该区间加上写锁，其他session不可以对该区间进行插入、删除、修改操作

查看系统库锁相关数据表

## 查看INFORMATION_SCHEMA系统库锁相关数据表
#  查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
#  查看锁
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
#  查看锁等待
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
#  释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id
#  查看锁等待详细信息
show engine innodb status\G;