Transaction mode problem

RaymondGoins · ‎05-18-2016

I know this has been asked, and I have tried all suggestion I could find. Maybe I am missing something. Here is my code

# ------------------------------------------------------------------------------
# Cstar_Update.py
# Created on: 2012-06-05 12:56:35.00000
# Updated 05/18/2016 by: Raymond Goins
#   
# Description:
# Updates Stop number in Customer Star database from selected feature in ArcMap
# ------------------------------------------------------------------------------

# Import arcpy module
import arcpy

# Local variables:
# table = r"C:\PWSB GIS\GIS Scripts\conn\CStar.odc\SVC_LOC"
table = r"C:\PWSB GIS\GIS Scripts\conn\PWMedia.odc\CSTARTMP"

# Set variable to see if a feature is selected
sel = len(arcpy.Describe("WATER.SERVICELOCATION").FIDSet)

if sel >= 1:
    with arcpy.da.SearchCursor("WATER.ServiceLocation", ['STOP_NUMBER', 'SVC_LOC_NB']) as wslrow:
        # Loop through selected features
        for row in wslrow:
            stopnumber = row[0]
            svcloc = row[1]
            # Make sure SVCLOC number is not null
            if svcloc is not None:
                # Check to see if SrvLoc number exists
                rcheck = arcpy.da.SearchCursor(table, ['*'], "SLOC_SVC_LOC_NBR = " + "'" + str(svcloc) + "'")
                i = 0            
                for r in rcheck:
                    i += 1
                del r
                del rcheck
                # If exists loop through
                if i > 0:
                    csrows = arcpy.da.UpdateCursor(table, ['SLOC_STOP_NUMBER'], "SLOC_SVC_LOC_NBR = '" + str(svcloc) + "'")
                    for csrow in csrows:
                        arcpy.AddMessage("ServiceLoc " + str(svcloc) + " found")
                        csrow.SLOC_STOP_NUMBER = stopnumber
                        csrows.updateRow(csrow)
                        arcpy.AddMessage("Stop number updated to " + str(stopnumber))
                    del csrows
                    del csrow                    
                else:
                    arcpy.AddMessage("Service Loc not found in CStar. Could not update Stop Number.")
                del i
                del svcloc
                del stopnumber
            else:
                arcpy.AddMessage("Service Loc Not Set in GIS.")
            del row
    del wslrow
else:
    arcpy.AddMessage("Please select a service Location.")
del sel

It works the first time but the get the workspace already in transaction mode error. I use this while in the same edit session. I basically click a feature update the field then push it into another database. Both databases use ORACLE but are on different servers.

Thanks for taking a look

Ray

RebeccaStrauch__GISP · ‎05-18-2016

Couple quick suggestions to try...

First, it's better if you don't have any spaces in path names....not always preventable but better in my opinoin. Take a look at Filenames and file paths in Python and How To Name Things In ArcGIS

Second, try commenting out the del row in line 52.

edit: also thro some printe/Arcpy.AddMessage statement to show what values you are getting from the cursor to make sure it is actually returning what you think it should.

RaymondGoins · ‎05-18-2016

I updated some of the code

# ------------------------------------------------------------------------------
# Cstar_Update.py
# Created on: 2012-06-05 12:56:35.00000
# Updated 05/18/2016 by: Raymond Goins
#   
# Description:
# Updates Stop number in Customer Star database from selected feature in ArcMap
# ------------------------------------------------------------------------------

# Import arcpy module
import arcpy

# Local variables:
# table = r"C:\PWSB GIS\GIS Scripts\conn\CStar.odc\SVC_LOC"
table = r"C:\PWSB GIS\GIS Scripts\conn\PWMedia.odc\CSTARTMP"

# Set variable to see if a feature is selected
sel = len(arcpy.Describe("WATER.SERVICELOCATION").FIDSet)

if sel >= 1:
    with arcpy.da.SearchCursor("WATER.ServiceLocation", ['STOP_NUMBER', 'SVC_LOC_NB']) as wslrow:
        # Loop through selected features
        for row in wslrow:
            stopnumber = row[0]
            svcloc = row[1]
            # Make sure SVCLOC number is not null
            if svcloc is not None:
                # Check to see if SrvLoc number exists
                with arcpy.da.SearchCursor(table, ['SLOC_SVC_LOC_NBR'], "SLOC_SVC_LOC_NBR = " + "'" + str(svcloc) + "'") as rcheck:
                    i = 0            
                    for r in rcheck:
                        i += 1
                    del r
                    # del rcheck
                    # If exists loop through
                    if i > 0:
                        with arcpy.da.UpdateCursor(table, ['SLOC_STOP_NUMBER'], "SLOC_SVC_LOC_NBR = '" + str(svcloc) + "'") as csrows:
                            for csrow in csrows:
                                arcpy.AddMessage("ServiceLoc " + str(svcloc) + " found")
                                csrow[0] = stopnumber
                                csrows.updateRow(csrow)
                                arcpy.AddMessage("Stop number updated to " + str(stopnumber))
                        del csrows
                        del csrow                    
                    else:
                        arcpy.AddMessage("Service Loc not found in CStar. Could not update Stop Number.")
                    del i                
            else:
                arcpy.AddMessage("Service Loc Not Set in GIS.")
    del svcloc
    del stopnumber
    del row
    del wslrow
