当前位置：首页 > news >正文

MySQL之事务、锁

news 来源：原创 2024/5/8 6:43:14

文章目录

- 事务
- - 事务和锁
  - 原理
  - 注意事项
  - 基本操作
  - 特性
  - 隔离
  - - 必要性
    - 隔离级别
    - 设置隔离级别
  - 锁
  - - 分类
    - - 按粒度
      - 按思想
      - 按使用方式
    - 行级锁
    - - 分类
      - 加锁
      - 间隙锁
    - 表锁
    - - 分类
      - 加锁
      - 自增锁
    - 悲观锁
    - 乐观锁
    - - 介绍
      - 版本字段（version）
      - 时间戳（Timestamp）
      - CAS算法
    - Innodb死锁
    - - 原因
      - 排查

事务

一个完整的业务逻辑单元，不可再分
- 保证数据的一致性
- 由一组相关的 dml 语句作为整体组成
  - 要么全部成功，要么全部失败

事务和锁

执行事务操作：dml 语句
- MySQL 会在表上加锁，防止其他用户改变表的数据
MySQL 的 InnoDB 引擎使用行级锁方式
- select ... for update
  - 基于索引，只适用于 InnoDB 存储引擎
    - 必须在事务处理下才生效
  - 查询字段有索引会锁住查询记录
  - 没有索引字段会锁整张表
被锁的记录只有获得锁的事务才能操作，其他事务无法操作

原理

开启事务机制后

执行 dml 语句成功
- 将执行记录到数据库的操作历史当中
- 不会真正操作修改硬盘中数据文件
执行 delete 将记录历史操作到缓存
- 不会真正修改硬盘数据
回滚事务
- 回退部分操作到保存点状态
- 清除保存点之后的历史操作
提交事务
- 修改硬盘中数据
- 清除历史操作

注意事项

不开启事务情况下 dml 操作自动提交，不能回滚
事务提交前
- 若开启事务后没有创建保存点使用 rollback 默认回到最开始
- 可创建多个保存点，任意选择要回退的保存点
mysql 的事务机制需要使用 innodb 存储引擎
- myIsam 不支持

基本操作

开始一个事务
- start transaction 或 set autocommit = off
rollback to 保存点：可回退到任意保存点
- 回退后取消部分事务
  - 该保存点之后的操作
- 回退后自动把该保存点之后的保存点删除
rollback：回退到刚开始状态
commit：执行之后会确认事务变化
- 结束事务，删除保存点，释放锁，数据生效
- 结束事务之后。其他连接可以查看事事务变化后的新数据

start transaction 		    -- 开始一个事务
savepoint 保存点			  -- 设置保存点
rollback to 保存点 		  -- 回退事务到指定保存点
rollback  				 	-- 回退全部事务，到开始状态
commit  					-- 提交事务，所有操作生效，不能回退（自动删除所有保存点）

特性

ACID
- 原子性：Atomicity
  - 事务是一个不可分割的工作单元
  - 其中操作要么都发生，要么都不发生
- 一致性：Consistency
  - 事务必须使数据库从一个一致性到另一个一致性状态
  - 开始前的数据和结束的数据总和保持一致
  - 即：必须提交一个事务才能到另一个事务
- 隔离性：Isolation
  - 多用户并发访问数据库
    - 数据库为每一个用户开启的事务不能被其他事务的操作数据干扰
  - 多个并发事务之间要相互隔离
- 持久性：Durability
  - 一个事务一旦被提交，对数据库中的数据的改变就是永久性的
    - 数据最终必须持久化到硬盘中事务才是成功结束
  - 事务提交后即使数据库发生故障也不应对其有影响

隔离

必要性

多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作
- 保证各个连接在获取数据时的准确性

若不考虑隔离，可能会被其他事务影响结果

脏读（dirty read）
- 一个事务读取另一个事务尚未提交的改变
  - 数据进行了增、删、改操作
不可重复读（nonrepeatable read）
- 同一查询在同一事务中多次进行，由于其他已提交事务所做的修改或删除，返回不同结果
  - 数据库中数据被修改或删除
幻读（phantom read）
- 同一查询在同一事务中多次进行，由于其他已提交事务所做的插入操作，返回不同结果
  - 数据库中新增数据

事务隔离级别	脏读	不可重复读	幻读	加锁读
读未提交：`read uncommitted`	√	√	√	不加锁
读已提交：`read committed`	×	√	√	不加锁
可重复读（默认）：`repeatable read`	×	×	×	不加锁
可串行化：`serializable`	×	×	×	加锁

