mysql事务

学习视频：B站

MySQL数据库教程天花板，mysql安装到mysql高级，强！硬！_哔哩哔哩_bilibili0基础如何学起？宋红康30天搞定Java核心：BV1Kb411W75N基础篇：P1 - P95高级篇：P96 - P199MySQL课程天花板：6大范式讲解、7大日志剖析、7大SQL性能分析工具、9大存储引擎剖析、10大类30小类优化场景、15个不同锁的应用讲解、18种创建索引的规则、300+张高清无码技术剖析图......https://www.bilibili.com/video/BV1iq4y1u7vj?p=161

一、事务的ACID特性

1.原子性（atomicity）：全部提交，或者全部回滚

2.一致性（consistency）：根据定义，一致性是指事务执行前后，数据从一个 合法性状态变换到另外一个合法性状态。这种状态是语义上的而不是语法上的，跟具体的业务有关。比如说银行存款，张三有100，李四有100，无论他们之间如何转账，总钱还是200，如过不是，则不符合一致性。

3.隔离性（isolation）：事务之间互不干扰

4.持久性（durability）：事务一旦提交，数据的改变是永久性的。

事务的原子性、一致性、持久性通过事务日志体现的，事务日志分为重做日志（REDO LOG）和回滚日志（UNDO LOG）；事务的隔离性通过锁来实现。

二、事务的状态

三、事务的分类

事务分为显示事务和隐式事务。

1. 显示事务

• 开启事务
  • 方式一：start transaction 后面可以跟read only / read write with consistent snapshet
  • 方式二：begin，后面什么也不跟
• 保存点savepoint

2.隐式事务

• 自动提交：autocommit
• 关闭隐式事务
  • 方式一：开启显示事务，则隐式事务自动关闭
  • 方式二：set autocommit = off / false，针对DML(数据定义语言)，不针对DDL

三、数据并发问题

1. 脏写：事务A修改了事务B修改的但未提交的数据

2. 脏读：事务A读写了事务B更新的但未提交的数据

3. 不可重复读：事务A读取数据后，事务B更新数据，A再读取，两次读取的数据不一样

4. 幻读：事务A读取数据后，事务B插入数据，A再读取，读取到的数据比之前多出几行数据

问题按照严重性来排一下序：脏写 > 脏读 > 不可重复读 > 幻读

四、SQL中的四种隔离级别

Oracle支持READ COMMITED（提交读）和SERIALIZABLE（串行化）

Mysql支持4中隔离，默认REPEATABLE READ（可重复读）

隔离级别	脏写可能性	脏读可能性	不可重复读可能性	幻读可能性
READ UNCOMMITED（未提交读）	No	Yes	Yes	Yes
READ COMMITED（提交读）	No	No	Yes	Yes
REPEATABLE READ（可重复读）	No	No	No	Yes
SERIALIZABLE（串行化）	No	No	No	No

五、MySQL并发事务访问相同记录

1. 读-读情况

2. 写-写情况

• 建立锁结构
  • 

• 几种说法
  • 不加锁。不生成锁结构。
  • 获取锁，或者加锁成功。生成锁结构，但iswaiting：false
  • 获取锁失败，或者加锁失败。生成锁结构，但iswaiting：true

3. 读-写或者写-读情况

• 会出现的问题：脏读、不可重复读、幻读
• 解决办法：
  • 方案一：读写都加锁
  • 方案二：读操作用MVCC（多版本并发控制，具体可参考下篇文章），写操作加锁。   
• 方案二比方案一性能更高

六、锁的分类

1.对数据的操作类型分为：

（1）读锁 / 共享锁 / S锁

• 对读取加S锁：

  select 、、、 LOCK IN SHARE MODE;
  或者
  select 、、、 FOR SHARE;(8.0版本新增)

（2）写锁 / 排它锁 / X锁

• 对读取加X锁

  select 、、、 FOR UPDATE

• 写操作
  • delete / update：加X锁
  • Insert：隐式锁（因为在加锁时，么有确切存在的数据）

2.锁粒度角度分为：

（1）表级锁（开销小，不会出现死锁，发生锁冲突概率大，并发度最低）

• 表级别的S锁、X锁
• 意向锁（intention Lock）

        ①意向锁还可以再细分为意向共享锁（IS）和意向排它锁（IX）

        ②意向锁视为调节表锁和行锁的问题

        ③当某行加了行锁，会自动在 表 / 页 上加一个意向锁

        ④

	IS	IX
IS	兼容	兼容
IX	兼容	兼容

        ⑤

	IS	IX
S锁	兼容	互斥
X锁	互斥	互斥

• 自增锁（AUTO-INC锁）
• 元数据锁（MDL锁）
  • MDL读锁
  • MDL写锁

（2）行级锁（会出现死锁，开销大，发生锁冲突概率小，并发度最高）

• 记录锁（Record Locks）
  • X型记录锁
  • Y型记录锁
• 间隙锁（Gap Locks）
  • 是为了解决REPEATALBE READ隔离级别下的幻读问题
  • 因为幻影记录尚不存在，所以不能加记录锁
  • 锁定一个范围，不包括记录本身
• 临间锁（Next-Key Locks）
  • 既锁住某行记录，同时阻止在其他事务在记录前边的间隙插入记录
  • 官方名：LOCK_ORDINARY
  • Innodb中事务级别在可重复读的情况下使用的数据库锁就是临间锁
  • 锁定一个范围，包含记录本身
  • 举例：

    begin
    select * from student where id <= 8 and id > 3 for update

• 插入意向锁（Insert Intention Locks）
  • 插入意向锁是插入一条记录行前，由Insert 操作产生的一种间隙锁
  • 

（3）页级锁

• 页级锁是MySQL锁定粒度介于行级锁和表级锁中间的一种锁。

• 表级锁速度快，但冲突多;行级锁冲突少，但速度慢。

• 所以页级锁是折中的，一次锁定相邻的一组记录。BDB支持页级锁。

3.对待锁的态度分为

（1）乐观锁

• 通过程序实现
• 适合读操作多的情况下
• 实现方式有
  • 版本号机制
    • 在表中设计一个 版本字段 version ，第一次读的时候，会获取 version 字段的取值。然后对数据进行更新或删除操作时，会执行 UPDATE ... SET version=version+1 WHERE version=version 。此时如果已经有事务对这条数据进行了更改，修改就不会成功。
  • CAB机制（时间戳机制）
    • 时间戳和版本号机制一样，也是在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行比较，如果两者一致则更新成功，否则就是版本冲突。
      你能看到乐观锁就是程序员自己控制数据并发操作的权限，基本是通过给数据行增加一个戳（版本号或者时间戳），从而证明当前拿到的数据是否最新。

（2）悲观锁

• 通过锁的机制实现
• 适合多写的操作
• 一操作就加锁

4.加锁的方式分为：

（1）隐式锁

（2）显示锁

5.其他分为：

（1）全局锁

• 对整个数据库加锁
• 使用场景：全库逻辑备份

（2）死锁

• 资源互相占用，进入死循环
• 解决办法：
  • 方法一：进入等待，知道循环超时结束
  • 方法二：主动回滚死锁链中的某一个事务（持有最少行级排他锁的事务进行回滚）