CODEWITHSUNDEEP
c#.nettransactions
ProfK
ProfK

Reputation: 51064

Am I using this transaction correctly?

I read a list of VehicleMovementEvent objects, most of which are plain entrances to and exits from a parkade zone. These have an indicator of entrance or exit, and a date and time. I use this list to produce a list of VehiclePresence objects that tell me a vehicle was present in zone x from a start time until an end time, gleaned from two matching VehicleMovementEvent objects. I would just like the whole list to be processed, or nothing to be processed, so a transaction seems fitting.

I don't often use transactions in code, so, am I doing this right? Especially w.r.t. the isolation level etc.

var opts = new TransactionOptions { IsolationLevel = IsolationLevel.RepeatableRead, Timeout = new TimeSpan(0, 0, 10, 0) };
using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew, opts))
{
    var vehicleMovements = startsbatch.Movements
                                .Where(m => m.MovementType.Direction == VehicleMovementEventType.Entry)
                                .OrderBy(m => m.EmpNo)
                                .ThenBy(m => m.MovementDateTime);


    presenceBatch.StartDateTime = DateTime.Now;
    presenceBatch.MovementBatch = startsbatch;
    _dbContext.VehiclePresenceBatches.Add(presenceBatch);
    _procTrace.TraceInformation("New VehiclePresencesBatch created. Id: {0}.", presenceBatch.Id);

    foreach (var movement in vehicleMovements)
    {
        var presence = new VehiclePresence 
                            {
                                PresenceBatch = presenceBatch,
                                EmpNo = movement.EmpNo,
                                Location = movement.Location,
                                StartDateTime = movement.MovementDateTime,
                                StartMovementBatchId = movement.BatchId,
                                StartMovementLineId = movement.LineId,
                                StartMovementId = movement.Id
                            };
        _dbContext.VehiclePresences.Add(presence);
        returnList.Add(presence);
    }
    _dbContext.SaveChanges();
    scope.Complete();
    _procTrace.TraceInformation("{0} VehicleMovements processed. {1} VehiclePresences created", vehicleMovements.Count(), returnList.Count);
}

Upvotes: 0

Views: 113

Answers (1)

Daniel J.G.
Daniel J.G.

Reputation: 34992

Is the startsbatch variable being created and inserted in the database as part of the VehiclePresenceBatch being added in that method? Because if so, then you don't need to start your own transaction at all as the EntityFramework's DBContext.SaveChanges() method starts its own transaction(see this). If your are not using EF, then you would just need a transaction wrapping the call to SaveChanges, using ReadCommitted as isolation level.

If the information in startsBatch already exists in the database but you don't care about other users updating it after you have read it, you are in the same situation as above and EF will take care of the transaction for you.

You need to pay more attention only if startsBatch already exists and you are worried about other users\processes updating that data after you read it:

  • One option would be to put some optimistic concurrency checks in place, like comparing a timestamp when saving the records and raising an error if the timestamp is not the same than the one you initially read. In this case, the transaction used by EF is still ok. (Providing the concurrency check is done by EF or your own stored procedure)

  • The other option is to include your code, including the piece of code that reads the startsBatch data inside a transaction and use a Repeatable Read or Serializable isolation levels. As you could imagine this makes the system less scalable as it would block anyone else trying to modify\update those rows for the duration of the transaction. (Serializable being more restrictive, even preventing from new rows to be inserted) Have a look at this question and the msdn here and here.

As a rule of thumb, you should be really careful when using Serializable and Repeatable Read isolation levels. Using a less restrictive isolation level like Read Commited, with some optimistic concurrency check (if needed at all, usually in update operations) should be enough in most cases and will perform better.

I also wanted to mention that in case you still need a transaction, consider using TransactionScopeOption.Required. This will just start a new transaction only if there isn't already an ambient transaction. So if your method is called as part of another transaction, it would be part of that transaction.

Upvotes: 1

Related Questions