第14章 阻塞与死锁——知识准备 611
14.1.
锁产生的背景–由于隔离的要求引起
·
一个事务的四个特征
o
原子性 - 事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。
o
一致性 - 事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如B树索引或双向链表）都必须是正确的
o
隔离性 - 由并发事务所做的修改必须与任何其它并发事务所做的修改隔离。事务识别数据所处的状态，要么是另一并发事务修改它之前的状态，要么是第二个事务修改它之后的状态，事务不会识别中间状态的数据。也就是说，虽然用户是在并发操作，但是，事务是串行执行的。对同一个数据对象的操作，事务读写修改是有先后顺序的。不是同一时间什么事情都能同时做的
o
持久性 - A transaction is durable in that once
it has been successfully completed, all of the changes it made to the system
are permanent. 哪怕SQL发生了异常终止，机器掉电，只要数据库文件还是完好的，事务做的修改必须还全部存在。
·
SQL
Server如何实现事务的ACID？
o
首先，数据库程序员要负责启动和结束事务，确定一个事务的范围
o
SQL SERVER数据库引擎会运用下面的方法保证每个逻辑事务的物理完整性
ü 锁定资源，使事务保持隔离 - SQL SERVER通过在访问不同资源时需要申请不同类型锁的方式，实现了不同事务之间的隔离。
ü 先写入日志方式，保证事务的持久性 - 即使服务器硬件，操作系统或数据库引擎实例自身出现故障，该实例也可以在重新启动时使用事务日志，将所有未完成的事务自动地回滚到系统出现故障的点，使得数据库进入一个从事务逻辑上来讲一致的状态
ü 事务管理特性，强制保证事务的原子性和一致性 - 事务启动后，就必须成功完成，否则数据库引擎实例将撤销该事务启动之后对数据所做的所有修改
·
阻塞是实现事务的隔离所带来的不可避免的代价。但可以从以下3方向去考虑减少大范围的阻塞：
o
申请资源的互斥度 - 如果不同的连接申请的锁都是相互兼容的，那么它们就不会产生阻塞
o
锁的范围和数目的多少 - 做同样一件事情，不同的程序设计可能导致SQL SERVER申请的锁的粒度和数目不一样。一个良好设计的程序可以使申请的锁的粒度和数目控制在最小的范围之内。这样，阻塞住别人的可能性就能大大降低。
o
事务持有锁资源的时间长短 - 如果一个锁是大家都需要用的，那么每个人持有它的时间越短，阻塞对性能的影响就会越小。最好是申请得越晚越好，释放得越早越好
14.2.
锁资源模式和兼容性 614
·
数据库引擎可以锁定的资源（粒度）
o
RID - 用于锁定堆（heap）中的某一行
o
KEY - 用于锁定索引上的某一行，或者某个索引键
o
PAGE - 锁定数据库中的一个8KB页，例如数据页或索引页
o
EXTENT - 一组连续的8页（区）
o
HOBT- 锁定整个堆或B树的锁
o
TABLE - 锁定包括所有数据和索引的整个表
o
FILE - 数据库文件
o
APPLICATION - 应用程序专用的资源
o
METADATA - 元数据锁
o
ALLOCATION_UNIT - 分配单元
o
DATABASE - 整个数据库
·
数据库引擎通常必须获取多粒度级别上的锁，才能完整地保护资源。例如，为了完整地保护对某一数据行的读取，SQL实例不但需要获取行上的共享锁还有辅以页和表上的意向共享锁。否则，如果同时有人对这个行所在的页面或表做了改动，也会妨碍事务的正常隔离
·
锁模式
o
共享（S） -资源上存在共享锁（S锁）时，任何其它事务都不能修改数据
o
更新（U）-在可重复读或可序列化事务中，一个修改需要先读取数据[获取资源（页或行）的共享锁（S锁）]，然后修改数据[此操作要求锁转换为排它锁（X锁）]。如果两个事务获得了同一个资源上的共享模式锁，然后试图同时更新数据，则事务会把共享锁转换为排它锁。由于两个事务都要转换为排它锁（X锁），并且每个事务都必须等待另一个事务释放共享锁之后才能得到排它锁，以至于两个事务都无法完成转换，因此发生死锁。为了避免这种潜在的死锁问题，SQL使用更新锁（U锁）。一次只有一个事务可以获得资源的更新锁。事务真正修改数据时，将更新锁（U锁）转换为排它锁（X锁）
o
排它（X）- 用于数据修改操作，例如insert update delete，确保不会同时对一个资源进行多重更新
o
意向 （I）intent - 用于建立锁的层次结构，意向锁包含三种类型：意向共享（IS）意向排它（IX）意向排它共享（SIX）
o
架构 - 在执行依赖于表架构的操作时使用，架构锁包含两种类型：架构修改（sch-m）和架构稳定性（sch-s）。数据库引擎在表数据定义语言（DDL）操作（例如添加列或删除表）的过程中使用架构修改（sch-m）锁，阻止其它用户对这个表格的访问。数据库引擎在编译和执行查询时使用架构稳定（sch-s）锁。sch-s锁不会阻止其它事务访问表格里的数据。但是，会阻止对表格做修改性的DDL操作和DML操作。
o
大容量更新（BU）- 数据库引擎在将大容量复制到表中时使用大容量更新（BU）锁，并指定tablelock提示或使用sp_tableoption设置table lock on
bulk load表选项。大容量更新锁（BU）锁允许多个线程将数据并发地大容量加载到同一张表(如通过bulk insert,bcp)，同时防止其它不进行大容量加载数据的进程访问该表
o
键范围 - 在使用可序列化（serializable）事务隔离级别时，对于TSQL语句读取的记录集，键范围锁可以隐式包含该记录集中包含的行范围。键范围锁可防止幻读。通过保护行之间键的范围，它还可防止对事务访问的记录集进行幻插入或删除。
·
锁兼容性
o
锁兼容性控制多个事务能否同时获取同一资源上的锁。如果资源已经被另一事务锁定，则仅当请求锁的模式与现有锁的模式相兼容时，才会授予新的锁请求。如果请求锁的模式与现有锁的模式不兼容，则请求新锁的事务将被迫进入等待状态，阻塞也就随之产生。下表显示了最常见的锁模式的兼容性
o
锁的模式和兼容性是SQL预先定义好的。没有任何参数或者配置能够去修改它们。但是申请锁的粒度，是数据库设计能够影响的。如果应用申请的锁粒度都比较小，产生阻塞的几率就会很小。应用能够影响隔离的第二个行为，就是一个连接什么时候释放它申请的资源。那什么行为会影响锁的粒度，以及持有锁的时间长短呢？这个当然和应用定义的事务性质有关系。
ü 一个事务内部要访问或者修改的数据量越大，它所要申请的锁的数目越多，粒度也就可能越大。
ü 一个事务做的事情越复杂，它要申请的锁的范围也就越大。
ü 一个事务延续的时间越长，它持有的锁的时间也会越长
o
上面的因素是比较浅显的，但是和应用逻辑紧密相关。对于一个固定的应用逻辑，这些因素能够调整的余地可能不是很大。为了缓解阻塞问题，还有两大因素影响锁粒度跟持有锁时间：
ü 事务的隔离级别能影响锁的申请以及释放时间
ü 语句的执行计划，影响到锁的粒度和申请的数量
因而，对于相同的逻辑，如果开发者选择合适的事务隔离级别，引导语句使用优化的执行计划，很多时候也能达到缓解阻塞的目的。
14.3.
事务隔离级别与锁的申请和释放 618
·
Read Phenomenon
o
脏读 – 第一个读到了第二个还未提交的更改。
o
不可重复读 -第一个读到了第二个提交的更改，但2次结果不一样。
o
幻读 - 第一个事务第一次没读到，但在事务结束之前第二次读到了，因为第二个事务添加了这些rows。
o
更新丢失 - occurs when two transactions read the same data and then try
to update the data with a different value.
·
事务隔离级别
o
未提交读（隔离事务的最低级别，只能保证不读取物理上损坏的数据）
o
已提交读（数据库引擎的默认级别，可以防止脏读）
o
可重复读 – 第一个事务未结束前，其它事务将不能更改相关的数据（但第一个事务如果是已提交读，则第二个事务可以更改数据，只要不在第一个事务正在读的时候修改就行，不管第一个事务结束与否）。
READ COMMITTED - Specifies that statements cannot read data that has been
modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other
transactions between individual statements within the current transaction,
resulting in nonrepeatable reads or phantom data. This option is the SQL Server
default.
o
快照 – 使用行版本控制来防止幻读，同时减少阻塞。
ü
2种实现机制：
Ø
READ_COMMITTED_SNAPSHOT ON –语句级
Ø
ALLOW_SNAPSHOT_ISOLATION – 事务级
ü
行版本控制并不是消除阻塞和死锁的万灵药。在决定使用之前，必须考虑下面两个问题。
Ø
最终用户是否接受行版本控制下的运行结果？它是希望哪怕被阻塞住也要读到最新版本的数据，还是能容忍读到旧版本数据呢？
Ø
SQL Server是否能支持行版本控制带来的额外负荷（主要是tempdb）？
o
可序列化（隔离事务的最高级别，它要求事务序列化执行，即事务只能一个接着一个执行，不能并发执行。因为它锁定了键的整个范围，并在事务完成之前一直保持范围锁）
·
SQLSERVER主要是通过对是否申请共享锁和对释放机制的不同处理，来实现不同事务隔离级别的。
o
下表列出了不同隔离级别对共享锁的不同处理方式。它表明了事务隔离级别越高，共享锁被持有的时间越长。而可序列化还要申请粒度更高的范围锁，并一直持有到事务结束。所以，如果阻塞发生在共享锁上面，可以通过降低事务隔离级别得到缓解。
隔离级别
|
是否申请共享锁
|
何时释放
|
有无范围锁
|
未提交读
|
不申请
|
--
|
无
|
已提交读
|
申请
|
当前语句做完时
|
无
|
可重复读
|
申请
|
事务提交时
|
无
|
可序列化
|
申请
|
事务提交时
|
有
|
o
需要说明的是，SQL Server在处理排它锁的时候，每个除SNAPSHOT的事务隔离级别都是一样的。都是在修改的时候申请，直到事务提交的时候释放（而不是语句结束以后立即释放）。如果阻塞是发生在排它锁上面，是不能通过降低事务隔离级别得到缓解的。
14.4.
如何监视锁的申请、持有和释放 628
14.4.1.
检查一个连接当前锁持有的锁 628
·
使用sp_lock这列出当前SQL Server里所有的连接持有的锁的内容。
·
也可以查询sys.dm_tran_locks这张系统动态管理视图来实现。
SELECT request_session_id ,
resource_type ,
resource_associated_entity_id ,
request_status ,
request_mode ,
resource_description
FROM sys.dm_tran_locks
·
还可以结合其它的动态管理视图，直接查出某个数据库上面的锁是在哪些表格，以及哪些索引上面。注意：必须在表格所属的那个数据库下运行。
USE AdventureWorks2012
GO
SELECT request_session_id ,
resource_type ,
resource_associated_entity_id ,
request_status ,
request_mode ,
resource_description ,
p.object_id ,
OBJECT_NAME(p.object_id) AS object_name ,
p.*
FROM sys.dm_tran_locks
LEFT JOIN sys.partitions p ON sys.dm_tran_locks.resource_associated_entity_id
= p.hobt_id
WHERE resource_database_id = DB_ID('AdventureWorks2012')
ORDER BY
request_session_id ,
resource_type ,
resource_associated_entity_id
14.4.2.
监视语句执行过程中sql对锁的申请和释放行为 629
·
有很多锁是在语句运行的过程中申请和释放的，语句运行结束之后，这些锁就会消失。如果这些锁申请不到，也会产生阻塞。那么怎么看一个语句执行过程中锁的申请和释放过程呢？管理员必须借助SQLServer Profiler。在定义一个trace（跟踪）的时候，需要选取下面的Events（事件）：Lock:Accquired，Lock:Released。
·
通过这种方式，就能看到一个语句在执行过程中锁的完整申请和释放过程。需要提醒的是，SQLServer锁的申请和释放是个非常复杂的行为，同样一句话在不同情况下执行（例如是否需要编译等），申请的锁都会不一样。有时候锁申请的数目可能大大超过你的想象。所以这种跟踪方式只能在测试环境里，针对特定的语句进行。如果在生产环境里这么做，会产生大量的跟踪记录，影响SQLServer的性能，是个危险的行为。
·
一般来说，用sys.dm_tran_locks或sp_lock的结果就能够做绝大部分的阻塞分析。
14.5.
锁的数量和数据库调优的关系–除了事务隔离等级和连接持有锁的长短，另一个影响SQL申请锁数目的重要因素就是语句的执行计划。表格上索引的不同会导致不同的执行计划，从而会对锁申请的数目有很大影响。
14.5.1.
一个常见的select动作要申请的锁 634
·
规律：
o
在非“未提交读”的隔离级别上，查询在运行过程中，会对每一条读到的记录或键值加S锁。如果记录不用返回，那锁就会被释放。如果记录需要被返回，则视隔离级别而定，如果是“已提交读”，则也释放。否则，不释放。
o
对每一个使用到的索引，SQL也会对上面的键值加S锁
o
对每个读过的页面，SQL会加一个意向锁
o
查询需要扫描页面和记录越多，锁的数目也会越多。查询用到的索引越多，锁的数目也会越多
·
避免阻塞采取的方法
o
尽量返回少的记录集，返回的结果越多，需要的锁也就越多
o
如果返回结果集只是表格所有记录的一小部分，要尽量使用index seek，避免全表扫描这种执行计划
o
可能的话，设计好合适的索引，避免SQL通过多个索引才找到数据
14.5.2.
一个常见的update动作要申请的锁 639
·
对于update语句,可以简单理解为SQL先做查询，把需要修改的记录给找到，然后在这个记录上做修改。找记录的动作要加S锁，找到修改的记录后加U锁，再将U锁升级为X锁。
·
Update语句申请的锁的规律
o
对每一个使用到的索引，SQL会对上面的键值加U锁
o
SQL Server只对要做修改的记录或键值加X锁
o
使用到要修改的列的索引越多，锁的数目也会越多
o
扫描过的页面越多，意向锁也会越多。在扫描的过程中，对所有扫描到的记录也会加锁，哪怕上面没有修改
·
避免阻塞采取的方法
o
尽量修改少的记录集。修改的记录越多，需要的锁也就越多
o
尽量减少无谓的索引。索引的数目越多，需要的锁也可能越多
o
但是也要严格避免表扫描的发生。如果只是修改表格记录的一小部分，要尽量使用index seek索引查找，避免全表扫描这种执行计划
14.5.3.
一个常见的delete动作要申请的锁 641
·
Delete语句申请的锁的规律
o
数据库上的S锁
o
表上的IX锁
o
相关的聚焦索引和非聚焦索引上各申请一个X锁，在它们的页面申请一个IX锁。
o
如果是heap表，还会在相应的RID上申请一个X锁，其它扫描过的页面申请一个IU锁。
·
避免阻塞采取的方法
o
Delete的过程是先找到符号条件的记录，然后做删除。可以理解为先是一个select,然后是delete。所以，如果有合适的索引,第一步申请的锁就会比较少 不用表扫描。
o
Delete不但是把数据行本身删除, 还要删除所有相关的索引键，所以一张表上索引数目越多，锁的数目就会越多, 也就越容易发生阻塞。
o
所以，为了防止阻塞,我们既不能绝对地不建索引,也不能随随便便地建立很多索引,而是要建立对查找有利的索引.对于没有使用到的索引, 还是去掉比较好。
14.5.4.
一个常见的insert动作要申请的锁 643
·
相对于select,update,delete，单条记录的insert操作对锁的申请比较简单。SQL会为新插入的数据本身申请一个X锁，在发生变化的页面上申请一个IX锁。由于这条记录是新插入的，被其它连接引用到的概率会相对小一些，所以出现阻塞的几率也要小。
·
Insert动作要申请的锁
o
数据库上的S锁（resource_type=DATABASE）
o
表上的IX锁（resource_type=OBJECT）
o
每个索引上都要插入一条新数据，所以有一个key上的X锁
o
在每个索引上发生变化的那个页面，申请了一个IX锁（resource_type=PAGE）
o
如果是heap结构，还得申请一个RID锁。因为真正的数据不是放在索引上，而是放在heap数据页面上。
14.6.
小结 -如果SQL DBA要控制SQL锁的申请和释放行为，以缓解阻塞和死锁问题，需要考虑的因素有：
·
事务隔离级别的选定 - 事务隔离级别越高，隔离度就越高，并发度也就越差。如果选择了比较高的隔离级别，SQL不可避免地要申请更多的锁，持有的时间也会增加。
·
事务的长短和事务的复杂度 - 事务的长度和复杂度决定论这个事务在SQL内部会持续多长时间，也能决定SQL会同时在多少张表和索引上申请和持有锁。事务越简单，就越不容易发生阻塞和死锁。
·
从应用整体并发度考虑，单个事务一次处理的数据量不能过多 - 应用的性能，不单要衡量单个连接的处理速度，也要衡量在并发处理的情况下，整体的平均速度怎么样。
·
针对语句在表格上设计合适的索引 - 合适的索引能使SQL在读取尽可能少的数据量的前提下，把需要处理的数据找到。如果没有合适的索引，SQL在做select, update,
delete的时候，会申请比要处理的目标数据量多得多的锁，从而导致阻塞或死锁。这种情形可以通过加索引的方式提高并发度。同时，SQL在做update, insert,
delete的时候，会对有关联的所有索引都做修改，在她们上面申请锁。从这个角度讲，索引越多，产生的锁的数目也就越多，阻塞和死锁的几率也就会越高。所以数据库设计员需要做的，是要确保有足够的索引，防止语句做全表扫描，但是也要去掉那些对语句运行贡献不大的索引。不能随便往表格上加索引。
