CODEWITHSUNDEEP
phpapitransactionsdistributed-transactions2phase-commit
odjuret
odjuret

Reputation: 19

Stored Procedure and REST API call as a transaction

Imagen that you have a stored procedure in a database that you call from PHP. That procedure works like a transaction and selects/inserts data into the database. If it succeeds it commits, if it fails it rollbacks.

Now imagen that you need to store the same data in a external system via REST - in a transaction.

So if you run the stored procedure first and it commits you wont be able to rollback the database if the API call fails. If you run the API call first and it succeeds you wont be able to rollback the API call if stored procedure fails.

I'm in a situation where I need to update local database (SP) and two different API endpoints in one transaction in PHP.

Could anyone give advice on how this transaction is supposed to be managed?

Thanks!

Upvotes: -1

Views: 799

Answers (1)

ccov77
ccov77

Reputation: 791

Try to design your services as detached from each other as possible. If your external services must be updated in a transaction, its an obvious sign of coupling. For your particular case, do the external services need to be immediately updated, or can we rely on eventual consistency?

I’d suggest trying to push the calls to the external services to a Queue. As far as your local service goes, the transaction should be executed and then two services should be dispatched to the queue to update your external services.

Now, the magic of Queues is that you can usually monitor them and in case of an error, replay the failed ones. In a normal situation, the Jobs in the Queue will run without problems. When there is an error, the developer goes in, patches it, and then replays the Jobs. Eventually, the external services catch up with the local one.

Upvotes: 0

Related Questions