一次由重複索引導緻的問題

 最近一個朋友公司的OA系統總是出故障，具體表現在某個特定使用者在登入後，無法檢視自己的任務。等過了一會後，就報503錯誤。讓我幫忙看看。

    首先伺服器是JBOSS，資料庫是SQL Server 2005 64位企業版。

    根據以上提供的資訊，首先找到JBOSS日志，當某個使用者登入後點檢視待辦任務，JBOSS日志就會顯示:

ERROR [org.jboss.ejb.plugins.LogInterceptor] TransactionRolledbackLocalException in method: public abstract xxxx xxx(), causedBy: 

org.jboss.util.NestedSQLException: No ManagedConnections available within configured blocking timeout ( 30000 [ms] ); - nested throwable: (javax.resource.ResourceException: No ManagedConnections available within configured blocking timeout ( 30000 [ms] )) 

    at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:106) 

    at com.adobe.pof.ConnectionWrapper.getConnection(ConnectionWrapper.java:25) 

    at com.adobe.pof.ConnectionWrapper.getMetaData(ConnectionWrapper.java:101) 

    at com.adobe.pof.POFBean.getAdapter(POFBean.java:120) 

    at com.adobe.pof.omapi.POFObjectManagerRemoteBean.getObjectManagerImpl(POFObjectManagerRemoteBean.java:108) 

    at com.adobe.pof.omapi.POFObjectManagerRemoteBean.getEnvironment(POFObjectManagerRemoteBean.java:489) 

    at sun.reflect.GeneratedMethodAccessor144.invoke(Unknown Source) 

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) 

    at java.lang.reflect.Method.invoke(Method.java:592) 

    at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:683)

    根據報錯日志，很明顯問題是出在連接配接逾時。當逾時超過30秒後，資料庫Session斷開。然後伺服器報503錯誤。而隻有這個特定使用者存在這個問題，測試了幾個其它使用者均不存在這個問題，是以可以知道錯誤應該沒出在應用程式那一端。

    由于隻有這個特定使用者存在這個問題，是以也基本排除資料庫無法連接配接，T-SQL代碼存在一些問題等原因。

    是以，逾時我首先想到是阻塞，阻塞我首先想到可能是鎖引起的。我先運作了這個代碼來看哪個session下鎖跑得最多。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

SELECT COUNT(*),request_session_id FROM 

( 

SELECT DB_NAME(resource_database_id) AS DatabaseName 

, request_session_id 

, resource_type 

, CASE 

WHEN resource_type = 'OBJECT' 

THEN OBJECT_NAME(resource_associated_entity_id) 

WHEN resource_type IN ('KEY', 'PAGE', 'RID') 

THEN (SELECT OBJECT_NAME(OBJECT_ID) 

FROM sys.partitions p 

WHERE p.hobt_id = l.resource_associated_entity_id) 

END AS resource_type_name 

, request_status 

, request_mode 

FROM sys.dm_tran_locks l 

) AS a 

GROUP BY a.request_session_id

    發現session id為150的連接配接上跑了4000+個鎖。再通過sys.dm_tran_locks 來具體的看，發現這些鎖都是key級的S鎖，以及對應Page和表上的IS鎖。當30秒過後逾時，所有這些鎖都會被自動釋放。

    我産生兩個疑問：

    1.為什麼僅僅是這個使用者一檢視資料庫就會産生這麼多的鎖，而其它使用者不會産生這麼多鎖呢？

    2.為什麼4000+個鎖沒有更新呢？

    帶着這個疑問,我使用Profiler對這個Session ID檢視待辦工作時的操作進行了抓取。發現對應的T-SQL語句僅僅是一個普通的Select語句。為了看清傳回的行數，我使用未送出讀看到這個使用者尚待辦的工作有1900條（使用者是一個地區的經理，所有地區産生的任務無論他是否參與都要轉給他一個）,而其它不産生阻塞的使用者待辦工作不超過50條。

    4000+個鎖沒有更新我想是因為表上存在了意向鎖。

    是以我懷疑表上的多個索引之間在選擇多條語句時可能産生互相阻塞。是以對相關的表上索引進行檢視，發現在用于關聯使用者和任務的assignment表中存在兩個索引的最左列完全一樣，估計這裡就是産生問題的根源。

    是以kill掉Session 150之後，Drop了那個鍵少的索引。問題解決！

分類: SQL性能調優

本文轉自CareySon部落格園部落格，原文連結：http://www.cnblogs.com/CareySon/archive/2012/10/17/2727697.html，如需轉載請自行聯系原作者