SQL Deadlocks in Vault Standard 6.1

[wkrzystek]

Since we've upgraded to vault 6.1, we've been encountering fairly regular SQL deadlocks. I think these are related to Index maintenance on the SgVault database. Is there something we can do to diagnose what's going on here? Or is this just related to how many users and transactions we have?

This is the SQL info from the latest instance. I've attached a screenshot from SQL Monitor showing the recent history of deadlocks. All of these are on the SgVault database.

Deadlocked processes: 149
Victim process: 163
Object blocked: Index/(Key) 72057594039435264
Deadlocked processes
SPID Victim Lock details Statement type
149 dbid: 9, object id: 72057594039435264, index id: (a90b9e5cd5cb) DELETE Line #: 30
163 Victim process dbid: 9, object id: 72057594039435264, index id: (a90b9e5cd5cb) INSERT Line #: 39

Deadlock encountered .... Printing deadlock information
Wait-for graph

Node:1
KEY: 9:72057594039435264 (a90b9e5cd5cb) CleanCnt:4 Mode:RangeS-S Flags: 0x0
Grant List 0:
Grant List 1:
Owner:0x00000003E44980C0 Mode: RangeS-S Flg:0x0 Ref:0 Life:02000000 SPID:163 ECID:0 XactLockInfo: 0x00000001B07CB168
SPID: 163 ECID: 0 Statement Type: INSERT Line #: 39
Input Buf: RPC Event: Proc [Database Id = 9 Object Id = 2119678599]
Requested By:
ResType:LockOwner Stype:'OR'Xdes:0x00000001399FCA50 Mode: RangeX-X SPID:149 BatchID:0 ECID:0 TaskProxy:(0x00000002B7A4C598) Value:0x136a7940 Cost:(0/548)

Node:2
KEY: 9:72057594039435264 (a90b9e5cd5cb) CleanCnt:4 Mode:RangeS-S Flags: 0x0
Grant List 0:
Owner:0x0000000213300100 Mode: RangeS-S Flg:0x0 Ref:0 Life:02000000 SPID:149 ECID:0 XactLockInfo: 0x00000001399FCA88
SPID: 149 ECID: 0 Statement Type: DELETE Line #: 30
Input Buf: RPC Event: Proc [Database Id = 9 Object Id = 2103678542]
Grant List 1:
Requested By:
ResType:LockOwner Stype:'OR'Xdes:0x00000001B07CB130 Mode: RangeI-N SPID:163 BatchID:0 ECID:0 TaskProxy:(0x00000002397EE598) Value:0xe84f4400 Cost:(0/104)

Victim Resource Owner:
ResType:LockOwner Stype:'OR'Xdes:0x00000001B07CB130 Mode: RangeI-N SPID:163 BatchID:0 ECID:0 TaskProxy:(0x00000002397EE598) Value:0xe84f4400 Cost:(0/104)

Recent Deadlock history on SgVault

deadlocks.jpg (67.27 KiB) Viewed 8589 times

[wkrzystek]

Here is some additional info from the sgvault.log file at the time of the deadlock:

