Database Transaction Handling about service HOT 8 CLOSED

When a transaction is initiated, it is because it is needed. I agree we can move the middleware to the front and not start that transaction until auth is completed. That is a great catch. Unfortunately, that is the best we can do since this middleware needs to be route specific.

I am going to make that change!!

from service.

Realized moving the middleware up made things worse. We really want it circling the handler function so it is the last thing that happens before the handler function is executed. So it was in the right place the whole time.

from service.

@ardan-bkennedy thank you for your response, Mr. Kennedy.

To clarify, my question is specifically regarding the logic within the handler. There are scenarios where we might have a substantial processing load, possibly including interactions with third-party services, that occurs before any database interaction. During this time, a database connection is held busy by the transaction, potentially for an extended period, without being utilized.

from service.

In normal database access, the connection is selected and used at the time it is needed. Only when we are starting a transaction would this occur. A request should never be held for an extended period of time because it will timeout. There are limits to how long a request can take. In your scenario I would question a few implementation details.

That being said, let's say you have a 30 second window and the request will take 30 seconds to process, then yes, the transaction is being held for 30 seconds since the transaction is started at the beginning of the request.

Now, if this was me and this was a problem, I would ask if this transaction is needed cross-domain or not. If the answer is NOT, then we can start the transaction in the business layer function.

If the transaction has to be cross-domain, then this is what it is. Now you need to potentially rethink the situation you have infront of you.

from service.

I understand the considerations regarding request timeouts and I agree that the implementation is clean and well-structured. My concern it's more about the general processing time of requests and the flexibility

let's say In some scenarios, a significant portion of a request's processing time – let's say approximately 95% – is dedicated to complex logic and validation phases that precede any database interaction. The actual time spent on database operations might only constitute about 5% of the total request duration. The crux of my concern lies in our current transaction management strategy. it involves holding a database connection for the entire duration of the request, even though the actual utilization of this connection is limited to a relatively small fraction

Another scenario that raises concerns is our handling of transactional dependencies, especially where subsequent logic within the same request hinges on the successful commit of a database transaction.

Would it be feasible to explore an optimization, it can give us the flexibility to control the transaction when we need it

from service.

I tried to answer your question before.

Now, if this was me and this was a problem, I would ask if this transaction
is needed cross-domain or not. If the answer is NOT, then we can start the
transaction in the business layer function.

If the transaction has to be cross-domain, then this is what it is. Now you
need to potentially rethink the situation you have infront of you.

Please ask me questions about this statement that didn't connect with your question.

from service.

my questions and concerns are primarily centered around the management of cross-domain transactions. Currently, we're handling these transactions through middleware. However, I'm contemplating whether there might be a more effective approach. like the Unit of Work pattern in DDD, which offers direct interaction with the business layer. This approach might lead to more efficient database connection management and avoid our dependency on HTTP specifics. I'm keen to hear your insights on this.

from service.

In think you should experiment, but know we had transactions at the business level and we couldn't achieve cross domain transactions that way. The solution we have today took 2 years to figure out.

If you need cross domain it must be initiated at the app layer. In the end, you should experiment with new ideas. Maybe you will come up with something better.

from service.