SQL进阶教程 | 史上最易懂SQL教程 5小时零基础成长SQL大师（4）

5pace

已于 2022-04-18 19:52:06 修改

阅读量745

点赞数

文章标签： mysql

于 2022-04-18 10:14:43 首次发布

本文链接：https://blog.csdn.net/m0_51448653/article/details/124240359

版权

【第十章】触发器和事件

触发器

触发器是在插入、更新或删除语句前后自动执行的一段SQL代码（A block of SQL code that automatically gets executed before or after an insert, update or delete statement）通常我们使用触发器来保持数据的一致性
创建触发器的语法要点：命名三要素，触发条件语句和触发频率语句，主体中 OLD/NEW 的使用
案例：在 sql_invoicing 库中，发票表中同一个发票记录可以对应付款表中的多次付款记录，发票表中的付款总额应该等于这张发票所有付款记录之和，为了保持数据一致性，可以通过触发器让每一次付款表中新增付款记录时，发票表中相应发票的付款总额（payement_total）自动增加相应数额

语法上，和创建储存过程等类似，要暂时更改分隔符，用 CREATE 关键字，用 BEGIN 和 END 包裹的主体
几个关键点：

命名习惯（三要素）：触发表_before/after(SQL语句执行之前或之后触发)_触发的SQL语句类型
触发条件语句：BEFORE/AFTER INSERT/UPDATE/DELETE ON 触发表
触发频率语句：这里 FOR EACH ROW 表明每一个受影响的行都会启动一次触发器。其它有的DBMS还支持表级别的触发器，即不管插入一行还是五行都只启动一次触发器，到Mosh录制为止MySQL还不支持这样的功能
主体：主体里可以对各种表的数据进行修改以保持数据一致性，但注意唯一不能修改的表是触发表，否则会引发无限循环（“触发器自燃”），主体中最关键的是使用 NEW/OLD 关键字来指代受影响的新/旧行（若INSERT用NEW，若DELETE用OLD，若UPDATE似乎两个都可以用？）并可跟 ‘点+字段’ 以引用这些行的相应属性

DELIMITER $$

CREATE TRIGGER payments_after_insert
    AFTER INSERT ON payments
    FOR EACH ROW
BEGIN
    UPDATE invoices
    SET payment_total = payment_total + NEW.amount
    WHERE invoice_id = NEW.invoice_id;
END$$

DELIMITER ;

测试：往 payments 里新增付款记录，发现 invoices 表对应发票的付款总额确实相应更新

INSERT INTO payments
VALUES (DEFAULT, 5, 3, '2019-01-01', 10, 1)

练习：创建一个和刚刚的触发器作用刚好相反的触发器，每当有付款记录被删除时，自动减少发票表中对应发票的付款总额

DELIMITER $$

CREATE TRIGGER payments_after_delete
    AFTER DELETE ON payments
    FOR EACH ROW
BEGIN
    UPDATE invoices
    SET payment_total = payment_total - OLD.amount
    WHERE invoice_id = OLD.invoice_id;
END$$

DELIMITER ;

测试：删掉付款表里刚刚的那个给3号发票支付10美元的付款记录，则果然发票表里3号发票的付款总额相应减少10美元.

DELETE FROM payments
WHERE payment_id = 9

查看触发器

用以下命令来查看已存在的触发器及其各要素

SHOW TRIGGERS

如果之前创建时遵行了三要素命名习惯，这里也可以用 LIKE 关键字来筛选特定表的触发器

SHOW TRIGGERS LIKE 'payments%'

删除触发器

和删除储存过程的语句一样

DROP TRIGGER [IF EXISTS] payments_after_insert
-- IF EXISTS 是可选的，但一般最好加上

最佳实践：

最好将删除和创建数据库/视图/储存过程/触发器的语句放在同一个脚本中（即将删除语句放在创建语句前，DROP IF EXISTS + CREATE，用于创建或更新数据库/视图/储存过程/触发器，等效于 CREATE OR REPLACE）并将脚本录入源码库中，这样不仅团队都可以创建相同的数据库，还都能查看数据库的所有修改历史

