- 死锁的定义: 当多个进程同时访问一个数据库时,其中的每个进程拥有的资源都是其他进程所需的,由此造成的每个进程都无法继续下去的情况。
- 死锁产生的原因:
一般情况只发生锁超时,就是一个进程需要访问数据库表或者字段的时候,另外一个程序正在执行带锁的访问(比如修改数据),那么这个进程就会等待,当等了很久锁还没有解除的话就会锁超时,报告一个系统错误,拒绝执行相应的SQL操作。所谓死锁:是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程。由于资源占用是互斥的,当某个进程提出申请资源后,使得有关进程在无外力协助下,永远分配不到必需的资源而无法继续运行,这就产生了一种特殊现象死锁。 一种情形,此时执行程序中两个或多个线程发生永久堵塞(等待),每个线程都在等待被其他线程占用并堵塞了的资源。例如,如果线程A锁住了记录1并等待记录2,而线程B锁住了记录2并等待记录1,这样两个线程就发生了死锁现象。计算机系统中,如果系统的资源分配策略不当,更常见的可能是程序员写的程序有错误等,则会导致进程因竞争资源不当而产生死锁的现象。锁有多种实现方式,比如意向锁,共享-排他锁,锁表,树形协议,时间戳协议等等。锁还有多种粒度,比如可以在表上加锁,也可以在记录上加锁。
- 死锁产生的四个必要条件:
1)互斥条件:指进程对所分配到的资源进行排它性使用,即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源,则请求者只能等待,直至占有资源的进程用毕释放。
2)请求和保持条件:指进程已经保持至少一个资源,但又提出了新的资源请求,而该资源已被其它进程占有,此时请求进程阻塞,但又对自己已获得的其它资源保持不放。
3)不剥夺条件:指进程已获得的资源,在未使用完之前,不能被剥夺,只能在使用完时由自己释放。
4)环路等待条件:指在发生死锁时,必然存在一个进程——资源的环形链,即进程集合{P0,P1,P2,···,Pn}中的P0正在等待一个P1占用的资源;P1正在等待P2占用的资源,……,Pn正在等待已被P0占用的资源。
-
减少和防止死锁产生的方法:
在系统设计、进程调度等方面注意如何不让这四个必要条件成立,如何确定资源的合理分配算法,避免进程永久占据系统资源。此外,也要防止进程在处于等待状态的情况下占用资源,在系统运行过程中,对进程发出的每一个系统能够满足的资源申请进行动态检查,并根据检查结果决定是否分配资源,若分配后系统可能发生死锁,则不予分配,否则予以分配 。因此,对资源的分配要给予合理的规划。
-
最大限度降低死锁的产生的方法:
对产生的死锁的建议:a. 按同一顺序访问对象:
按同一顺序访问对象也就是:第一个事务提交或回滚后,第二个事务继续进行,这样不会发生死锁。
如果所有并发事务按同一顺序访问对象,则发生死锁的可能性会降低。例如,如果两个并发事务获得 Supplier 表上的锁,然后获得 Part 表上的锁,则在其中一个事务完成之前,另一个事务被阻塞在 Supplier 表上。第一个事务提交或回滚后,第二个事务继续进行。不发生死锁。将存储过程用于所有的数据修改可以标准化访问对象的顺序。
b. 避免事务中的用户交互:
避免编写包含用户交互的事务,因为运行没有用户交互的批处理的速度要远远快于用户手动响应查询的速度,例如答复应用程序请求参数的提示。例如,如果事务正在等待用户输入,而用户去吃午餐了或者甚至回家过周末了,则用户将此事务挂起使之不能完成。这样将降低系统的吞吐量,因为事务持有的任何锁只有在事务提交或回滚时才会释放。即使不出现死锁的情况,访问同一资源的其它事务也会被阻塞,等待该事务完成。
c. 保持事务简短并在一个批处理中:在同一数据库中并发执行多个需要长时间运行的事务时通常发生死锁。事务运行时间越长,其持有排它锁或更新锁的时间也就越长,从而堵塞了其它活动并可能导致死锁。 保持事务在一个批处理中,可以最小化事务的网络通信往返量,减少完成事务可能的延迟并释放锁。d. 使用低隔离级别:确定事务是否能在更低的隔离级别上运行,执行提交读取允许事务读取另一个事务已读取(未修改)的数据,而不必等待第一个事务完成。使用较低的隔离级别(例如提交读取)而不使用较高的隔离级别(例如可串行读)可以缩短持有共享锁的时间,从而降低了锁定争夺。e. 使用绑定连接:使用绑定连接使同一应用程序所打开的两个或多个连接可以相互合作。次级连接所获得的任何锁可以象由主连接获得的锁那样持有,反之亦然,因此不会相互阻塞。
(1)对于频繁使用的表使用集簇化的索引;
(2)设法避免一次性影响大量记录的T-SQL语句,特别是INSERT和UPDATE语句;
(3)设法让UPDATE和DELETE语句使用索引;
(4)使用嵌套事务时,避免提交和回退冲突;
(5)对一些数据不需要及时读取更新值的表在写SQL的时候在表后台加上(nolock),如:Select * from tableA(nolock)
用存储过程查出引起死锁的进程和SQL语句
假如发生了死锁,我们怎么去检测具体发生死锁的是哪条SQL语句或存储过程?此时我们可以使用以下存储过程来检测,就可以查出引起死锁的进程和SQL语句。
- use master
- go
- create procedure sp_who_lock
- as
- begin
- declare @spid int,@bl int,
- @intTransactionCountOnEntry int,
- @intRowcount int,
- @intCountProperties int,
- @intCounter int
- create table #tmp_lock_who (
- id int identity(1,1),
- spid smallint,
- bl smallint)
- IF @@ERROR<>0 RETURN @@ERROR
- insert into #tmp_lock_who(spid,bl) select 0 ,blocked
- from (select * from sysprocesses where blocked>0 ) a
- where not exists(select * from
- (select * from sysprocesses where blocked>0 ) b
- where a.blocked=spid)
- union select spid,blocked from sysprocesses where blocked>0
- IF @@ERROR<>0 RETURN @@ERROR
- -- 找到临时表的记录数
- select @intCountProperties = Count(*),@intCounter = 1
- from #tmp_lock_who
- IF @@ERROR<>0 RETURN @@ERROR
- if @intCountProperties=0
- select '现在没有阻塞和死锁信息' as message
- -- 循环开始
- while @intCounter <= @intCountProperties
- begin
- -- 取第一条记录
- select @spid = spid,@bl = bl
- from #tmp_lock_who where Id = @intCounter
- begin
- if @spid =0
- select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10))
- + '进程号,其执行的SQL语法如下'
- else
- select '进程号SPID:'+ CAST(@spid AS VARCHAR(10))
- + '被进程号SPID:'+ CAST(@bl AS VARCHAR(10))
- + '阻塞,其当前进程执行的SQL语法如下'DBCC INPUTBUFFER
与锁定有关的两个问题--死锁和阻塞
死锁
死锁是一种条件,不仅仅是在关系数据库管理系统 (RDBMS) 中发生,在任何多用户系统中都可以发生的。当两个用户(或会话)具有不同对象的锁,并且每个用户需要另一个对象的锁时,就会出现死锁。每个用户都等待另一个用户释放他的锁。当两个连接陷入死锁时,Microsoft® SQL Server? 会进行检测。其中一个连接被选作死锁牺牲品。该连接的事务回滚,同时应用程序收到错误。
如果死锁变成单个公用事件,而且它们的回滚造成过多的性能降级,那么就需要再次进行深入彻底的调查。使用跟踪标记 1204。例如,下面的命令从命令提示符启动 SQL Server,并启用跟踪标记 1204:
c:\mssql\binn\sqlservr -T1204
现在所有消息都会显示在启动 SQL Server 的控制台屏幕上和错误日志中。
使用分布式事务时,也可能发生死锁。
阻塞
任何基于锁的并发系统都不可避免地具有可能在某些情况下发生阻塞的特征。当一个连接控制了一个锁,而另一个连接需要冲突的锁类型时,将发生阻塞。其结果是强制第二个连接等待,或在第一个连接上阻塞。
在本主题中,术语"连接"是指数据库的单个登录会话。每个连接都作为系统进程 ID (SPID) 出现。尽管每一个 SPID 一般都不是单独的进程上下文,但这里常常用来指一个进程。更确切的说,每个 SPID 都是由服务器资源和数据结构(为给定客户单个连接的请求提供服务)组成。单个客户应用程序可能有一个或多个连接。就 SQL Server 而言,从单个客户机上的单个客户应用程序来的多个连接和从多个客户应用程序或多个客户机来的多个连接是没有区别的。不管是来自同一应用程序还是来自两台不同客户机上单独的应用程序,一个连接都可以阻塞另一个连接。