topic Re: Deadlocks in SQL Server causing Underlying DBMS error ESRI 10 SP3 in Data Management Questions

Deadlocks in SQL Server causing Underlying DBMS error ESRI 10 SP3

JohnCuthbertson — Fri, 25 May 2012 03:55:30 GMT

Under very heavy load (we were refreshing lots of cached area all at the same time) we get the following warning and error in ArcGIS log

The Layer xxxx; in Map yyyy; is invalid. The base table definition string "zzzzzz" is invalid. Underlying DBMS error [sde.DEFAULT][STATE_ID = 513].
Container process xxx has crashed on machine xxxx

We also see the following in some of the FME jobs running at the same time

Could not open the Enterprise Geodatabase.
Please check that the connection parameters specified are correct.
The error number from ArcObjects is: '-2147216072'.
The error message from ArcObjects is: {Underlying DBMS error [sde.DEFAULT][STATE_ID = 692]}

At the same time our SQL 2008R2 deadlock monitor reports a deadlock (and the victim is the one that gets the above error message)

The deadlock relates to objectname="sde.SDE_state_locks�?� table and is being called by sde.SDE_state_check_lock_conflicts and sde.SDE_state_lock_def_insert stored procedures

By using SQL trace I was able to extract out lots of calls to these stored procedures. Putting these calls in half a dozen sql streams and running them all against each other (using a copy of live geodatabase with a "sde.SDE_state_locks�?� table populated with a few thousand dummy entries) I was able to reproduce the deadlock situation.

We have raised a call with ESRI support, but are looking for anyone else who may be having a similar problem, and better still, has a solution!.

Re: Deadlocks in SQL Server causing Underlying DBMS error ESRI 10 SP3

DeanRother — Mon, 24 Sep 2012 12:57:48 GMT

I'm having the same problem.

Re: Deadlocks in SQL Server causing Underlying DBMS error ESRI 10 SP3

MichaelFischer — Tue, 15 Apr 2014 16:47:58 GMT

Having the same problem with a 10.2 geodb on SQL server... has there been any resolution or suggestion for this?

Thanks

Re: Deadlocks in SQL Server causing Underlying DBMS error ESRI 10 SP3

JohnCuthbertson — Wed, 23 Apr 2014 00:50:41 GMT

Having the same problem with a 10.2 geodb on SQL server... has there been any resolution or suggestion for this?

Thanks

Michael, we were never able to convince ESRI that it was a real problem. Our final solution was to patch [sde].[SDE_state_lock_def_insert] (and to cross our fingers!!). This has proved to be a great success on 10.0.

We are just about to start on 10.2 migration and so we will be reviewing situation to see if our patch is still required.

On a side note we have recently started to see occasional deadlocks in pinfo_tran and table_lock_del_tran (Still on 10.0). I have not yet had a chance to investigate

Re: Deadlocks in SQL Server causing Underlying DBMS error ESRI 10 SP3

MichaelFischer — Thu, 24 Apr 2014 22:40:58 GMT

Michael, we were never able to convince ESRI that it was a real problem. Our final solution was to patch [sde].[SDE_state_lock_def_insert] (and to cross our fingers!!). This has proved to be a great success on 10.0.

We are just about to start on 10.2 migration and so we will be reviewing situation to see if our patch is still required.

On a side note we have recently started to see occasional deadlocks in pinfo_tran and table_lock_del_tran (Still on 10.0). I have not yet had a chance to investigate

Thanks... I think you might find it will still be needed. If so, can you share your patch?

Re: Deadlocks in SQL Server causing Underlying DBMS error ESRI 10 SP3

Oumer_ShafiBhat — Tue, 08 Jul 2014 07:57:48 GMT

Hi John,

I am also facing similar issue with SQL Server (ArcGIS SDE 10 SP3), can you kindly share your patch

Re: Deadlocks in SQL Server causing Underlying DBMS error ESRI 10 SP3

ilanithoffman — Tue, 08 Jul 2014 08:38:16 GMT

We have stumbled the same issue, thank you John for identifying the problem and sending us to the right direction.

As John mentioned the issue is with the [dbo.SDE_state_lock_def_insert] procedure.

we have investigated the issue deeply, and found that the deadlocks are happening when the [dbo.STATE_state_check_lock_conflicts] trying to convert the IX (Intent Exclusive) lock to X (Exclusive). on the [dbo.SDE_state_locks] table while opening a local cursor with a table hint.

The problem is that before [SDE_state_lock_def_insert] procedure runs, the [dbo.SDE_state_lock_def_insert] calls the

[dbo.SDE_state_lock_def_delete] (in the same transaction) that making cleanup in the [dbo.SDE_state_locks] table, via DELETE instruction. this in turn acquiring a IX lock on [dbo.SDE_state_locks] table, and then if it delete any row while running, it acquiring row X lock on the deleted row. this locks are held till the end of the outmost transaction.

so when the later procedure called, it is deadlocked, on this X locks, cause it tries to get X lock on whole table, and for that it need to lock wait till other sessions release the X locks on the rows, which in turn waiting on other to do the same.

The solution, that is worked for us:

setting a table hint inside the [dbo.SDE_state_lock_def_delete] procedure on both DELETE operations, that instruct to acquire a table exclusive lock on whole table, right away.

apply the hint after the FROM clause as follows: WITH (TABLOCKX, HOLDLOCK).

it worked like a charm, no deadlocks anymore .

still checking for performance penalty. but it worth it

hope ESRI will understand that it is a major issue and will fix that in the new releases.

Re: Deadlocks in SQL Server causing Underlying DBMS error ESRI 10 SP3

SandraSkaar — Mon, 20 Oct 2014 14:51:44 GMT

This was resolved with the 10.2.1 database upgrade and was a confirmed but in 10.0 SP5.

Re: Deadlocks in SQL Server causing Underlying DBMS error ESRI 10 SP3

FemadataSupport — Wed, 30 Mar 2016 16:33:51 GMT

Is it possible that Not applying something in a 10.0 pre 10.3 period would carry forward this behavior?

I'm currently supporting a Data collector app in a 10.3 Geodatabase. I did not do the upgrade but this is a long standing geodatabase. Currently under a heavy load ; 200 or so users it is deadlocking to the point that the application is unusable.