1

I have a python script tool that I would like to publish as a geoprocessing service, but I'm not sure it'll work because this script uses multiple update and insert cursors to update database tables.

More than once instance of these cursors cannot occur to ensure data integrity, so I am wondering how to make it so only one person can run this geoprocessing service at a time?

I see there is a "pooling" parameter, but instance are per machine so if I'm understanding it right, and just using the below as an example, each user could have up to 2 instances of the geoprocessing service running. Even if I reduced the number to 1, that still could cause conflicts since instances are per user machine, not just one user.

enter image description here

1

GP service configuration

The instance per machine is referring to the server machine, not the client's machine. This means that if you set it to be 1, then only a single instance of the GP service can be up and running at single point of time. If another user (that is, client) will submit a request, one would need to wait for the currently running instance to complete. So, this should probably give you a feeling of safety.

Safety of DBMS data updates

However, generally, I don't recommend using arcpy for updating the production data in a DBMS. It is usually considered to be safer to use SQL in your DBMS to edit the data as it provides transactional support. This means that data changes are encapsulated into transactions meaning that no editing conflicts can occur. Moreover, should something go terribly wrong during the update, the transaction will be rolled back and the data are left intact.

Transaction support in arcpy

It is true that arcpy does provide transactional support using the arcpy.da.Editor class, however, I have seen that it behaved unstable in some environments, so again, I would not recommend using it for editing the enterprise geodatabase data using GP services where concurrent editing can occur. It does not have any advantages over SQL except that you are using a familiar Python construct such as arcpy.da.UpdateCursor.

Alternatives

If you are comfortable using SQL, but still would like to use arcpy and Python, do consider using the arcpy.ArcSDESQLExecute class which would let you execute SQL queries from Python. This is very handy, because you would be able to do all kinds of data preparation and analysis in arcpy, but update the production data using safe SQL operation.

Another way to work with the database data updates is via a Python package that is capable of connecting to DBMS databases and executing SQL queries. Depending on the RDBMS used, you might consider pymssql for MS SQL Server, pyodbc for Oracle, or psycopg for PostgreSQL. SQLAlchemy is another popular choice for developers working with relational databases.

Using SQL for data updates will make your design future proof should you decide to let multiple users perform concurrent editing in the future using GP services. If you will base your workflow on arcpy, you would need to re-engineer the whole solution. So why not make it right from the beginning.

  • thanks for the well-thought-out answer! Do you happen to know what happens exactly if someone tries to run a GP service that only allows one instance to be made and there is one running? Does it just queue up the second request in the background? Do they get some error message saying they need to try again later? I will definitely be exploring the best way to update the DBMS based on your suggestions too, thanks! – MKF Mar 15 '18 at 16:10
  • You can learn more about in this help page. It is for 10.2 version, but not much has changed since that time in this regard resources.arcgis.com/en/help/main/10.2/#/…. Read through the whole doc paying particular attention to the Timeouts section. – Alex Tereshenkov Mar 15 '18 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.