topic Re: SDE Personal Database lock in Python Questions

SDE Personal Database lock

CCWeedcontrol — Sun, 12 Dec 2021 07:07:35 GMT

I have a ArcSDE Personal geodatabase that i need to edit. I am connecting to the feature class through a save connection other wise i can't edit the feature class. The problem i am having is once i run the code and it runs fine it seem to lock the entire ArcSDE Personal server not just the geodatabase the specific feature class is in. I thought the 'with' ith arcpy.da.SearchCursor as cur: would release it but it does not. I also tried running the with statement inside of a function to remove the locks on the server when done. I also tried arcpy.ClearWorkspaceCache_management but nothing is working. So i need some help, i am not sure is there is something wrong with my python script. I need to run this script inside Arcmap's python window.

import arcpy, os 

arcpy.env.overwriteOutput = True
arcpy.env.workspace = r'Database Connections\Connection to test.sde'  #Database Connections


fc1 = 'DBO.TaxParcels1' # Taxparcels
fc2 = 'DBOAddresPointsTest_1'

def main():
    try:
        lyr2 = arcpy.MakeFeatureLayer_management(fc2, "In_memory\lyr2")
        arcpy.AddField_management(fc2, "Verifi2", "Text", "","",50) 
            
        #build a dictionary of OBJECTID : Address pairs, change OID@ to your uniqueID
        addDict = {row[0]:row[1] for row in arcpy.da.SearchCursor(fc1, ['ACCOUNT','SiteAddress'])}

        # Start an edit session. Must provide the worksapce.
        edit = arcpy.da.Editor(arcpy.env.workspace)

        # Edit session is started without an undo/redo stack for versioned data
        #  (for second argument, use False for unversioned data)
        edit.startEditing(True)

        # Start an edit operation
        edit.startOperation()


        #Spatial joins the points to taxparcels if "Match" is in Verifi2 field, updates points attributes.
        arcpy.env.workspace = r'Database Connections\Connection to test.sde'
        ptSelection = "DBO.AddresPointsTest_1"  
        pointLayer = arcpy.env.workspace + os.sep + "DBO.AddresPointsTest_1"  # DBO.AddresPointsTest
        parcel = 'DBO.TaxParcels1' #Taxparcels
        sjpoints = "In_memory\sjpoints"
        poly = "ACCOUNT_1" 
        Pnt =  "Account"

        ptCount = int(arcpy.GetCount_management(pointLayer).getOutput(0))

        dsc = arcpy.Describe(ptSelection)     
        selection_set = dsc.FIDSet             
        if len(selection_set) == 0:           
            print "There are no features selected"        
                         
        elif ptCount >= 1:
            #Run the Spatial Join tool, using the defaults for the join operation and join type
            arcpy.SpatialJoin_analysis(lyr2, parcel, sjpoints)

            # define the field list from the spatial join
            sourceFieldsList = ['ACCOUNT_1', poly,'Field_1','Field_2', 'Field_3','Field_4','Field_5']    #,'StreetType_1'

            # define the field list to the original points
            updateFieldsList = ['Account', Pnt,'Field1', 'Field2', 'Field3', 'Field4','Field4', 'Field5'] #, 'StreetType'

            # populate the dictionary from the polygon
            valueDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(sjpoints, sourceFieldsList)}    
            whereclause = "Verifi2 = 'Match'"
            with arcpy.da.UpdateCursor(ptSelection, updateFieldsList ,whereclause) as updateRows:    
                for updateRow in updateRows:    
                    keyValue = updateRow[0].title()
                    if keyValue in valueDict:
                        for n in range (1,len(sourceFieldsList)):      
                            updateRow[n] = valueDict[keyValue][n-1]
                        updateRows.updateRow(updateRow)
                del valueDict
                del updateRows
                del updateRow

        # Stop the edit operation.
        edit.stopOperation()
        # Stop the edit session and save the changes
        edit.stopEditing(True)

        #Clear geodatabase locks.
        # Set environment settings
        arcpy.env.workspace = "Database Connections\Connection to Test.sde" # Creates a connection to enterprise geodatabase
        fcList = arcpy.ListFeatureClasses() # Show that we are connected
        print(str(fcList) + "\n")
        arcpy.env.workspace = "" # Release hold on enterprise geodatabase workspace created in previous step.

        # Execute the Clear Workspace Cache tool
        arcpy.ClearWorkspaceCache_management("Database Connections\Connection to Test.sde")
        print(arcpy.GetMessages() + "\n")
    except:   
       arcpy.AddError("Python Messages: " + arcpy.GetMessages())
    
