【MySQL】事务、事务隔离级别、死锁

1. 事务

• 事务：单个工作单元的一组SQL语句。事务中的所有语句都应成功完成，否则事务会运行失败。

1.1 事务的属性 ACID

1. 原子性 Atomicity：要么全部成功，即事务内的所有语句都成功执行并提交；要么全部回滚，即事务被退回，所做的所有更改都被撤销。
2. 一致性 Consistency：事务执行前后数据库处于一致性。例如：不会出现订单没有项目的情况 ；AB之间转账，A少100，B必须增加100。
3. 隔离性 Isolation：事务之间相互隔离。当同样的数据被不同事务更改时，事务之间不会互相干扰。如果多个事务想更新相同的数据，受到影响的行会被锁定，因此一次只有一个事务可以更新行，其他事务必须等正在运行的事务完成。
4. 持久性 Durability：一旦事务被提交，事务对数据库产生的更改和影响是永久的。

1.2 创建事务

• commit：自动提交事务
• rollback：事务回滚，退回事务并撤销所有更改。撤销已执行但未提交的操作，将数据库恢复到以前的状态。

start transaction;

SQL语句

commit; / rollback;

1.3 autocommit

• autocommit变量，如果事务中的语句都没有错误就会自动提交。

show variables like 'autocommit'

2. 并发和锁定

• 并发：两个及以上的用户同时访问相同数据的情况
• 锁： 防止两个事务同时更新同样的数据，使事务一个一个地按照顺序运行。一个事务执行更新时，会放一个锁在执行的行上；此时另一个事务尝试更新带锁的行，必须等第一个事务完成后（事务被提交或退回），才能更新。

2.1 并发问题

1. 丢失更新
   • 情景：两个事务尝试更新相同的数据且没有上锁，较晚提交的事务会覆盖早提交事务做的更改。
   • 解决方法：可重复读 / 序列化。
2. 脏读
   • 情景：事务读取了未被提交（commit）的数据。例如，事务A将客户1积分从10变为20，但未提交事务。此时事务B使用此客户1的积分20用于计算折扣。后事务A回滚了，客户1的积分最终还是10。事务B读取了事务A没有提交的数据，造成了脏读。
   • 解决方法：建立事务隔离级别，读已提交 / 可重复读 / 序列化。使事务修改的数据不会立马被其他事务读取。
   • 读已提交：事务只能读取已提交的数据。
3. 不可重复读 / 不一致读：
   • 情景：在事务中读取了相同的数据两次，但得到了不同的结果。例如，事务A第一次读取客户积分为10，中间事务B将客户积分改为0并提交，事务A后续又读取客户积分此时变为0。事务A两次读取的数据是不一致的。
   • 解决方法：建立事务隔离级别，可重复读 / 序列化。在此隔离级别上，读取到的数据是可重复且一致的。
   • 可重复读：事务A首次读取数据时创建快照，此时事务B更改了数据，事务A再次读取时是读取的首次读取时创建的快照。
4. 幻读
   • 情景：查询中缺失了一行或者多行，因为有另外一个事务正在修改数据，而查询的事务没有意识到其他事务的修改。例如，事务A查询积分超过10的所有顾客，事务B为另外一位顾客更新了积分超过10，但事务A的查询中没有返回这个新的顾客数据。
   • 解决方法：隔离级别，序列化。能保证当有别的事务B在更新数据时，正在执行的事务A能够知晓数据的变动。如果事务B修改了可能影响事务A查询结果的数据，事务A必须等事务B执行完，这样事务就会按序列化执行。
   • 序列化：是应用于一个事务的最高隔离级别，为操作提供了最大的确定性。但用户和并发事务越多，等待时间越长，系统会变慢 。所以隔离级别会损害性能和可扩展性。 因此只有在真的有必要防止幻读的情况下才用序列化。

3. 事务隔离级别

• 总览
  

• 隔离级别越高，会有越重的性能和可扩展性的问题。隔离级别越低，更容易并发，会有更多用户可以在同时接触到同一数据。

  • 隔离界别从低到高：读未提交——读已提交——可重复读——序列化
  • MySQL中默认的事务隔离级别：可重复读。可防止除幻读以外的大多数并发问题。只在必要时更改事务隔离级别。

• 查看当前隔离级别：默认是可重复读

  show variables like 'transaction_isolation'
  

  

• 更改事务隔离级别

  -- 为下一个事务更改隔离级别
  set transaction isolation level serializable 
  

  -- 为当前会话或当前连接中的事务设置隔离级别，当前连接立即生效
  set session transaction isolation level serializable
  

  -- 全局，不包括当前连接，之后新获取的连接都会生效。
  set global transaction isolation level serializable
  

3.1 读未提交

• 可能会发生：所有并发问题，丢失更新、脏读、不一致读、幻读。
• 等级最低的隔离级别。但是最快的隔离级别，不设置任何锁，且忽略了其他事务设置的锁。
• 可以在不需要精确一致性或数据不怎么更新，且想要更好的性能时的情况下使用。

3.2 读已提交

• 解决：脏读
• 可能会发生：可重复读
• 可以在不需要精确一致性或数据不怎么更新，且想要更好的性能的情况下使用。

3.3 可重复读：MySQL的默认事务隔离级别

• 解决：不可重复读 / 不一致读。
• 可能会发生：幻读
• 默认的事务隔离级别。比序列化更快，且防止了除幻读以外的并发问题。在此级别下，事务中读取到的数据是一致的。

3.4 序列化

• 解决：幻读
• 最高的事务隔离级别。解决所有的并发问题。
• 但事务是一个一个运行的，当用户较多时，等待时间增加、性能不好。

4. 死锁

• 当不同事物因握住彼此事物需要的锁，两个事物都一直在等待对方，永远无法释放锁。两个事务互相锁住了对方将要操作的数据

• 例如：两个会话中的事务在更新数据时，语句顺序不同，会发生死锁。事务1的更新1执行后，执行事务2的更新1，再执行事务2的更新2时，会等待事务1释放更新1的锁。事务1执行更新2时又要等待事务2释放更新1的锁。彼此之间握住了对方需要的锁，发生死锁。

  -- 事务1
  use sql_store;
  start transaction ;
  update customers set state = 'VA' where customer_id = 1;
  update orders set status = 1 where  order_id = 1;
  commit;
  

  -- 事务2
  use sql_store;
  start transaction ;
  update orders set status = 1 where  order_id = 1;
  update customers set state = 'VA' where customer_id = 1;
  commit;
  

• 解决方案

  • 在不同事务中更新数据时遵照相同的语句顺序。检查经常出现死锁的事务中的语句顺序。如果不同的事务以相反的顺序更新数据，就很可能发生死锁。
  • 尽量简化事务，缩短事务运行时长。