事务

事务概念

事务Transaction代表单个工作单元的一组SQL语句，所有语句都要运行成功，否则事务失败

事务四大特征ACID：

• 原子性Atomicity：事务是最小单位，不可再分
• 一致性Consistency：事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败
• 隔离性Isolation：事务间相互隔离，数据被更改时各自受保护
• 持久性Durability：事务的更改是永久的，事务终结的标志(内存的数据持久到硬盘文件中)

创建事务

START TRANSACTION;-- 开始事务

INSERT INTO orders(customer_id,order_date,`status`)
VALUES(1,'2021-01-01',1);

INSERT INTO order_items 
VALUES(LAST_INSERT_ID(),1,1,1); -- LAST_INSERT_ID()函数获取最近一次“插入”的id

COMMIT; -- 提交事务

-- ROLLBACK()手动设置回滚事务

mysql是设置默认自动提交的，增删改每一条语句会装在事务，如果没返回错误就会提交

SHOW VARIABLES LIKE 'autocommit'   -- autocommit	ON

并发和锁定

问题由来：现实世界多个用户同时使用同一个数据库，当一个用户在修改其它用户正在检索或修改的数据时，并发可能成为一个问题

数据库默认对并发控制，对正在被操作的行上锁，直到第一个事务被提交或退回

并发问题

• 丢失更新 ： 两个事务更新同一行，而最后提交的事务覆盖了先前所做的更改。解决：上锁，每次只有一个用户在操作数据

• 脏读：一个事务读取了尚未被提交的数据。解决办法：事务隔离（如，隔离级别read committed)

• 不可重复读：在事务中读取相同数据两次，得到不同的结果。解决：增加事务隔离级别，repeatable read可重复读，保证我们读取的数据是可重复和一致的。就算有事务中途修改了数据，我们看到的是首次读取创建的快照。

  因为读取不需提交commit，
  

• 幻读：查询中缺失了一行或多行，因为另一个事务正在修改数据，我们没有意识事务正在修改。解决：增加序列化隔离级别（若有影响查询结果的事务，要等它执行完）按序列化执行，用户和并发越多，等待时间越长

事务隔离级别

	lost updates	dirty reads	non-repeating reads	phantom reads
read uncommitted
read committed		√
repeatable read	√	√	√
serializable	√	√	√	√

隔离级别越高越会损害性能和可扩展性，因为需要用到更多隔离事务的锁

在mysql中，默认事务隔离级别是可重复读取

SHOW VARIABLES LIKE 'transaction_isolation'  -- REPEATABLE-READ

手动设置事务隔离级别：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 为一下个事务设定序列化隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 为当前会话
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;-- 所有会话的所有新事务设置全局隔离级别的

读未提交隔离级别

连接一：

START TRANSACTION;
UPDATE customers
SET points=20
WHERE customer_id=1;
ROLLBACK;

连接二：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;-- 设置读未提交隔离级别

SELECT points
FROM customers
WHERE customer_id=1; -- 这里会读到连接一未提交的脏数据

读已提交隔离级别

连接一：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;-- 设置读已提交隔离级别

START TRANSACTION;
SELECT points FROM customers WHERE customer_id=1; -- 第一次读 100

SELECT points FROM customers WHERE customer_id=1; -- 连接二运行完并提交后，读 44
COMMIT;  -- 存在重复读数据不一致问题

连接二：

START TRANSACTION;
UPDATE customers
SET points=44
WHERE customer_id=1;
COMMIT;

可重复读隔离级别

连接一：

-- 这个顾客查位于VA州的
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;
SELECT * FROM customers WHERE state='VA'; -- 在连接二未提交之前，只有一个在'VA‘州的

COMMIT;

连接二：

-- 这个顾客正在更新VA州的
START TRANSACTION;
UPDATE customers
SET state='VA'
WHERE customer_id=1;
COMMIT;

序列化隔离级别

连接一：此事务会等待另一事务完成

连接二：

-- 这个顾客正在更新VA州的
START TRANSACTION;
UPDATE customers
SET state='VA'
WHERE customer_id=3;
COMMIT;

序列化能把所有并发问题解决，因为所有事务是一个接一个按顺序完成的

死锁

如果事务因为死锁被退回，可以重新恢复。无法永远消除它，只能减少发生可能性。比如，按顺序执行，简化事务缩小事务的运行时长

连接一：

USE sql_store;
START TRANSACTION;

UPDATE orders SET `status`=2 WHERE order_id=1; -- 执行到这一步，然后握有这行数据的锁
UPDATE order_items SET quantity=10 WHERE order_id=2;-- 这行的锁在连接二，等待锁中

COMMIT;

报错信息：

UPDATE order_items SET quantity=10 WHERE order_id=2

1213 - Deadlock found when trying to get lock; try restarting transaction

连接二：

USE sql_store;
START TRANSACTION;

UPDATE order_items SET quantity=10 WHERE order_id=2;-- 执行到这一步，然后握有这行数据的锁
UPDATE orders SET `status`=2 WHERE order_id=1; -- 这行锁在连接一，发生死锁

COMMIT;