笔记48-徐 阻塞问题定位方法

笔记48-徐 阻塞问题定位方法

--阻塞问题定位方法
--阻塞是事务隔离带来的副作用，是一个数据库系统常见的现象。如果
--阻塞持续的时间非常短，可能对性能的影响不会很大。所以在遇到性能问题
--的时候，DBA或应用开发者要首先确认性能问题是不是由于阻塞直接导致
--，以及阻塞在多大程度上影响了SQL的性能。不要一看到SQL里发生了阻塞，就
--认为他是性能问题的主要原因

--1、哪些应用出了问题，这些应用是在使用同一个数据库还是不同的数据库？
--一般来讲，一个SQL里会有多个数据库。每个数据库可能在支持多个应用程序
--假如其中一个数据库出现了阻塞，而我们关注的那个应用没有使用他，很有
--可能这个阻塞和我们关注的性能问题没关系，无须做太多分析
--但是如果有其他应用和关注的应用共享同一个数据库，而这个数据库上发生
--了阻塞，那DBA就不能只关注一个应用上。因为阻塞很有可能因为不同应用之间
--的相互作用导致的

--2、应用端问题的现象
--应用端问题表征对定位问题非常重要。不是所有问题都是阻塞导致的。但是
--很多时候数据库有阻塞，应用也能运行得很好

--如果应用遇到一些错误，那至少要得到应用从数据库端收到的错误原文是什么。
--有时候应用会对错误封装，或者没有准确捕捉到错误原文。如果遇到这样情况
--可能有必要对应用修改，要求他把错误的详细原文打印出来

--如果应用端的现象是长时间没有响应（hang）或很慢，那首先要从应用端分析。
--他是运行到哪一步以后才出问题，这一步是不是在做数据库操作，是不是等待
--数据库返回。造成应用没有响应或响应很慢，数据库只是可能的因素之一。如果
--直接跳进数据库性能分析，会有忽视其他因素的风险

--3、什么因素让你认为阻塞是问题的主要原因？
--得到错误信息后，要确认这个错误是否是SQL返回的。一般阻塞会导致命令不能及时
--完成。所以得到的错误应该是运行时错误。如果错误不是SQL返回的，或者不是运行超时
--那么问题的直接原因不是阻塞，要从其他角度去分析问题


--4、阻塞发生的特征
--如果怀疑阻塞是问题原因，要搞清楚阻塞本身的特征：阻塞每天在什么时间发生
--每次持续多久，是不是应用负载越重，阻塞越严重，还是在运行某些特定任务的
--的时候才容易发生阻塞，阻塞发生后，是自动消失，还是必须要重启SQL才能解决

--如果确定是阻塞和当前问题相关,DBA要用以下问题来定位：
--（1）SQL里有没有阻塞发生，是什么时候发生，在哪个数据库上，阻塞发生在那个表
--哪些资源上
--（2）和阻塞有关的连接是从哪些客户应用来的

--（3）为什麽阻塞会发生
--现在阻塞发生在哪个或哪些资源上
--阻塞的源头是在做什么事情时候申请了这些锁，为什麽会申请这些锁
--阻塞的源头当前的状态是什么，是一直在执行，还是已经进入空闲状态
--如果他一直在执行，为什麽要执行这麽久
--如果已经进入空闲状态，那为什么没有释放锁资源
--其他阻塞的连接他们想要做什么？为什麽也要申请这些锁资源

--SQL提供了很多信息帮助DBA回答上面问题，SQL2005以后又有新的改进

--首先在范例数据库上造成一个阻塞，用SSMS建立两个连接
--连接1：
USE [AdventureWorks]
BEGIN TRAN
UPDATE [dbo].[Employee_Demo_Heap]
SET [Title]='aaa'
WHERE [EmployeeID]=70
UPDATE [dbo].[Employee_Demo_BTree]
SET [Title]='aaa'
WHERE [EmployeeID]=70

--连接2：
USE [AdventureWorks]
SELECT [EmployeeID],[LoginID],[Title] FROM [dbo].[Employee_Demo_Heap] WHERE [EmployeeID] IN (3,30,200)

--之前说过，由于连接2会选取一个表扫描的执行计划，所以被连接1阻塞住

-------------------正式开始-------------------------------------------------------------------------