DELIMITER $$

DROP TRIGGER IF EXISTS payments_after_insert;
/*
实验了一下好像这里用$$也可以,
但为什么可以用;啊？
*/

CREATE TRIGGER payments_after_insert
    AFTER INSERT ON payments
    FOR EACH ROW
BEGIN
    UPDATE invoices
    SET payment_total = payment_total + NEW.amount
    WHERE invoice_id = NEW.invoice_id;
END$$

DELIMITER ;

使用触发器进行审核

导航：
之前已经学习了如何用触发器来保持数据一致性，触发器的另一个常见用途是为了审核的目的将修改数据的操作记录在日志里。
建立一个审核表（日志表）以记录谁在什么时间做了什么修改，实现方法就是在触发器里加上创建日志记录的语句，日志记录应包含修改内容信息和操作信息两部分。
案例：用 create-payments-table.sql 创建 payments_audit 表，记录所有对 payements 表的增删操作，注意该表包含 client_id, date, amount 字段来记录修改的内容信息（方便之后恢复操作，如果需要的话）和 action_type, action_date 字段来记录操作信息。注意这是个简化了的 audit 表以方便理解。

具体实现方法是，重建在 payments 表里的的增删触发器 payments_after_insert 和 payments_after_delete，在触发器里加上往 payments_audit 表里添加日志记录的语句

具体而言：

往 payments_after_insert 的主体里加上这样的语句：

INSERT INTO payments_audit
VALUES (NEW.client_id, NEW.date, NEW.amount, 'insert', NOW());

往 payments_after_delete 的主体里加上这样的语句：

INSERT INTO payments_audit
VALUES (OLD.client_id, OLD.date, OLD.amount, 'delete', NOW());

测试：

-- 增：
INSERT INTO payments
VALUES (DEFAULT, 5, 3, '2019-01-01', 10, 1);

-- 删：
DELETE FROM payments
WHERE payment_id = 10

发现 payments_audit 表里果然多了两条记录以记录这两次增和删的操作

实际运用中不会为数据库中的每张表建立一个审核表，相反，会有一个整体架构，通过一个总审核表来记录，这在之后设计数据库中会讲到。

事件

事件是一段根据计划执行的代码，可以执行一次，或者按某种规律执行，比如每天早上10点或每月一次

通过事件我们可以自动化数据库维护任务，比如删除过期数据、将数据从一张表复制到存档表或者汇总数据生成报告，所以事件十分有用。

首先，需要打开MySQL事件调度器（event_scheduler），这是一个时刻寻找需要执行的事件的后台程序

查看MySQL所有系统变量：

SHOW VARIABLES;
SHOW VARIABLES LIKE 'event%';
-- 使用 LIKE 操作符查找以event开头的系统变量
-- 通常为了节约系统资源而默认关闭

用SET语句开启或关闭,不想用事件时可关闭以节省资源，这样就不会有一个不停寻找需要执行的事件的后台程序

SET GLOBAL event_scheduler = ON/OFF

案例：创建这样一个 yearly_delete_stale_audit_row 事件，每年删除过期的（超过一年的）日志记录（stale adj. 陈腐的；不新鲜的）

DELIMITER $$

CREATE EVENT yearly_delete_stale_audit_row

-- 设定事件的执行计划：
ON SCHEDULE  
    EVERY 1 YEAR [STARTS '2019-01-01'] [ENDS '2029-01-01']    

-- 主体部分：（注意 DO 关键字）
DO BEGIN
    DELETE FROM payments_audit
    WHERE action_date < NOW() - INTERVAL 1 YEAR;
END$$

DELIMITER ;

关键点：

