SDE Personal Database lock

CCWeedcontrol · ‎11-17-2017

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() 


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

VinceAngelo · ‎11-17-2017

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.

CCWeedcontrol · ‎11-17-2017

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.

DanPatterson_Retired · ‎11-17-2017

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

CCWeedcontrol · ‎11-17-2017

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() 


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

DanPatterson_Retired · ‎11-17-2017

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

CCWeedcontrol · ‎11-20-2017

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

DanPatterson_Retired · ‎11-17-2017

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.

DanPatterson_Retired · ‎11-18-2017

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

JoshuaBixby · ‎11-18-2017

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.