----7/23/2013 8:26:12 AM mraffel--mraffel01.mis.local(10.0.0.146)--SSL Disabled Login
----7/23/2013 8:26:13 AM mraffel--mraffel01.mis.local(10.0.0.146)--SSL Disabled Logout
----7/23/2013 8:26:20 AM mraffel--mraffel01.mis.local(10.0.0.146)--SSL Disabled Login
----7/23/2013 8:26:20 AM sjohnson--sjohnson03.mis.local(10.0.0.111)--SSL Disabled System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.
at System.Data.SqlClient.SqlTransaction.ZombieCheck()
at System.Data.SqlClient.SqlTransaction.Commit()
at VaultServiceSQL.VaultSqlConn.CommitTransaction() at System.Data.SqlClient.SqlTransaction.ZombieCheck()
at System.Data.SqlClient.SqlTransaction.Commit()
at VaultServiceSQL.VaultSqlConn.CommitTransaction()
----7/23/2013 8:26:20 AM sjohnson--sjohnson03.mis.local(10.0.0.111)--SSL Disabled Rolling Back a transaction at VaultServiceSQL.VaultSqlConn.RollbackTransaction()
at VaultServiceSQL.VaultSqlConn.CloseConn()
at VaultServiceAPILib.VaultServiceAPISystem.DoLogin(Boolean allowwebonlyusers, Boolean bAdminLogin, HttpSessionState hss, String strHostname, String strLogin, String strPassword, String strRMKey, String& strAuthToken, String strDomain)
at VaultServiceAPILib.VaultServiceAPISystem.DoLogin(Boolean allowwebonlyusers, Boolean bAdminLogin, HttpSessionState hss, String strHostname, String strLogin, String strPassword, String strRMKey, String& strAuthToken)
at VaultService.VaultService.Login(String strHostname, Boolean bUseFullFiles, String username, String strEncryptedPassword, String strRMKey, String& strAuthToken)
at System.RuntimeMethodHandle._InvokeMethodFast(IRuntimeMethodInfo method, Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeType typeOwner)
at System.RuntimeMethodHandle.InvokeMethodFast(IRuntimeMethodInfo method, Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeType typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.Web.Services.Protocols.LogicalMethodInfo.Invoke(Object target, Object[] values)
at System.Web.Services.Protocols.WebServiceHandler.Invoke()
at System.Web.Services.Protocols.WebServiceHandler.CoreProcessRequest()
at System.Web.Services.Protocols.SyncSessionlessHandler.ProcessRequest(HttpContext context)
at System.Web.Script.Services.ScriptHandlerFactory.HandlerWrapper.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
at System.Web.HttpApplication.PipelineStepManager.ResumeSteps(Exception error)
at System.Web.HttpApplication.BeginProcessRequestNotification(HttpContext context, AsyncCallback cb)
at System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context)
at System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr managedHttpContext, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
at System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr managedHttpContext, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
at System.Web.Hosting.UnsafeIISMethods.MgdIndicateCompletion(IntPtr pHandler, RequestNotificationStatus& notificationStatus)
at System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr managedHttpContext, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
at System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr managedHttpContext, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)

----7/23/2013 8:26:20 AM sjohnson--sjohnson03.mis.local(10.0.0.111)--SSL Disabled System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.
at System.Data.SqlClient.SqlTransaction.ZombieCheck()
at System.Data.SqlClient.SqlTransaction.Rollback()
at VaultServiceSQL.VaultSqlConn.RollbackTransaction() at System.Data.SqlClient.SqlTransaction.ZombieCheck()
at System.Data.SqlClient.SqlTransaction.Rollback()
at VaultServiceSQL.VaultSqlConn.RollbackTransaction()
----7/23/2013 8:26:21 AM mraffel--mraffel01.mis.local(10.0.0.146)--SSL Disabled Logout
----7/23/2013 8:26:28 AM sjohnson--sjohnson03.mis.local(10.0.0.111)--SSL Disabled Login
----7/23/2013 8:26:32 AM sjohnson--sjohnson03.mis.local(10.0.0.111)--SSL Disabled Logout

[wkrzystek]

And here's one more example that shows up a little bit different in sgvault.log:

Deadlocked processes: 187
Victim process: 247
Object blocked: Index/(Key) 72057594039435264
Deadlocked processes
SPID Victim Lock details Statement type
187 dbid: 9, object id: 72057594039435264, index id: (d00c220261b8) DELETE Line #: 30
247 Victim process dbid: 9, object id: 72057594039435264, index id: (d00c220261b8) DELETE Line #: 30
SQL query fragment:
RPC Event: Proc [Database Id = 9 Object Id = 2103678542]

Deadlock encountered .... Printing deadlock information
Wait-for graph

Node:1
KEY: 9:72057594039435264 (d00c220261b8) CleanCnt:4 Mode:RangeS-S Flags: 0x0
Grant List 0:
Grant List 2:
Owner:0x00000002CFD42EC0 Mode: RangeS-S Flg:0x0 Ref:0 Life:02000000 SPID:247 ECID:0 XactLockInfo: 0x000000037436B3B8
SPID: 247 ECID: 0 Statement Type: DELETE Line #: 30
Input Buf: RPC Event: Proc [Database Id = 9 Object Id = 2103678542]
Requested By:
ResType:LockOwner Stype:'OR'Xdes:0x000000038C0FF500 Mode: RangeX-X SPID:187 BatchID:0 ECID:0 TaskProxy:(0x00000002CE5AC598) Value:0xc1d9eb80 Cost:(0/540)

Node:2
KEY: 9:72057594039435264 (d00c220261b8) CleanCnt:4 Mode:RangeS-S Flags: 0x0
Grant List 0:
Owner:0x00000004189C1B00 Mode: RangeS-S Flg:0x0 Ref:0 Life:02000000 SPID:187 ECID:0 XactLockInfo: 0x000000038C0FF538
SPID: 187 ECID: 0 Statement Type: DELETE Line #: 30
Input Buf: RPC Event: Proc [Database Id = 9 Object Id = 2103678542]
Grant List 2:
Requested By:
ResType:LockOwner Stype:'OR'Xdes:0x000000037436B380 Mode: RangeX-X SPID:247 BatchID:0 ECID:0 TaskProxy:(0x0000000396668598) Value:0x80260640 Cost:(0/336)

