SQL SERVER 阻塞与死锁

在 SQL Server 数据库中，死锁是两个或多个事务在尝试获取资源时相互阻塞的现象。

死锁会导致事务执行效率降低。

要解决SQL Server 中的死锁问题，可以尝试以下方法：1. 分析死锁：使用 SQL Server Profiler 或 Extended Events 追踪死锁事件，找出导致死锁的事务和资源。

分析完后，针对性地解决死锁问题。

1. 优化锁的粒度：使用较低级别的锁，如行锁（ROWLOCK），代替页面锁或表锁，减少锁定范围，提高并发性。

请注意，这也可能会导致锁争用和事务延迟。

1. 使用 READ COMMITTED SNAPSHOT 或 SNAPSHOT 事务隔离级别：这可以将读取操作与其他事务隔离，以减少锁定冲突。

复制更新时，仍然需要锁定资源，但其他读取事务不会受到阻塞。

1. 保持事务简短并减少锁定时间：缩短事务持续时间，减少锁定资源的时间。

这有助于减少因事务阻塞而导致的死锁风险。

1. 按照相同的顺序访问资源：按照相同的顺序对资源进行加锁可以避免死锁。

这样，在任何给定时刻，事务只需要等待另一个事务释放钥匙，而不是陷入死循环。

1. 使用 TRY...CATCH 语句监视死锁错误：对执行事务的代码进行异常处理，并在TRY...CATCH 语句中处理死锁错误。

这意味着如果死锁发生，事务将被捕获并显示错误信息。

根据需求，可以选择重试事务以处理死锁。

1. 使用 NOLOCK 选项：对于查询只读的情况，可以尝试使用 NOLOCK 选项进行查询。

这允许读取未提交的数据，避免发生死锁。

请注意，这可能会导致脏读问题。

在使用 NOLOCK 之前，务必权衡一下使用该选项所带来的风险。

解决 SQL Server 数据库死锁问题需要针对具体情况进行分析和调整。

对数据库表和事务进行优化，根据实际应用场景选择适当的锁策略，有助于降低死锁的发生概率。

在确保数据完整性的前提下，采取上述方法之一或多个来解决死锁问题。

SQLServer⽇常维护--查询当前正在执⾏的语句、死锁、堵塞查询当前正在执⾏的语句：1SELECT2 der.[session_id],der.[blocking_session_id],3 stwaittype,sp.hostname,sp.program_name,sp.loginame,4 der.[start_time]AS'开始时间',5 der.[status]AS'状态',6 dest.[text]AS'sql语句',7DB_NAME(der.[database_id]) AS'数据库名',8 der.[wait_type]AS'等待资源类型',9 der.[wait_time]AS'等待时间',10 der.[wait_resource]AS'等待的资源',11 der.[logical_reads]AS'逻辑读次数'12FROM sys.[dm_exec_requests]AS der13INNER JOIN master.dbo.sysprocesses AS sp ON der.session_id=sp.spid14CROSS APPLY sys.[dm_exec_sql_text](der.[sql_handle]) AS dest15--WHERE [session_id]>50 AND session_id<>@@SPID16ORDER BY der.[session_id]17GOView Code是否堵塞：1SELECT spid,blocked,waittime,waittype,waitresource,p.dbid,cpu,physical_io,memusage,open_tran2 ,status,login_time,last_batch,hostname,program_name,hostprocess,loginame,cmd,text3FROM master.dbo.sysprocesses p CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) s4WHERE blocked >0OR spid IN(SELECT blocked FROM master.dbo.sysprocesses WHERE blocked >0)5goView Code是否有未提交事物：1select spid,blocked,waittime,waittype,waitresource,p.dbid,cpu,physical_io,memusage,open_tran2 ,status,login_time,last_batch,hostname,program_name,hostprocess,loginame,cmd,text3from master.dbo.sysprocesses p cross apply sys.dm_exec_sql_text(p.sql_handle) s4where open_tran <>05goView Code死锁：1--drop table #deadlock23CREATE TABLE #deadlock(LogDate DATETIME,ProcessInfo VARCHAR(20),Text VARCHAR(2000))4go5INSERT INTO #deadlock EXEC xp_readerrorlog 0,1,'deadlock victim',NULL,'2016-07-20 14:00:00','2016-07-30','DESC'6goView Code死锁加nolock优化。

