Solve race condition with parrell running applications

The problem seems to be -as the problems are with race conditions- speed.

It takes longer to update a state then it takes to check that state, therefore, while any process is updating a state, the other process may decide at the same time, to update that state too, resulting in 2 processes being fed the same state, whereas the desired outcome is that process 1 reads state n, and process 2 reads state n+1

In the real world now,

The current flow is (pseudo code)

while(jobs and timeleft) {
 get Job (max jobs returned=1, oldest job in range is returned)
 process job
 if return status = too soon, update job timeout+=30
}

Perhaps you can implement your idea in the same kind of pseudo code. Abstract thinking is good, but sooner or later it will have to be worked out... right?

Thank you for the suggestion.

I have implement a variant of this successfully.

MySQL allows the creation of named locks. The locks do not have any relation to the tables or columns (as far as mysql is concerned)

What I have done is:

Whenever the applications query a number, it will first try to obtain a named lock (prefix+number). If it can obtain this lock, then it goes and read the information required (has cooldown period expired) - and in case the number is not expired, it keeps the lock, until either the application disconnects from the mysql server, OR, until the application requests to update the timestamp for that number.

Test runs confirm the race condition to be solved.

I tested this by having a small script set to execute at a fixed point in time (like say 3pm), trying to determine if the number has cooled down. The script was started 10 times (parallel processes). (the script was also said to wait 10secs after reading the value before shutting down)

The old system gave me these results (note each line is output from a different instance)

true
true
true
true
true
true
true
true
true
true
true

As you see, the race condition exists here.

With the locking in place, the output of the same program (run 10x parallel) is:

true
null
null
null
null
null
null
null
null
null

In effect, only the first one to obtain the lock gets at the value. All the other ones receive null (busy signal)

SELECT GET_LOCK('name',max_timeout)

Is very useful in this case.

With excuses to moorhouselondon: I was going to give you some pts, but I closed it too fast to remember.

You can ask a moderator to re-open the question and split points.

good idea.