SQL语言笔记 第十章 事务 TRANSACTION

使用的是MySQL数据库

1. 事务

• 事务是代表单个工作单元的一组SQL语句。
• 事务使用场景：需要对数据库进行多次更改的情况下，并且希望所有更改作为一个单元一起成功或失败时。
• 什么是工作单元：比如银行转账，小明转账给小红10元，会进行两个操作，先从小明账户取出10元，再在小红账户中添加10元，这两个操作一起就是一个工作单元。只有这两个操作都成功，这个事务才会被提交；但凡有一个失败，事务都会被退回并且更改被撤销。
• 事务的属性ACID：
  （1）原子性Atomicity：事务是牢不可破的，一个事务就是一个工作单元，不管包含多少语句，要么一起成功要么一起失败；（小明给小红转账100元，小明的账户少了100元，但由于中途系统出错，小红的账号没能收到这100元，则这个100元就会原路返回到小明的账户里）
  （2）一致性Consistency：使用事务，数据库将始终保持一致的状态；（比如小明账户转出钱，肯定会有另一个账户转进钱）
  （3）隔离性Isolation： 1-不同的事务之间相互隔离互不干扰，2-如果多个事务想要更新相同行数据，受影响的行会被锁定，一次只有一个事务可以更新该行，其他事务必须等这个事务完成后才能更新；（比如小明给小红连续两次转账，只有第一次转账成功，第二次转账才会执行）
  （4）持久性Durability：事务一旦被提交，事务产生的更改是永久的；即使停电或系统崩溃，数据库也不会丢失更改内容。（比如小明今天给小红转账成功100元，这个100元永远不会自己退回到小明的账户）

2. 创建事务

-- 创建事务的语法
START TRANSACTION;

-- 中间写一组sql语句，以分号结尾隔开

COMMIT; -- COMMIT语句关闭此事务
 
 
-- 实现：在订单表中插入一条记录（顾客id、订单日期、订单状态）后，也在订单项目表中插入对应订单的详细信息（订单id、商品id、数量、单价）
START TRANSACTION;

INSERT INTO orders(customer_id,order_date,status)
VALUES (1,'2022-01-21',1);

INSERT INTO order_items
VALUES(LAST_INSERT_ID(),1,2,3);

COMMIT; -- 提交当前事务
-- ROLLBACK与COMMIT相反，是撤销当前事务；如果需要进行错误检查并手动退回事务，把COMMIT改成ROLLBACK
 
 
-- 另外，MySQL会把每条执行的语句装在事务中，如果语句没有错误，就会自动提交。比如一条普通的查询语句，虽然没有用START TRANSACTION;COMMIT;开头结尾，但MySQL会自动为其添加再执行。
-- 自动提交是由一个叫做AUTOCOMMIT的系统变量来控制的，可以通过SHOW VARIABLES LIKE 'autocommit';进行查看，一般默认设置为开。

3. 并发和锁定 Concurrency and Locking（默认情况下，MySQL如何处理并发问题）

• 并发的概念：存在有两个及更多的用户同时访问相同数据的情况。
• 并发可能成为问题的场景：当一个用户修改其他用户正在检索或修改的数据时会发生。

-- 模拟两个用户试图同时更新顾客1的积分的情况，给顾客1的积分加10：
-- 场景：会话1先开启事务、更新行但不提交；然后去会话2开启事务、更新行，但不提交。
-- 什么现象：会话2一直尝试执行更新但没有成功。
-- 原因：执行会话1的更新时，MySQL在更新的顾客行上放了一个锁；如果会话2的事务尝试更新同一行，必须等到会话1的事务完成（提交或退回）。
 
-- 会话1、2的代码，使用工具栏Query->Execute Current Statement（执行当前语句）逐行执行这个脚本
START TRANSACTION;
UPDATE customers
SET points = points + 10
WHERE customer_id = 1;
COMMIT;

4. 常见的并发问题（模拟两个会话两个事务，会话1执行事务A，会话2执行事务B）

