从数据库应用系统查找解决阻塞问题

一个大型数据库应用系统做得不好，会遇到连接到server的进程被另一个正在读写数据库的进程阻塞。这种阻塞数量越来越多，以至于client端好像死机。连接server越来越慢或导致超时连接不成功是引起阻塞的重要原因。通过对多个已投入运行的数据库应用软件的修改优化，我们已能够解决这种问题。此方法主要从如下几个方面入手：
1． 检查所有存储过程。

 存储过程中如果有语句：

begin tran

 select ???from x where ???

 update y set ???

 if a条件 return（number）

 commit tran

那么a条件成立，则此存储过程运行时，对x，y表的锁将不会释放，从而阻塞其它进程。这时用“sp－who”看该“spid”进程状态应为“awaiting command”。应将该存储过程修改为

 “begin tran

 select ??? from x where ???

 update y set ???

 if a 

 begin 

 rollback tran

 return（number）

 end

 ???

 commit tran”



2． 检查存储过程，将大事物细小化。

金融、电信业务中，经常有一些批量业务，如代发工资、代扣话费等。这种业务一般要用到“cursor”，但应尽量避免以下这种情况：

 begin tran 

 select ??? from x where ???

 declare x－cursor cursor

for ???

 fetch x－cursor into ???

 while @@sqlstatus!=2

 begin

 update ???

 insert ???

 fetch x－cursor into 

 end

commit tran

因为，事物中使用的所有表的相关页在进程提交前一直被锁，并长时间阻塞所有访问该页的进程。应该为： 

 select ??? from x where ???

 declare x－cursor cursor

 for ???

fetch x－cursor into ???

 while @@sqlstatus!=2

 begin

 begin tran 

 update ???

 insert ???

 commit tran 

 fetch x－cursor into 

 end

经过这样修改，既可提高存储过程的执行速度，又不影响其它网点的业务，阻塞机会有非常明显的减少。

3． 使用sybase数据库设计调优策略及数据库应用调优策略。

sybase各种调优策略能够使存储过程的运行效率得到明显提高，运行速度大幅度提高，从而缩短阻塞时间及减少阻塞概率。如下几个方面会对解决阻塞有明显效果。

1） 数据库应用表的设计合理，应尽量避免大表间跨表操作。

2） 索引使用优化，提高读写速度。

3） 尽量做到索引覆盖查询。

4） 对引起阻塞业务相应存储过程，根据其特点，使用其他相应的优化措施。

5） 经常对应用表进行update statistic，sp－recompile，改善数据页的充满度，提高存储过程的运行效率。

4． 确定引起阻塞的存储过程和表的方法。

有了上述解决问题的办法，怎样从庞大的应用系统中，寻找相应的存储过程呢？

首先，当发生阻塞时要及时保留数据库系统运行的有关现场信息，包括：sp－who、sp－lock的运行结果、master库sysprocesses表的内容。

sp－who的运行结果可用来查出引起阻塞的进程的spid。通过blk域的值找对应的spid，如果blk值不为0，则该进程被spid=blk值的进程阻塞。这样找下去，直至找到spid对应的blk值为0，则此spid进程为引起阻塞进程。然后记录其spid、loginname、hostname、dbname、cmd等。

sp－lock的运行结果可用来查出对哪个表写操作引起阻塞。根据sp－who得到的spid，找sp－lock结果中对应的locktype为ex－table－blk的一行，取table－id值，通过object－name（table－id）得到表名。

master库sysprocesses表的内容可用来查出须修改优化的存储过程。根据spid找到相应行，记录其status、hostname、cmd、id、linenum等通过object－name（id）得到存储过程名。得到上述信息可对存储过程进行修改优化，解决阻塞问题（举例过程略）。


*****************************************************
推荐跟踪存储过程名另一个方法:
dbcc traceon(3604)
dbcc traceoff
权限要够哦 

(aladdin)
*****************************************************

 us sp_showplan spid,null,null,null display query plan  
 
( chenfeng825 
*****************************************************