--1、运行
SELECT * FROM master.[sys].[sysprocesses]
--sysprocesses是一张非常重要的系统管理视图
--重要字段
--spid :SQL会话也叫SQL进程ID，这是SQL内部对一个连接的编号，一般来讲，小于50
--的是SQL系统会话，大于50的是用户连接

--kpid：Windows线程ID threadID

--blocked：正在阻塞请求的会话的ID。如果此列为0，则表示请求未被阻塞，以下这些小于0
--的值，有特殊含义
-- -2：阻塞资源由孤立的分布式事务拥有
-- -3：阻塞资源有延迟的恢复事务拥有
-- -4：由于内部锁latch状态转换而无法确定阻塞锁所有者的会话ID


--waittyep：当前连接的等待资源编号。如果进程没有等待，值是0x0000。等待资源的含义参考11章

--waittime：当前等待时间（毫秒） 0的意思是进程没有等待

--lastwaittype：指示上次或当前等待类型名称的字符串

--waitsource：锁资源的文本化表示法

--dbid：当前正由进程使用的数据库ID

--uid：执行命令的用户ID

--login_time：客户端进程登录到服务器的时间。对于系统进程，将存储SQL的启动时间

--last_batch：客户端进程上次执行远程存储过程调用或execute语句的时间。对于系统
--进程，将存储SQL的启动时间

--open_tran：进程的打开事务个数。如果有嵌套事务，就会大于1

--status：进程ID状态。可能的值有：
--dormant:SQL正在重置会话
--running：会话正在运行一个或多个批处理。多个活动的结果集MARS启用后，会话可以运行多个批处理
--background：会话正在运行一个后台任务，例如死锁检测
--rollback：会话正在处理事务回滚
--pending：会话正在等待工作线程变为可用
--runnable：会话中的任务在等待获取scheduler来运行的可执行队列中
--spinloop：会话中的任务正在等待自旋锁变为可用
--suspended：会话正在等待事件（如I/O）完成

--hostname：建立连接的客户端工作站的名称

--program_name：应用程序的名称

--hostprocess：建立连接的应用在客户端工作站里的进程ID

--cmd：当前正在执行的命令

--nt_domain：客户端的Windows域（如果使用Windows身份验证）或可信连接的Windows域

--nt_username：进程的Windows用户名（如果使用Windows身份验证）或可信连接的Windows用户名

--net_address：为每个用户工作站的网络适配器的唯一标识符。当用户登录时，该标识符插入
--net_address列

--net_library：用于存储客户端网络库的列。每个客户端进程都在网络连接上进入。常用
--常用的网络库有LPC(shared memory),tcp/ip,name pipe

--loginname：登录名





--2、数据库上面有没有阻塞发生，哪些连接发生了阻塞，是谁阻塞住了谁
--查找[sysprocesses]的blocked字段不为0，如果不为0，而且也不是-2，-3，-4
--那他就是被这个字段值的那个连接给阻塞住了。一般来讲，阻塞源头的blocked
--字段会是NULL。如果他也不等于0，说明他也被别人阻塞，要继续查找阻塞住他的连接


--如果你发现一个连接的blocked字段的值等于他自己，那倒不说明什么问题。常常是因为
--这个连接正在做磁盘读写，他要等自己的I/O做完。

--什么时候开始的？
--这里只要看waittime,就可以知道此次阻塞发生的时间。如果你运行多次查询，每次
--被阻塞住的SPID waittime都很短，那说明单个阻塞持续的时间都不是很长。阻塞
--可能还不是很严重（当然这也会影响响应速度）

--范例结果SPID53的waittye是557421，也就是说已经阻塞了557.421秒，这是一个发生
--时间很长的阻塞。如果真的发生在生产环境里，这种阻塞的影响会很大


--在哪个数据库上？
--一般检查dbid即可。得到dbid以后，可以运行以下查询得到数据库的名字
SELECT name,dbid  FROM sys.[sysdatabases]


