I am looking for the best locking design on a very particular situation.
I have a lot of robots running on several servers. All of them are running several times per minute and, at the end, they need to update a statistical table. Once every 15 minutes, I also have another robot which does some more intensive process on the statistical table.
When the robot which runs every 15 minutes does it job, it will look to see if the daily record exist. If not, it will create it. Then, we populate the stats for the day on that particular record. However, all other robots are doing the same verification. If the daily record does not exist, they will create it. So, it is a question of who gets it first. I discovered that I ended up with two or sometimes three daily records and this was because the more active robots are sometimes also seeing that the daily record does not exist and they will attempt to create it.
So, I need to enhance that, from some kind of locking approach, so that if the daily record does not exist only one will be able to create it.