Victim Resource Owner:
ResType:LockOwner Stype:'OR'Xdes:0x000000037436B380 Mode: RangeX-X SPID:247 BatchID:0 ECID:0 TaskProxy:(0x0000000396668598) Value:0x80260640 Cost:(0/336)



----7/3/2013 9:23:00 AM wkrzystek--wkrzystek01.mis.local(10.0.0.136)--SSL Disabled Login
----7/3/2013 9:23:01 AM wkrzystek--wkrzystek01.mis.local(10.0.0.136)--SSL Disabled Logout
----7/3/2013 9:23:09 AM wkrzystek--wkrzystek01.mis.local(10.0.0.136)--SSL Disabled System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 85) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at VaultServiceSQL.VaultSqlSystem.LoginUser(VaultSqlConn conn, String strLogin, String strDbHashPwd, Guid& gSession) at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at VaultServiceSQL.VaultSqlSystem.LoginUser(VaultSqlConn conn, String strLogin, String strDbHashPwd, Guid& gSession)
----7/3/2013 9:23:09 AM sjohnson--sjohnson03.mis.local(10.0.0.111)--SSL Disabled Login
----7/3/2013 9:23:09 AM wkrzystek--wkrzystek01.mis.local(10.0.0.136)--SSL Disabled Rolling Back a transaction at VaultServiceSQL.VaultSqlConn.RollbackTransaction()
at VaultServiceAPILib.VaultServiceAPI.Login(VaultSqlConn conn, Boolean bAllowWebOnly, String strLogin, String strDecryptedPassword, String strHostname, String strSessionID, String strDomain, String& strToken, VaultUser& vu, Int32& nValidUserAttempts)
at VaultServiceAPILib.VaultServiceAPISystem.DoLogin(Boolean allowwebonlyusers, Boolean bAdminLogin, HttpSessionState hss, String strHostname, String strLogin, String strPassword, String strRMKey, String& strAuthToken, String strDomain)
at VaultServiceAPILib.VaultServiceAPISystem.DoLogin(Boolean allowwebonlyusers, Boolean bAdminLogin, HttpSessionState hss, String strHostname, String strLogin, String strPassword, String strRMKey, String& strAuthToken)
at VaultService.VaultService.Login(String strHostname, Boolean bUseFullFiles, String username, String strEncryptedPassword, String strRMKey, String& strAuthToken)
at System.RuntimeMethodHandle._InvokeMethodFast(IRuntimeMethodInfo method, Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeType typeOwner)
at System.RuntimeMethodHandle.InvokeMethodFast(IRuntimeMethodInfo method, Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeType typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.Web.Services.Protocols.LogicalMethodInfo.Invoke(Object target, Object[] values)
at System.Web.Services.Protocols.WebServiceHandler.Invoke()
at System.Web.Services.Protocols.WebServiceHandler.CoreProcessRequest()
at System.Web.Services.Protocols.SyncSessionlessHandler.ProcessRequest(HttpContext context)
at System.Web.Script.Services.ScriptHandlerFactory.HandlerWrapper.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
at System.Web.HttpApplication.PipelineStepManager.ResumeSteps(Exception error)
at System.Web.HttpApplication.BeginProcessRequestNotification(HttpContext context, AsyncCallback cb)
at System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context)
at System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr managedHttpContext, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
at System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr managedHttpContext, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
at System.Web.Hosting.UnsafeIISMethods.MgdIndicateCompletion(IntPtr pHandler, RequestNotificationStatus& notificationStatus)
at System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr managedHttpContext, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
at System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr managedHttpContext, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)

----7/3/2013 9:23:09 AM wkrzystek--wkrzystek01.mis.local(10.0.0.136)--SSL Disabled Login failed: FailDBUpdate
----7/3/2013 9:23:12 AM sjohnson--sjohnson03.mis.local(10.0.0.111)--SSL Disabled Logout
----7/3/2013 9:23:20 AM sjohnson--sjohnson03.mis.local(10.0.0.111)--SSL Disabled Login
----7/3/2013 9:23:20 AM sjohnson--sjohnson03.mis.local(10.0.0.111)--SSL Disabled Logout

[Beth]

Could you put your Vault Server log into debug mode? That is done from the Vault admin web page under logging.

Then as soon as you get another deadlock, send the full log to me. Send the email to support at sourcegear.com (attn: Beth) with a link to this forum thread and the log attached.