为了避免数据脏读和幻读等问题,保证数据读写的准确性和安全性,我们在优化数据库的时候使用锁机制。但是在高并发执行的情况下,很多语句要同时读写和访问 数据资源,形成一种等待和竞争的关系。如果出现不默契的情况,死锁就应运而生。大家都想争夺资源,但谁都得不到。死锁会导致系统效率大大降低,而在数据库 为了解决死锁现象而杀掉相关进程的时候,会中断客户端正常操作,造成系统异常。
我们先介绍一种查看死锁的方法,通过创建和执行一下存储过程,准确的定位哪些语句相互死锁:
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 IF @@ERROR<>0 RETURN @@ERROR INSERTINTO #tmp_lock_who(spid,bl) IF @@ERROR<>0 RETURN @@ERROR — 找到临时表的记录数 IF @@ERROR<>0 RETURN @@ERROR IF @intCountProperties=0 — 循环开始 IF @spid =0 — 循环指针下移 DROP TABLE #tmp_lock_who RETURN 0 |
发现死锁,就需要解决,我们下面列出两种死锁的现象以及处理的办法。
- insert和select事务并发
测试现象:在查询分析器1中执行
DECLARE @au_id varchar(11), @au_lname varchar(40) SELECT @au_id = ‘111-11-1111’, @au_lname = ‘test1′ BEGIN TRANSACTION SELECT * COMMIT |
在查询分析器2中执行
DECLARE @au_id varchar(11), @au_lname varchar(40) SELECT @au_id = ‘111-11-1112’, @au_lname = ‘test2′ BEGIN TRANSACTION SELECT * –DELETE FROM authors WHERE au_id = ‘111-11-1111’ |
处理方法:查询不带锁“with(nolock)”,允许脏读,去掉事务,使用try catch等。
- 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 WAITFOR DELAY ’00:00:01’ SELECT * |
查询分析器2中执行
DECLARE @au_id varchar(11) , @au_lname varchar(40) , @i int SELECT @au_id = ‘111-11-1111’, @au_lname = ‘test1′ BEGIN TRANSACTION WHILE @i > 0 WAITFOR DELAY ’00:00:01’ SELECT * |
处理方法:需要为表的关键字段增加非聚集索引。
死锁的优化必须通过数据库结构设计来解决,死锁现象则需要在各种测试环境中反复模拟才能出现。所以死锁优化是个长期积累得过程。
评论