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?

1 Answers

2
usr On Best Solutions

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.