從數(shù)據(jù)庫應(yīng)用系統(tǒng)查找解決阻塞問題

jazy

一個大型數(shù)據(jù)庫應(yīng)用系統(tǒng)做得不好，會遇到連接到server的進程被另一個正在讀寫數(shù)據(jù)庫的進程阻塞。這種阻塞數(shù)量越來越多，以至于client端好像死機。連接server越來越慢或?qū)е鲁瑫r連接不成功是引起阻塞的重要原因。通過對多個已投入運行的數(shù)據(jù)庫應(yīng)用軟件的修改優(yōu)化，我們已能夠解決這種問題。此方法主要從如下幾個方面入手：
　　1． 檢查所有存儲過程。

　　 存儲過程中如果有語句：

　　begin tran

　　 select ???from x where ???

　　 update y set ???

　　 if a條件 return（number）

　　 commit tran

　　那么a條件成立，則此存儲過程運行時，對x，y表的鎖將不會釋放，從而阻塞其它進程。這時用“sp－who”看該“spid”進程狀態(tài)應(yīng)為“awaiting command”。應(yīng)將該存儲過程修改為

　　 “begin tran

　　 select ??? from x where ???

　　 update y set ???

　　 if a

　　 begin

　　 rollback tran

　　 return（number）

　　 end

　　 ???

　　 commit tran”

　　

　　2． 檢查存儲過程，將大事物細小化。

　　金融、電信業(yè)務(wù)中，經(jīng)常有一些批量業(yè)務(wù)，如代發(fā)工資、代扣話費等。這種業(yè)務(wù)一般要用到“cursor”，但應(yīng)盡量避免以下這種情況：

　　 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

　　因為，事物中使用的所有表的相關(guān)頁在進程提交前一直被鎖，并長時間阻塞所有訪問該頁的進程。應(yīng)該為：

　　 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

　　經(jīng)過這樣修改，既可提高存儲過程的執(zhí)行速度，又不影響其它網(wǎng)點的業(yè)務(wù)，阻塞機會有非常明顯的減少。

　　3． 使用sybase數(shù)據(jù)庫設(shè)計調(diào)優(yōu)策略及數(shù)據(jù)庫應(yīng)用調(diào)優(yōu)策略。

　　sybase各種調(diào)優(yōu)策略能夠使存儲過程的運行效率得到明顯提高，運行速度大幅度提高，從而縮短阻塞時間及減少阻塞概率。如下幾個方面會對解決阻塞有明顯效果。

　　1） 數(shù)據(jù)庫應(yīng)用表的設(shè)計合理，應(yīng)盡量避免大表間跨表操作。

　　2） 索引使用優(yōu)化，提高讀寫速度。

　　3） 盡量做到索引覆蓋查詢。

　　4） 對引起阻塞業(yè)務(wù)相應(yīng)存儲過程，根據(jù)其特點，使用其他相應(yīng)的優(yōu)化措施。

　　5） 經(jīng)常對應(yīng)用表進行update statistic，sp－recompile，改善數(shù)據(jù)頁的充滿度，提高存儲過程的運行效率。

　　4． 確定引起阻塞的存儲過程和表的方法。

　　有了上述解決問題的辦法，怎樣從龐大的應(yīng)用系統(tǒng)中，尋找相應(yīng)的存儲過程呢？

　　首先，當發(fā)生阻塞時要及時保留數(shù)據(jù)庫系統(tǒng)運行的有關(guān)現(xiàn)場信息，包括：sp－who、sp－lock的運行結(jié)果、master庫sysprocesses表的內(nèi)容。

　　sp－who的運行結(jié)果可用來查出引起阻塞的進程的spid。通過blk域的值找對應(yīng)的spid，如果blk值不為0，則該進程被spid=blk值的進程阻塞。這樣找下去，直至找到spid對應(yīng)的blk值為0，則此spid進程為引起阻塞進程。然后記錄其spid、loginname、hostname、dbname、cmd等。

　　sp－lock的運行結(jié)果可用來查出對哪個表寫操作引起阻塞。根據(jù)sp－who得到的spid，找sp－lock結(jié)果中對應(yīng)的locktype為ex－table－blk的一行，取table－id值，通過object－name（table－id）得到表名。

　　master庫sysprocesses表的內(nèi)容可用來查出須修改優(yōu)化的存儲過程。根據(jù)spid找到相應(yīng)行，記錄其status、hostname、cmd、id、linenum等通過object－name（id）得到存儲過程名。得到上述信息可對存儲過程進行修改優(yōu)化，解決阻塞問題（舉例過程略）。


*****************************************************
推薦跟蹤存儲過程名另一個方法:
dbcc traceon(3604)
dbcc traceoff
權(quán)限要夠哦

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

us sp_showplan spid,null,null,null display query plan  

( chenfeng825 )
*****************************************************