命名：用时间间隔（频率）开头，可以方便之后分类检索，时间间隔（频率）包括【once】/hourly/daily/monthly/yearly 等等
执行计划：
规律性周期性执行用 EVERY 关键字，可以是 EVERY 1 HOUR / EVERY 2 DAY 等等
若只执行一次就用 AT 关键字，如：AT ‘2019-05-01’
开始 STARTS 和结束 ENDS 时间都是可选的

另外：

NOW() - INTERVAL 1 YEAR 等效于 DATE_ADD(NOW(), INTERVAL -1 YEAR) 或 DATE_SUB(NOW(), INTERVAL 1 YEAR)，但感觉不用DATEADD/DATESUB函数，直接相加减（但INTERVAL关键字还是要用）还简单直白点

小结：

查看和开启/关闭事件调度器（event_scheduler）：

SHOW VARIABLES LIKE 'event%';
SET GLOBAL event_scheduler = ON/OFF

创建事件：

……
CREATE EVENT 以频率打头的命名
ON SCHEDULE
    EVERY 时间间隔 / AT 特定时间 [STARTS 开始时间][ENDS 结束时间]
DO BEGIN
……
END$$
……

查看、删除和更改事件

导航：

上节课讲的是创建事件，即“增”，这节课讲如何对事件进行“查、删、改”，说来说去其实任何对象都是这四种操作

查（SHOW）和删（DROP）和之前的类似：

SHOW EVENTS 
-- 可看到各个数据库的事件
SHOW EVENTS [LIKE 'yearly%'];  
-- 之前命名以时间间隔开头这里才能这样筛选
DROP EVENT IF EXISTS yearly_delete_stale_audit_row;

“改” 要特殊一些，这里首次用到 ALTER 关键字，而且有两种用法：

如果要修改事件内容（包括执行计划和主体内容），直接把 ALTER 当 CREATE 用（或者说更像是REPLACE）直接重建语句
暂时地启用或停用事件（用 DISABLE 和 ENABLE 关键字）

ALTER EVENT yearly_delete_stale_audit_row DISABLE/ENABLE

【十一章】事务和并发

事务

事务（trasaction）是完成一个完整事件的一系列SQL语句。这一组SQL语句是一条船上的蚂蚱，要不然都成功，要不然都失败，如果一部分执行成功一部分执行失败那成功的那一部分就会复原（revert）以保持数据的一致性。
例子1
银行交易：你给朋友转账包含从你账户转出和往他账户转入两个步骤，两步必须同时成功，如果转出成功但转入不成功则转出的金额会返还
例子2
订单记录：之前学过向父子表插入分级（层）/耦合数据，一个订单 (order) 记录对应多个订单项目 (order_items) 记录，如果在记录一个新订单时，订单记录录入成功但对应的订单项目记录录一半系统就崩了，那这个订单的信息就是不完整的，我们的数据库将失去数据一致性
ACID 特性

事务有四大特性，总结为 ACID（刚好是英文单词“酸的”）：

Atomicity 原子性，即整体性，不可拆分行（unbreakable），所有语句必须都执行成功事务才算完成，否则只要有语句执行失败，已执行的语句也会被复原
Consistency 一致性，指的是通过事务我们的数据库将永远保持一致性状态，比如不会出现没有完整订单项目的订单
Isolation 隔离性，指事务间是相互隔离互不影响的，尤其是需要访问相同数据时。具体而言，如果多个事务要修改相同数据，该数据会被锁定，每次只能被一个事务有权修改，其它事务必须等这个事务执行结束后才能进行
Durability 持久性，指的是一旦事务执行完毕，这种修改就是永久的，任何停电或系统死机都不会影响这种数据修改

创建事务

准备：先用 create-databases.sql 恢复一下数据库
案例：创建一个事务来储存订单及其订单项目（为了简化，这个订单只有一个项目）
用 START TRANSACTION 来开始创建事务，用 COMMIT 来关闭事务

USE sql_store;

START TRANSACTION;

