Background
I have an PayPal IPN handler in PHP`(5.2) that processes transactions and store date in a MySQL database. The site has low activity.
Occasionally the MySQL query fails. (Very rarely) I log everything and if a PayPal message failed to be processed I can just look in my log file and resend the PayPal message.
Ideas / Concerns
But I was wondering if I could make the process a bit more robust and instead have the handler re-try the mysql query before giving up.
I probably want to wait a little bit - not hammering multiple query attempts immediately after each other.
I was hoping to find some patterns for this - searching for stuff like "php mysql query retry" without having much success in that.
Questions
Are there good practice guides for this?
Existing libraries?
Is a simple loop and sleep between each query attempt ok, or can that have undesired side-effects? Too native?
Good transaction safe code in the php world is not the norm but there are plenty of examples if you look in java or .net code samples.
Most good database code I've seen puts queries in a function and retries about 3 times immediately before giving up and logging/queuing. Hopefully that will take care of your problem.
Best practice would be to use innodb tables and put all your critical sql statements in a transaction that is either committed or rolled back. That way your data is in a consistent state even if one particular query fails. (note this can also compound locking problems if you are not careful).
You don't want to do a long sleep with polling as this could hang your users browser. They need an immediate response to confirm that the purchase went through. Putting the job in a queue with a cron job or server daemon is another way to go, but doesn't give the user the immediate feedback and is probably overkill for this purpose.
If immediate retries don't work you've got some other kind of underlying locking problem, reason for failure that you need to workout.