SQLServer死锁产⽣原因及解决⽅法⼀、什么是死锁死锁是指两个或两个以上的进程在执⾏过程中,因争夺资源⽽造成的⼀种互相等待的现象,若⽆外⼒作⽤,它们都将⽆法推进下去.此时称系统处于死锁状态或系统产⽣了死锁,这些永远在互相等的进程称为死锁进程.⼆、死锁产⽣的四个必要条件互斥条件：指进程对所分配到的资源进⾏排它性使⽤，即在⼀段时间内某资源只由⼀个进程占⽤。

如果此时还有其它进程请求资源，则请求者只能等待，直⾄占有资源的进程⽤毕释放请求和保持条件：指进程已经保持⾄少⼀个资源，但⼜提出了新的资源请求，⽽该资源已被其它进程占有，此时请求进程阻塞，但⼜对⾃⼰已获得的其它资源保持不放不剥夺条件：指进程已获得的资源，在未使⽤完之前，不能被剥夺，只能在使⽤完时由⾃⼰释放环路等待条件：指在发⽣死锁时，必然存在⼀个进程——资源的环形链，即进程集合{P0，P1，P2，···，Pn}中的P0正在等待⼀个P1占⽤的资源；P1正在等待P2占⽤的资源，……，Pn正在等待已被P0占⽤的资源这四个条件是死锁的必要条件，只要系统发⽣死锁，这些条件必然成⽴，⽽只要上述条件之⼀不满⾜，就不会发⽣死锁。

三、如何处理死锁1) 锁模式1. 共享锁（S）由读操作创建的锁，防⽌在读取数据的过程中，其它事务对数据进⾏更新；其它事务可以并发读取数据。

共享锁可以加在表、页、索引键或者数据⾏上。

在SQL SERVER默认隔离级别下数据读取完毕后就会释放共享锁，但可以通过锁提⽰或设置更⾼的事务隔离级别改变共享锁的释放时间。

2.独占锁(X)对资源独占的锁，⼀个进程独占地锁定了请求的数据源，那么别的进程⽆法在此数据源上获得任何类型的锁。

独占锁⼀致持有到事务结束。

3.更新锁(U)更新锁实际上并不是⼀种独⽴的锁，⽽是共享锁与独占锁的混合。

当SQL SERVER执⾏数据修改操作却⾸先需要搜索表以找到需要修改的资源时，会获得更新锁。

更新锁与共享锁兼容，但只有⼀个进程可以获取当前数据源上的更新锁，其它进程⽆法获取该资源的更新锁或独占锁，更新锁的作⽤就好像⼀个序列化阀门（serialization gate），将后续申请独占锁的请求压⼊队列中。

死锁和阻塞的概念及区别
死锁和阻塞是两个与并发编程相关的概念，它们的主要区别如下：
1. 概念：
- 死锁：指在并发系统中，两个或多个进程因为互相等待对方释放资源而无法继续执行的状态。

- 阻塞：指一个进程或线程在执行过程中，因为某种原因而无法继续执行，需要等待某个条件满足后才能继续执行。

2. 原因：
- 死锁：通常是由于进程之间的相互竞争和独占资源造成的。

- 阻塞：通常是由于某个条件不满足，需要等待其他事件的发生。

3. 影响：
- 死锁：会导致资源无法释放，系统停滞，进程无法继续运行。

- 阻塞：只会影响当前进程，等待条件满足后，进程可以继续执行。

4. 解决方法：
- 死锁：可以采用死锁避免、死锁检测、死锁恢复等方法来解决。

- 阻塞：可以通过等待条件满足后继续执行，或者使用非阻塞IO等技术来解决。

总的来说，死锁是由于多个进程相互等待对方释放资源而无法继续执行，而阻塞是一个进程由于某种原因无法继续执行，需要等待条件满足后才能继续执行。

死锁通常会导致系统停滞，而阻塞只会影响当前进程。

SQLServer2008数据库查看死锁、堵塞的SQL语句死锁和堵塞⼀直是性能测试执⾏中关注的重点。

下⾯是我整理的监控sql server数据库，在性能测试过程中是否出现死锁、堵塞的SQL语句，还算⽐较准备，留下来备⽤。

