数据库堵塞和死锁详解

数据库阻塞:第一个连接占有资源没有释放，而第二个连接需要获取这个资源。如果第一个连接没有提交或者回滚，第二个连接会一直等待下去，直到第一个连接释放该资源为止。对于阻塞，数据库无法处理，所以对数据库操作要及时地提交或者回滚。

阻塞示例：

第一个链接执行：

--创建表
CREATE TABLE [dbo].[A_Table](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [NAME] [Nvarchar](50) NOT NULL ,
    [CreateTime] [datetime] NOT NULL)

--插入数据
INSERT INTO [dbo].[A_Table]
           ([NAME],[CreateTime])
     VALUES
           ('joye1',GETDATE())
INSERT INTO [dbo].[A_Table]
           ([NAME],[CreateTime])
     VALUES
           ('joye2',GETDATE())
INSERT INTO [dbo].[A_Table]
           ([NAME],[CreateTime])
     VALUES
           ('joye3',GETDATE())

select * from [dbo].[A_Table]

begin tran
update [dbo].[A_Table] set [NAME]='joye' where ID=2
--commit tran第一个连接，不提交或者回滚：

第二个链接执行：

update [dbo].[A_Table] set [NAME]='joye1' where ID=2

 第二个一直在执行中，因为第一个连接占有A_Table表没有释放资源，而第二个连接一直在等待第一个连接释放该资源。默认情况下除非设置了LOCK_TIMEOUT，否则事务会一直等待下去。

避免阻塞的最好方式就是及时的提交事务和回滚事务。

查询阻塞的sql语句：

--sql查询阻塞的sql语句
SELECT SPID=p.spid,
       DBName = convert(CHAR(20),d.name),
       ProgramName = program_name,
       LoginName = convert(CHAR(20),l.name),
       HostName = convert(CHAR(20),hostname),
       Status = p.status,
       BlockedBy = p.blocked,
       LoginTime = login_time,
       QUERY = CAST(TEXT AS VARCHAR(MAX))
FROM   MASTER.dbo.sysprocesses p
       INNER JOIN MASTER.dbo.sysdatabases d
         ON p.dbid = d.dbid
       INNER JOIN MASTER.dbo.syslogins l
         ON p.sid = l.sid
       CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE  p.blocked = 0
       AND EXISTS (SELECT 1
                   FROM   MASTER..sysprocesses p1
                   WHERE  p1.blocked = p.spid)

--查询引起阻塞和被阻塞的sql
SELECT wt.blocking_session_id AS BlockingSessesionId
      ,sp.program_name AS Blocking_ProgramName
      ,COALESCE(sp.LOGINAME, sp.nt_username)  AS Blocking_HostName    
      ,ec1.client_net_address  AS ClientIpAddress
      ,db.name  AS DatabaseName        
      ,wt.wait_type AS WaitType                    
      ,ec1.connect_time AS BlockingStartTime
      ,wt.WAIT_DURATION_MS/1000 AS WaitDuration
      ,ec1.session_id   AS BlockedSessionId
      ,h1.TEXT  AS BlockedSQLText
      ,h2.TEXT  AS BlockingSQLText
FROM sys.dm_tran_locks  AS tl WITH(NOLOCK)
   INNER JOIN sys.databases AS db  WITH(NOLOCK)
  ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt  WITH(NOLOCK)
  ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.dm_exec_connections  ec1 WITH(NOLOCK)
  ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections  ec2 WITH(NOLOCK)
  ON ec2.session_id = wt.blocking_session_id
LEFT OUTER JOIN master.dbo.sysprocesses AS sp WITH(NOLOCK)
  ON SP.spid = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1 
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2 

 

数据库死锁：第一个连接占有资源没有释放，准备获取第二个连接所占用的资源，而第二个连接占有资源没有释放， 准备获取第一个连接所占用的资源。这种互相占有对方需要获取的资源的现象叫做死锁。对于死锁，数据库处理方法：牺牲一个。

死锁实例：

创建B表和C表

--创建B表
CREATE TABLE [dbo].[B_Table](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [NAME] [Nvarchar](50) NOT NULL ,
    [CreateTime] [datetime] NOT NULL)

--插入数据
INSERT INTO [dbo].[B_Table]
           ([NAME],[CreateTime])
     VALUES
           ('joye1',GETDATE())
INSERT INTO [dbo].[B_Table]
           ([NAME],[CreateTime])
     VALUES
           ('joye2',GETDATE())
INSERT INTO [dbo].[B_Table]
           ([NAME],[CreateTime])
     VALUES
           ('joye3',GETDATE())

select * from [dbo].[B_Table]

begin tran
update [dbo].[B_Table] set [NAME]='joye' where ID=2
waitfor  delay '0:0:20'
update [dbo].[C_Table] set [NAME]='joye' where ID=2
--commit tran

--创建C表
CREATE TABLE [dbo].[C_Table](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [NAME] [Nvarchar](50) NOT NULL ,
    [CreateTime] [datetime] NOT NULL)

--插入数据
INSERT INTO [dbo].[C_Table]
           ([NAME],[CreateTime])
     VALUES
           ('joye1',GETDATE())
INSERT INTO [dbo].[C_Table]
           ([NAME],[CreateTime])
     VALUES
           ('joye2',GETDATE())
INSERT INTO [dbo].[C_Table]
           ([NAME],[CreateTime])
     VALUES
           ('joye3',GETDATE())

select * from [dbo].[C_Table]

begin tran
update [dbo].[C_Table] set [NAME]='joye' where ID=2
waitfor  delay '0:0:20'
update [dbo].[B_Table] set [NAME]='joye' where ID=2
--commit tran

执行结果：

一个事务执行成功，如图

 

下个事务被牺牲：

消息 1205，级别 13，状态 45，第 26 行
事务(进程 ID 58)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。

因为第一个连接占有表B，想要获取表C的资源，而第二个连接占有表C,想要获取表B的资源，这种互相占有对方想要获取的资源，满足死锁现象。最后第一个连接执行成功，而第二个连接事务被选作死锁牺牲品。两个或多个进程之间的相互等待。但是由于SQL Server有数据库引擎死锁检测方案，至少5秒钟会消除一个现有的死锁。对性能的影响往往没有阻塞严重。

避免死锁：

尽管死锁不能完全避免，但是可以把机会降到最低：

按同一顺序访问对象（如果所有并发事务按同一顺序访问对象，则发生死锁的可能性会降低。）

避免事务中的用户交互（避免编写包含用户交互的事务，因为没有用户干预的批处理的运行速度远快于必须等待用户响应时的查询速度。

保持事务简短并处于一个批处理中（运行时间越长，等待时间就越长，造成死锁的机会就越高。）

使用较低的隔离级别(确定事务能否在低隔离级别上运行。尽可能使用较低的隔离级别。)

调整语句的执行计划，减少锁的申请数目(可以从执行计划中找出哪些资源耗得比较多。此时锁的数目也会相应增多)

查询死锁：

-- 查询死锁
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName    
from sys.dm_tran_locks   
where resource_type='OBJECT' 

--查询主机名，数据库等信息
exec sp_who2 59

--杀死死锁进程
kill 59

查询如下：

 也可使用SQL Server Profile 跟踪阻塞或者死锁的SQL