How to guarantee web script execution sequence/order or not parallels queues

Question

This is a very simplified version of the problem:

You have an integer saved on a server called "B" and you have an script saved on a server call "A" exposed to the internet. The script pulls the integer from server B, adds one to it, and then updates the remote integer. None of this operations are atomic, the pulling, the adding and the update, all of them have a random latency.

If there is only one user calling the script in sequence there is not going to be any problems, and the int is going to be incremented consistently. But, since script is exposed to internet, it can be called in parallel from several users at the same time, so while an instance of the script will be incrementing the value locally before updating it remotely, another one will pull the actual value from the remote server, incrementing an old version of it.

My solution is to use a queue (or at least try to); So instead of calling the script, the users will call another script that will write on a DB the execution request (a message; "please call script.php"). Since the writing in the DB is not atomic, I'm also writing the microtime of the server so later I can order the execution requests by it. A third script, usually call called a worker, will query the DB for this messages and process the request.

The problem I have is most of the actual web queue solutions just call the worker every time they need it, because the don't care about order, they just want to make a synchronous job, asynchronous so the website can be faster. I cannot call the worker script every time a new request arrive since the problem will persist, two or more workers can be instantiated at the same time messing the calculations. I can put the worker on a cronjob to be lunch each 60 seconds, but it will be slow for my needs. I can also just put a while loop inside the worker that just query the DB indefinitely but that just seems inefficient to me, or maybe this is actually how queue works? just a while cicle looping till the end of times with maybe an sleep on it... What do you think?

Answer 1

Since the writing in the DB is not atomic,

It needs to be.

If you're using any sensible, relational database then you should be able to use Transactions to make the update+read cycle atomic. Note the order.

begin transaction ; 

   update table_with_counter 
   set counter = counter + 1 
   where ... ; 

   select counter 
   from table_with_counter ; 

commit ; 

The transaction will ensure that no-one else (no other process) can update the counter until the first update has either completed or rolled back (in the unlikely event of a failure). Different DBMSs vary in how they achieve this, but the principle is the same.

Answer 2

How you can solve this depends on the synchronization primitives you have available or can easily implement. However, that should be unnecessary: any ACID-compliant database guarantees atomicity. If your database can't do that, it's either MongoDB or useless. But why would you want to cripple yourself deliberately? There are many high-quality free software database systems available for you to use.

Locks and Mutexes

To sequence the writes, you can make the writes mutually exclusive. Every process that wants to write to the DB first acquires the lock, then releases it after the write. This ensures proper ordering. As a simple inter-process lock mechanism, you can use a lock file since all common file systems can be used mostly ACID-ly (see the flock() system call in Linux and most Unixes).

You could also offer users of your webservice timed locks, i.e. the lock is either released with a write request by the user, or after a timeout. This can be problematic with many users (since they might have to wait a long time for a lock), can be easily targeted by a DoS attack, and may be unusable for users with high-latency connections since they might be physically unable to make a write request within the timeout.

Compare and Swap

For a change request, the user submits both the old value they received when they decided to change the value, and the new value they want to change to. The server uses some mechanism to ensure its own atomicity, and then only performs the user request if the current value matches the value submitted by the user. If anything changed, the user is notified and they can resubmit.

This is problematic if you have many users: of all users that are currently submitting a change, only one request will succeed, and all others have to retry. Also, users with high-latency connections are less likely to succeed which degrades their experience even further.