√：可能会出现
×：不会出现
加锁：一个事务操作一张表未提交时其他事务无法同时操作

隔离级别

隔离级别越高安全性越高
- 性能越低
read uncommitted：读未提交
- 问题：可以读到其他事务未提交数据
  - 被另一个未提交事务影响
- 存在脏读现象
read committed：读已提交
- 问题：不可重复读
  - 被另一个已提交事务影响
- 解决了脏读现象
- Oracle 默认使用
repeatable read：可重复读
- 问题：幻读
  - 被另一个已提交事务影响
  - InnoDB 和 Falcon 存储引擎通过多版本并发控制（MVCC）机制解决了该问题
- 解决了不可重复读问题
  - 不被其他事务的操作影响当前操作数据表
  - 当前操作数据表是原数据的备份
  - 原数据可能已经被其他事务修改
- MySQL 默认隔离级别
serializable：可串行化，序列化读
- 非并发读，解决了所有问题
- 缺点：效率低，需要事务排队
  - 当前事务未结束另一个事务无法操作当前表

设置隔离级别

select @@tx_isolation;			-- 查看当前会话隔离级别
select @@global.tx_isolation;	-- 查看当前系统隔离级别
set session transaction isolation level read uncommitted;	-- 设置当前会话隔离级别 为读未提交
set global transaction isolation level read uncommitted;	-- 设置当前系统隔离级别为 读未提交

mysql 默认隔离级别：repeatable read
- 一般情况无需修改，可满足大部分项目需求

锁

行级锁

加锁

insert、update、delete 操作自动加排他锁
select 正常操作不加锁
- 前提条件：检索数据时需要通过索引
  - 不通过索引条件查询的时候，InnoDB 会使用表锁，表锁会产生锁冲突
  - MySQL会比较不同执行计划，当全表扫描比索引效率更高时，InnoDB就使用表锁
    - 不一定使用索引就一定会使用行锁，也可能使用表锁
- 手动加共享锁：select ... lock in share mode;
- 手动加排他锁：select ... for update;
行锁分为两步获得的：锁住主键索引，锁住非主键索引
- 容易产生死锁
查看意向锁及行锁
- select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

间隙锁

默认 InnoDB 在 REPEATABLE READ 事务隔离级别运行
- InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读
RR 隔离级别，InnoDB 行查询都采用 Next-Key Lock 的组合锁定算法
- 但查询的列是唯一索引时 Next-key Lock 降级为 Record Lock，仅锁住索引本身而非范围
  - 包含主键索引

表锁

一般在数据库引擎不支持行锁的时候才会被用到
- 表级读锁会阻塞写操作，但是不会阻塞读操作
- 而写锁则会把读和写操作都阻塞

加锁

insert、update、delete 默认自动加锁自动释放
手动上读锁：lock table tableName read
手动上写锁：lock table tableName write
释放锁：UNLOCK TABLES
- 客户端断开的时候也会自动释放锁
查看表上加过的锁：show open tables;

自增锁

MySQL InnoDB 存储引擎中在设计表结构时，通常会建议添加一列作为自增主键
- 涉及自增锁：AUTO-INC Locks
  - 属于表锁的一种，在 INSERT 结束后立即释放
执行 show engine innodb status\G 查看自增锁的状态信息
核心参数：innodb_autoinc_lock_mode
- 有 0、1、2 三个值，保持默认值即可
- 0：traditional，旧方式，不推荐
- 1：consecutive，连续的，对于批量插入使用传统的 aut-inc
- 2：interleaved，交替式的，性能最大化建议使用 row-based

悲观锁

操作数据时比较悲观，认为会有同时修改数据其他事务
- 在操作数据时直接把数据锁住
  - 直到操作完成后才会释放锁
- 上锁期间其他人不能修改数据
  - 并发机率较低时效率较低
- 行级锁就是一种悲观锁
实现方式：加锁
- 共享锁：多个用户拥有权限，但都只读不可修改
- 排他锁：同时只能有一个用户操作数据
  - 记录修改前在 sql 语句最后追加 for update 加锁
    - MySQL InnoDB 引擎添加行级锁
  - 事务提交之前其他事务无法操作被锁的记录，行级锁
  - 在事务提交之后自动解锁

乐观锁

介绍

在操作数据时非常乐观，认为不会同时修改数据
- 因此不会上锁
- 只在执行更新的时判断是否修改了数据
  - 通过 CAS 机制或版本号机制验证是否修改数据
  - 数据被修改则放弃操作，否则执行操作