INSERT INTO orders (customer_id, order_date, status)
VALUES (1, '2019-01-01', 1);
-- 只需明确声明并插入这三个非自增必须（不可为空）字段

INSERT INTO order_items 
-- 所有字段都是必须的，就不必申明了
VALUES (last_insert_id(), 1, 2, 3);

COMMIT;

执行，会看到最新的订单和订单项目记录

当 MySQL 看到上面这样的事务语句组，会把所有这些更改写入数据库，如果有任何一个更改失败，会自动撤销之前的修改，这种情况被称为事务被退回（回滚）（is rolled back）

为了模拟退回的情况，可以用 Ctrl + Enter 逐条执行语句，执行一半，即录入了订单但还没录入订单项目时断开连接（模拟客户端或服务器崩溃或断网之类的情况），重连后会发现订单和订单项目都没有录入

手动退回：
多数时候是用上面的 START TRANSACTION + COMMIT 来创建事务，但当我们想先进行一下事务里语句的测试/错误检查并因此想在执行结束后手动退回时，可以将最后的 COMMIT; 换成 ROLLBACK;，这会退回事务并撤销所有的更改
autocommit ：
？我们执行的每一个语句（可以是增删查改 SELECT、INSERT、UPDATE 或 DELETE 语句），就算没有 START TRANSACTION + COMMIT，也都会被 MySQL 包装（wrap）成事务并在没有错误的前提下自动提交，这个过程由一个叫做 autocommit 的系统变量控制，默认开启
因为有 autocommit 的存在，当事务只有一个语句时，用不用 START TRANSACTION + COMMIT 都一样，但要将多个语句作为一个事务时就必须要加 START TRANSACTION + COMMIT 来手动包装了

SHOW VARIABLES LIKE 'autocommit';

小结：

START TRANSACTION;

……;

COMMIT / ROLLBACK;

SHOW VARIABLES LIKE 'autocommit';

并发和锁定

并发：之前都只有一个用户访问数据，现实中常出现多个用户访问相同数据的情况，这被称为“并发”（concurrency），当一个用户企图修改另一个用户正在检索或修改的数据时，并发会成为一个问题
导航：本节介绍默认情况下MySQL是如何处理并发问题的，接下来几节课将介绍如何最小化并发问题
开启两个会话：
逐行执行：
案例：假设要通过如下事务语句给1号顾客的积分增加10分

USE sql_store;
START TRANSACTION;
UPDATE customers
SET points = points + 10
WHERE customer_id = 1;
COMMIT;

现在有两个会话（注意是两个链接（connection），而不是同一个会话下的两个SQL标签，这两个链接相当于是在模拟两个用户）都要执行这段语句，用 command+Enter 逐句执行，当第一个执行到UPDATE 而还没有 COMMIT 提交时，转到第二个会话，执行到UPDATE语句时会出现旋转指针表示在等待执行（若等的时间太久会超时而放弃执行），这时跳回第一个对话 COMMIT 提交，第二个会话的 UDDATE 才不再转圈而得以执行，最后将第二段对话的事务也COMMIT提交，此时刷新顾客表会发现1号顾客的积分多了20分
在这里插入图片描述

上锁：
所以，可以看到，当一个事务修改一行或多行时，会给这些行上锁，这些锁会阻止其他事务修改这些行，直到前一个事务完成（不管是提交还是退回）为止，由于上述MySQL默认状态下的锁定行为，多数时候不需要担心并发问题，但在一些特殊情况下，默认行为不足以满足你应用里的特定场景，这时你可以修改默认行为，这正是我们接下要学习的

并发问题

现在已经知道什么是并发了，我们来看看它带来的常见问题：

