Database Transaction Handling

[3bd-dev]

I've noticed in our current implementation that database transactions are initiated at the start of processing requests. This approach engages database connections early on, even for requests that might not ultimately require DB interaction due to validation failures or specific business logic conditions, etc ...

Key Concern:

• Early Database Connection Engagement: The practice of starting transactions at the beginning potentially leads to suboptimal use of database connections, particularly if the request does not proceed to the database interaction phase.

Potential Impact:

• Resource Efficiency: Might delaying transaction initiation until after our business logic improve the efficiency of our database connection usage?
• System Performance: Could this approach enhance system performance, especially under high load, by avoiding unnecessary occupation of database resources?

Question:
Why do we initiate transactions so early in the request lifecycle? Could there be benefits to starting transactions later, ensuring database connections are utilized only when there's a definite need for DB interaction?

[ardan-bkennedy]

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!!

[ardan-bkennedy]

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.

[3bd-dev]

@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.

[ardan-bkennedy]

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.

[3bd-dev]

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

[ardan-bkennedy]

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.

[3bd-dev]

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.

[ardan-bkennedy]

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.