--阻塞在哪个表上，哪些资源上？
--可以运行sp_lock，在结果集中寻找状态是wait的锁资源。或者直接运行下面的查询
--得到一样的结果
SELECT 
CONVERT(SMALLINT,[req_spid]) AS spid,
[rsc_dbid] AS dbid,
[rsc_objid] AS objid,
[rsc_indid] AS indid,
SUBSTRING(v.[name],1,4) AS TYPE,
SUBSTRING([rsc_text],1,32) AS RESOURCE,
SUBSTRING(u.[name],1,8) AS mode,
SUBSTRING(x.[name],1,5) AS status
FROM master.[dbo].[syslockinfo],
master.[dbo].[spt_values] v,
master.[dbo].[spt_values] x,
master.[dbo].[spt_values] u
WHERE master.[dbo].[syslockinfo].[rsc_type]=v.[number]
AND v.[type]='LR'
AND master.[dbo].[syslockinfo].[req_status]=x.[number]
AND x.[type]='LS'
AND master.[dbo].[syslockinfo].[req_mode]+1=u.[number]
AND u.[type]='L'
AND SUBSTRING(x.[name],1,5)='WAIT'
ORDER BY [SPID]
------------------------------------------------------------------------------
EXEC [sys].[sp_lock]
SELECT DB_NAME(1)
SELECT OBJECT_NAME(1115151018)

--数据库名字和表名可以使用DB_NAME和OBJECT_NAME函数
--查询索引名字
SELECT * FROM sys.[indexes] WHERE [object_id]=1115151018 --表

--以下是对不同种类的资源含义的解释,只有了解他们的意思,才能真正看懂阻塞
--RID：格式为fileid:pagenumber:rid的标识符，其中fileid标识包含页的文件，
--pagenumber标识包含行的页，rid标识页上的特定行。fileid与sys.databases_files
--目录视图中的file_id列相匹配
--例如：例子中有个正处于wait状态的RID Resource是1:6681:26
--他的意思是在第一个数据文件上的第6681页上的第26个行上的锁资源。如果要查看
--这个页面上到底有哪些数据，可以考虑使用DBCC PAGE（） 第一章


--KEY：数据库引擎内部使用的十六进制数。这个值和sys.partions.hobt_id相对应
--出现这种资源说明锁是在一个索引上面。通过查询sys.partitions视图里相应的
--object_id和index_id就能找到这个索引
--例子里有个KEY(46000227c460)， 他的dbid是9，object_id是834674039，index_id是1
--从上面sys.indexes的结果，就可以知道他是PK_Employee_EmployeeID_Demo_BTree这个
--索引


--PAG：格式为fileid:pagenumber的数字，其中fileid标识包含页的文件，pagenumbe标识页
--EXT：标识区中的第一页的数字。该数字的格式为fileid:pagenumber
--TAB：没有提供信息，因为已在Objid列中标识了表
--DB：没有提供信息，因为已经在dbid列中标识了数据库
--FIL：文件的标识符，与sys.database_files目录视图中的file_id列相匹配

EXEC [sys].[sp_helpdb] @dbname = gposdb -- sysname
--通过这些资源的名字，读者可以找到阻塞发生在哪个对象的哪种资源上


--和阻塞有关的连接是从哪些应用来的？
--[检查]：sysprocesses的结果提供了很多信息告诉用户一个连接是从什么客户端连接
--过来的。

--hostname:建立连接的那台机器叫什么名字
--program_name：建立的程序叫什么名字
--hostprocess：运行程序的进程在他的机器上ID是多少。program_name是应用告诉SQL的
--名字。有些应用如果自己没有特别的名字，那很可能会是.NET SQLCLIENT DATA PROVIDER
--之类的通用名字。需要到任务管理器通过进程ID找到应用程序到底是哪一个

--loginname,nt_domain,nt_username：从这些字段可以知道连接是用什么帐号连入SQL的

--net_address：客户端机器的MAC地址。有时候应用程序没有告诉SQL自己的信息，
--hostname,program_name之类的字段会是空的。这时候只能通过MAC地址来找是哪台
--客户机连上来的

--net_library：连接是使用哪个网络协议连上来的。一般为tcp/ip,named pipe,LPC(local procedure call)

--1、阻塞的源头是在做什么事情的时候申请了这些锁？为什麽会申请这些锁
--这个问题比较复杂。锁可能是会话正在运行中的语句申请的，但也可能是这个会话
--在先前开启了一个事务，一直都没有提交或回滚，锁资源是事务开启后的任何一个
--语句申请的，当时阻塞可能还没有发生

--如果是前者只要捉住连接发过来的最后一句话即可。如果是后者，则要在阻塞发生之前
--预先开启SQL Trace，一直跟踪到问题发生。
--如果阻塞问题已经发生而跟踪没有开启，那就没有办法知道事务是怎麽开启的，以及
--锁是什么语句申请的。

