Comment by gunnarmorling
Comment by gunnarmorling 14 hours ago
> No they are not common at all. You probably invented them just to make pg look bad.
Interactive transactions are a well-defined concept in database technology, exactly with the semantics described by the author: transactions with multiple queries, with application logic in between, for instance taking a result from one query, processing it, and running a second query with the outcome of that processing as input.
That said, the example in the blog post feels a bit contrived to me, the question being whether that transaction could be structured in a why so that both updates run right after each other towards the end of the transaction, thus significantly reducing the duration of the row lock being held (which is not to say that the general problem described in the article doesn't exist, it does for sure).
I wanted the example to be simple and relatable. So I went with something close to the transaction example on the Postgres site (which to be honest also feels contrived).
In practice I'd never implement a ledger like this these days, I'd much prefer an append only model with some triggers. But, like you said interactive transactions are very much a thing.
Out of curiosity do you have a better/less contrived example in mind?