Multi Bot Database Performance issue

Help Community
,
1

Hi there,
we have a UiPath solution that manipulate database table at backend.
Issue is solution is used by 3 bots at the same time and scenario is bot 19 is trying to update table and lock it while other bot also try to access the same table to read or update it and they receive the deadlock victim error.
Here is the orchestrator log:
any Advice on this issue or database design?
Thanks

BOT019 Main.xaml: Update database status to :In progress
BOT013 Updating the TaskStatus
BOT013 Transaction (Process ID 241) was deadlocked on lock
BOT033 Get Pending Record
BOT033 Transaction (Process ID 133) was deadlocked on lock
2

@afsaneh_keivani

You can use a retry scope with a random delay …so that the operation is retried after a random
Delay by any bot and it can move forward as in that time other bot would have processed its request

Cheers

3

Hi Anil,
Thanks for providing the feedback!
Yes retry is added but i was wondering if there is any thing can be done from database side.
we have Store procedure that update table or fetch data.
Rather than Retry is there any alternative design that can improve the performance?
Thanks

4

@afsaneh_keivani

Other way would be to use different connections for eqch bot…instead os using same user

Cheers

5

Hi Anil,
Each bot is considered a different user.
Senario is diffrent Bots(Users) try to update the same table at same time and Dead lock happen.
Kind of Database issue.my question what is the best practice to design the database and store procedutres to prevent this issue.
Thanks

6

@afsaneh_keivani

So you need a locking mechanism for comits

Cheers