分享到：微信新浪微博QQ好友QQ空间腾讯微博复制网址

为了避免数据脏读和幻读等问题，保证数据读写的准确性和安全性，我们在优化数据库的时候使用锁机制。但是在高并发执行的情况下，很多语句要同时读写和访问 数据资源，形成一种等待和竞争的关系。如果出现不默契的情况，死锁就应运而生。大家都想争夺资源，但谁都得不到。死锁会导致系统效率大大降低，而在数据库 为了解决死锁现象而杀掉相关进程的时候，会中断客户端正常操作，造成系统异常。

我们先介绍一种查看死锁的方法，通过创建和执行一下存储过程，准确的定位哪些语句相互死锁：

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

INSERTINTO #tmp_lock_who(spid,bl)
SELECT  0 ,blocked
FROM (SELECT*
FROM sysprocesses
WHERE  blocked>0 )  AS A
WHERE not exists(SELECT1
FROM (SELECT*FROM sysprocesses WHERE  blocked>0 ) B
WHEREA.blocked=spid)
UNION ALL
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

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 (@bl )

— 循环指针下移
SET @intCounter = @intCounter + 1
END

DROP TABLE #tmp_lock_who

RETURN 0
END

发现死锁，就需要解决，我们下面列出两种死锁的现象以及处理的办法。

  1. insert和select事务并发

测试现象：在查询分析器1中执行

DECLARE @au_id varchar(11), @au_lname varchar(40)
SELECT @au_id = ‘111-11-1111’, @au_lname = ‘test1′

BEGIN TRANSACTION
INSERT Authors VALUES
(@au_id, @au_lname)
WAITFOR DELAY ’00:00:05’

SELECT *
FROM authors
WHERE au_lname LIKE ‘Test%’

阿里云-推广AD

COMMIT

在查询分析器2中执行

DECLARE @au_id varchar(11), @au_lname varchar(40)
SELECT @au_id = ‘111-11-1112’, @au_lname = ‘test2′

BEGIN TRANSACTION
INSERT Authors VALUES
(@au_id, @au_lname)
WAITFOR DELAY ’00:00:05’

SELECT *
FROM authors
WHERE au_lname LIKE ‘Test%’
COMMIT

–DELETE FROM authors WHERE au_id = ‘111-11-1111’
–DELETE FROM authors WHERE au_id = ‘111-11-1112’

处理方法：查询不带锁“with（nolock）”，允许脏读，去掉事务，使用try catch等。

  1. update和select事务并发

测试现象：在查询分析器1中执行

DECLARE  @au_id varchar(11)
, @au_lname varchar(40)
, @i   int
SELECT @au_id = ‘111-11-1112’, @au_lname = ‘test2′
SET @i = 5

BEGIN TRANSACTION

WHILE @i > 0
BEGIN
UPDATE Authors
SET au_lname = @au_lname
WHERE au_id = @au_id

WAITFOR DELAY ’00:00:01’

SELECT *
FROM authors
WHERE au_lname LIKE ‘Test%’
SET @i = @i -1
END
COMMIT

查询分析器2中执行

DECLARE  @au_id varchar(11)
, @au_lname varchar(40)
, @i   int

SELECT @au_id = ‘111-11-1111’, @au_lname = ‘test1′
SET @i = 5

BEGIN TRANSACTION

WHILE @i > 0
BEGIN
UPDATE Authors
SET au_lname = @au_lname
WHERE au_id = @au_id

WAITFOR DELAY ’00:00:01’

SELECT *
FROM authors
WHERE au_lname LIKE ‘Test%’
SET @i = @i -1
END
COMMIT

处理方法：需要为表的关键字段增加非聚集索引。

死锁的优化必须通过数据库结构设计来解决，死锁现象则需要在各种测试环境中反复模拟才能出现。所以死锁优化是个长期积累得过程。