else:
    arcpy.AddMessage("Please select a service Location.")
del sel

Thanks for the quick response. The spaces haven't been an issue as of yet. I can check it though.

I have commented it out before I posted the question, but no go.

As I said it works fine the first time I run it then error's out after that. If I shut down ArcMap and reopen it, it works again once then dies.

Ray

RebeccaStrauch__GISP · ‎05-18-2016

I would also comment out

del wslrow

since it should not be needed when using da cursors and the "with".

From what I understand, the arcpy.da. typically cleans up after itself (at least when using the with)

RaymondGoins · ‎05-19-2016

I have tried commenting and commenting all the del statements and no go.

This is the complete error

Traceback (most recent call last):

File "C:\PWSB GIS\GIS Scripts\Cstar_Update.py", line 38, in <module>

for csrow in csrows:

RuntimeError: workspace already in transaction mode

FC_Basson · ‎05-19-2016

It sounds like it is not liking the multiple transaction modes. Maybe you can find an answer in one of the following links:

arcgis desktop - Debugging RuntimeError: workspace already in transaction mode from arcpy.da.UpdateC...

Can I have multiple open arcpy.da.InsertCursor at the same time?

JoshuaBixby · ‎05-19-2016

I am not sure if it will address your issue, but you could cut out one search cursor (# Check to see if SrvLoc number exists search cursor) by using Make Feature Layer, Select Layer By Attribute, and Get Count to determine whether the service location number exists instead of creating a search cursor and looping over it to count records.

How are you executing this code, in a tool? Cursors aren't always great with releasing locks either right away or sometimes at all. To get around the issue, when it becomes an issue, I wrap the update cursor in a function and call it so that it goes out of scope when complete. For reasons I haven't seen explained, just deleting the cursor object doesn't always do the job.

RaymondGoins · ‎05-19-2016

Thanks for the tip. I will look into it. One thing I should have mentioned is only the SERVICELOCATION table is a feature layer. The odc connection is just an oracle table in another database. The reason I wanted the checks in there is in case the GIS editor puts the wrong SVCLOC number in the GIS table I want to make sure it exists in the SVC_LOC table.

I have been going through the code and found this works fine.

# ------------------------------------------------------------------------------
# Cstar_Update.py
# Created on: 2012-06-05 12:56:35.00000
# Updated 05/18/2016 by: Raymond Goins
#   
# Description:
# Updates Stop number in Customer Star database from selected feature in ArcMap
# ------------------------------------------------------------------------------

# Import arcpy module
import arcpy

# Local variables:
table = r"C:\PWSB GIS\GIS Scripts\conn\CStar.odc\SVC_LOC"
# table = r"C:\PWSB GIS\GIS Scripts\conn\PWMedia.odc\CSTARTMP"

# Set variable to see if a feature is selected
sel = len(arcpy.Describe("WATER.SERVICELOCATION").FIDSet)

if sel >= 1:
    wslrow = arcpy.da.SearchCursor("WATER.ServiceLocation", ['STOP_NUMBER', 'SVC_LOC_NB'])
    # Loop through selected features
    for row in wslrow:
        stopnumber = row[0]
        svcloc = row[1]
        # Make sure SVCLOC number is not null
        if svcloc is not None:
            #Check to make sure Stop number is not null
            if stopnumber is not None:
                # Check to see if SrvLoc number exists
                rcheck = arcpy.SearchCursor(table, "SLOC_SVC_LOC_NBR = " + "'" + str(svcloc) + "'")
                i = 0            
                for r in rcheck:
                    i += 1
                del r
                del rcheck
                # If exists loop through
                if i > 0:
                    csrows = arcpy.UpdateCursor(table, "SLOC_SVC_LOC_NBR = '" + str(svcloc) + "'")
                    for csrow in csrows:
                        arcpy.AddMessage("ServiceLoc " + str(svcloc) + " found")
                        csrow.setValue("SLOC_STOP_NUMBER", stopnumber)
                        csrows.updateRow(csrow)
                        arcpy.AddMessage("Stop number updated to " + str(stopnumber))
                    del csrows
                    del csrow
                else:
                    arcpy.AddMessage("Service Loc not found in CStar. Could not update Stop Number.")
                del i
            else:
                arcpy.AddMessage("Stop Number Not Set in GIS.")    
        else:
            arcpy.AddMessage("Service Loc Not Set in GIS.")
    del svcloc
    del stopnumber
    del row
    del wslrow
else:
    arcpy.AddMessage("Please select a service Location.")
del sel

Seems the arcpy.da function is causing the problem. I also removed "with" and went to deleting the objects manually.

All seems to work fine so far

Thanks again for the help and tips

Ray