【Mysql深入理解系列6】事务隔离级别

发表于 | 分类于 | |

事务隔离级别深入理解

  1. Mysql事务级ACID特性详解
  2. Mysql事务隔离级别详解
  3. Mysql锁机制详解
  4. Mysql锁优化建议

事务隔离级别ACID

事务及其ACID属性。

事务的特性 ACID:

ACID是原子性，一致性，持久性，独立性的缩写。

原子性：一个操作不是成功就是失败，要么执行成功，要么执行失败。

一致性：事务的索引规则，约束等不受破坏。事务开始中，所有相关的数据结果都必须保持状态一致，比如一个事务对三个数据进行修改，事务结束后三个状态必须都是被修改过的，保持一致。

隔离性： 每个事务之间不要相互影响。A事务查的结果不要被别的影响。就是说A事务执行过程中，结果集不能变。

持久性：事务完成后的对结果产生影响要持久。不能一会儿变来变去。

并发事务带来的问题

  1. 更新丢失(脏写)

事务A对数据进行+1，事务B对数据进行-1。B的更新操作把A覆盖掉了。

  1. 脏读

事务A读取了事务B还未提交的数据，但是事务B进行了回滚。事务A读到了脏数据。

  1. 不可重复读

事务A读取了数据1，但是被B修改成了2，事务A再读变成了2。与之前的不同。

  1. 幻影读

事务A，count了一个数据块，事务B插入了一条数据，事务A再进行count的 时候数据变了。

以上问题都是事务隔离性问题。所以要引入事务隔离级别来解决这些问题。

事务隔离级别

图片

事务隔离级别是Mysql提供的预设的几种级别。

  1. 未提交读：事务T在读取数据的时候并未对数据进行加锁，事务T在修改数据的时候对数据增加行级共享锁，这种隔离级别没解决脏读。
  2. 已提交读：事务T在读取数据时增加行级共享锁，读取一旦结束，立即释放；事务T在修改数据时增加行级排它锁，直到事务结束才释放，这种隔离级别解决了脏读。
  3. 可重复读：事务T在数据读取时，必须增加行级共享锁，直到事务结束；事务T在修改数据过程中，必须增加行级排它锁，直到数据结束；这种隔离级别没解决幻读。
  4. 序列化：事务T在读取数据时，必须先增加表级共享锁，直到事务结束时才释放；事务T在修改数据时，必须先增加表级排它锁，直到事务结束才释放。

这些隔离级别采用的是
MVCC:multi version concurrency controller

锁机制详解

锁机制是事务隔离级别的实现方式

锁分类

性能上分：悲观锁，乐观锁。
数据库操作上分：读锁，写锁。
从数据库的操作力度来分：表锁，行锁。

  • 表锁
    开销小，加锁快。锁力度大， 一般在离线操作数据迁移的时候进行，比较少会用。
  • 读锁（s)
    可以进行共享读。
  • 写锁（x)
    不能进行写。也不能进行读。

重点

  • 行锁
    开销大，加锁慢。
    innodb支持行锁。支持事务。
  • 间隙锁
    innodb采用间隙锁，在可重复读级别解决了幻读问题。
  • 临键锁（next-ke）
    行锁 + 间隙锁

锁优化建议

行锁分析

1
show status like 'innodb_row_locks'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查看事务
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

锁优化建议

  1. 尽可能让所有数据检索都通过索引来完成，避免误索引锁升级为表锁。
  2. 合理设计索引，尽量缩小锁的范围。
  3. 尽可能减少检索条件范围，避免间隙锁。
  4. 尽量控制事务大小，减少锁定资源和时间长度，涉及事务加锁的sql尽量放在事务后执行。
  5. 尽可能低级别事务隔离。