为了避免数据脏读和幻读等问题,保证数据读写的准确性和安全性,我们在优化数据库的时候使用锁机制。但是在高并发执行的情况下,很多语句要同时读写和访问数据资源,形成一种等待和竞争的关系。如果出现不默契的情况,死锁就应运而生。大家都想争夺资源,但谁都得不到。死锁会导致系统效率大大降低,而在数据库为了解决死锁现象而杀掉相关进程的时候,会中断客户端正常操作,造成系统异常。
我们先介绍一种查看死锁的方法,通过创建和执行一下存储过程,准确的定位哪些语句相互死锁:
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
处理方法:需要为表的关键字段增加非聚集索引。
死锁的优化必须通过数据库结构设计来解决,死锁现象则需要在各种测试环境中反复模拟才能出现。所以死锁优化是个长期积累得过程。