--所以有时候要捉住阻塞问题的根源，必须下决心在出问题之前就开启服务器端跟踪，等到问题
--重现为止

--如果没有跟踪，可以运行一些脚本得到某个连接当前正在运行的语句，和空闲连接上次
--运行的最后一条语句



--正在运行中的语句
--下面这个查询返回所有正在运行中的连接和他正在运行的语句，如果一个连接处于空闲状态，就
--不会被返回
SELECT  p.[session_id] ,
        p.[request_id] ,
        p.[start_time] ,
        p.[status] ,
        p.[command] ,
        p.[blocking_session_id] ,
        p.[wait_type] ,
        p.[wait_time] ,
        p.[wait_resource] ,
        p.[total_elapsed_time] ,
        p.[open_transaction_count] ,
        p.[transaction_isolation_level] ,
        SUBSTRING(qt.[text], p.[statement_start_offset] / 2,
                  ( CASE WHEN p.[statement_end_offset] = -1
                         THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
                         ELSE p.[statement_end_offset]
                    END - p.[statement_start_offset] ) / 2) AS 'sql statement' ,
        p.[statement_end_offset] ,
        batch = qt.[text]
FROM    master.sys.[dm_exec_requests] p
        CROSS APPLY [sys].[dm_exec_sql_text](p.[sql_handle]) AS qt
WHERE   p.[session_id] > 50


--空闲连接上次运行的最后一条语句
--运行DBCC INPUTBUFFER(SPID)可以获得从客户端发送到SQL实例的最后一个批处理语句
--这句话的优点是不管连接是否正在运行，都会返回结果。缺点是他返回的是整个批处理
--语句，而不是当前正在执行的子句。所以对于正在运行的连接，第一种方法比较好
DBCC INPUTBUFFER(58)

--2、阻塞的源头当时是什么状态，是一直在执行还是已经进入空闲状态
--[检查]一个最简单的方法，是看sysprocesses里面的kpid和waittype两个字段
--如果两个都是0，就是处于空闲状态，如果不都是0，或者连接正在运行
--或者因为资源等待而暂时挂起

--3、如果一直运行，为什麽执行这麽久？
--[检查]如果一个连接的kpid值不是0（连接拿到了一个线程资源），waittype值是0
--（他不需要等待任务资源），他的状态就会是runnable或running。如果一个连接
--的kpid值不是0，waittype值也不是0，则说明他要等待某个资源才能继续执行。这时候
--连接的状态一般会是suspended

--4、如果已经进入空闲状态，那为什么没有释放资源
--[检查]如果一个连接的kpid值是0（连接没有占用线程资源），waittype值也是0
--他不需要等待任务资源，那么这个连接已经完成了客户端发过来的所有请求，现在
--进入空闲状态。正在等待客户端发送新的请求

--如果这是他还是阻塞源头，一般是他有先前开启的事务没有及时提交。这可以通过
--检查sysprocesses里的open_tran字段是否大于0确认。通过
DBCC INPUTBUFFER(58)
--也可以知道最后发过来的那句话是什么


--5、其他阻塞的连接他们想要做什么？为什麽也要申请这些锁资源
--使用以下脚本
SELECT  p.[session_id] ,
        p.[request_id] ,
        p.[start_time] ,
        p.[status] ,
        p.[command] ,
        p.[blocking_session_id] ,
        p.[wait_type] ,
        p.[wait_time] ,
        p.[wait_resource] ,
        p.[total_elapsed_time] ,
        p.[open_transaction_count] ,
        p.[transaction_isolation_level] ,
        SUBSTRING(qt.[text], p.[statement_start_offset] / 2,
                  ( CASE WHEN p.[statement_end_offset] = -1
                         THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
                         ELSE p.[statement_end_offset]
                    END - p.[statement_start_offset] ) / 2) AS 'sql statement' ,
        p.[statement_end_offset] ,
        batch = qt.[text]
FROM    master.sys.[dm_exec_requests] p
        CROSS APPLY [sys].[dm_exec_sql_text](p.[sql_handle]) AS qt
WHERE   p.[session_id] > 50
--去比较sp_lock的结果，就能大致判断他申请的锁数量是否合理
--如果不合理，可以通过优化语句，加合适索引解决




