News for hackers. Stuff that matters.
Re:Transactions Are Not Locks (2022) by maxbond on Saturday October 21, 2023 @10:27PM (#37972320)
I'd caution against holding a transaction for the duration of an API call to a foreign system. API calls can take a very long time, especially if you are retrying on timeouts. Long lived transactions are dangerous, because it makes your locks very long lived, which interferes with concurrent transactions and vacuuming. I've seen it bring down production before.

Consider for example the following scenario; you are experiencing higher than normal load, leading you to send a lot of requests to this other system. Their scaling is flawed, and your traffic actually brings them down. This leads to timeouts and long transaction times. Now you are holding lots of locks for a very long time, and you database is under a heavy load - you're now in danger of overloading your database and going down.

I'd suggest commiting a record representing your intention to integrate with this other system, executing your API call, and then updating your state (assuming the call is successful). (For the example above, circuit breakers would also be an important mitigation.)

There are no comments on this comment yet.