CODEWITHSUNDEEP
performanceentity-frameworktransactionsdatabase-performance
DarcyThomas
DarcyThomas

Reputation: 1288

Does using a transaction but not actually making any queries have a resource cost?

Ok so one of our team members has suggested that at the beginning of every http request we begin a DB transaction (we are using Entity Framework Core), do the work of the request, and then complete the transaction if the response is 200 Ok, or roll back if it is anything else.

This means we would only commit on successful requests.

That is well and good, when we perform reads and writes to the DB.

However I am wondering does this come at a cost, if we don't actually make any reads or writes to the db?

Upvotes: 4

Views: 559

Answers (2)

Rick James
Rick James

Reputation: 142298

Don't blindly put BEGIN...COMMIT around everything. There are cases where this is just wrong.

What if the web page records the presence of the user, or the loading of the particular page? Having a ROLLBACK destroys that information.

What if there are two actions on the page, and they are independent of each other? That is a ROLLBACK for one is OK, but you want to COMMIT the other?

What if there are no writes on the page? Then there is no need for BEGIN...COMMIT.

Upvotes: 1

usr
usr

Reputation: 171178

If you use TransactionScope for this then the transaction is only physically opened on the first database access. The cost for an unused scope is extremely low.

If you use normal EF transactions then an empty transaction will hit the database three times:

  1. BEGIN TRAN
  2. COMMIT
  3. Reset connection for connection pooling

Each of these is extremely low cost. You can test the cost of this by simply running this 100000 times in a loop. It might very well be the case that you don't care about this small cost.

I still would advise against this. In my experience web applications require more flexibility than a 1:1 correspondence of web request and transaction. Also, the rule to use the HTTP status code to decide the transaction will turn out to be inflexible.

Also, you must pick an isolation level (and possibly timeout) for each transaction. At the beginning of an HTTP request it is not known what the right values are. Only the action knows.

I had good experiences with using one EF context per HTTP request and then manually using transactions inside of each action. The overhead in terms of LOC is very small. There is no pressing need to centralize this.

Upvotes: 4

Related Questions