优化数据库死锁之必收藏篇，不看不收藏以后就用到怎办？

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

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

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%'

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等。

2. 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

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

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