Initiate a Transaction per Request

[oldschoolbg]

This is a general question more around "is this possible" and "how would I go about achieving this?"

My scenario is that I would like to initiate a transaction for every incoming request (I'm using nestjs, but that's bascially express under the hood I think) and then either commit or roll back based on whether any exceptions were thrown anywhere in my code.

The purpose is to retain transactional integrity across a complex system where many writes, from many services, could take place throughout the lifetime of each request.

I can see a solution involving interceptors and a global transaction being created but would welcome any input or suggestions.

I'll update this Discussion as my own research and investigation goes along, to assist anyone coming after me who may have a similar requirement.

[vitaly-t]

A transaction is an atomic operation, so splitting it across multiple asynchronous handlers, though possible, is a difficult task.

I was asked this before, I would always refer to poor system design. You should instead roll requests into some kind of internal state that can trigger a summary transaction once sufficient data is available, and share its result.

This library however doesn't stop you from trying to split a transaction, but it doesn't provide anything extra for it. I remember seeing some library(s) trying to do this, can't remember their names presently, but you can and search for them yourself. Other than that, I won't be going into detail here on how to achieve it, for it would be quite time-consuming.

[vitaly-t]

This is confusing. Transactions here are to give you the chance to execute any number of requests within the same connection context. This is their primary task. So what problem exactly you are having with that? What you described above, you can do with transactions here naturally.

[vitaly-t]

If you are talking specifically about disconnecting COMMIT from transaction logic, then no, you cannot do it with this library, not without re-implementing transaction logic manually, which is doable, but a treacherous path.

[oldschoolbg]

OK so I'm not explaining this well.

I'm saying we initiate a transaction when we start handling an httpRequest, any connection to the database appends to that transaction, and then when the httpRequest is about to complete it calls COMMIT.

Consider this case.

service one causes data to be written to the database within a transaction.
service two later causes data to be written to the database within a second transaction.

If the call from service two fails for whatever reason. the data written by service one will remain.

If the transaction was at request scope then the call from service two failing would cause the data from service one to never be COMMITed.

Does that make more sense?

[vitaly-t]

Ok, you will know better the internal logic of it, I got nothing else to add really :) If you run into something that's library-specific, then feel free to ask ;)

[oldschoolbg]

thank you :) I certainly shall. I truly appreciate your time on this, and as everyone always says - thank you for creating pgPromise. Cheers