事务不是与当前连接无关联就是已完成_跟飞哥学编程：SQL入门-：事务隔离级别和锁...

如果数据库严格遵守事务隔离性要求，作用于相同数据事务就只能一个接一个的排队执行，就像这样：

事务从左到右依次执行

但实际上，为了提高数据库性能，我们可以让多个事务能尽可能的同时执行，这就是所谓的

并发

就像这样：

①②③之间的每个时间段里都有多个事务“一起”执行

允许并发，就意味着当一个事务正在操作某个数据的时候，另一个事务也能对这个数据进行操作。这必然就带来所谓“并发冲突”的问题。比如：

• 第一个事务，我用UPDATE将Student中陈元的成绩改成了89；
• 正当我要提交事务的时候，另一个事务将陈元的成绩改成了65；
• 然后我提交第一个事务，数据库怎么办？

这被称之为“丢失的更新”（常规无法演示）。

为了解决（实际上是控制）并发冲突造成的问题，数据库引入了

事务隔离级别

我们可以运行：DBCC USEROPTIONS，来查看当前连接的隔离级别：

可以看到，当前的隔离级别（isolation level）是read committed（中文称之为提交读，或读已提交）。意思就是：一个事务只能够读其它事务已经提交了的结果；或者说，一个事物不能够读取正在由另一个事务处理（增删改，不包括查）的数据。我们可以用以下SQL演示：

BEGIN TRANSACTION       -- 需要显式的开启事务
UPDATE BangMoney SET Balance += 10  WHERE [Name] = N'幸龙泰';
-- 注意不要COMMIT或ROLLBACK,让事务保持

New Query新开一个窗口，模拟一个新的数据库连接（因为事务是基于连接的，不可能在一个连接中有多个事务），执行SQL语句：

SELECT * FROM BangMoney WHERE [Name] = N'幸龙泰';

无法得到查询结果，界面一直显示：

实际上就是在等，等着第一个事务执行完成。我们ROLLBACK或COMMIT第一个事务，就能得到查询结果了：这就是事务隔离级别的效果。

如果想要在第一个事务执行的过程中，第二个事务立即就能得到查询结果，就需要将第二个使用SELECT的事务降到隔离级别READ UNCOMMITTED（读未提交，或者未提交读），SQL语句如下：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

未提交读肯定会更快，但带来的问题就是：

脏读

在第二个事务读取到数据之后，如果第一个事务进行了回滚，那么第二个事务读取到的数据就是数据库中不存在的。

读已提交 可以解决脏读的问题，但是无法解决下一个问题：

不可重复读

即在同一个事务中，进行两（多）次相同的查询，会得到不同的结果！在第一个事务（隔离级别READ COMMITTED）中进行第一次查找，SQL语句如下：

BEGIN TRANSACTION 
SELECT * FROM BangMoney WHERE [Name] = N'幸龙泰';  --第一次查找

第一次查询结果为10。然后在第二个事务中，修改该行数据：

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

再回到第一个事务，进行第二次查找：

SELECT * FROM BangMoney WHERE [Name] = N'幸龙泰';

查询结果变成了20 —— 这就是不可重复读。要避免这种现象，需要将事务隔离级别提升到REPEATABLE READ（可重复读）。

在第一个SELECT事务中，执行SQL:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

重复上述过程，当在第二个事务中执行UPDATE的时候，就会一直等待，知道第一个事务结束。但是，可重复读还无法解决下一个问题：

幻影读

我们直接上代码，在第一个事务（隔离级别REPEATABLE READ）中执行：

BEGIN TRANSACTION       -- 需要显式的开启事务
UPDATE BangMoney SET Balance = 10  --注意没有条件，更改的是整张表

然后在第二个事务中插入一条数据：

INSERT BangMoney VALUES(N'Ghost', 100);

回答第一个事务，用SELECT查询整张表，得到如下结果：

不是整张表所有Balance都为10么？哪里冒出来的一个Ghost，而且值为100？对于第一个事务而言，这一行数据就像一个“幻影（幽灵）”一样突然出现了：这就是 幻影读 名称的由来。

要想终结幻影读，需要将事务隔离级别提升到SERIALIZABLE（序列化/串行化） ……

演示：

那事务的隔离级别是如何实现的呢？它利用的是：

锁机制

锁，可以理解为一种“标注”：表明数据被事务使用……

有了锁，在执行事务中的SQL语句时，可以

1. 检查数据上有没有（其他事务加的）锁，以确定当前事务的行为，比如：等待，或
2. 加锁：以提示其他事务该数据正在被当前事务操作
3. 释放锁：操作完成之后删除/消除之前加上的锁

锁可以简单的“排他”，只要数据上有锁，就不容许其他事务操作。但是，这样显然不利于性能优化。比如对同一数据两次只读不写的事务，没有必要互相等待。

所以数据库通常会将锁进行分类，然后在不同种类的锁之间建立“兼容”关系……

最基本的锁的类型有：

共享（Shared）锁（我们用S表示）：用于只读的SELECT语句，所以又称之为读锁。共享锁上可以加共享锁（说明：事务的执行通常要先加锁——PPT演示），我们说S锁兼容S锁。

排他（Exclusive）锁（又称独占锁，我们用X表示）：用于INSERT/UPDATE/DELETE语句，所以又被称之为写锁。我们说X锁不兼容任何锁，意思是排他锁上不能加任何锁，任何锁上也不能加排他锁。

有了这两种锁，就能实现：

• 同一个数据可以被多个事务同时读取（之前的操作演示，其中一个事务总是“写”）
• 修改某个数据的时候，该数据不能被读取
• 读取某数据的时候，该数据不能被修改

但是，请注意我们在删改的时候，通常首先需要使用WHERE子句进行查找。这种查找过程也需要加X锁么？可以，但这样效率不高，因为涉及的数据很可能是整张表；

