innodb mysql 事务锁 mysql事务与锁

转载

mob64ca14031c97 2023-10-06 20:49:18

文章标签 innodb mysql 事务锁 mysql 事务锁数据 文章分类 MySQL 数据库

文章目录

1. 事务

1.1 概念
1.2 特性
1.3 隔离级别-sql标准

2. 并发控制

2.1 锁是事务拥有的
2.2 锁类别
2.3 锁粒度

2.3.1 行锁
2.3.2 表锁

3. 不可重复读和幻读的解决

3.1 不可重复读

3.1.1 现象：[隔离级别]()
3.1.2 解决方式
3.1.3 具体实现

3.2 幻读

3.2.1 现象：[隔离级别]()
3.2.2 解决方式

1. 事务

1.1 概念

事务是一组原子性的sql操作，该组sql要么全部成功，要么全部失败。

注：事务和sql语句没有直接关系，一个事务可以包含多个查询和dml语句；但是ddl语句自动提交事务。

1.2 特性

原子性：设计的sql操作，要么全部成功，要么全部失败；
一致性：多张表的数据从一个一致性状态，变成另外一个一致性状态，这种数据的一致性是人们所期望的；
隔离性：通常来说，一个事务在未提交之前对另外一个事务是透明的，影响因素：1）数据库引擎选择的隔离级别 2）锁的控制；
持久性：事务提交以后，数据被永久保存。

1.3 隔离级别-sql标准

资源贴

READ UNCOMMITED(未提交读)
事务可以读取其它事务没有提交的内容，会产生脏读现象
READ COMMITTED(读已提交)
事务只能发现其它已经提交的事务，会发生不可重复读现象，这个是大多数数据默认级别。
REPEATEABLE READ(可重复读)
在读已提交的基础上，增加了读一致性，即多次读取结果一样，但是会产生另外一个问题幻读——一个事务读数据，另开一个事务插入一条数据并提交，前事务不会读到这个新插入数据，但是执行更新所以行，会更新到这条新插入的数据，这就是幻读。mysql默认的级别就是这个。
SERIALIZABLE(串行)
所有事务串行

2. 并发控制

2.1 锁是事务拥有的

锁是事务持有的

2.2 锁类别

官方文档共享锁排它锁

读锁(x)
也称共享锁，多个事务可以同时拥有共享锁；但是不能再获取排它锁。

使用：select …… lock in share mode;

写锁(s)
也称排它锁，同一时刻只能有一个事务拥有排它锁，其它事务不能拥有共享锁和排它锁。

使用：select …… for update;

注：InnoDB引擎insert、update、delete会自动给涉及的数据加排他锁（X）；对于一般的Select语句，不会加任何锁。

2.3 锁粒度

2.3.1 行锁

注：实验结果和网上的信息有点出路，欢迎各路大神指正

行锁是基于索引的,锁定一行数据;但如果查询条件不是主键或非唯一索引再或者不是索引列那么就会有锁定上个间隙和和下个间隙的情况出现。

写在开始之前：关于间隙

假设索引包含值10,11,13和20.此索引的可能的下一个键锁定覆盖以下间隔，其中圆括号表示排除间隔端点，方括号表示包含端点：

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

记录锁（Record Locks）

-- id 列为主键列或唯一索引列
SELECT * FROM table WHERE id = 11 FOR UPDATE;

实验截图：

条件为主键（其它唯一索引类似）

innodb mysql 事务锁 mysql事务与锁_mysql

条件为3非

innodb mysql 事务锁 mysql事务与锁_事务_02

结论：id为主键列或唯一索引列，只会锁住id=11这一行；若id不为主键列、不唯一索引或者不是索引列则全表锁定。

间隙锁（GapLocks）

区间使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据

SELECT * FROM table WHERE id BETWEN 10 AND 11 FOR UPDATE;

实验截图

条件为主键（唯一索引类似）

innodb mysql 事务锁 mysql事务与锁_数据_03

条件为3非

innodb mysql 事务锁 mysql事务与锁_innodb mysql 事务锁_04

结论：id主键列或唯一索引列，锁住10和11两条记录，并且会锁定下一个间隙(11,13]；若id不为主键列、不唯一索引或不是索引列则全表锁定。

临键锁（Next-KeyLocks）
主键或唯一索引，在锁定间隙时会同时锁定下一个间隙。

2.3.2 表锁

锁定整表的记录

3. 不可重复读和幻读的解决

3.1 不可重复读

3.1.1 现象：隔离级别

3.1.2 解决方式

重点：不可重复读主要是相应的行读取以后，被别人改了，导致多次读取不一致，所以锁住相应的行即可。

将事务的隔离级别调成REPEATEABLE READ（多版本控制mvvc）
加入锁控制，共享和排它都可以

注：mvvc是基于乐观锁实想设计的(资源贴(mvvc))

3.1.3 具体实现

悲观锁：使用排它锁锁住记录 select * from ? where ? for update;这样的事务就不能修改这条记录了。
乐观锁：在这条记录上加一个version字段，更新的时候就+1，select的时候带出这个字段，当实际更新的时候判断当前version是不是等于记录中的version，是则成功，反之失败回退。