Hi. I have a python based geoprocessing service running on arcgis server 10.8 which is deployed on several environments. There is an application that makes multithreaded calls (roughly 9 calls concurrently) but about 2 or 3 will fail every time. I get one of the following 4 errors:
- Error executing tool. {Name of Tool} : Could not open SDE workspace. or RuntimeError: Object: Could not open SDE workspace {Path to sde}/testgis.sde
- Connection information provided was for a non-administrative user
- RuntimeError: cannot open {Path to sde}\GIS.sde\GIS.DBO.TravelPolygons\GIS.DBO.TP_SITES_60'
- Error executing tool. {Name of Tool}: ERROR 000229: Cannot open {Path to sde}\GIS.sde\GIS.DBO.Designation_PC
Usually the service will fail at the first time the script tries to connect/query a database table sometimes at the second or third time it tries to connect (bullet 3 or 4). I do not get any error if a single call is made and it is only happening in our production environment. The concurrent calls work in our lower environments. My theory so far is that the concurrent calls may be causing locks which prevents a few of the threads from connecting to the database. Adding a delay is not an option on the application side. I have tried to adding a random delay right before it connects using the sde in the geoprocessing service code but it has not worked consistently. Does anyone know a way to get around the locks or any database settings that could be changed?
below are the two functions where the errors typically occur:
def getCTfromTable (x,y,minutes):
tableName = g_CT_Table_Name
isFound = False
Cts=''
expression = 'lat ='+str(y) +' and long='+str(x) +' and traveltime = '+str(minutes)
arcpy.AddMessage("where clause CT " + expression)
g_CT_Full_Path = os.path.join(g_SDE_Location,g_CT_Table_Name)
arcpy.AddMessage (g_CT_Full_Path)
with arcpy.da.SearchCursor(g_CT_Full_Path, ['OBJECTID','lat','long', 'cts'], expression) as cursor:
isFound = True
for row in cursor:
Cts = row
return Cts
def getTPfromtable(x,y,minutes):
tp = None
tableName = "TP_sites_"+minutes
expression ='latitude ='+str(y) +' and longitude='+str(x)
isFound = False
arcpy.AddMessage("where clause TP " + expression)
g_TP_Table_1 = g_TP_Table+str(int(minutes))
layerpPath = os.path.join(g_SDE_Location,g_TP_Table_1)
arcpy.AddMessage(" TP table " +layerpPath)
arcpy.AddMessage("Expression .... " + expression)
with arcpy.da.SearchCursor(layerpPath, ['Shape@'], expression) as cursor:
for row in cursor:
isFound = True
arcpy.AddMessage("line TP FOUND IN THE TABLE... ")
tp = row[0]
if isFound == False:
preResult = AutoPreprocess.preprocess(y,x)
arcpy.AddMessage ("NOT FOUND IN TP - PREPROCESSED RAN")
with arcpy.da.SearchCursor(layerpPath, ['Shape@'], expression) as cursor:
for row in cursor:
isFound = True
arcpy.AddMessage("FOUND AFTER PREPROCESSED... ")
tp = row[0]
return tp
