MySQL 事务

Learn1993
77 阅读4分钟

这是我参与11月更文挑战的第6天，活动详情查看：2021最后一次更文挑战

对ACID特性的支持

原子性

事务是一个原子操作单元， 对数据的修改，要么全部执行，要么都不执行。mysql是通过redo和undo日志进行保证的， 如果事务提交，Buffer Pool 没刷盘， redo日志会保证事务生效。事务没提交, Buffer Pool 刷盘， undo日志保证事务回滚。

持久性

一个事务一旦提交， 对数据库中数据的改变是永久性的， 后续的操作或故障不应该对其有任何影响， 不会丢失。mysql 提交事务的操作有: binlog落地， 发送binlog， 存储引擎提交， flush_logs, check_point, 事务提交标记等。mysql的持久性也和WAL技术有关。

隔离性

一个事务的执行不能被其他事务干扰， 一个事务内部的操作及使用的数据对其他事务是并发的。mysql通过MVCC和锁用于保证隔离性

一致性

事务开始之前和事务结束之后，数据库的完整性限制未被破坏。

MVCC

基于CopyOnWrite的思想， 支持读读，读写和写读并行，写写无法共享。MVCC只在RC和RR两种隔离级别下工作

原理

MVCC中，读操作可以分成两类: 快照读与当前读。

  • 快照读: 读取的是记录的快照版本，不用加锁(普通select)
  • 当前读: 读取的是记录的最新版本， 当前读放回的记录都会加锁，保证其他事务不会并发修改记录。(select ... for update 或 lock in share mode, insert/delete/update)

MVCC中，每条记录都有三个隐含字段: DB_ROW_ID, DB_TRX_ID和DB_ROLL_PT

当修改一行数据时，会用排他锁锁定一行，将修改前的值复制到undo log， 修改当前行的值，填写事务编号，并将回滚指针指向undo log中修改前的行。

事务隔离级别

  • 读未提交

    RU: 可能会发生脏读，不可重复读，幻读

  • 读已提交

    RC： 可能发生不可重复读， 幻读

  • 可重复读

    RR: 可能发生幻读

  • 可串行化

    Serializable: 可能导致大量的超时和锁竞争，效率低下

Mysql默认可重复读， 不可重复读说的是一个事务在执行的时候，另一个事务修改了事务一查询过的数据并且提交了事务， 事务一第二次查询时发现数据修改了。幻读说的时一个事务在执行的时候，另一个事务新增了事务一所查询的条件对应的数据， 事务一第二次查询发现数据增加了。

show variables like 'tx_isolation'
select @@tx_isolation
set tx_isolation='READ-UNCOMMITTED'
set tx_isolation='READ-COMMITTED'
set tx_isolation='REPEATABLE-READ'
set tx_isolation='SERIALIZABLE'

锁

分类

从粒度分为 表级锁， 行级锁, 页级锁

从操作类型分为读锁和写锁

读锁(S): 共享锁， 多个读操作可以同时进行，不会互相影响

写锁(X): 排它锁， 写操作没有完成前，阻断写锁和读锁

IX， IS : 意向锁，属于表锁， S 和X 属于行锁

从操作性能分为乐观锁和悲观锁

隔离级别用锁情况
RU对数据修改加X， 查询数据加S
RC修改加X 读加S 读完立刻释放S
RR修改加X 读加S 事务结束释放S
Serializable其余机制

行锁

Record Lock

锁定单个行记录，RC，RR都支持

Gap Lock

间隙锁，锁定索引记录间隙，确保索引记录间隙不变， RR支持

Next-key Lock

记录锁和间隙锁组合，同时锁住数据和前后范围

在RR隔离级别， 对记录加锁都是先采用Next-Key Lock， 当SQL操作含有唯一索引， Innodb会对Next-Key lock优化，降级RecordLock, 仅锁住索引本身而非范围。

  • select ... from .. : MVCC实现非阻塞读
  • select... from lock in share mode: 追加了共享锁， Innodb会使用Next-Key Lock处理，如果有唯一索引，降级为RecordLock
  • select * from for update: 追加排他锁, 如果有唯一索引，降级为RecordLock
  • update ...where .. ： Next-Key lock 处理， 如果有唯一索引，降级为RecordLock
  • delete ... where ..: Next-Key lock 处理， 如果有唯一索引，降级为RecordLock
  • insert : 在要插入的那一行设置排他的RecordLock

悲观锁

  • 表级锁

    lock table tableName read|write, tableName2 read|write
show open tables;
unlock tables;

  • 共享锁

  • 排他锁

    默认在update, delete语句上加上for update

死锁排查

show engine innodb status \G;
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: 系统启动后到现在总共等待的次数
avatar
文章
阅读
粉丝