事务四大特性

以“转账”为例，实际上是两条UPDATE语句：

UPDATE User SET Balance = Balance + 150 WHERE Id = 1
UPDATE User SET Balance = Balance - 150 WHERE Id = 6

一旦中间出现故障（比如网络故障/系统断电……），就会造成 Id=1 的用户余额增加而 Id=3 的用户没有减少的情况。

解决的办法就是将上述两条语句放到一个事务中。

BEGIN TRANSACTION        -- 开始事务
UPDATE User SET Balance = Balance + 199 WHERE Id = 1
UPDATE User SET Balance = Balance - 199 WHERE Id = 6       -- 因违法CHECK(Balance>0)约束而报错
COMMIT TRANSACTION       -- 提交事务

说明事务的：（记忆小窍门：ACID）

1. 原子性（Atomicity）：事务作为一个整体不可再分。事务中的所有内容，要完全部完成，要么全部不完成；不能一部分完成一部分没完成。
2. 一致性（Consistency）：事务执行前后，数据的状态都要保证一致（正确，符合逻辑和预期）。
3. 隔离性（Isolation）：正在执行的事务不能被其他事务干扰。
4. 持久性（Durablity）：一旦完成（无论COMMIT还是ROLLBACK），改变就是永久性的。COMMIT和ROLLBACK

实际上，各种关系数据库对上述1、2、4条要求都执行得比较严格，但对于3隔离性因为并发（复习）的原因，不得不做出妥协。

为了提高数据库性能，就要允许数据库被并发的访问，就不能严格遵守事务隔离性要求，所以数据库是允许在一个事务的进行过程中，有另一个事务同时进行的。

数据库一般也允许用户自行设置数据库事务的

隔离级别

#冲击12K，^_^

如下表所示：

隔离级别	解决的问题
读未提交	丢失的更新
读已提交	脏读
可重复读	不可重复读
序列化/串行化	幻影读

从上到下：

• 事务隔离级别：越来越高
  
• 隔离性越来越好，执行效率越来越低
• 下一个隔离级别就能解决上一个隔离级别的问题

演示准备

两个连接窗口，每个窗口都可以启动事务。

说明：一个窗口（session/query）中的多次操作不会形成并发。

在当前连接中：

• 设置隔离级别：

  SET TRANSACTION ISOLATION LEVEL isolation_level

• 查看的事务隔离级别：

  T-SQL	mysql
  DBCC USEROPTIONS	SELECT @@transaction_isolation;

另外，任何单条SQL语句，都自动被包裹在事务中执行：

UPDATE Student SET Age = 66 WHERE Id = 1; 
-- 等于
BEGIN TRAN
UPDATE Student SET Age = 66 WHERE Id = 1; 
COMMIT

依次演示

先设置事务隔离级别为：

READ UNCOMMITTED

保证不会出现：丢失的更新（后更新会覆盖之前的更新）

1. 事务1中更新一条数据，未提交
2. 事务2试图更新该条数据：无法更新，需要等待，直到事务1完成（提交/回滚）

但是，会出现：脏读（Dirty Read）

1. 重复1
2. 将事务2设置为READ UNCOMMITTED，然后在事务2中查询该条数据：能读取被1修改过后的数据
3. 事务1回滚，事务2读到的是“脏”（未提交的）数据

将事务2的隔离级别设置成：

READ COMMITTED

就可以保证不会出现：脏读。事务2需等待事务1完成之后才能获得查询结果。

但是会出现：不可重复读 （Unrepeatable Read）

1. 在事务1中查询一条数据，未提交
2. 事务2修改该条数据
3. 在事务1中再次查询该数据，得到和1不一样的结果

#理解#：在同一次事务中，针对同一（行）数据是查询，（按照事务隔离性的要求）应该得到相同的结果。

将事务1的的隔离级别设置为：

REPEATABLE READ

就可以保证不会出现：不可重复读。上述第2步操作无法完成，需要等待事务1完成才能继续进行。

但是会出现：幻影读（Ghost Read）

1. 事务1中更新全表数据，未提交
2. 事务2插入一条数据
3. 事务1提交：发现好像有一条数据未更改？（实际上是2插入的数据）

将事务1的隔离级别给成：

SERIALIZABLE

就可以保证不会出现幻影读。事务2要一直等待事务1完成之后才能执行。

锁实现

#冲击18K，^_^

锁，可以视为对数据的一种标记。大致来说，

• 在事务执行前，先检查数据有无被加锁
• 一旦开始读写数据，就加上相应的锁，防止其他事务执行

这样就能实现事务隔离。

上述事务隔离级别可以通过对数据：

