Question posted in 
We have automation to login into different websites and perform some operations on website accounts. It is required that only one BOT can login simultaneously into a specific account to avoid inconsistency in automation flow.
We have a table in which we have stored website credentials:
|---------------------|------------------|------------------------|------------------------|
| id | email | locked_at | last_fetched_at |
|---------------------|------------------|------------------------|------------------------|
| 1 | [email protected] |2020-09-14 14:35:35 UTC |2020-09-14 14:35:35 UTC |
|---------------------|------------------|------------------------|------------------------|
| 2 | [email protected] | NULL | NULL |
|---------------------|------------------|------------------------|------------------------|
| 3 | [email protected] |2020-09-14 14:35:35 UTC |2020-09-14 14:35:35 UTC |
|---------------------|------------------|------------------------|------------------------|
| 4 | [email protected] | NULL | NULL |
|---------------------|------------------|------------------------|------------------------|
We fetch credentials with this query to be precise:
SELECT `credentials`.* FROM `credentials` WHERE `credentials`.`locked_at` IS NULL ORDER BY last_fetched_at asc LIMIT 1
Then we update the locked_at field with current time to lock the credential row for next process.
This is happening in a node.js app with mysql as backend db and being accessed by multiple BOT processes concurrently. We want to make sure two process don’t get same credentials & used transactions/ select for update to make this operation atomic but no good successful way/query to do so so far.
We are open to any third party integration like redis or is there is something in node which we can use to achieve this.
Thank you for your time.
2
Answers
The challenge here is to handle the various exceptions that will disrupt the expected flow and how to recover from them. In order to design the actual solution, you need to take into account average process times, how many bots working on how many websites and the severity of failure and whether it can be fixed as a side process. If the websites are within your control (not 3rd party sites), I would prefer to use instead a messaging (pub-sub) type of solution where your infrastructure notifies an agent on the website to handle the update, and the same agent ensures that only a single update is taking place at one time (per your requirement).
If this type of setup is not possible, then your next bet is to use something like what @Akina is suggesting, but also come up with a recovery action for every pitfall that might happen, including handling race conditions, bots timing out or returning incomplete tasks, websites returning unexpected responses, etc. This might get a bit tiring after a while if someone does not keep an eye on the process and adjust it to handle every unexpected surprise you’re bound to see over the long term.
Since you are open to use third party integrations, Redis is a good choice for distributed locking.
You can achieve the desired behaviour by making an entry related to each website in the redis.
setnx command is useful in locking,as it returns 0 if the value is already set.
Whenever a bot is process on a website, use redis command
setnx WEBSITE_NAME "BOTID"
with some expiry value.
You can set an expiry value of twice the average time it takes to complete processing a website.
If the response for the command is 1, i.e website is open for processing.
Response 0 indicates that a bot is processing the website.
Upon completion of processing, BOT should remove the entry in redis.
Setting expiry removes the lock automatically,even in the event of bot failing to remove the lock from Redis.
Redis is single threaded in server side and executes the commands sequentially, hence it takes care of the concurrency.