Re:Practical Guide to SQL Transaction Isolation
by stickfigure on Tuesday August 08, 2017 @02:59AM
(#14955170)
A lock still doesn't give you a transaction across two systems that aren't transactionally connected. If you're specifically worried about retries you can create a "task" (row in a table) in the transaction and have a consumer read tasks and propagate the message.
A lock is seductive because in the happy path it looks like you have a distributed transaction. In practice making reliable calls across distributed systems involves more complexity. The hard part is figuring out what to do when your remote call times out and you aren't sure if it succeeded; once you resolve that, you can usually work with any concurrency scenario.
But this brings me back to my original point: having a library that blindly re-tries POST requests that result database transactions on every conflict is not going to work. Until every external API, from printers to credit card processors, has an Idempotency Key equivalent you just can't do that generically, and you will have to do that with a custom bit of code for every situation.
Isn't it easier to just acquire an advisory lock for the critical bit and take your own database's concurrency contention out of the equation?