For my case multiple application uses data from db based on date, this data is date specific so if for a date data exist application should just read it otherwise it should fetch information from different site and do some calculations to create data for a date and store it in db and use it.
Since multiple applications require this data I'm looking for data in db by date if it exist simply read it otherwise fetch data from internet and after some processing store it in db with date.
But as same thing will be done by multiple application there are chances that first application comes and look for data in db for a date and founds no data so it start fetching this data from internet, in the meanwhile other application also looks for same date data but it also finds no data so it will also start fetching data and by the time its done other application would have already stored that information so second application would try to insert duplicate information in the table.
What is the best approach in this scenario to avoid duplicate ? Should checking just before insert or using transaction can help ?
Best Answer
You can add unique key constraint on the db table , the table itself will prevent the second application to insert duplicate data and return error code such as "unique key violation" , in the application you will need to handle this error/exception.