topic Re: ArcGIS Pro 2.6.x Issues with ArcPy: Needing help with stale connections and state locks on SDE connections in Python Questions

ArcGIS Pro 2.6.x Issues with ArcPy: Needing help with stale connections and state locks on SDE connections

David_Lindsey — Sat, 11 Dec 2021 08:53:33 GMT

Hello everyone,

I feel like I've been plagued with unusual Python 3.x (arcpy) issues ever since I upgraded to 2.6.x

The latest issues I'm facing include stale SDE connections and state locks not properly releasing upon completion when I am disabling/enabling Editor Tracking (arcpy.EnableEditorTracking_management and arcpy.DisableEditorTracking_management).

My workflow includes disabling editor tracking, calculating some values with an update cursor, then re-enabling editor tracking on an SDE database.

After discovering some locking issues not going away, I've isolated the issue specifically to the enabling/disabling of editor tracking. I've created a testing environment (and script) for studying these tools specifically which will be provided below.

Usually, arcpy.ClearWorkspaceCache_management does the trick and cleans up the script's locks, but that isn't working with the above tools for whatever reason in Pro 2.6 update of Python. To provide more clarity, I run many workflow tools in sequence so my goal is to clean up all locks affiliated with each tool before beginning the next.

I have confirmed that the state locks are properly releasing in the Python 2.7 flavor of arcpy, and I never had issues with Pro 2.2-2.5 with these issues either.

Here is my script that I've been testing with:

import arcpy
from Static_Values import Class_Static_Values as stat_val
import time

arcpy.env.workspace = stat_val.sdeConnection_RegionalGIS_DefaultVersion

fc_list = arcpy.ListFeatureClasses()

for fc in fc_list:

    if "EMS" in fc:

        #arcpy.DisableEditorTracking_management(fc,
        #                                       "DISABLE_CREATOR",
        #                                       "DISABLE_CREATION_DATE",
        #                                       "DISABLE_LAST_EDITOR",
        #                                       "DISABLE_LAST_EDIT_DATE")

        arcpy.EnableEditorTracking_management(fc,
                                              "CreateID",
                                              "DateCreate",
                                              "UpdateID",
                                              "DateUpdate",
                                              "NO_ADD_FIELDS",
                                              "UTC")

        print(arcpy.GetMessages())


print("attempting to remove locks...")
arcpy.ClearWorkspaceCache_management()
print("attempt complete...")


# The following loop allows me to review/refresh the Geodatabase Administration window for 10 seconds to verify if any locks still exist from the above line of code.

counter = 0
while counter < 10:

    time.sleep(1)
    counter = counter + 1‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Has anyone else encountered this, or can you think of other options to try in order to clean up the locks?

Once the above, isolated script gets to Line 33, two stale SDE connections and two persistent state locks remain and do not go away until the script completes its full execution after Line 41.

The two stale connections are highlighted here (ignore the grayed out one, as that is my current view within Catalog for studying purposes):

The two persistent state locks are highlighted here (ignore the grayed out one, as that is my current view within Catalog for studying purposes):

Interestingly enough, I can get one of these state locks to disappear if I include the line arcpy.env.workspace = None just before the arcpy.ClearWorkspaceCache_management() line, but I don't recall ever needing to do that. I also can't figure out what else is leaving the other state lock. I've tried resetting the environment, clearing the cache, garbage collection, as well as deleting the fc_list variable with no luck.

Any help or insight would be most appreciated!

Re: ArcGIS Pro 2.6.x Issues with ArcPy: Needing help with stale connections and state locks on SDE connections

DanPatterson — Mon, 14 Sep 2020 01:46:11 GMT

This is the type of issue... with a workaround ... that should be reported to Tech Support to have a case opened.

You have identified that it worked in previous issues.

You have provided a workaround.

Tech support would be the ones that can associate it with other issues that are seemingly unrelated.

Tech support can also test on a larger number of test cases to confirm whether it is data source and or type specific.

Please, contact Tech Support and reference this thread, in case others weigh in.

Re: ArcGIS Pro 2.6.x Issues with ArcPy: Needing help with stale connections and state locks on SDE connections

MehdiPira1 — Sat, 11 Dec 2021 08:53:35 GMT

Hi ‌David Lindsey‌

You can right-click the available connections and select Disconnect to disconnect users from the geodatabase and run the script again. If it still has the locking issue, I recommend including the following script in your script:

ws = r'Database Connections\XXX.sde'    
users = arcpy.ListUsers(ws)    
for user in users:        
    arcpy.DisconnectUser(ws, user.ID) ‍‍‍‍‍‍‍‍

This releases the locks created by users.

Re: ArcGIS Pro 2.6.x Issues with ArcPy: Needing help with stale connections and state locks on SDE connections

David_Lindsey — Mon, 14 Sep 2020 13:25:46 GMT

Hi Mehdi,

I appreciate the feedback. I am aware that this could help remove locks, but I am hesitant to incorporate it into our scripts. We use database replication and provide various web services that act as users connected to our SDEs. The user name information that Esri tracks within the user list is not granular enough for me to determine which "DBO" user lock is directly tied to the script itself, unless I add more fancy coding to keep track of lock times perhaps (no thanks!).

An idea we may consider is creating a script-specific username with DBO privileges so that I can more easily identify any locks created by the script itself. Even this seems like a band-aid fix though. I'll reach out to Esri Support today and report back any new findings. Thanks again!

Re: ArcGIS Pro 2.6.x Issues with ArcPy: Needing help with stale connections and state locks on SDE connections

David_Lindsey — Mon, 14 Sep 2020 14:05:51 GMT

Thanks, Dan. Issue now logged with Esri Support. I'll report back with any new findings!

Re: ArcGIS Pro 2.6.x Issues with ArcPy: Needing help with stale connections and state locks on SDE connections

David_Lindsey — Tue, 15 Sep 2020 23:05:40 GMT

Hey Mehdi,

I wanted to follow up with some findings. I was playing around with your suggestion, and it partially works by removing some stagnant locks. However, I do not believe it can solve all of my issues at the moment.

I get the following error.....
RuntimeError: Unable to disconnect: The connection ID provided is for the current administrator connection.

...which led me down a rabbit hole:

This error is occurring when I attempt to iterate each user and remove select ones matching "DBO" (which is what the script currently utilizes). Since the script itself is executing the function, it doesn't appear that I am able to disconnect the script while it's still connected.

And since the script isn't properly disconnecting itself after running certain tools, I guess that puts me in a Catch-22 situation. Ha.

The Esri documentation:
DisconnectUser—ArcGIS Pro | Documentation

Anyway, Esri Support let me know today that the locking issue can be replicated on the Esri side and is suspected to be a defect specific to Pro 2.6.x. I should be hearing more from Esri Support this week concerning this issue, so I'll follow up with more once I hear back.