--每秒死锁数量SELECT * FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Number of Deadlocksc%';--查询当前阻塞WITH CTE_SID ( BSID, SID, sql_handle )AS ( SELECT blocking_session_id ,session_id ,sql_handleFROM sys.dm_exec_requestsWHERE blocking_session_id <> 0UNION ALLSELECT A.blocking_session_id ,A.session_id ,A.sql_handleFROM sys.dm_exec_requests AJOIN CTE_SID B ON A.SESSION_ID = B.BSID)SELECT C.BSID ,C.SID ,S.login_name ,S.host_name ,S.status ,S.cpu_time ,S.memory_usage ,st_request_start_time ,st_request_end_time ,S.logical_reads ,S.row_count ,q.textFROM CTE_SID CJOIN sys.dm_exec_sessions S ON C.sid = s.session_idCROSS APPLY sys.dm_exec_sql_text(C.sql_handle) QORDER BY sid在压⼒测试过程中，不间断的按F5键执⾏上⾯的SQL语句，如果出现死锁或者堵塞现象，就会在执⾏结果中罗列出来。

SqlServer阻塞的常见原因和解决办法阻塞的常见原因和解决办法：1. 由于语句运⾏时间太长⽽导致的阻塞，语句本⾝在正常运⾏中，只须等待某些系统资源解决办法：a. 语句本⾝有没有可优化的空间b. Sql Server 整体性能如何，是不是有资源瓶颈影响了语句执⾏速度，如内存、硬盘和 CPU 等2. 由于⼀个未按预期提交的事务导致的阻塞这⼀类阻塞的特征，就是问题连接早就进⼊了空闲状态(sysprocesses.status='sleeping'和sysprocesses.cms='awaiting command')，但是，如果检查 sysprocesses.open_tran,就会发现它不为0，以及事务没有提交。

这类问题很多都是因为应⽤端遇到了⼀个执⾏超时，或者其他原因，当时执⾏的语句倍提前终⽌了，但是连接还保留着。

应⽤没有跟随发来的事务提交或回滚指令，导致⼀个事务被遗留在 Sql Server ⾥。

解决办法：应⽤程序本⾝必须意识到任何语句都有可能遇到意外终⽌的情况，做好错误处理⼯作。

这些⼯作包括：· 在做 Sql Server 调⽤的时候，须加上错误捕捉和处理语句：If @@Trancount>0 RollBack Tran;(在程序中设置If @@Error<>0 Rollback Tran; 并不总是能执⾏到该语句)· 设置连接属性"Set XACT_ABORT ON"。

如果没有办法很规范应⽤程序的错误扑捉和处理语句，⼀个最快的⽅法就是在每个连接建⽴以后，或是容易出问题的存储过程开头，运⾏ "Set XACT_ABORT ON"·考虑是否要关闭连接池。

发⼀句 sp_reset_connection 命令清理当前连接上次遗留下来的所有对象，包括回滚未提交的事务。

3. 由于客户端没有及时把结果集取出⽽导致的语句长时间运⾏语句在 Sql Server 内执⾏总时间不仅包含 Sql Server 的执⾏时间，还包含把结果集发给客户端的时间。

Sqlserver锁机制一、什么是锁锁定是Microsoft SQL Server 数据库引擎用来同步多个用户同时对同一个数据块的访问的一种机制。

在事务获取数据块当前状态的依赖关系（比如通过读取或修改数据）之前，它必须保护自己不受其他事务对同一数据进行修改的影响。

事务通过请求锁定数据块来达到此目的。

锁有多种模式，如共享或独占。

锁模式定义了事务对数据所拥有的依赖关系级别。

如果某个事务已获得特定数据的锁，则其他事务不能获得会与该锁模式发生冲突的锁。

如果事务请求的锁模式与已授予同一数据的锁发生冲突，则数据库引擎实例将暂停事务请求直到第一个锁释放。

当事务修改某个数据块时，它将持有保护所做修改的锁直到事务结束。

事务持有（所获取的用来保护读取操作的）锁的时间长度，取决于事务隔离级别设置。

一个事务持有的所有锁都在事务完成（无论是提交还是回滚）时释放。

应用程序一般不直接请求锁。

锁由数据库引擎的一个部件（称为“锁管理器”）在内部管理。

当数据库引擎实例处理Transact-SQL 语句时，数据库引擎查询处理器会决定将要访问哪些资源。

查询处理器根据访问类型和事务隔离级别设置来确定保护每一资源所需的锁的类型。

然后，查询处理器将向锁管理器请求适当的锁。

