本篇主要内容如下：
「（2）一致性Consiitency」
「（3）隔离性Isolation」
「（4）持久性Durability」
二、锁
2.1 事务中的锁
（1）SQL Server使用锁来实现事务的隔离。
（2）事务获取锁这种控制资源，用于保护数据资源，防止其他事务对数据进行冲突的或不兼容的访问。
2.2 锁模式
「（1）排他锁」
如：当前事务获得了某个资源的排他锁，则其他事务不能获得该资源的任何其他类型的锁。其他事务获得了某个资源的任何其他类型的锁，则当前事务不能获得该资源的排他锁。
「（2）共享锁」
2.3 排他锁和共享锁的兼容性
（1）如果数据正在由一个事务进行修改，则其他事务既不能修改该数据，也不能读取（至少默认不能）该数据，直到第一个事务完成。
（2）如果数据正在由一个事务读取，则其他事务不能修改该数据（至少默认不能）。
2.4 可锁定的资源的类型
RID、KEY（行）、PAGE（页）、对象（例如表）、数据库、EXTENT（区）、分配单元（ALLOCATION_UNIT）、堆（HEAP）、以及B树（B-tree）。
「RID: 标识页上的特定行」
其中fileid标识包含页的文件， pagenumber标识包含行的页，rid标识页上的特定行。
fileid与sys.databases_files 目录视图中的file_id列相匹配
在查询视图sys.dm_tran_locks的时候有一行的resource_description列显示RID 是1:109:0 而status列显示wait,
表示第1个数据文件上的第109页上的第0行上的锁资源。
2.5 锁升级
SQL Server可以先获得细粒度的锁（例如行或页），在某些情况下将细粒度锁升级为更粗粒度的锁（例如，表）。
例如单个语句获得至少5000个锁，就会触发锁升级，如果由于锁冲突而导致无法升级锁，则SQL Server每当获取1250个新锁时出发锁升级。
三、阻塞
3.1 阻塞
当多个事务都需要对某一资源进行锁定时，默认情况下会发生阻塞。被阻塞的请求会一直等待，直到原来的事务释放相关的锁。锁定超时期限可以限制，这样就可以限制被阻塞的请求在超时之前要等待的时间。
3.2 排除阻塞问题
例子：
3.2.1 准备工作：
--先创建一张表Product作为测试。id为表的主键，price为product的价格
CREATE TABLE [dbo].[myProduct](
[id] [int] NOT NULL,
[price] [money] NOT NULL
) ON [PRIMARY]
GO
--插入一条数据，id=1,price=10
INSERT INTO [TSQLFundamentals2008].[dbo].[myProduct]([id],[price])VALUES(1,10)
在SQL Server中打开三个查询窗口Connection1、Connection2、Connection3，分别按顺序执行表格中的执行语句。
--Connection1
BEGIN TRAN
UPDATE dbo.myProduct SET price = price + 1 WHERE id=1
「结论1：」
为了更新id=1这一行数据，会话必须先获得一个排他锁。事务处于一直打开状态，没有提交，所以事务一直持有排他锁，直到事务提交并完成。
--Connection2
SELECT * FROM dbo.myProduct WHERE id=1
「结论2：」
事务为了读取数据，需要请求一个共享锁，但是这一行已经被其他会话持有的排他锁锁定，而且共享锁和排他锁不是兼容的，所以会话被阻塞，进入等待状态
--Connection3
SELECT request_session_id AS 会话id ,
resource_type AS 请求锁定的资源类型 ,
resource_description AS 描述 ,
request_mode AS 模式 ,
request_status AS 状态
FROM sys.dm_tran_locks
「结论3：」
「会话56：」（1）状态WAIT-等待锁
（2）正在等待第1个数据文件上的第109页上的第0行资源的共享锁
（3）持有第1个数据文件上的第109页资源的意向共享锁
（3）持有OBJECT资源，意向共享锁
（4）持有DATABASE资源，意向共享锁
「会话52：」
（1）状态WAIT-授予锁
（2）正在等待第1个数据文件上的第109页上的第0行资源的排他锁（3）持有第1个数据文件上的第109页资源的排他锁
（3）持有OBJECT资源，排他锁
（4）持有DATABASE资源，排他锁
演示与总结如下所示：
3.2.2 分析阻塞原因
3.2.2.1 sys.dm_tran_locks 视图
（1）该动态视图可以查询出哪些资源被哪个进程ID锁了
（2）查询出对资源授予或正在等待的锁模式
（3）查询出被锁定资源的类型
上面的查询语句3已经用到了这个视图，可以参考上图中的分析说明。
3.2.2.2 sys.dm_exec_connections 视图
（1）查询出该动态视图可以查询出进程相关的信息
（2）查询出最后一次发生读操作和写操作的时间last_read,last_write
（3）查询出进程执行的最后一个SQL批处理的二进制标记most_recent_sql_handle
SELECT session_id ,
connect_time ,
last_read ,
last_write ,
most_recent_sql_handle
FROM sys.dm_exec_connections
WHERE session_id IN ( 52, 56 )
「结论：」
「会话52：」
（1）connect_time连接时间：2016-06-07 07:09:41.103
（2）last_read最后一次读操作时间：2016-06-07 07:10:56.233
（3）last_write最后一次写操作时间：2016-06-07 07:10:57.873
（4）most_recent_sql_handle这是一个二进制标记，最后一个SQL批处理
「会话56：」
（1）状态WAIT-授予锁
（2）正在等待第1个数据文件上的第109页上的第0行资源的排他锁（3）持有第1个数据文件上的第109页资源的排他锁
（3）持有OBJECT资源，排他锁
（4）持有DATABASE资源，排他锁
演示与总结如下所示：
3.2.2.3 sys.dm_exec_sql_text 表函数
示例：
会话52：
执行的SQL语句：
BEGIN TRAN
UPDATE dbo.myProduct
SET price = price + 1
WHERE id = 1
会话56：
执行的SQL语句：
(@1 tinyint)
SELECT * FROM [dbo].[myProduct]
WHERE [id]=@1
（1）该函数可以将二进制标记most_recent_sql_handle作为参数，然后返回SQL代码。
（2）阻塞进程在不断地运行，所以在代码中看到的最后一个操作不一定是导致问题的语句。在本例中最后一条执行语句是导致阻塞的语句。
SELECT session_id ,
text
FROM sys.dm_exec_connections
CROSS APPLY sys.dm_exec_sql_text
(most_recent_sql_handle) AS ST
WHERE session_id IN ( 52, 56 )
演示与总结如下所示：
3.2.2.4 sys.dm_exec_sessions 视图
（1）会话建立的时间login_time
（2）特定于会话的客户端工作站名称host_name
（3）初始化会话的客户端程序的名称program_name
（4）会话所使用的SQL Server登录名login_name
（5）最近一次会话请求的开始时间last_request_start_time
（6）最近一次会话请求的完成时间last_request_end_time
SELECT * FROM sys.dm_exec_sessions
演示与总结如下所示：
3.2.2.5 sys.dm_exec_requests 视图
（1）识别出阻塞链涉及到的会话、争用的资源、被阻塞会话等待了多长时间
SELECT * FROM sys.dm_exec_sessions
「结论：」
「会话56：」
（1）被会话52阻塞，blocking_session_id = 52
（2）会话52的开始时间start_time
（3）状态挂起status = suspended
（4）挂起的命令command
演示与总结如下所示：
3.2.3 解决阻塞问题
3.2.3.1 Lock_TIMEOUT 选项
（1）设置会话等待锁释放的超时期限
（2）默认情况下会话不会设置等待锁释放的超时期限
（3）设置会话超时期限为5秒， SET Lock_TIMEOUT 5000
（4）锁定如果超时，不会引发事务回滚
（5）取消会话超时锁定的设置，SET LOCK_TIMEOUT -1
如果超时，将显示以下错误：
3.2.3.1 KILL
（1）杀掉会话52，KILL 52
（2）杀掉会话，会引起事务回滚，同时释放排他锁
四、隔离级别
4.1 基本概念
「（1）隔离级别用来做什么」
「（2）写操作」
「（3）读操作：」
4.2 隔离级别的分类
（1）未提交读 （READ UNCOMMITTED）
（2）已提交读（READ COMMITTED）（默认值）
（3）可重复读（REPEATABLE READ）
（4）可序列化（SERIALIZABLE）
（5）快照（SNAPSHOT）
（6）已经提交读快照（READ_COMMITTED_SNAPSHOT）
4.3 隔离级别的设置
「（1）设置整个会话的隔离级别」
SET TRANSACTION ISOLATION LEVEL <isolation name>;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
「（2）用表提示设置查询的隔离级别」
SELECT ... FROM <table> WITH (<isolation name>);
SELECT * FROM dbo.myProduct WITH (READCOMMITTED);
注意：
4.4 隔离级别的行为方式
4.4.1 未提交读 （READ UNCOMMITTED）
打开两个查询窗口，Connetion1,connection2
--阶段2
UPDATE myProduct
SET price = price + 1
WHERE id = 1;
SELECT id ,
price
FROM dbo.myProduct
WHERE id = 1;
--阶段3
UPDATE myProduct
SET price = price + 5
WHERE id = 1;
SELECT id ,
price
FROM dbo.myProduct
WHERE id = 1;
--阶段4
COMMIT TRAN
--在阶段2执行之后
SET TRAN ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN;
SELECT id ,
price
FROM dbo.myProduct
WHERE id = 1
COMMIT TRAN;
两个事务的流程图：
「大家可以看到事务B有两种结果，这就是“未提交读 （READ UNCOMMITTED）”隔离级别的含义：」
（1）读操作可以读取未提交的修改（也称为脏读）。
（2）读操作不会妨碍写操作请求排他锁，其他事务正在进行读操作时，写操作可以同时对这些数据进行修改。
（3）事务A进行了多次修改，事务B在不同阶段进行查询时可能会有不同的结果。
4.4.2 已提交读（READ COMMITTED）（默认值）
打开两个查询窗口，Connetion1,connection2
Step1: 执行Connection1的SQL 语句
UPDATE` `dbo.myProduct ``SET` `price = price + 1 ``WHERE` `id=1
SELECT` `* ``FROM` `dbo.myProduct ``WHERE` `id =1
Step2: 执行Connection2的SQL 语句
SET` `TRANSACTION` `ISOLATION` `LEVEL` `READ` `COMMITTED
SELECT` `* ``FROM` `dbo.myProduct ``WHERE` `id = 1
两个事务的流程图：
由于在当前隔离级别下，事务A的排他锁和事务B的共享锁存在冲突，所以事务B需要等待事务A释放排他锁后才能读取数据。
（1）必须获得共享锁才能进行读操作，其他事务如果对该资源持有排他锁，则共享锁必须等待排他锁释放。
（2）读操作不能读取未提交的修改，读操作读取到的数据是提交过的修改。
（3）读操作不会在事务持续期间内保留共享锁，其他事务可以在两个读操作之间更改数据资源，读操作因而可能每次得到不同的取值。这种现象称为“不可重复读”
4.4.3 可重复读（REPEATABLE READ）
打开两个查询窗口，Connetion1,connection2
Step1: 执行Connection1的SQL 语句
SET` `TRANSACTION` `ISOLATION` `LEVEL` `REPEATABLE` `READ
SELECT` `* ``FROM` `dbo.myProduct ``WHERE` `id = 1
Step2: 执行Connection2的SQL 语句
UPDATE` `dbo.myProduct ``SET` `price = price + 1 ``WHERE` `id=1
演示与总结如下所示：
两个事务的流程图：
由于在当前隔离级别下，事务A的共享锁和事务B请求的排他锁存在冲突，所以事务B需要等待事务A释放共享锁后才能修改数据。
**“可重复读 （REPEATABLE READ）”隔离级别的含义： **
（1）必须获得共享锁才能进行读操作，获得的共享锁将一直保持直到事务完成之止。
（2）在获得共享锁的事务完成之前，没有其他事务能够获得排他锁修改这一数据资源，这样可以保证实现可重复的读取。
（3）两个事务在第一次读操作之后都将保留它们获得的共享锁，所以任何一个事务都不能获得为了更新数据而需要的排他锁，这种情况将会导致死锁（deadlock），不过却避免了更新冲突。
4.4.4 可序列化（SERIALIZABLE）
打开两个查询窗口，Connetion1,connection2
Step1: 执行Connection1的SQL 语句
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SELECT * FROM dbo.myProduct WHERE id = 1
Step2: 执行Connection2的SQL 语句
INSERT` `INTO` `dbo.myProduct(id, price) ``VALUES` `(1, 20)
演示与总结如下所示：
两个事务的流程图：
由于在当前隔离级别下，事务B试图增加能够满足事务A的读操作的查询搜索条件的新行，所以事务A的共享锁和事务B请求的排他锁存在冲突，事务B需要等待事务A释放共享锁后才能插入数据。
「“可序列化（SERIALIZABLE）”隔离级别的含义：」
（1）必须获得共享锁才能进行读操作，获得的共享锁将一直保持直到事务完成之止。
（2）在获得共享锁的事务完成之前，没有其他事务能够获得排他锁修改这一数据资源，且当其他事务增加能够满足当前事务的读操作的查询搜索条件的新行时，其他事务将会被阻塞，直到当前事务完成然后释放共享锁，其他事务才能获得排他锁进行插入操作。
（3）事务中的读操作在任何情况下读取到的数据是一致的，不会出现幻影行（幻读）。
（4）范围锁：读操作锁定满足查询搜索条件范围的锁。
4.5 隔离级别总结
「脏读：」 读取未提交的更改。
「不可重复读：」 读操作不会在事务持续期间内保留共享锁，其他事务可以在两个读操作之间更改数据资源，读操作因而可能每次得到不同的取值。
「丢失更新：」 两个事务进行读操作，获得资源上的共享锁，读取完数据后，不再持有资源上的任何锁，两个事务都能更新这个值，最后进行更新的事务将会覆盖其他事务做的更改，导致其他事务更改的数据丢失。
「幻读：」 第一次和第二次读取到的数据行数不一致。
「范围锁：」 读操作锁定满足查询搜索条件范围的锁
五.死锁
死锁是指一种进程之间互相永久阻塞的状态，可能涉及两个或更多的进程。
打开两个查询窗口，Connetion1,connection2
Step1: 执行Connection1的SQL 语句
SET` `TRANSACTION` `ISOLATION` `LEVEL` `READ` `COMMITTED
BEGIN` `TRAN
UPDATE` `dbo.myProduct ``SET` `price = price + 1 ``WHERE` `id=1
SELECT` `* ``FROM` `dbo.myOrder ``WHERE` `id =1
Step2: 执行Connection2的SQL 语句
SET` `TRANSACTION` `ISOLATION` `LEVEL` `READ` `COMMITTED
BEGIN` `TRAN
UPDATE` `dbo.myOrder ``SET` `customer = ``'ddd'` `WHERE` `id = 1
SELECT` `* ``FROM` `dbo.myProduct ``WHERE` `id = 1
演示与总结如下所示：
两个事务的流程图：
“「死锁 （Dead Lock）」” 的一些注意事项：
（1）如果两个事务没有设置死锁优先级，且两个事务进行的工作量也差不多一样时，任何一个事务都有可能被终止。
（2）解除死锁要付出一定的系统开销，因为这个过程会涉及撤销已经执行过的处理。
（3）事务处理的时间时间越长，持有锁的时间就越长，死锁的可能性也就越大，应该尽可能保持事务简短，把逻辑上可以不属于同一个工作单元的操作移到事务以外。
（4）上面的例子中，事务A和事务B以相反顺序访问资源，所以发生了死锁。如果两个事务按同样的顺序来访问资源，则不会发生这种类型的死锁。在不改变程序的逻辑情况下，可以通过交换顺序来解决死锁的问题。
最后
文章有帮助可以点个「在看」或「分享」，都是支持，我都喜欢！
我是Guide哥，Java后端开发，会一点前端知识，喜欢烹饪，自由的少年。一个三观比主角还正的技术人。我们下期再见！
本文分享自微信公众号 - JavaGuide（JavaGuide）。
如有侵权，请联系 [email protected] 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。