切割：UPDATE/DELETE 和 WHERE

那能不能也使用和SELECT一样的S锁呢？不行，因为这样很容易形成死锁：

比如有两个UPDATE事务并发地更新同一数据。只要运行的过程是这样的：

1. 第一个（蓝色）事务首先进行WHERE子句查找，于是在数据上加S锁
2. 在第一个事务找到数据但还没有进行更新之前，第二个（黑色）事务开始执行
3. 黑色事务也要进行WHERE子句查找，因为S锁和S锁兼容，于是它也在数据上加了S锁
4. 接着蓝色事务想要进行更新操作，需要加一个X锁，但是因为黑色事务已经加了一个S锁，S锁和X锁不兼容，所以只能等待黑色事务执行完成……
5. 但这时候黑色事务也无法继续执行了（原因同4），它要继续更改操作就得等着蓝色事务执行完成……

这样两个事务，我执行需要等你执行完，你执行完又需要我执行完，我执行完却需要你执行完，你执行完又需要我执行完……这样就形成了一个“死循环”，这就被称之死锁。

为了解决这个问题，SQL Server引入了

更新（UPDATE）锁

我们用U表示，用于在UPDATE/DELET之前的查找过程，仅在WHERE子句的查找过程中使用，一旦数据被找到，U锁就立即转变成X锁。

U锁和U锁不能兼容，但U锁和S锁可以兼容。所以就可以这样：

避免了两个“写”事务时的死锁

一个“写”事务一个“读”事务也能并发执行

@想一想@：为什么UPDATE比SELECT更慢？

为了更好的性能优化，SQL Server还引入了其他一些种类的锁，此处不再详述。有兴趣的同学可以参考MSDN。

你可能注意到我们在上面只是笼统的讲了“数据”被加锁，那么这个数据指的一行呢还是一张表？或者若干行几张表？这就涉及到锁的

粒度

锁住的数据越少，我们就说锁的粒度越小；反之，粒度就越大。从小到大，我们学过的（复习），常用的锁粒度包括：

1. RID：堆中的单个行的行标识符。
2. KEY：索引键。
3. PAGE：数据库中的 8 KB 页。
4. EXTENT：区，一组连续的八页。
5. HoBT：堆或 B 树。
6. TABLE：包含所有数据和索引的整个表。
7. FILE：数据库文件。
8. DATABASE：整个数据库。

锁的粒度小（例如行），可以提高并发度，但如果锁定了许多行，每一行都需要一把锁，锁数量的增加会导致数据库的开销（CPU/内存资源消耗）更高。

锁的粒度大（例如表），会降低了并发度，但因为需要维护的锁较少，所以开销会更低。

所以，锁的粒度通常由SQL Server自动管理。绝大多数时候，不建议自行指定（确需指定使用表提示WITH）。另外，即使SQL中指定了锁的粒度，SQL Server都可以依据实际情况进行升级。

同样的原因，惯常的做法，都是使用 事务隔离级别 来间接的控制锁，而不要自行指定。

回头我们来捋一捋，这四个事务控制级别是如何使用锁的：

1. READ UNCOMMITTED：读取时不加共享锁，所以在其他事务写时也能同时读（可产生脏读）。
2. READ COMMITTED：读取时先申请共享锁，所以其他事务写时不能读（避免脏读）；但共享锁得到结果就立即释放，所以之后其他事务可以写（可产生不可重复读）。
3. REPEATABLE READ：共享锁一直到事务结束才释放（避免不可重复读）；但共享锁的范围仅局限于查找到的数据上（可产生幻影读）。
4. SERIALIZABLE：共享锁的范围扩展到所有可能影响到该事务的对象（避免了幻影读）

以上锁控制，变化的只有共享锁，排他锁始终是在事务结束后释放。

最后，虽然说U锁可以避免一部分的死锁，但死锁还是广泛存在且无法避免的。我们来看一个最简单的

死锁实例

首先确保表BangMoney上Name为主键（想想为什么？）

然后在事务一中，执行：

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

接着在事务二中，执行：

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

看起来两个SQL都能顺利执行。

继续，在事务一中执行：

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

这句SQL语句就不能执行了，提示executing，要等着了…… 注意，等待不是死锁，死锁是彻底的锁死在那里了！无论怎么等待，等到天荒地老，都没办法活过来的那种……^_^

所以再加一把火，完成死锁。在事务二中执行：

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

形成死锁之后，SQL SERVER会试着自行裁判其中一个事务作为victim，进行回滚，解除死锁。我们可以看到如下提示：

Msg 1205, Level 13, State 51, Line 4
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

整个死锁形成背后原因如下图所示：

@想一想@：提高事务的隔离级别能避免这种死锁么？

每日单词：

作业：

1. 利用SQL语句演示从READ COMMITTED到SERIALIZABLE可能存在和可以避免的各种问题，同时用PPT演示上述现象背后的锁机制。
2. 思考题：游戏买装备
   1. 比如某用户现有100个游戏币，某装备30个游戏币一套。
   2. 现该用户利用软件，瞬间向服务器发送无数条购买装备的请求。
   3. 结果就真的到手了n套装备（n>3），这是怎么一回事呢？

---

助（Problem），他的帮帮币（BMoney）也会相应减少，但他的帮帮币总额不能少于0分：请综合使用TRY...CATCH和事务完成上述需求。

---

感谢童鞋们的阅读！^_^

我就是：黑律师/包工头/创业狗/老码农……现在还是教书匠的大飞哥。

再次重申这个系列的目标是：

1）通俗易懂。2）实战为主。3）面向就业。

系列内容的完善需要你的反馈！

欢迎点赞和评论，以及加入我们的QQ交流群：326801052。