PHP MySQL and Queues, Table Locking, Reader/Writer Problem

I have following Scenario:

PHP(Server, Writer) ----> MySQL Database <------ PHP(Client, Reader/ Writer);

• PHPS = PHP Server
• PHPC = PHP Client

How it works?

1. PHPS writes data to temporary database tables (queue_*).
2. PHPC is triggered by a 1 h开发者_运维知识库our cron.
3. PHPC starts, connects to database and caches all records locally (how? no idea, local mysql db? sqlite?)
4. PHPC executes tasks defined in those records one by one
   • if the task is successful it removes it from databases
   • if its unsuccessful it adds that record in database under a reports table.

How do i implement this such that

• No half written records from PHPS get to PHPC.
• PHPC can cache all records locally after one query to process them.

Any other ideas that you might have and share are highly appreciated.

MySQL's default locking will ensure that no "half-written" rows are fetched. As far as "caching locally", it seems like all that means in your scenario is reading them out of the database into a local PHP data structure like an array.

you can see about MySQL locking here: Locking in MySQL. Remeber unlock table after finishing write data.