Lost Updates 丢失更新
例如，当事务A要更新john的所在州而事务B要更新john的积分时，若两个事务都读取了john的记录，在A跟新了州且尚未提交时，B更新了积分，那后执行的B的更新会覆盖先执行的A的更新，州的更新将会丢失。
解决方法就是前面说的锁定机制，锁定会防止多个事务同时更新同一个数据，必须一个完成的再执行另一个
Dirty Reads 脏读
例如，事务A将某顾客的积分从10分增加为20分，但在提交前就被事务B读取了，事务B按照这个尚未提交的顾客积分确定了折扣数额，可之后事务A被退回了，所以该顾客的积分其实仍然是10分，因此事务B等于是读取了一个数据库中从未提交的数据并以此做决定，这被称作为脏读
解决办法是设定事务的隔离等级，例如让一个事务无法看见其它事务尚未提交的更新数据，这个下节课会学习。标准SQL有四个隔离等级，比如，我们可以把事务B设为 READ COMMITED 等级，它将只能读取提交后的数据
积分提交完之后，B事务依此做决定，如果之后积分再修改，这就不是我们考虑的问题了，我们只需要保证B事务读取的是提交后的数据就行了
Non-repeating Reads 不可重复读取（或 Inconsistent Read 不一致读取）
上面的隔离能保证只读取提交过的数据，但有时会发生一个事务读取同一个数据两次但两次结果不一致的情况
例如，事务A的语句里需要读取两次某顾客的积分数据，读取第一次时是10分，此时事务B把该积分更新为0分并提交，然后事务A第二次读取积分为0分，这就发生了不可重复读取或不一致读取
一种说法是，我们应该总是依照最新的数据做决定，所以这不是个问题。在商务场景中，我们一般不用担心这个问题
另一种说法是，我们应该保持数据一致性，以事务A在开始执行时的数据初始状态为依据来做决定，如果这是我们想要的话，就要增加事务A的隔离等级，让它在执行过程中看不见其它事务的数据更改（即便是提交过的），SQL有个标准隔离等级叫 Repeatable Read 可重复读取，可以保证读取的数据是可重复和一致的，无论过程中其它事务对数据做了何种更改，读取到的都是数据的初始状态
Phantom Reads 幻读（n. 幽灵；幻影，幻觉）
最后一个并发问题是幻读
例如，事务A要查询所有积分超过10的顾客并向他们发送带折扣码的E-mail，查询后执行结束前，事务B更新了（可能是增删改）数据，然后多了一个满足条件的顾客，事务A执行结束后就会有这么一个满足条件的顾客没有收到折扣码，这就是幻读，Phantom是幽灵的意思，这种突然出现的数据就像幽灵一样，我们在查询中错过了它因为它是在我们查询语句后才更新的
解决办法取决于想解决的商业问题具体是什么样的以及把这个顾客包括进事务中有多重要
我们总可以再次执行事务A来让这顾客包含进去
但如果确保我们总是包含了最新的所有满足条件的顾客是至关重要的，我们就要保证查询过程中没有任何其他可能影响查询结果的事务在进行，为此，我们建立另一个隔离等级叫 Serializable 序列化，它让事务能够知晓是否有其它事务正在进行可能影响查询结果的数据更改，并会等待这些事务执行完毕后再执行，这是最高的隔离等级，为我们提供了最高的操作确定性。但 Serializable 序列化等级是有代价的，当用户和并发增加时，等待的时间会变长，系统会变慢，所以这个隔离等级会影响性能和可扩展性，出于这个原因，我们只要在避免幻读确实必要的情形下才使用这个隔离等级
导航
这里只是先总体介绍，之后的课程会详细讲解每个并发问题以及如何用相应的隔离等级来解决它们

事务隔离级别

总结：并发问题与隔离等级
四个并发问题：

Lost Updates 丢失更新：两个事务更新同一行，最后提交的事务将覆盖先前所做的更改
Dirty Reads 脏读：读取了未提交的数据
Non-repeating Reads 不可重复读取（或 Inconsistent Read 不一致读取）：在事务中读取了相同的数据两次，但得到了不同的结果
Phantom Reads 幻读：在查询中缺失了一行或多行，因为另一个事务正在修改数据而我们没有意识到事务的修改，我们就像遇见了鬼或者幽灵

