I plan to use PHP to connect to 2 remote servers. One is a MySQL database. The other one is a Job Queue server.
For every Job that I push to the Job Queue server, I want to ensure that (exactly) 1 record is inserted into the MySQL DB. I want to ensure that the MySQL DB records and the Job Queue tasks execution are 100% in sync.
Since both are remote servers, I am worried that sometimes there may be connection problem to one of the servers (or both).
In my PHP application, how can I ensure that 2 remote operations complete successfully, and rollback all operations if 1 of the remote operations fails? Is there any programming best practices that I should follow for this kind of scenario?
This sounds like a pretty basic AND use case. Since you're in PHP, you're not worried about asyncronous calls or timeout waits. To pseudocode it:
Start Transaction1
... do stuff... //transaction 1 still open, no commit
Start Transaction2
... do more stuff...
If (Transaction1 success && Transaction2 success) {
commit Transaction1
commit Transaction2
} else {
rollback Transaction1
rollback Transaction2
}