问题	解释	场景	结果	解决
Lost Updates 丢失更新	当两个事务尝试更新相同的数据，并且更新行没有上锁时会发生。	事务A\B都试图更新同一个顾客，事务A先增加积分不提交，事务B后更新顾客的所属州不提交，此时先提交事务B再提交事务A	这种情况下，后提交的事务会覆盖先提交事务做的更改。事务A提交后会发现，顾客的积分增加了，但所属州还是更新前的。	使用锁。Mysql默认情况下会使用锁定机制，防止两个事务同时更新相同的数据。
Dirty Reads 脏读	当一个事务读取了尚未被提交的数据时发生。	该顾客购买东西时，每一个积分抵扣1元。现在事务A给该顾客的积分增加10不提交，事务B搜索积分给顾客商品打折，此时事务A退出之前增加的积分	因为事务A退回了，所以该顾客的积分并没有改变，而事务B读到了根本不存在的数据，因此事务B读取的数据是脏的。	为事务建立隔离机制。READ COMMITTED读已提交、REPEATABLE READ可重复读、SERIALIZABLE序列化三种之一的隔离机制都能解决。
Non-repeating Reads 不可重复读	同一事务中，读取了某个数据两次，两次得到了不同的结果。	事务A有两条查询语句组成，且都查询顾客1的积分；事务B更新顾客1的积分；事务A先执行第一条查询语句，事务B更新语句并提交，最后事务A再执行第二条查询语句。（如商场根据积分打折，那不管中间积分的变化如何，都应该按照一开始查询到的数据进行计算）	事务A两条查询语句查出来的积分不同。	为事务建立隔离机制。REPEATABLE READ可重复读、SERIALIZABLE序列化两种之一的隔离机制都能解决。
Phantom Reads 幻读	某些数据在事务执行查询后（没提交）才添加、更新或删除，导致这些数据无法在查询中看到。			保证没有其他 影响查询符合条件的数据的 事务正在进行。给事务添加隔离机制SERIALIZABLE序列化。

5. 事务隔离级别 Transaction Isolation Levels

• READ UNCOMMITTED（读未提交） 可以读取未提交的数据；
• READ COMMITTED（读已提交） 只能读取已提交的数据；
• REPEATABLE READ（可重复读） 一个事务中多次读取某一数据的值保持一致性或重复；
• SERIALIZABLE（序列化） 如果被读取的事务在读取过程中发生改变，则读取的事务就会停止读取，等待更新完成，再读取最新数据。

	Lost Updates	Dirty Reads	Non-repeating Reads	Phantom Reads
READ UNCOMMITTED
READ COMMITTED		OK
REPEATABLE READ	OK	OK	OK
SERIALIZABLE	OK	OK	OK	OK

• 隔离级别从低到高排序：READ UNCOMMITTED < READ COMMITTED < REPEATABLE READ < SERIALIZABLE
• 隔离级别越低，越容易并发，会有更多用户同时接触到同一数据，因此也会产生更多并发问题，但同时有着更高的性能，以及用于隔离事物的锁也更少。
• 隔离级别越高，对并发的限制也更好，因为会需要用到更多锁来隔离事务，所以并发问题更少，但是也因此需要更多的资源存储等待的事务，以降低性能和可扩展性为代价。
• MySQL中默认的隔离级别是REPEATABLE READ可重复读，可以解决大部分的并发问题，除了幻读。

-- 查看默认隔离级别
SHOW VARIABLES LIKE'transaction_isolation';


-- 更改事务隔离级别
-- 1、只作用于下一条执行的事务，比如设置隔离级别读已提取
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 2、作用于当前会话或连接的未来所有事务，加上SESSION
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 3、作用于所有会话的所有新事务，即设置全局隔离级别，加上GLOBAL
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

6. 死锁

解释：当两个事务均因要更新对方事务锁住的行而无法完成的情况（当然也可能不止两个事务）。这就导致两个事务都在等待对方，并永远无法释放锁。

-- 创建两个会话，会话1和会话2的更新语句顺序颠倒
-- 会话1
START TRANSACTION;
UPDATE customers SET state = 'VA' WHERE customer_id = 1;
UPDATE orders SET status = 1 WHERE order_id = 1;
COMMIT;

-- 会话2
START TRANSACTION;
UPDATE orders SET status = 1 WHERE order_id = 1;
UPDATE customers SET state = 'VA' WHERE customer_id = 1;
COMMIT;

-- 模拟死锁的场景：会话1执行完第一条更新语句后，mysql自动将顾客1的该行锁定；然后会话2执行完第一条更新语句，mysql自动将订单1的该行锁定；此时若再执行会话1的第二条更新语句会发现一直尝试执行但没有成功（执行会话2的第二条也是相同情况）
-- 原因：因为这两个会话的第一条更新语句都是对方第二条要更新的行，但mysql已经将这两行锁定了，执行第二条的时候都在等对方先提交解锁，但永远也无法等到，所以就出现这种现象，也称为死锁。