Save an entity with a one-to-many relationship using C# and TSQL - race conditions

Question

I have an entity with a one-to-many relationship as follows

public class Car
{
    public int ID;
    public string Color;
    public List<Passenger> Passengers;
}

public class Passenger
{
    public int ID;
    public string Name;
}

This entity is stored in my database in two tables

Table: Cars
Fields: ID int (primary), Color nvarchar(20)

Table: Passengers
Fields: ID int (primary), CarID int (foreign), Name nvarchar(100)

I need a procedure that will save the state of the car and its passengers. I could do something simple like having two procedures:

usp_SaveCar
DELETE FROM Cars WHERE ID = @ID
INSERT INTO Cars VALUES (@ID, @Color)

usp_SavePassenger
DELETE FROM Passengers WHERE ID = @ID
INSERT INTO Passengers VALUES (@ID, @CarID, @Name)

I'd call this using a transaction by calling usp_SaveCar to save the car and then calling usp_SavePassenger to save each passenger associated with that car. Note: Passengers can be added or subtracted from the car and then resaved.

This will work for a single thread. The application, however, will be distributed. As such, I expect race conditions from this method where multiple cars (and their assigned passengers) might be saved at the same time. The process could go:

User 1: DELETE executed
User 2: DELETE executed
User 1: INSERT executed
User 2: INSERT exceuted

This would either cause a primary key violation if two inserts contained an identical primary key or could result in both sets of passengers being inserted if the IDs of the INSERT were mutually exclusive.

How, then, should I go about saving an entity with a one-to-many relationship to the database and avoid such problems. I want to avoid race conditions as well as deadlocks. I want to make sure User 1 saves the car entirely, including all passengers, before User 2 can begin saving the car and passengers. ie: Only one of the 2 user's instances of car+passengers should exist and it should match their entity (not be polluted by the other user's update) One of the two saves (the one happening first) will be overwritten immediately and that's fine as long as the second save is proper and complete.

From my research, I could use C#'s TransactionScope which would allow me to lock rows for a transaction during which I call several stored procedures. My plan would be

Start a new transaction with an appropriate isolation level
    Call a procedure to update the car (probably using a MERGE as the car may or may not exist)
    Call a procedure to delete and reinsert all passenger records for the car.
End the transaction

What techniques and isolation level I should use to complete this task?

score 0 · Answer 1 · answered Jun 12 '14 at 19:25

The technique is to finish all select statements in one transaction scope. A transaction scope is a single logical transaction, any properly implemented DB servers will schedule the tasks such that if you have 3 transactions T1, T2, and T3, they can be executed concurrently and the results are still correct.

To do that in C#, simple use multiple select statements in SqlCommand.

var sql = new SqlCommand("SELECT * FROM Cars; SELECT * FROM Passengers", someConnectionString);
sql.Connection.Open();
var reader = sql.ExecuteReader();
while(reader.Read()){
    //do something with the Cars
}
reader.NextResult();
while(reader.Read()){
    //do something with Passengers
}

score 0 · Answer 2 · answered Jun 12 '14 at 19:30

I'd consider doing the work in a single stored procedure, perhaps one that passes in the car information and another parameter capable of holding 0..n passengers (perhaps in an XML parameter?).

Then the whole thing can be done at the database level in one hit, wrapping the work inside the stored procedure in a BEGIN..... COMMIT TRANSACTION.

Save an entity with a one-to-many relationship using C# and TSQL - race conditions

2 Answers2