版本字段（version）

数据表增加版本(version) 字段
- 用来查看被读的记录有无变化，防止记录在业务处理期间被其他事务修改
每操作一次，记录的版本号加 1

时间戳（Timestamp）

与 version 字段基本一致
给数据表增加一个字段，字段类型使用 timestamp 时间戳，通过时间戳比较数据版本

CAS算法

即 compare and swap（比较与交换）
- 有名的无锁算法，即不使用锁的情况下实现多线程之间的变量同步
三个操作数
- 当且仅当 V 的值等于 A 时，CAS 通过原子方式用新值 B 来更新 V 的值
  - V：变量当前内存值
  - A：旧的预期值
  - B：要写入的新值
- 否则不会执行任何操作
  - 比较和替换是一个原子操作
- 一般情况是一个自旋操作：即不断的重试
缺点
1. ABA 问题
  - 线程1读到某变量的值为 A，处理的过程中，线程2将该变量的值从A先修改为B、然后又改回A
  - 此时，线程1通过 CAS 操作进行新值写入可以成功，但实际上预期值的A 和当前值的A已经不是同一个
2. CPU开销大
  - 虽然 CAS 算法是非阻塞，但如果 CAS 操作一直不成功不断循环，会浪费 CPU 资源
3. 只能保证一个共享变量的原子性
  - 当对多个变量进行操作时，CAS算法无法保证原子性。
  - 可以将多个变量封装为一个对象再使用 CAS 算法
    - Java中的AtomicReference

Innodb死锁

MySQL 中死锁不会发生在 MyISAM 存储引擎中，但会发生在 InnoDB 存储引擎中
- 因为 InnoDB 是逐行加锁的，极容易产生死锁

原因

死锁产生的四个条件

互斥条件：一个资源每次只能被一个进程使用
- 互斥条件是非共享资源所必须的，不仅不能改变，还应加以保证
- 恢复死锁主要是破坏产生死锁的其他三个条件
请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放
不剥夺条件：进程已获得的资源，在没使用完之前，不能强行剥夺
循环等待条件：多个进程之间形成的一种互相循环等待资源的关系

排查

发生死锁时，InnoDB 存储引擎会自动检测，并自动回滚代价较小的事务来解决死锁问题
- 但很多时候一旦发生死锁，InnoDB 存储引擎的处理效率很低下或根本解决不了问题，需要手动解决
排查 InnoDB 锁问题
1. 查看近期死锁日志信息：show engine innodb status;
2. 查看锁状态变量，分析系统中的行锁的争夺情况：show status like'innodb_row_lock%';
  - Innodb_row_lock_current_waits：当前正在等待锁的数量
  - Innodb_row_lock_time：从系统启动到现在锁定总时间长度
  - Innodb_row_lock_time_avg：每次等待锁的平均时间
  - Innodb_row_lock_time_max：从系统启动到现在等待最长的一次锁的时间
  - Innodb_row_lock_waits：系统启动后到现在总共等待的次数
  - 如果等待次数高，且每次等待时间长，则需要对其进行分析优化
3. 打开 innodb_lock_monitor 表
  - 打开 InnoDB 监控机制，每 15 秒输出一次 INNODB 运行状态信息到错误日志
  - 注意使用后记得关闭，否则会影响性能
4. MySQL 5.5 版本之后，查看 information_schema 库下面的 innodb_locks、innodb_lock_waits、innodb_trx 三个视图排查
  - 信息数据库。保存关于 MySQL 服务器维护的所有其他数据库的信息
    - 如数据库名，数据库的表，表栏的数据类型与访问权限等
  - INNODB_LOCKS：查询 innodb 引擎事务中正在请求的且并未获得的且同时阻塞了其他事务的锁信息
    - 即没有发生不同事务之间的锁等待的锁信息查看不到
      - 例如：只有一个事务时，该事务所加的锁信息无法查看
    - 该表中的内容可以用于诊断高并发下的锁争用信息
  - INNODB_TRX：查询当前在 InnoDB 引擎中执行的每个事务（不包括只读事务）的信息
    - 包括事务是否正在等待锁、事务什么时间点开始、以及事务正在执行的SQL语句文本信息等（如果有SQL）
  - INNODB_LOCK_WAITS：查询关于每个被阻塞的InnoDB事务的锁等待记录
    - 包括发生锁等带事务所请求的锁和阻止该锁请求被授予的锁