为了解决这些问题，我们有四个标准的事务隔离等级：

Read Uncommitted 读取未提交：无法解决任何一个问题，因为事务间并没有任何隔离，他们甚至可以读取彼此未提交的更改
Read Committed 读取已提交：给予事务一定的隔离，这样我们只能读取已提交的数据，这防止了Dirty Reads 脏读，但在这个级别下，事务仍可能读取同个内容两次而得到不同的结果，因为另一个事务可能在两次读取之间更新并提交了数据，也就是它不能防止Non-repeating Reads 不可重复读取（或 Inconsistent Read 不一致读取）
Repeatable Read 可重复读取：在这一级别下，我们可以确信不同的读取会返回相同的结果，即便数据在这期间被更改和提交
Serializable 序列化：可以防止以上所有问题，这一级别还能防止幻读，如果数据在我们执行过程中改变了，我们的事务会等待以获取最新的数据，但这很明显会给服务器增加负担，因为管理等待的事务需要消耗额外的储存和CPU资源

并发问题 VS 性能和可扩展性：
更低的隔离级别更容易并发，会有更多用户能在相同时间接触到相同数据，但也因此会有更多的并发问题，另一方面因为用以隔离的锁定更少，性能会更高
相反，更高的隔离等级限制了并发并减少了并发问题，但代价是性能和可扩展性的降低，因为我们需要更多的锁定和资源
MySQL的默认等级是 Repeatable Read 可重复读取，它可以防止除幻读外的所有并发问题并且比序列化更快，多数情况下应该保持这个默认等级。
如果对于某个特定的事务，防止幻读至关重要，可以改为 Serializable 序列化
对于某些对数据一致性要求不高的批量报告或者对于数据很少更新的情况，同时又想获得更好性能时，可考虑前两种等级
总的来说，一般保持默认隔离等级，只在特别需要时才做改变
设定隔离等级的方法

读取隔离等级

SHOW VARIABLES LIKE 'transaction_isolation';

显示默认隔离等级为 ‘REPEATABLE READ’
改变隔离等级：

SET [SESSION]/[GLOBAL] TRANSACTION ISOLATION LEVEL SERIALIZABLE;

默认设定的是下一次事务的隔离等级，加上 SESSION 就是设置本次会话（链接）之后所有事务的隔离等级，加上 GLOBAL 就是设置之后所有对话的所有事务的隔离等级

如果你是个应用开发人员，你的应用内有一个功能或函数可以链接数据库来执行某一事务（可能是利用对象关系映射或是直接连接MySQL），你就可以连接数据库，用 SESSION 关键词设置本次链接的事务的隔离等级，然后执行事务，最后断开连接，这样数据库的其它事务就不会受影响

导航：接下来讲逐一讲解各个隔离级别

读取未提交隔离级别

主要通过模拟脏读来表明 Read Uncommitted（读取未提交）是最低的隔离等级并会遇到所有并发问题
建立链接1和链接2，模拟用户1和用户2，分别执行如下语句：

链接1：

查询顾客1的积分，用于之后的商业决策（如确定折扣等级）

注意里面的 SELECT 查询语句虽然没被 START TRANSACTION + COMMIT 包裹，但由于 autucommit，MySQL会把执行的每一条没错误的语句包装在事务中并自动提交，所以这个查询语句也是一个事务，隔离等级为上一句设定的 READ UNCOMMITTED（读取未提交）

USE sql_store;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT points
FROM customers
WHERE customer_id = 1

链接2：

建立事务，将顾客1的积分（由原本的2293）改为20

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

模拟过程:

链接1将下一次事务（感觉是针对本对话的下一次事务）的隔离等级设定为 READ UNCOMMITTED 读取未提交

→ 链接2执行了更新但尚未提交

→ 链接1执行了查询，得到结果为尚未提交的数据，即查询结果为20分而非原本的2293分