--5种常见的阻塞类型
--类型          waittype          open_tran     status      自我修复          
--1              不为0             >=0           runnable    是的，当语句运行结束之后
--类型1：由于语句运行时间太长而导致阻塞，语句本身正常运行，只需等待某些系统资源
--解决方法：
--DBA要与数据库应用设计人员合作，共同解决以下问题：
--（1）语句本身有没有可优化的空间？
--这里包括修改语句本身复杂度，修改表设计，调整索引
--（2）SQL整体性能如何，是不是有资源瓶颈影响了语句执行速度？
--当SQL遇到诸如内存，硬盘读写，CPU等资源瓶颈时，原来能很快完成的语句有可能会花很长时间

--（3）如果语句天生就很复杂，无法调优（很多处理报表的语句就是这样）就需考虑怎样
--把这一类应用（一般就是数据库仓库应用）从OLTP系统中隔离出来


--类型       waittype        open_tran     status          自我修复          
--2           ox0000            >0         spleeping      不能，要运行KILL语句   在SQL Trace里能够看到一个ATTENTION事件

--类型2：由于一个未按预期提交的事务导致的阻塞
--这一类阻塞的特征，就是问题连接早就进入了空闲状态（sysprocesses.status='sleeping'和sysprocesses.cmd=
--awaiting command）但是如果检查open_tran，就会发现他不为0，事务没有提交。这类问题很多是因为
--应用端遇到了一个执行超时，或者其他原因，当时执行的语句被提前终止了，但是连接还保留着。应用
--没有跟随发来的事务提交或回滚，导致一个事务被遗留在SQL里

--一般ADO或ADO.NET的连接默认超时时限是30秒。如果30秒以内SQLSERVER没有完成语句返回任何结果，客户端
--会发送一个ATTENTION的消息给SQL，告诉SQL他不想继续等下去了。SQL收到这个消息之后，会终止当前正在
--运行的语句（或批处理），但是为了维护客户端的逻辑，SQL默认不会自动回滚或提交这个连接已经打开的
--事务，而是等待客户端的后续决定。如果客户端不发来回滚或提交指令，SQL会永远把这个事务保持下去
--直到客户端断开连接为止

--实验：
USE [AdventureWorks]
GO
BEGIN TRAN
SELECT * FROM [Person].[Address] WITH(HOLDLOCK)
SELECT * FROM sys.[objects] s1,sys.[objects]s2
COMMIT TRAN

--由于使用了holdlock参数，第一句select会在运行结束之后，在表格上维持一个TAB的S锁。
--如果批处理全部完成，这个锁会在提交事务的时候释放。但是第二句select会执行很久。
--请在等待3~4秒以后取消执行。然后运行下面的语句，检查open_tran和锁的情况
SELECT @@TRANCOUNT
GO
[sys].[sp_lock]
GO

--（1）批处理被取消之后，commit tran这条语句没有被执行到，SQL没有对begin tran开启的那个事务做
--任何处理，只是保持其活动状态
--（2）第一句select带来的锁由于事务没有结束，所以锁还保持着objectid=55671246,type=TAB,mode=S
--现在如果有其他连接要修改这张表，就会被阻塞住

--解决办法：
--应用程序必须意识到任何语句都有可能意外终止的情况，做好错误处理工作，这些工作包括
--（1）在做SQL调用的时候，需加上错误捕捉和处理语句
--SQL客户端驱动程序（包括OLEDB和ODBC）当语句执行遇到意外终止时（包括超时）的时候,都会
--向应用程序返回错误信息.客户端在捕捉错误信息时,除了做记录以外（这对问题定位很重要）
--运行下面这句话把没有提交的事务回滚掉
IF @@TRANCOUNT>0 ROLLBACK TRAN

--有些程序员会问，我在TSQL批处理里已经写了TSQL层面的错误捕捉和处理语句
IF @@ERROR<>0 ROLLBACK TRAN
--还有必要让应用程序再做一遍吗？需要意识到，有些异常（比如超时）终止的是整个TSQL批处理语句
--的执行。而不仅仅是当前语句。所以，当这些异常发生的时候，TSQL层面的错误捕捉和处理语句很可能
--也一起被取消了。他们不能发挥想象中的作用。在应用程序里的错误捕捉和处理语句是必不可少的