如果与其他事务所持有的锁不会发生冲突，锁管理器将授予该锁。

二、锁粒度和层次结构SQL Server 数据库引擎具有多粒度锁定，允许一个事务锁定不同类型的资源。

为了尽量减少锁定的开销，数据库引擎自动将资源锁定在适合任务的级别。

锁定在较小的粒度（例如行）可以提高并发度，但开销较高，因为如果锁定了许多行，则需要持有更多的锁。

锁定在较大的粒度（例如表）会降低了并发度，因为锁定整个表限制了其他事务对表中任意部分的访问。

但其开销较低，因为需要维护的锁较少。

数据库引擎通常必须获取多粒度级别上的锁才能完整地保护资源。

这组多粒度级别上的锁称为锁层次结构。

例如，为了完整地保护对索引的读取，数据库引擎实例可能必须获取行上的共享锁以及页和表上的意向共享锁。

本帖提供两种做法，可避免在SQL Server 事务锁定时产生的不正常或长时间阻塞，让用户和程序也无限期等待，甚至引起connection pooling 连接数超过容量。

所谓的「阻塞」，是指当一个数据库会话中的事务，正在锁定其他会话事务想要读取或修改的资源，造成这些会话发出的请求进入等待的状态。

SQL Server 默认会让被阻塞的请求无限期地一直等待，直到原来的事务释放相关的锁，或直到它超时(根据SET LOCK_TIMEOUT，本文后续会提到)、服务器关闭、进程被杀死。

一般的系统中，偶尔有短时间的阻塞是正常且合理的；但若设计不良的程序，就可能导致长时间的阻塞，这样就不必要地锁定了资源，而且阻塞了其他会话欲读取或更新的需求。

遇到这种情况，可能就需要手工排除阻塞的状态，而本文接下来要介绍两种排除阻塞的做法。

日前公司server-side 有组件，疑似因撰写时exception-handling 做得不周全，导致罕见的特殊例外发生时，让SQL Server 的事务未执行到cmmmit 或rollback，造成某些表或记录被「锁定(lock)」。

后来又有大量的request，要透过代码访问这些被锁定的记录，结果造成了严重的长时间「阻塞」，最后有大量process (进程) 在SQL Server 呈现「等待中(WAIT)」的状态。

由于SQL Server 的「事务隔离级别」默认是READ COMMITTED (事务期间别人无法读取)，加上SQL Server 的锁定造成阻塞时，默认是别的进程必须无限期等待(LOCK_TIMEOUT = -1)。

结果这些大量的客户端request 无限期等待永远不会提交或回滚的事务，并一直占用着connection pool 中的资源，最后造成connection pooling 连接数目超载。

查了一些书，若我们要查询SQL Server 目前会话中的lock 超时时间，可用以下的命令：SELECT @@LOCK_TIMEOUT执行结果默认为-1，意即欲访问的对象或记录被锁定时，会无限期等待。

sqlserver数据库死锁解决方法
在使用sqlserver数据库时，可能会遇到死锁的问题，这种情况通常发生在多个并发用户同时访问同一个资源时，其中一个用户的操作被阻塞，导致其他用户也无法继续执行。

下面是几种解决sqlserver数据库死锁的方法：
1. 数据库设计优化
在设计数据库时应该考虑到并发访问的情况，尽量避免多个用户同时对同一个资源进行修改，可以通过合理的表设计和索引设计来提高并发性能。

2. 事务控制
对于频繁发生死锁的操作，可以将其放在一个事务中，并使用合理的事务隔离级别来控制并发读写，例如使用“读已提交”或“可重复读”级别，避免使用“串行化”级别。

3. 优化查询语句
优化查询语句可以减少死锁的发生，例如使用合理的索引和查询语句，
避免使用过多的子查询和连接操作，以及避免使用不必要的锁。

4. 限制并发访问
可以通过设置时间限制、并发数量限制等方式来限制并发访问，减少死锁的发生。

5. 监控和诊断
对于频繁发生死锁的情况，可以使用sqlserver的性能监视器和诊断工具来进行监控和诊断，找出问题的原因并进行调整。

总结：
死锁是一种常见的数据库并发性能问题，要解决这个问题，需要综合考虑数据库设计优化、事务控制、查询语句优化、并发访问限制和监控诊断等多方面的因素。

通过合理的调整和优化，可以提高数据库的并发性能，避免死锁的发生。