Distributing multiple transactions to multiple Robots without deadlock using Database

Build
#1

Hi Everyone,

Need some of your suggestions : -

Requirement - In a DB, i have multiple records(eg. multiple policies), i want those policies to be processed by distributing the policies to multiple Robots without any deadlock or duplicate creations. i dont have orchestrator so i cannot use queue transactions for this. All i have is a DB and multiple machines in a same network which can run the Robots.

I got to know that stored procedures would work here but not sure how to do that.

please drop your valuable suggestions.

#2

Hi @Raju23c,

I have deal with same scenario before.
step 1 : Need to add one extra column in the same table with null/empty values.
(your table should have any Identity column or any column contains unique values)
step 2: loop and iterate through that yourTable
step 3: inside for each add one condition to check Trim(row.item(“yourNewlyAddedColumnName”).ToString)=""

if above condition is True then process that record/Transaction
step 4: before you start processing that transaction hit one update query like
Update yourTableName SET yourNewlyAddeddColumnName=‘inProgress’ where IdentityColumn=yourUniqueValueFromIdentityColumn

If Trim(row.item(“yourNewlyAddedColumnName”).ToString)="" is false then skip that record by assuming another bot is performing /performed that one
So, above scenario will eliminate database deadlock.

Hope this will work for you.

#3

Hi @Deepak94 thank you for your suggestion, but there is a possibility of deadlock if we do this. for eg, we are suing 5 BOTS here, if all the 5 BOTS run the same query simultaneously means there would be high chances for duplications, please correct me if am wrong.

#4

Hi @Raju23c,
I don’t think so,
Because we are checking for ‘null’ value of that newly added extra column after not picks up particular record/ transaction. (Inside main for each)
If we get empty or null value for that record that means its fresh record and we will proceed with record by updating that newly added extra column to ‘inProcess’
So, when other bot will pick that same record then we will check the value of newly added extra column and we will found its value as inprocess and we will skip that record

#5

Ok @Deepak94. Will try this method. Thanks for your help.