--（2）设置连接属性
SET XACT_ABORT ON
--当为ON时，如果执行TSQL语句产生运行错误，则整个事务将终止并回滚
--当为OFF时，处理方法是不唯一的。有时只回滚TSQL语句，而事务继续进行
--如果错误很严重，即使SET XACT_ABORT 为OFF，也会回滚整个事务
--OFF是默认值
--如果没有办法很快规范应用程序的错误捕捉和处理语句，一个最快的方法就是在每个连接建立以后
--或者是最容易出问题的存储过程的开头，运行SET XACT_ABORT ON，让SQL帮助应用程序回滚事务

https://social.msdn.microsoft.com/Forums/zh-CN/b1370f2d-41f8-4907-bace-2bb5a184d9db/xactabort?forum=sqlserverzhchs
基本上你只有在SSMS里面才能达到这个默认效果。
大部分程序里面自动就设置为ON了。

--（3）考虑是否需要关闭连接池
--一般SQL应用都会使用连接池来得到良好的性能。如果有一个连接忘记把事务关闭就退出连接
--那么这个连接会被交还给连接池，但是这时候，事务不会被清理。客户端驱动程序会在这个
--连接下一次被重用的时候（又有新的用户要建立连接），发一句sp_reset_connection命令（这是一个存储过程）
--清理当前连接上次遗留下来的所有对象，包括回滚未提交的事务。如果连接交还给连接池以后
--很久都没有被重用，那他的事务就会持续很长时间，引起阻塞。有些JAVA程序使用的驱动程序
--提供连接池功能，但是不提供连接重用时的事务清理功能。这样的连接池对应用开发质量
--要求很高，比较容易发生阻塞
--如果不能很快实施建议（1）和（2），把连接池关闭能缩短事务持续时间，也能从一定程度上
--缓解阻塞问题

--2、分析为什麽会遇到异常终止
--这里又谈到错误信息记录了。有了错误信息，就可以判定是超时问题，还是其他SQL错误。
--如果是超时问题，可按照第一种阻塞进程处理

--还有一种孤儿事务的来源，是连接开启了隐式事务（implicit transaction）而没有加入及时
--提交事务的机制。如果连接处于隐式事务
SET IMPLICIT_TRANSACTIONS ON
--并且连接当前不在事务中，则执行下列任何一条语句都会开启一个新的事务
--会开启新事物的语句
--alter table      fetch       revoke
--create           grant       select
--delete           insert      truncate table
--drop             open        update


--对于因为此设置为ON而自动打开的事务，SQL会自动帮你打开事务，但是不会自动帮你提交
--用户必须在该事务结束时将其显式提交或回滚。否则，当用户断开连接时，事务及其
--包含的所有数据更改将被回滚。事务提交后，执行上述一条语句又会开启一个新事物
--隐式事务将始终生效，直到连接执行
SET IMPLICIT_TRANSACTIONS OFF
--语句使连接恢复为自动提交模式，在自动提交模式下，所有单个语句都会自动提交
--不会有事务遗留

--为什麽会有连接要开启隐式事务呢？除了程序员有意之外，很多是客户端的数据库连接驱动
--或者控件为了实现他的事务功能（注意不是SQL通过TSQL语句直接提供的）而选用这个机制
--如果应用程序出现意外，或者脚本没有处理好，会有应用层事务未提交的现象。在SQL里
--也体现为一个孤儿事务。严格约束应用层对事务的使用，直接使用SQL里面的事务，是避免
--这种问题出现的好方法


--类型3： wait type           open_tran        status      自动修复
--类型3：oxooo,oxo800,oxoo63  >=0             runnable  不能，直到客户把所有结果都主动取走或断开连接或KILL但是要花
--长达30秒  客户端没有及时把所有结果取走，这时可能open_tran=0，事务隔离级别也为默认
--read committed，但这个连接还会持有锁资源

--类型3：由于客户端没有及时把结果集取出而导致的语句长时间运行
--语句在SQL内执行总时间不仅包含SQL的执行时间，还包含把结果集发给客户端的时间
--如果结果集比较大，SQL会分几次打包发出，每发一次，都要等待客户端确认。只有
--确认之后，SQL才会发送下一个结果集包。所有结果都发完以后，SQL才认为语句执行
--完毕，释放执行申请的资源（包括锁资源）

--如果出于某种原因，客户端应用处理结果非常慢甚至没有响应，或者干脆不理睬SQL
--发送结果集的请求，则SQL会耐心地等待，因此会导致语句长时间执行而产生阻塞

