数据库并发事务笔记及实验

事务

• 事务是并发控制的基本单位，具有ACID四个特性
  1. 原子性（Atomicity）：要么全部执行，要么全部失败回滚
  2. 一致性（Consistency）：数据库在事务执行前后保持一致
  3. 隔离性（Isolation）：一个事务的修改在最终提交以前，对其他事务不可见
  4. 持久性（Durability）：一旦事务提交，则事务执行结果永远保存到数据库
• ACID并不是平级关系,开发的话重点关注一致性，
  • 在无并发的条件下，只保证原子性就可以达到一致性，在有并发的条件下，需要保证隔离性，破坏隔离性会导致并发一致性问题
    

并发一致性问题

• 核心问题是并发环境下没有保障隔离性（提交之前不被其他线程看到）

1. 丢失修改: 自己的修改被其他修改覆盖
2. 脏读: 读到撤回的数据
3. 不可重复读: 两次读取同一数据之间被其他事务修改
4. 幻读: 两次读取同个范围的数据之间被另一个事务修改

封锁

用加锁的形式来保证隔离性

• 封锁粒度：行级锁和表级锁
• 封锁类型：读写锁和意向锁
• 当试图修改数据时，事务会为所以来的数据资源请求写锁，一旦成功获取则事务一直持有到事务完成。

封锁协议

1. 一级封锁: 只要求加写锁，只能够解决丢失修改问题
2. 二级封锁: 要求加读锁，但是读完之后马上释放，能够解决脏读问题（写事务提交前读事务被阻塞）
3. 三级封锁: 要求加读锁，但是在读事务提交后马上释放，能够解决不可重复读的问题
4. 两段锁协议: 保证串行操作，解决幻读问题

隔离级别：

• 未提交读(Read_Uncommitted): 事务中的修改未提交就被读

• 提交读(Read_Committed): 一个事务只能读取已经提交的事务的修改

• 可重复读(REAPETED): 保证同一个事务中多次读取的结果是一样的

• 可串行读(Serializable): 强制事务串行

• 隔离级别与并发问题，打’F’表示不会出现, 'T’表示会出现

  隔离级别	脏读	不可重复读	幻读	加锁读
  未提交读	T	T	T	F
  提交读	F	T	T	F
  可重复读	F	F	T	F
  可串行化	F	F	F	T

• 启发:

  • 保证一级封锁协议，可以达到未提交读的水平
  • 保证二级封锁协议，可以达到提交读
  • 保证三级封锁协议，可以达到可重复读
  • 保证两段锁协议，可达到可串行化

• PS：保证四种隔离级别，不一定是用封锁协议，比如InnoDB的则采用MVCC的方式，可以实现提交读和可重复读两个隔离级别。可串行化隔离级别需要对所有读取的行都加锁，单纯使用MVCC无法实现

• InnoDB默认支持的的隔离级别是repeateable read

并发控制的主要技术有

• 封锁（三级封锁，众多产品常用，但是并发度还不够高），运用三级封锁协议
• 时间戳
• 乐观控制法
• MVCC

活锁死锁

• 活锁：多个事务争用数据R，有的事务会处于饥饿状态，因此避免活锁的方法是使用FIFO
• 死锁：与多线程的死锁一样，争用资源和事务成一个单向的环，避免方法是有一次封锁法（比如说修改某一行，会对整个表加锁）

隔离级别实验(开启两个终端连接数据库)

1. 尝试实现丢失修改

   # 终端1
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
   start transaction;
   update course set cno='2-123' where cname='模拟电路';   
   
   # 终端2
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
   start transaction;
   update course set cno='3-123' where cname='模拟电路';   # 发现会被阻塞，直到等锁超时
   

2. 未提交读(会出现脏读及更高级的情况)

   # 终端1
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
   start transaction;
   update course set cno='2-123' where cname='模拟电路';   
   
   # 终端2
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
   start transaction;
   select * from course;   # 会读到终端1中未提交的数据
   
   # 终端1
   rollback;
   select * from course;   # 确认回滚了
   
   # 终端2
   select * from course;   # 发现跟上一次读不一样了
   

3. 提交读(写锁读完就释放，会出现不可重复读及更高级的情况)

   # 终端1
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   start transaction;
   select * from course;   
   
   # 终端2
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   start transaction;
   update course set cno='2-123' where cname='模拟电路';   
   
   # 终端1
   select * from course;   # 发现跟上次读一样,就像写锁根本没排斥读锁(MVCC的效果)
   
   # 终端2
   commit;
   
   # 终端1
   select * from course;  # 发现跟上次读不一样了，不可重复读
   