→ 链接2的更新事务被中断退回（可能是手动退回也可能是因故障中断）

这样我们的对话1就使用了一个数据库中从未存在过的值，这就是脏读问题，总之，READ UNCOMMITTED 读取未提交是最低的隔离等级，在这一级别我们会遇到所有的并发问题、

读取已提交隔离级别

Read Committed 读取已提交等级只会读取别人已提交的数据，所以不会发生脏读，但因为能够读取到执行过程中别人已提交的更改，所以还是会发生不可重复读取（不一致读取）的问题
案例1：不会发生脏读
就是把上一节链接1的设置隔离级别语句改为 READ COMMITTED 读取已提交等级，就会发现链接1不会读取到链接2未提交的更改，只有当改为20分的事务提交以后才能被链接1的查询语句读取到
案例2：可能会发生不可重复读取（不一致读取）

虽然不会存在脏读，但会出现其他的并发问题，如 Non-repeating Reads 不可重复读取，即在一个事务中你会两次读取相同的内容，但每次都得到不同的值

为模拟该问题，将顾客1的分数还原为2293，将上面的连接1里的语句变为两次相同的查询（查询1号顾客的积分），连接2里的UPDATE语句不变，还是将1号顾客的积分（由原本的2293）更改为20

USE sql_store;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT points FROM customers WHERE customer_id = 1;
SELECT points FROM customers WHERE customer_id = 1;
COMMIT;

注意虽然案例1里已经执行过一次 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 但这里还是要再执行一次，因为该语句是设定（本对话内）【下一次（next）】事务的隔离等级，如果这里不执行，事务就会恢复为MySQL默认隔离等级，即 Repeatable Read 可重复读取

还有因为这里事务里有两个语句，所以必须手动添加 START TRANSACTION + COMMIT 包装成一个事务，否则autocommit会把它们分别包装形成两个事务

模拟过程：

再次设定隔离等级为 READ COMMITTED，启动事务，执行第一次查询，得到分数为2293

→ 执行链接2的 UPDATE 语句并提交

→ 再执行链接1的第二次查询，得到分数为20，同一个事务里的两次查询得到不同的结果，发生了 Non-repeating Reads 不可重复读取（或 Inconsistent Read 不一致读取）

重复读取隔离级别

在这一默认级别上，不仅只会读取已提交的更改，而且同一个事物内读取会始终保持一致性，但因为可能会忽视正在进行但未提交的可能影响查询结果的更改而漏掉一些结果，即发生幻读
之前说了MySQL默认等级正是 REPEATABLE READ（重复读取）而且MySQL默认会在执行事务内的增删改语句时锁定相关行，所以可以判断 REPEATABLE READ（重复读取）正是通过执行修改语句时锁定相关行来避免更新丢失问题的（不过执行查询语句时应该不是通过锁定而只是是通过记忆原始值来保证一致读取的，因为查询语句中途并不会阻止别人更改相关行）
案例1：不会发生不可重复读取（不一致读取）
此案例和上一个案例完全一样，只是把隔离等级的设定语句改为了 REPEATABLE READ 可重复读取，然后发现两次查询中途别人把积分从2293改为20不会影响两次查询的结果，都是初始状态的20分，不会发生不可重复读取（不一致读取）
案例2：可能发生幻读

但这一级别还是会发生幻读的问题，一个模拟情形如下：

用户1：查询在 ‘VA’ 州的顾客

USE sql_store;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM customers WHERE state = 'VA';
SELECT points FROM customers WHERE customer_id = 1;
COMMIT;

用户2：将1号顾客所在州更改为 ‘VA’

USE sql_store;
START TRANSACTION;
UPDATE customers
SET state = 'VA'
WHERE customer_id = 1;
COMMIT;

假设customer表中原原本只有2号顾客在维州（‘VA’）

