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

转帖从数据库应用系统查找解决阻塞问题
　　一个大型数据库应用系统做得不好，会遇到连接到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）得到存储过程名。得到上述信息可对存储过程进行修改优化，解决阻塞问题（举例过程略）。