'Several competing processes sharing and iterating through MS SQL rows

I am not sure what the best way to solve my problem is. In Python I have 48 processes running concurrently with multiprocessing.Pool

Each of those processes needs to obtain a server IP located in an SQL table called Servers with the following Columns: ServerIP LastUsed

A separate SQL table contains a log of whether the last use of the server was not successful. That table is called ServerLogUnsuccessful with the following Columns: ServerIP Date

I want each of the 48 processes to get a ServerIP from the Server Table that has no more than 10 unsuccessful rows for that same ServerIP in the table ServerLogUnsuccessful within the last 1 hour. However, I do not want the same ServerIP to be used twice in a row by any of the 48 processes running in Python. Each time a ServerIP is obtained, the LastUsed column in the Server table should be updated to the current time, such that it is only used again after first using all of the other Servers with an older LastUsed time that have no more than 10 unsuccessful rows/entries in the table ServerLogUnsuccessful within the last 1 hour.

What would the best way be to accomplish this?

Solution 1:^[1]

Assuming that other processing is not performed between obtaining the ip address from [Servers] and updating [Servers].[LastUsed], you could use the SQL Server locking mechanism to block execution whilst obtaining an ip address and updating the [LastUsed] column.

The following could exist within a stored procedure or script block, but must be executed as a single batch.

begin transaction
-- Block other executions of this code
exec sp_getapplock
    @Resource = 'Get Server ip'
    ,@LockMode = 'Exclusive';

/*
    Insert code to select ip address from [Servers] table

    Insert code to update [Servers].[LastUsed]
*/

-- Allow other processes to continue
exec sp_releaseapplock
    @Resource = 'Get Server ip';
commit;

See the following msdn links for more info:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-getapplock-transact-sql?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-releaseapplock-transact-sql?view=sql-server-ver15