I made a website using mysql (InnoDB), php-mysqli and PDO to track progress data.
Originally, there are massively simultaneous SELECT-UPDATE
queries at the backend. In one transaction, two statements are presented: SELECT id FROM ... WHERE started = 0 FOR UPDATE
, UPDATE ... SET started = 1 ...
. They are separated because of some pre-processing. The backend works quite well.
Now I'm coding a frontend monitoring the database and plan to use statements like SELECT COUNT(started) FROM ... WHERE started = 0
. I'm unsure about which kind of lock mysql will use under such circumstance. As I needn't guarantee every frontend call is successful, the success rate of the backend is more important.
To clarify, "backend" here is several PHP files that would be accessed by program as REST API; "frontend" is a PHP accessed by user.
Q: May these monitoring SELECT
result in backend failures? If so, is there a way of prioritizing backend access?