Multiple scheduled tasks update the same table, how to ensure that the data is not duplicated?

requirements:
there is a table called the target table, where there are daily, weekly, and monthly goals for each employee. The fields in this table are roughly divided into four categories, each recording different aspects of performance, such as a recording orders, b recording sales, and then each category has a target quantity and a real quantity. Now it is necessary to recalculate the true number of all employees" yesterday"s goals and recent weekly and monthly goals after 12:00 every night. The structure of the table is roughly like this.

clipboard.png

problem:
is now implemented by writing four scheduled tasks, such as methodA, methodB, methodC and methodD, to calculate different goals. I first get a collection of all employees, and then put them into the for loop to find out the corresponding target data for each employee separately. The key point is that some employees may not have created goals for themselves. At this time, I will create a target data for him. Just fill in the real quantity, and the target quantity is empty. If he has created it himself, I will directly update the corresponding quantity field in this data. The code logic looks like this.

public void methodA(){
    //id
    List<Integer> userIds = userService.findList(queryVo);
    for(Integer userId:userIds)
    //
    GoalsVo goalsVo = new GoalsVo();
    goalsVo.setUserId(userId);
    if (goalsVo!=null){
        //
    }else{
        //
    }
}

the problem is that because it is divided into four methods to update different data of the same employee, if methodA does not find the target data and goes to the logic of the new data but has not yet performed the insertion, it just so happens that the methodB also finds that the employee does not have the target data and goes to the logic of the new data. This will result in an employee having two targets to record data.

solution:
now I"ve come up with some ideas, but there"s still something wrong with it.
1. Write another scheduled task to check whether the target data has been created, but if there is too much data, it may also cause the above result
2. Lengthen the time between each scheduled task and stagger it to address the symptoms rather than the root of the problem.
3. Set the index to prevent an employee from having multiple daily target data on the same day, but in this way, the data in the method executed later cannot be updated to the data created earlier, resulting in data loss

is there any good solution?

=
Thank you very much for your ideas. It has been verified that these ideas are all achievable. I have tried the idea of adding locks by my friend HamilFei, which can solve the problem.
2018.10.14

Aug.03,2021

set lock flag
which program wants to update first query whether there is a lock flag, if not, set it to lock, start updating
update finished, set unlock


set trigger to remove duplicates, maybe you can


add a lock before querying if the userId exists in the database.

  

judge the proportion of new cases according to the actual situation.
if the number of daily threads is small, extract the new thread as a separate thread, and make new judgments in this thread, such as using the ID, of daily insert logging to prevent repeated insertion, and interact with this thread when additional data is needed in other sites. Similar things can also be handled using caches, queues, and so on.
if there are a large number of new partitions per day, there are 4 inter-thread partitions, such as 1-100 for thread-1 cycle and 1-200 for thread-2 cycle.
you can also use database primary key policies or constraints to ensure that only the first insert is successful, and then discard it using try catch if it violates the constraint.

MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-7c27de-16573.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-7c27de-16573.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?