Error trying to view locks

[MichaelStranovsky]

I am receiving this error while clicking on the lock tab in Geodatabase Administration using ArcCatalog 10.5.1.  Has anyone else experienced this?

[MichaelVolz]

Ray:

Can you explain in more detail what you mean by an orphaned edit service?

[RayChilcote]

An SDE schema lock owned (caused) by an ArcServer service.  But the lock doesn't show up in any of the locks tables.

Perhaps too much information follows.  But maybe somebody will benefit...

By design (in our case, Esri's Roads and Highways extension), an edit by a service is meant to be very short lived (and the lock short lived).  But instead of releasing the lock, on rare occasions the lock remains.  Almost more like a physical lock is retained, but the lock rows are no longer in the locks tables.

The feature class in SDE becomes locked by this ghost lock (better term?  If there's a more proper definition or insight to what this is, let me know.).

We saw this as early as 10.3.1 when refreshing data from our Editing tier to our Publishing tier.  A feature class (or many) on the Publishing tier could not be altered (renamed, deleted, most any schema change).  Yet there were no locks in the locks tables.  We stopped and started most every piece of hardware.  Stopping and starting individual services would not release the lock.  Finally, the ArcServer Machine was stopped and started.  Then voila, the lock would release.  

Our editing services tend to reveal a little more insight.  After one of these ghost locks occur, the other load balancing machines then (also) retain a lock (maybe they are getting in line behind the ghost lock?), these can be seen in the locks tables.  We end up seeing repetitive locks, building up to match the number of load balancing machines.  They will not be released until the ArcServer Machine causing the issue is stopped and started.

We have not seen any database Deadlocks during any of this.

I'm also throwing out a warning to everyone.  If there are Nulls in the locks tables, there is an issue somewhere.  Find the cause, especially if it can be repeated.  We've pinpointed ours (above).  While reconciling all versions, it will error on one that has a service lock.  We will not Compress after, as that is what creates the NULLs in question (for us).  Second warning: editing after the failure can introduce conflicts.

[BillFox]

Good morning Ray,

Is stopping all the ArcGIS server windows services, gutting the SDE_state_lineages and geoprocessing history part of your state zero chore?

[RayChilcote]

Yes, stopping the services is part of our process for maintenance.  But this does not get the lock that isn't even showing.  Until we stop the ArcServer Machine, the lock will persist (no changes can be made).

In prepping for maintenance, we have traditionally first checked ArcCatalog locks. Admittedly, I haven't checked all the locks tables in the middle of our maintenance though (usually just the version/object lock relationship).  If there are any problems after that, we immediately move to stopping the ArcServer Machines. 

I'll definitely look into all these locks tables during our next maintenance cycle.  Perhaps there's something to be found.  Gutting them out is an excellent idea.

[garyleunis]

I solved the mystery. First off it does still exist in Arc10.6.1. I was able to figure out the issue looking at my 10.4 version of arcCat. here was the problem, there seemed to be a Locked table that did not have an owner name. Ill call it a GHOST. Because the vaule was null, the ERROR box appeared "column value is null"

I went into sde_table_locks and removed ALL the ghosts.

there error is not sensitive to the actual table you right click in arc catalog, but if ANY table lock which has a null owne rvalue. seems i had several. and once every null owner name record was removed, the error message went away.

[RayChilcote]

I hadn't thought of it before, but it would be nice to set up a trace to determine the query being made to fulfill the table.  This would show why the error is being made.  Well, it would lead us to some answers.

It appears that there are several different cases here that are throwing the error.  In other words, the query can likely fails for several reasons.

I had this error pop up again this past Monday.  The cause was unmatched rows.  I had one row in state_locks and the other in Object_locks.  There were no matching rows for either in process_information. [Thanks again Matthew Boyle for the query!]  Once I got rid of the two rows, all was good.

Btw, I'm still against Esri ignoring the error  [what is "the fix"?].  If there's an error, then there's some incongruity in the tables.  I don't want the error covered up.  In my case, the question becomes why are the two rows still there?

[Jen_Zumbado-Hannibal]

Thanks for the script @mpboyle . Yet, last line I had to manually input because when copying and pasting I must have carried a hidden character and it would run.