if __name__ == '__main__':  
    main() 


‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: SDE Personal Database lock

VinceAngelo — Fri, 17 Nov 2017 22:06:10 GMT

What exact version of ArcGIS are you using? I don't use non-enterprise geodatabases, but I wouldn't have expected the "Database Connections\*.sde" path for the connection file to an Express database (Database Servers). Your code's comment asserts an enterprise geodatabase, which generally locks specific tables. I would guess that your edit session is superseding the table lock, so you can probably comment out the entire with block for purposes of testing.

Re: SDE Personal Database lock

DanPatterson_Retired — Fri, 17 Nov 2017 22:15:58 GMT

Me either... but backgrounder for those about to check cursors and locks

In Python, the lock persists until the cursor is released. Otherwise, all other applications or scripts could be unnecessarily prevented from accessing a dataset. A cursor can released by one of the following:
Including the cursor inside a with statement, which will guarantee the release of locks regardless of whether or not the cursor is successfully completed
Calling reset() on the cursor
The completion of the cursor
Explicitly deleting the cursor using Python's del statement
An edit session in ArcMap applies a shared lock to data during the edit session. An exclusive lock is applied when edits are saved. A dataset is not editable if an exclusive lock already exists.

Re: SDE Personal Database lock

CCWeedcontrol — Fri, 17 Nov 2017 22:49:31 GMT

I am on ArcGIS10.4.1, sorry i should have mentioned that. I removing the entire 'with' block and run the code and ArcSDE Personal server is not locked. It's the 'with' block that locks it up but i am not sure why it's not release it because the cursor is inside a statement. I am at a loss here.

Re: SDE Personal Database lock

DanPatterson_Retired — Fri, 17 Nov 2017 22:52:02 GMT

and you tried reset() on the cursor and del as well as suggested options?

Re: SDE Personal Database lock

CCWeedcontrol — Sun, 12 Dec 2021 07:07:38 GMT

Dan, yes i did try. line 65-67

import arcpy, os 

arcpy.env.overwriteOutput = True
arcpy.env.workspace = r'Database Connections\Connection to test.sde'  #Database Connections


fc1 = 'DBO.TaxParcels1' # Taxparcels
fc2 = 'DBOAddresPointsTest_1'

