When NOT to use transactions?

Question

I used to rarely use transactions until all of the sudden I was faced with a scenario that had a lot of db footprints, so I panicked and since then started to think about using transactions for any logic that might involve data manipulation statements (Insert, Update, Delete) and could result in unexpected disasters, exceptions.

Here is the model that I keep on using:

using (var db = new X_Entity())
{
    using (var trans = db.Database.BeginTransaction())
    {
        try
        {
            #region Logic
            // Logic that might include at least one data manipulation statement
            // db.Insert(), db.Update, db.Delete
            #endregion

            db.SaveChanges();
            trans.Commit();
        }
        catch (Exception exc)
        {
            trans.Rollback();
            HandleExceptions(exc);
        }
    }
}

Say there is a single Update, insert or delete statement, should a transaction be used in this case? My understanding is that in the following cases transactions are not necessary to use:

Get/Select/Join statements...etc
When the data manipulation statement is not followed by error prone logic

`SaveChanges` already wraps everything in a transaction, everything you added, deleted, updated on that DbContext will all fail or succeed as a part of a transaction. The only reason you might need one is if you have multiple calls to `SaveChanges` that you want to bundle as a part of a single transaction. — Igor, Aug 07 '17 at 19:40
@Igor, how about this scenario: Upload file to server then insert record in db? If uploading fails for any reason, no data should be recorded. In this scenario I assume that the data transaction is tied to some other kind of logic that does not involve other db modifying calls. — usefulBee, Aug 07 '17 at 19:48
Can also matter if you need distributed transactions or not. — Matthew Whited, Aug 07 '17 at 19:49
@usefulBee - that scenario has nothing to do with a db transaction because there is only 1 call to the db (an insert statement). If the upload fails the code should not reach the code to execute a db insert. — Igor, Aug 07 '17 at 19:53
@Igor, I see the point but that is assuming that developers attempt to upload files first then follow with an insert statement; while this might be the best practice, others might do the opposite and attempt to make the db call first, in this case the risk will remain if a transaction is not used. — usefulBee, Aug 07 '17 at 19:59
Distributed transactions would be an issue if you have multiple databases or services you need to keep in sync.... best to avoid where possible. — Matthew Whited, Aug 07 '17 at 20:06

score 0 · Answer 1 · answered Aug 07 '17 at 19:50

Transactions should be used to prevent your database changes falling into invalid state. What an invalid state is will depend on your domain. It is used when you have more than one actions that would complete the changes. For get/select, there is no point in using Transactions because you are not altering the state of the data. Even if it fails, the data will be in a valid state. A simple example is a amount transfer situation in a banking app. Lets say when you transfer $100 to your friend, for this action to be complete (in a very simple scenario)

$100 must be deducted from your bank account
$100 must be added to your recipients account

Without both of these steps succeeding (atomic) the data will go in invalid state. So to prevent these, you should use transaction/transaction scope. When you wrap both of the steps in a transaction, either they both will succeed or both will fail.

When NOT to use transactions?

1 Answers1