→ 用户2现在正要将1号顾客也改为VA州，已执行UPDATE语句但还没有提交，所以这个更改技术上讲还在内存里
→ 此时用户1查询身处VA州的顾客，只会查到2号顾客
→ 用户2提交更改
→ 若1号用户未提交，再执行一次事务中的查询语句会还是只有2号顾客，因为在 REPEATABLE READ 可重复读取隔离级别，我们的读取会保持一致性
→ 若1号用户提交后再执行一次查询，会得到1号和2号两个顾客的结果，我们之前的查询遗漏了2号顾客，这被称作为幻读

简单讲就是在这一等级下1号用户的事务只顾读取当前已提交的数据，不能察觉现在正在进行但还未提交的可能对查询结果造成影响的更改，导致遗漏这些新的“幽灵”结果

下节课讲如何用序列化隔离级别来解决幻读问题

序列化隔离级别

案例：

和上面那个案例一摸一样，只是把用户1事务的隔离等级设置为 SERIALIZABLE 序列化，模拟场景如下：

→ 用户2现在正要将1号顾客也改为VA州，已执行UPDATE语句但还没有提交，所以这个更改技术上讲还在内存里
→ 此时用户1查询身处VA州的顾客，会察觉到用户2的事务正在进行，因而会出现旋转指针等待用户2的完成
→ 用户2提交更改
→ 用户1的查询语句执行并返回最新结果：顾客1和顾客2

小结：
SERIALIZABLE（序列化）是最高隔离等级，它等于是把系统变成了一个单用户系统，事务只能一个接一个依次进行，所以所有并发问题（更新丢失、脏读、不一致读取、幻读）都从从根本上解决了，但用户和事务越多等待时间就会越漫长，所以，只对那些避免幻读至关重要的事务使用这个隔离等级。默认的可重复读取等级对大多数场景都有效，最好保持这个默认等级，除非你知道你在干什么（Stick to that, unless you know what you are doing）

死锁

不管什么隔离等级，事务里的增删改语句都会锁定相关行，如果两个同时在进行的事务分别锁定了对方下一步要使用的行，就会发生死锁，死锁不能完全避免但有一些方法能减少其发生的可能性
案例：

用户1：将1号顾客的州改为’VA’，再将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：和用户1完全相同的两次更改，只是顺序颠倒

USE sql_store;
START TRANSACTION;
UPDATE orders SET status = 1 WHERE order_id = 1;
UPDATE customers SET state = 'VA' WHERE customer_id = 1;
COMMIT;

模拟场景：
用户1和2均执行完各自的第一个更改
→ 用户2执行第二个更改，出现旋转指针
→ 用户1执行第二个更改，出现死锁，报错：Error Code: 1213. Deadlock found ……
缓解方法：

死锁如果只是偶尔发生一般不是什么问题，重新尝试或提醒用户重新尝试即可，死锁不可能完全避免，但有一些方法可以最小化其发生的概率：

注意语句顺序：如果检测到两个事务总是发生死锁，检查它们的代码，这些事务可能是储存过程的一部分，看一下事务里的语句顺序，如果这些事务以相反的顺序更新记录，就很可能出现死锁，为了减少死锁，我们在更新多条记录时可以遵循相同的顺序
尽量让你的事务小一些，持续时间短一些，这样就不太容易和其他事务相冲突
如果你的事务要操作非常大的表，运行时间可能会很长，冲突的风险就会很高，看看能不能让这样的事物避开高峰期运行，以避开大量活跃用户

5pace

关注

0
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
SQL进阶教程 | 史上最易懂SQL教程 5小时零基础成长SQL大师（4）

【第十章】触发器和事件触发器触发器是在插入、更新或删除语句前后自动执行的一段SQL代码（A block of SQL code that automatically gets executed before or after an insert, update or delete statement）通常我们使用触发器来保持数据的一致性创建触发器的语法要点：命名三要素，触发条件语句和触发频率语句，主体中 OLD/NEW 的使用案例：在 sql_invoicing 库中，发票表中同一个发票记录可以
复制链接

扫一扫