深入理解MySQL事务隔离级别与锁机制

theme: geek-black

ACID属性

• 原子性(Atomicity)：事务是一个原子操作，要么全部一起执行成功，要么一起执行失败
• 一致性(Consistent)：事务开始和结束，数据都必须保持一致
• 隔离性(Isolation): 事务处理过程中需要保证相互隔离、独立
• 持久性(Durale): 事务结束之后，对于数据的修改是永久保存下来的

并发事务处理带来的问题

• 更新丢失(Lost Update) 或 脏写: 多个事务在不知道其他事务存在的情况下，同时对数据做修改，就会出现脏写
  • 最后的更新覆盖了其他事务所做的更新
• 脏读(Dirty Reads): 当一个事务读取一条记录并对其他进行修改，但是还未提交，此时另一个事务也来读取，如果不加控制，第二个事务读取到的数据就是脏数据
  • 事务A读取到事务B已经修改但还未提交的数据
• 不可重复度(Non-Repeatable Reads): 一个事务读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生改变
  • 事务A内部相同查询语句在不同时刻读出的结果不一致，不符合隔离性
• 幻读(Phantom Reads): 一个事务按相同的查询条件读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据
  • 事务A读到了事务B提交的新增数据，不符合隔离性

隔离级别

脏读、不可重复读、幻读，其实都是数据库读一致性的问题，必须由数据库提供一定的事务隔离机制来解决，而MySQL默认的是课重复读

• 读未提交：事务A更新了数据(还未提交)，而事务B却能查询到事务A更新的数据
• 读已提交: 事务A更新了数据(还未提交)，事务B不能查询到事务A更新的数据(解决了脏读问题)
• 可重复度: 事务A查询了记录(假如400)，事务B更改了数据(400 - 50 = 350，然后commit)，事务A在没有提交事务之前再次读取的值还是400，直到事务A提交事务才能读到最新更新的数据(保证了事务间的隔离性)
• 串行化: 加了行锁的数据，其他事务不能进行增删改查等操作，相当于独占锁

锁机制

• 锁是计算机协调多个进程/线程并发访问某一资源的机制
• 锁分类
  • 从对数据库上的性能
    • 乐观锁(版本号对比)
    • 悲观锁
  • 从对数据库操作的类型
    • 读锁(共享锁，多个读操作可以同时进行互不影响)
    • 写锁(排它锁，写操作没完成前，其它都无法进行操作，保证串行)
  • 从堆数据库操作的粒度
    • 表锁
    • 行锁

表锁

• 每次操作锁住整张表，开销小，加锁快，不会出现死锁，锁粒度大，发生锁冲突的概率更高，并发读最低，一般用于数据迁移的场景

行锁

• 每次操作锁住一行数据，开销大，加锁慢，会出现死锁，锁粒度最小，发生锁冲突的概率最低，InnoDB支持事务以及行锁，但是MylSAM不支持
• 一个session开启事务更新不提交，另外一个session更新同一条记录会阻塞，更新不同记录不会阻塞

间隙锁

• 锁的就是两个值之间的空隙.
• 间隙锁在某些情况下可以解决幻读问题
• 间隙锁在可重复度隔离级别下才会生效

update account set name = '100' where id > 8 and id <18;
其他Session没法在这个范围所包含的所有行记录以及范围内间隙行记录锁在原表的间隙里插入或修改任何数据.即id在(3,20]区间都无法修改数据，注意最后那个20也是包含在内的。

• 例如SQL: : 间隙有 id 为 (3,10),(10,20),(20,正无穷) 这三个区间.

临间锁

• 行锁与间隙锁的组合
• 临键锁主要母的也是为了避免幻读，如果事务降级为读已提交，临键锁会失效
• 例如上述间隙锁例子SQL,间隙为(3,20]整个区间

无锁引行锁会升级为表锁

锁主要是加在索引上，如果对非锁引字段更新，行锁有可能会变成表锁

例如: 假如现在将无索引的char类型字段更改成另外一个varchar类型，因为没有走索引，此时会全表扫描，最终升级为'表锁'

锁优化建议

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

锁小结

• 行锁、间隙锁、临键锁都属于排它锁
• 行锁就是锁住一行记录
• 间隙锁只在RR级别中才会产生
• 唯一索引只有锁住多条记录或一条不存在的记录才会产生间隙，指定给某条存在的记录加锁的时候，只会加行锁，不会产生间隙锁
• 普通索引不管是锁住单行还是多行记录，都会产生间隙锁
• 间隙锁锁住的两值之间的空白区域，为了防止出现幻读
• RC情况下，间隙锁会失效
• MylSAM在执行查询语句select前，会自动给涉及的所有表加读锁,在执行update、insert、delete等操作会自动给涉及的表加写锁.
• InnoDB在执行查询语句select时(非串行隔离级别)，不会加锁，但是update、insert、delete等操作会加行锁
• 读锁会阻塞写，但是不会阻塞读
• 写锁会阻塞读写
• InnoDB存储引擎实现了行锁，虽然在锁机制的实现方面带来的性能损耗会比表锁更高，但是整体并发处理能力远远优于Mylsam的表锁锁定，所以在并发场景，InnoDB整体性能由于MylSAM
• 如果一条SQL中用不到索引是不会使用行锁的,会使用表锁