--解决办法：
--（1）设计程序时，一定要慎重返回大结果集。这种行为不仅会对SQL和网络带宽带来
--很大负担，对应用程序来说，也要花很多资源去处理结果集。如果最终用户只需要
--部分结果集就可以，则在发送SQL指令时就要指定好。避免语句不管三七二十一
--所有数据都要，而结果集只取走开头一部分去展示这样的行为发生

--（2）如果应用程序的确需要返回大结果集，例如一些报表系统，则要考虑报表数据库
--和生产数据库分开

--（3）如果1和2在短期内不能实现，可以和最终用户协商，返回大结果集的连接使用
--read uncommitted事务隔离级别，这样查询语句就不会申请S锁了


--类型4： wait type           open_tran        status      自动修复
--类型4：   oxoooo               >0            rollback   是的  在SQL Trace里能够看到这个SPID已经发来了一个
--ATTENTION事件，说明客户端已经遇到了超时，或者主动要求回滚

--类型4:阻塞的源头连接一直处于rollback状态
--这种情况是由第一类情况衍生出来的。有时候DBA发现一个连接阻塞住了别人，为了
--解决问题，会让连接主动退出（强制退出应用，或者直接在SQL端KILL掉连接）。
--对于大部分情况，这些措施会消除阻塞，但是要记住，不管是在客户端退出还是
--服务点KILL，为了维护数据库事务一致性。SQL都会对连接还没有来得及完成提交
--的事务做回滚操作。SQL要找到所有当前事务修改过的记录，把他们改回原来
--的状态。所以，如果一个delete,insert,update已经运行了一个小时，可能回滚也
--需要一个小时。在这个过程中，阻塞还会延续，我们只能等待

--有些用户可能等不及，直接重启SQL。当SQL关闭的时候，回滚操作会被中断
--SQL会被很快关掉。但是这个回滚动作在下次SQL重启的时候会重新开始
--（恢复数据库的时候），重启的时候如果回滚不能很快结束，整个数据库都不可用
--可能会带来更严重的后果

--解决办法；
--最好的方法就是在工作时间尽量不要做这种大的修改操作，这些操作尽量安排
--在半夜或周未的时间完成。如果操作已经做了很久，最好耐心等他做完。
--如果一定要在工作负荷的时候做，最好把一个大操作分成若干个小操作分步完成


--类型5： wait type           open_tran        status      自动修复
--        各种值都有可能       >=0              runnable    不能，直到客户把所有结果都主动取走或断开连接或KILL但是--要花长达30秒   应用程序运行中产生死锁，在SQL中，以阻塞形式体现，sysprocesses里阻塞和被阻塞的连接
--hostname值是一样的

--类型5：应用程序运行中产生死锁，在SQL中以阻塞形式体现
--一个客户端的应用在运行过程中会使用到许多资源，包括线程资源，信号量资源，内存资源，I/O资源等
--SQL也是资源之一。如果发生死锁的两端不全是SQL，SQL的死锁判断机制可能不起作用。这时
--如果没有处理好，可能会永远等待下去。而SQL内部的表现可能仅仅是一个阻塞，但是
--这个阻塞不会自动消除。这样的阻塞对SQL的性能会产生很大影响

--下面举两个应用端死锁的例子
--（1）在应用的一个线程中开启不止一个数据库连接而产生死锁
--假设应用有一个线程有这样的逻辑：
--开始运行
--建立数据库连接A，调用存储过程ProcA，打开结果A
--建立数据库连接B，调用存储过程ProcB，打开结果B
--轮流读取结果集A，B，整合输出最终结果
--关闭结果集A，B，关闭连接A，B
--结束运行



--应用端死锁
--连接A 运行ProcA 调用ProcA获取结果集A
--连接B 运行ProcB 调用ProcB获取结果集B

--连接A 获取结果集A取到一行之后，去取结果集B  所有结果都取完之后关闭两个连接

--在正常情况下，这种设计没有问题，但是实际上很脆弱。因为在线程内部，这个逻辑
--是线性执行。假设存储过程ProcA是一个事务，在返回结果集之前因为一些操作申请了
--一些排他锁，而ProcB为了返回结果又要用到这些锁，那会发生什么情况呢？
--发生的情况会是连接A在等线程把连接B上的结果读出来，再来处理结果集A，而连接B
--等待连接A完成事务后再释放锁。双方相互等待，产生死锁




--（2）两个线程间的死锁
--如果应用有两个线程，每个线程各开一个数据库连接，那上面的逻辑不会出问题。
--因为运行ProcA的那个线程会先做完，释放阻塞住连接B的锁，让B也能够接着跑完。
--但是假设有下列逻辑：

