Suppose I have a table that is constantly being updated with tasks to be processed. One column in this table is status and when a task is first entered into the table, status = ‘submitted’, when a host is processing the task, it changes status to ‘processing’ and when the data processing for the task has been completed, it updates status to ‘completed’. I will have multiple hosts that will be accessing this specific table to check to see if a task has been added. Currently this is the code that I have:

conn = get_mssql_conn().connect()

result = conn.execute(
                    "SELECT TOP 1 * "
                    "FROM ad_tool_enhancement "
                    "WHERE status = 'submitted' "
                    "ORDER BY jobid ASC" 
                    )

row = result.fetchall()
   
if row:
    jobid, Job_name, Email, status, date_submit, time_submit, process_time, json_data, Input, analysis_type, source, groupby = row[0]

    start = datetime.datetime.now()

    conn.execute(f"UPDATE ad_tool_enhancement "
                f"SET status = 'processing' "
                f"WHERE jobid = {jobid}"
                )
    
    #Then the task is processed                

This works fine for when there is only one host running/processing the tasks but in the future there will be more and I believe that I will need to use locks/transactions to ensure that two hosts do not start processing the same task at the same time.

Where I am confused is that I have never seen a transaction used for a read statement (and that part is just as important as the update part for locking) so I am not sure if it will work.

I have tried looking at the documentation for SQL Alchemy and for MS SQL Server but I have not found anything specific to my problem.