Concurrent requests queue and database write lock

[theoretic]

Hi friends! Many thanks for the brilliant Processwire!

I have an interesting question concerning concurrent requests. I'm working on a PW-driven API which should pass some tests required by my client, including a so-called concurrent request test. The test harness generates a certain number of unique http requests and "fires" them in almost no time (they're received by webserver with time difference of the order of milliseconds). However far from real life use cases, this test is absolutely required by my client.

Experiencing such a high (however short) load, PW reveals some weaknesses mainly concerning database interaction. Almost all concurrent requests should update a PW-driven numeric data field (in fact a database table). Concurrent requests try to increment/decrement its value, and after that a final check of the field value is performed.

In most cases the final check fails. In-depth study shows that PW may receive new requests before the current request is processed, and this may lead to wrong final value.

I tried some possible solutions:

1. Wrapping field-changing operations in a database transaction. No success.
2. Using mysql table lock. In my case this also didn't give a versatile result.
3. Writing a lock mechanism denying any operation with data field until the previous request(s) are ended. Not versatile enough again.
4. Organizing request queue. Maybe the best possible solution, but didn't try it yet.

So what are your ideas in this case? Any possible approach is welcome ?

[Pixrael]

Are you using innoDB? read: https://processwire.com/blog/posts/using-innodb-with-processwire/

[theoretic]

@Pixrael, good question! I thought that PW database uses InnoDB by default but it was wrong. Did an InnoDB migration without changing any code, the concurrent tests are experiencing the same troubles as before with MyISAM, but now there will be more space for experiments. Thanks!

[ryan]

@theoretic 

Sounds like a fun project. If I understand correctly, you are doing a select to read the value, then incrementing the value in PHP, then writing the value. With concurrent requests doing that, there's always a gap between the read and the write, so you should expect that some of the requests are naturally going to collide, unless you use a different strategy.

What you probably need instead is a single update query that does a "col=col+1" instead. Otherwise you'll have multiple requests reading a value (like the number "3"), incrementing it, then writing the same number "4" to the DB. So the final number would always be less than the number of requests sent. The reason being that some other request may have incremented the value in between the time another request read and then wrote the value, so they'd always be overwriting each other, writing the same "4" to the DB. 

If you need separate read/write queries, you'd need to obtain both a read and write lock to the table row you intend to update, before you even read the value. And you'd need to release the lock after you write the value. You'd also have to account for the cases where you cannot obtain a lock (due to being locked by another request), by having a retry mechanism. Without a retry mechanism, you'll also end up with some unaccounted for requests.

A good example of the pattern you are trying to achieve is implemented in the SessionHandlerDB module. It has to obtain a read/write row lock and read the session values. Then at the end of the request, it has to write the session values, then release the row lock. That way concurrent requests in the same session can't overwrite each other's data. But it also means that concurrent requests are queued and have to wait in line for their turn to control the data. The same would be true in your test case. 

Lastly, pay attention to the http response code on the concurrent requests. Requests can be refused, especially if concurrent from the same session or IP. They can be refused by Apache or PW. So a request cannot count towards a total unless it returns a "200 OK" response code (this would be true for any concurrent testing of web requests whether w/PW or elsewhere).