• 加不同种类的锁
• 设定不同的加锁/解锁时间
• 锁定不同范围的数据

来实现。

强烈建议：在学习之前尽可能的熟练事务隔离级别。

共享和排他锁

共享（Shared）锁：可用于只读的SELECT语句，所以又称之“读锁”。被加共享锁的数据，不影响其他事务读取。

排他（Exclusive）锁：用于INSERT/UPDATE/DELET语句（以下简称“写数据”），所以又称之“写锁”。

1. 被加锁的数据，无法再被加锁，
2. 要操作的数据，哪怕是只有一个共享锁，也无法再加排他锁

所以（PPT演示）：

1. 写数据前总是（“能+会”）加排他锁，就可以避免：丢失的更新
2. 读数据不用加共享锁，就可以实现：读未提交
3. 读数据要加共享锁，就可以实现：读已提交
4. 一旦读到数据（SELECT执行完成）就解锁，会造成：不可重复读；但直到整个显式事务执行完成才解锁，就可以避免，实现：可重复读。

粒度和范围

我们可以把锁加在行上，也可以加在表上，一个锁能“控制”的数据单位，就被称之锁的“粒度”。

一次事务要锁多少数据，3行5行，1张表3张表，这是锁的“范围”。

粒度是数据库锁机制可以决定的，范围是SQL语句（比如WHERE条件）决定的。

@想一想@：粒度是大好，还是小好？

粒度小：并发性更好。比如“锁3行”，肯定比“锁3张表”影响的数据更少，其他事务可以操作的数据更多。

但如果要锁的粒度太小，同样范围，用表锁只需要3个锁，用行锁可能就要几千几万个，加锁解锁性能消耗更多。

#体会#：没有银弹。凡是选择，必有代价……

把加锁的范围扩大到所有相关表，就可以消除幻影读。

更新锁

带WHERE子句的UPDATE语句，也会涉及到查询。

@想一想@：这里的查询，加什么锁？

如果使用S锁，就很容易形成死锁：

1. 事务1查询到某数据，加S锁，未提交
2. 事务2也查询到该数据，也加S锁，未提交
3. 事务1试图更新该数据，加X锁，不行，因为上面已有事务2的S锁，只能等待事务2解锁
4. 事务2遇到和步骤3一样的问题，也只能等事务1解锁
5. 而事务1在等着事务2，事务2又等着事务1
6. ……

当然加X锁可以避免死锁，但性能不高呀！

所以需要引入更新（Update）锁：

• 和S锁兼容（能加上，不影响SELECT语句），提高和SELECT的并发性
• 和U锁/X锁不兼容

在查找过程中使用的就是U锁；一旦查找到需要的数据，U锁就转变成X锁。

死锁

死锁出现条件：

• 双方均需要对方释放锁为前提，才能继续进行；
• 而要释放锁，又必须要事务继续进行。
  

死锁不是等待。而是彻底的锁死在那里了！是无论怎么等待，都没办法活过来的那种……^_^

示范：一个最简单的死锁（注意WHERE条件要使用主键，@想一想@：为什么？）

1. 事务1中，执行：

   BEGIN TRAN
   UPDATE Student SET Score = Score + 10 WHERE Id = 1;

2. 事务2中，执行

   BEGIN TRAN
   UPDATE BangMoney SET Balance -= 10 WHERE [Name] = N'陈元';

3. 事务1中，继续执行：

   UPDATE BangMoney SET Balance -= 10 WHERE [Name] = N'陈元';

   提示：执行中……，需要等着了
4. 事务2中，继续执行：

   UPDATE BangMoney SET Balance += 10 WHERE [Name] = N'幸龙泰';

只能依靠SQL SERVER来自行裁判其中一个事务作为victim，进行回滚。

作业

1. 给用户（User）添加一列帮帮币（BMoney INT），当用户发布求助（Problem）时会设置悬赏（Reward），于是作者的帮帮币也会相应减少，但作者的帮帮币有不能少于0的约束：请使用事务完成上述SQL语句，提交或回滚。
2. 利用PPT和SQL语句演示各种并发问题，解释/显示事务隔离级别和锁如何应对上述问题。
3. 思考题：游戏买装备。比如只有100个游戏币，装备30个游戏币一套。恶意用户利用软件，瞬间向服务器发送无数条购买装备的请求。结果就真的到手了n（>3）多装备，这可能是怎么一回事呢？

源栈学历

在当前系列 SQL 中继续学习：

多快好省！前端后端，线上线下，名师精讲

• 先学习，后付费；
• 不满意，不要钱。
• 编程培训班，我就选源栈

更多了解 加：