4. 可重复读(可以重复读)

   # 终端1
   SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   start transaction;
   select * from course;   
   
   # 终端2
   SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   start transaction;
   update course set cno='3-123' where cname='模拟电路';   
   
   # 终端1
   select * from course;   # 发现跟上次读一样
   
   # 终端2
   commit;
   
   # 终端1
   select * from course;  # 发现跟上次读一样了，可重复读
   commit;
   select * from course; # 发现读的结果已经和终端2修改后一样了
   

• 总结: 由于MVCC的存在，并没有发生读锁阻塞写锁的情况而只发生写写互斥的情况，这是因为在版本号的约束下，只读事务读到的是就版本的数据
• PS: 另外经过实验可以发现，如果一个终端没显示开启事务，表现起来像是不会受到事务隔离级别的约束的。但究其根本，是因为每条语句都隐式是一个事务，所以仍然会有阻塞的问题（这里的“阻塞”指的是表现出来的形式，并不一定真的用了锁）。

MVCC（Multi-Version Concurrency Control, 多版本并发控制）

• 版本是指数据库中数据对象的一个快照，记录了数据对象某个时刻的状态。
• 只有提交读和可重复读两种隔离级别会使用MVCC。（因为读未提交总是读取最新的数据行，而SERIALIZABLE则会对所有读取的行都加锁）
• 版本号：
  • 系统版本号：一个递增的数字，每开始一个新的事务，系统版本号就会自动递增
  • 事务版本号：事务开始的系统版本号
• MVCC在每行记录后面都保存两个隐藏的列，用来存储两个版本号
  • 创建版本号（指示创建一个数据行的快照时的系统版本号）
  • 删除版本号（删除时的系统版本号），删除并没有马上消除，只是标记了删除
  • PS：其实还有一个回滚指针列，指向上一个版本
• Undo日志：该日志把一个数据行的所有快照（所有版本）连接起来，而在实现隔离级别时，需要理解的重点是，当开启新一个事务时，该事务的版本号肯定会大于当前所有数据行快照的创建版本号。
• 在MVCC下，读操作读的是快照中的数据，这样可以减少加锁开销；而不是用MVCC时，读取的总是最新的数据，需要加锁。
• 以 可重复读 隔离级别作为例子，看看MVCC具体操作
  • SELECT：只有满足以下条件的记录才能返回
    1. 只查找版本号早于当前事务版本的数据行（就不会读到其他线程新修改的数据）
    2. 行的删除版本要么未定义，要么大于当前版本号，能够确保事务读取到的行在事务开始之前未被删除，即只能读到自己的删除记录
  • INSERT：建立快照，为插入的每一行保存当前版本号作为行创建版本号，但是要保证在最新更改（包括其他事务的更改）之后添加版本。
  • DELETE：为删除的每一行保存当前系统版本号作为删除版本号（对于事务而言，如果该快照的删除版本号大于当前事务版本号则表示快照有效，否则表示该快照之前已经被当前事务删除）
  • UPDATE：先delete再insert，保存当前系统版本号作为行创建版本号，同时保存当前系统版本号到原来的行作为行删除号。

实际案例——Mysql处理秒杀问题

• 问题描述：每次购买都是一个事务，在两个事务进行可重复读下，一个事务将库存清空（货品数减到0），而另一个事务由于保证可重复读，又会在一次减库存，就会出现负数，即超买的现象
• 实验现象：
  
  
• 问题讨论：如何解决秒杀的性能问题和超卖的讨论
• 解决方案1：在update语句加上数量约束 update orderitems set quantity=quantity-1 where order_num=20009 and order_item=5 and quantity>0;。实验发现，当另一个事务修改了但是未提交时，当前事务提交被阻塞（可以通过超时异常来进行回滚），当另一个事务提交了之后，则当前事务是 0 rows affected
  • 探究原因：单纯的可重复读只是保证读到的是未被其他事务更改的版本。而当在其他事务更改提交后，当前事务仍想修改的话，就需要在最新的版本上修改。
  • 实验结果
    
• 解决方案2：使用缓存，读写缓存再保存到数据库
• 解决方案3：使用消息队列

参考资料

1. CS-Notes数据库系统原理
2. 施瓦茨. 高性能 MYSQL(第3版)[M]. 电子工业出版社, 2013.
3. MySQL InnoDB 的多版本并发控制（MVCC