--线程A：建立数据库连接A，不断读取表A，按条取出记录，做一定处理后发给线程B
--的输入缓存

--线程B：建立数据库连接B，从输入缓存读取数据，依据收到的记录对表A进行修改

--这个逻辑会产生什么问题呢？我们知道表格修改会在表上申请一些排他锁。如果
--线程A正在读取这条记录，修改动作会被阻塞住。这时候线程B就会进入等待状态。
--但是线程A需要线程B把输入缓存清空后才能写入。如果线程B还没得及清空，他
--也不得不等待，这时候也会产生死锁（在SQL里是一个阻塞）


--线程B不能清空缓冲，线程A读到的数据放不进来
--线程B把缓冲区的结果做处理，修改表A数据
--线程A将表A里的结果取出来放在缓冲区里
--线程A的查询阻塞住了线程B的修改动作


--解决方法：
--为了避免死锁，应用在调用SQL的时候设置执行超时，并写好错误处理机制，一旦
--死锁发生，SQL的操作在等待一段时间后会因为超时而放弃，并且释放出SQL内部的
--资源，解决死锁


--小结：
--解决阻塞需要在应用程序层面做很多工作，不能光从SQL端努力是不能解决阻塞问题的
--例如应用在做连接的时候选择什么隔离级别，事务开始和结束的时间点选择，连接的
--建立和回收机制，指令复杂度的控制等。应用程序还应该考虑到控制结果集的大小，
--并及时从SQL端取走数据。还要考虑SQL指令执行时间长短控制，以及发生超时或其他
--意外后的错误处理机制等。尤其是对高并发，高响应要求的关键业务系统，在设计应用
--选择的是能够满足业务需求的最低隔离级别，事务大小已经控制到了最小的粒度。
--而运行的语句也要有良好的数据库设计，保证他不会随着数据库的增大和用户量的增多
--占用更多资源和运行时间。

--如果做不到这几点，那么当用户量和数据量增大的时候，性能也越来越慢

--有时候一些应用会利用现成的数据库控件，以提高开发效率。某些控件会把数据库访问
--功能打包，例如什么时候建立连接，什么时候开启事务，数据结果集用什么方式取回，
--都由控件底层完成，对开发者透明。如果是要求开发性能比较高的数据库应用，必须
--仔细评估这些控件的处理机制。例如，有些控件会默认开启隐式事务（implicit transaction）
--使得事务的长度大大加长。有些控件会大量使用数据库端游标，让SQL必须为这些
--游标维护相应的资源。有些控件默认使用Unicode传递参数，妨碍了非Unicode字段
--上的索引的使用。有些控件甚至默认使用最高的事务隔离级别，使得并发度很低。
--这些都需要开发者做到心知肚明，达到扬长避短的效果



--对于一个设计良好的应用程序，SQL是完全有能力支持高并发的繁忙的业务系统的。
--本人曾多次看到每秒能够处理完3000甚至4000多个批处理（BATCH）的SQL应用系统
--前端并发的SQL连接数目（不考虑连接池等客户端机制帮助SQL减少实际并发用户数目）
--可以超过2000多个并发连接。这种系统一般来讲设计得比较精细，事务控制紧凑，
--程序错误控制严密，并且严格分离了OLTP系统和数据仓库系统这两类需求，从而
--严格控制指令的复杂度，有些还会通过定期归档的方式控制OLTP数据库的大小
--这些都能够帮助一个数据库应用有效地避免阻塞问题

 

sys.syslockinfo (Transact-SQL)
SQL Server 2014

锁资源类型：
1 = NULL 资源（未使用）
2 = 数据库
3 = 文件
4 = 索引/KEY
5 = 表
6 = 页
7 = 键
8 = 区
9 = RID（行 ID）
10 = 应用程序


KEY（出现这种资源说明锁是在一个索引上面）：数据库引擎内部使用的十六进制数。这个值和sys.partions.hobt_id相对应
出现这种资源说明锁是在一个索引上面。通过查询sys.partitions视图里相应的
object_id和index_id就能找到这个索引
例子里有个KEY(46000227c460)， 他的dbid是9，object_id是834674039，index_id是1
从上面sys.indexes的结果，就可以知道他是PK_Employee_EmployeeID_Demo_BTree这个
索引