def main():
    try:
        lyr2 = arcpy.MakeFeatureLayer_management(fc2, "In_memory\lyr2")
        arcpy.AddField_management(fc2, "Verifi2", "Text", "","",50) 
            
        #build a dictionary of OBJECTID : Address pairs, change OID@ to your uniqueID
        addDict = {row[0]:row[1] for row in arcpy.da.SearchCursor(fc1, ['ACCOUNT','SiteAddress'])}

        # Start an edit session. Must provide the worksapce.
        edit = arcpy.da.Editor(arcpy.env.workspace)

        # Edit session is started without an undo/redo stack for versioned data
        #  (for second argument, use False for unversioned data)
        edit.startEditing(True)

        # Start an edit operation
        edit.startOperation()


        #Spatial joins the points to taxparcels if "Match" is in Verifi2 field, updates points attributes.
        arcpy.env.workspace = r'Database Connections\Connection to test.sde'
        ptSelection = "DBO.AddresPointsTest_1"  
        pointLayer = arcpy.env.workspace + os.sep + "DBO.AddresPointsTest_1"  # DBO.AddresPointsTest
        parcel = 'DBO.TaxParcels1' #Taxparcels
        sjpoints = "In_memory\sjpoints"
        poly = "ACCOUNT_1" 
        Pnt =  "Account"

        ptCount = int(arcpy.GetCount_management(pointLayer).getOutput(0))

        dsc = arcpy.Describe(ptSelection)     
        selection_set = dsc.FIDSet             
        if len(selection_set) == 0:           
            print "There are no features selected"        
                         
        elif ptCount >= 1:
            #Run the Spatial Join tool, using the defaults for the join operation and join type
            arcpy.SpatialJoin_analysis(lyr2, parcel, sjpoints)

            # define the field list from the spatial join
            sourceFieldsList = ['ACCOUNT_1', poly,'Field_1','Field_2', 'Field_3','Field_4','Field_5']    #,'StreetType_1'

            # define the field list to the original points
            updateFieldsList = ['Account', Pnt,'Field1', 'Field2', 'Field3', 'Field4','Field4', 'Field5'] #, 'StreetType'

            # populate the dictionary from the polygon
            valueDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(sjpoints, sourceFieldsList)}    
            whereclause = "Verifi2 = 'Match'"
            with arcpy.da.UpdateCursor(ptSelection, updateFieldsList ,whereclause) as updateRows:    
                for updateRow in updateRows:    
                    keyValue = updateRow[0].title()
                    if keyValue in valueDict:
                        for n in range (1,len(sourceFieldsList)):      
                            updateRow[n] = valueDict[keyValue][n-1]
                        updateRows.updateRow(updateRow)
                updateRows.reset()
                del valueDict
                del updateRows

        # Stop the edit operation.
        edit.stopOperation()
        # Stop the edit session and save the changes
        edit.stopEditing(True)

        #Clear geodatabase locks.
        # Set environment settings
        arcpy.env.workspace = "Database Connections\Connection to Test.sde" # Creates a connection to enterprise geodatabase
        fcList = arcpy.ListFeatureClasses() # Show that we are connected
        print(str(fcList) + "\n")
        arcpy.env.workspace = "" # Release hold on enterprise geodatabase workspace created in previous step.

        # Execute the Clear Workspace Cache tool
        arcpy.ClearWorkspaceCache_management("Database Connections\Connection to Test.sde")
        print(arcpy.GetMessages() + "\n")
    except:   
       arcpy.AddError("Python Messages: " + arcpy.GetMessages())
    
if __name__ == '__main__':  
    main() 


‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: SDE Personal Database lock

DanPatterson_Retired — Fri, 17 Nov 2017 23:59:54 GMT

sort of grasping here, but those two del statements look like they should be dedented by one level

Re: SDE Personal Database lock

DanPatterson_Retired — Sat, 18 Nov 2017 14:18:11 GMT

Those two del statements look like they should be dedented by one level

Re: SDE Personal Database lock

JoshuaBixby — Sat, 18 Nov 2017 16:18:37 GMT

Can you elaborate on "lock the entire ArcSDE Personal server not just the geodatabase the specific feature class is in." ? How are you determining the entire server is locked? What actions are you taking and what are you seeing? Are you getting error messages when trying to do something else? What error messages?

Since the script involves more than just an update cursor, it might be a bit premature to assume that is the cause. Have you tried commenting out the update cursor lines to see if starting the edit session or setting the workspace.

Re: SDE Personal Database lock

CCWeedcontrol — Mon, 20 Nov 2017 15:29:41 GMT

you are right, i dedented them and re-rand the script but still locks up the Personal SDE server.

Re: SDE Personal Database lock

CCWeedcontrol — Mon, 20 Nov 2017 15:41:21 GMT

ok, so after i run the script in arcmap the features updated based on the script, no error there. They way i found out that it was locking that ArcSDE Personal Sever was that i was trying to open another feature class from that ArcSDE Personal Server in a new mxd. If i try to look at a feature class on that ArcSDE Personal Sever with ArcCatalog i get the error " Failed to connect to database. Maximum number of connections to instanced exceeded." My knowledge is that you are allowed 3 connections to a ArcSDE Personal Server and i just have the one mxd that i run the script on and a second new one to add a feature class to see if the ArcSDE Personal Server is locked. I know that it is the update cursor because if i commented it out on the script runs fine and i can open the ArcSDE Personal Server and add features from that server to a new mxd.