On LINQ to SQL, Concurrency and Timestamps

I came across a bit of a glitch in using timestamps for checking concurrency violations in LINQ to SQL and thought I’d share.

Say I’ve got a table like;

create table Person
(
  id int identity primary key,
  firstName nvarchar(30),
  lastName nvarchar(30),
  timestamp 
)

so, we have a simple table that has a timestamp on it and I want to use that to detect any concurrency problems that I might have in LINQ to SQL.

Let’s populate this table with some data;

insert person(firstname,lastname) values('first1', 'last1')
insert person(firstname,lastname) values('first2', 'last2')
insert person(firstname,lastname) values('first3', 'last3')

 

and then I can bring that into my LINQ to SQL environment and I can check what the concurrency options are set to;

image

So, you can see that we’ve got Update Check set to Always and that means that the timestamp will be added to any where clauses for Deletes or Updates and if a particular update doesn’t find any row to update because of that where clause then we have a concurrency violation.

Note also that Auto-Sync is set to Always which is a good thing because it means that if we insert an entity we’ll automatically get the timestamp back and if we update an entity we’ll similarly get an updated timestamp so that the timestamp in the original values of our entity in memory will represent the timestamp of the record when we queried/inserted/updated it and will only get “stale” if someone else were to update it (or delete it) in the meantime.

So, with LINQ to SQL we can write some code such as the stuff below which prompts for a new surname, changes the value in the entities that it has queried then tries to do a SubmitChanges and tries to make sure that it captures as many concurrency violations as it can ( the ContinueOnConflict parameter ).

    using (DemoDataContext ctx = new DemoDataContext())
    {
      var people = ctx.Persons.ToList();

      Console.WriteLine("Enter a new surname");
      string surname = Console.ReadLine();

      // Cause some changes.
      foreach (Person p in people)
      {
        p.lastName = surname;
      }
      // HERE...
      Console.WriteLine("Now's the time to cause a concurrency violation");
      Console.ReadLine();

      bool allSaved = false;

      while (!allSaved)
      {
        try
        {
          ctx.SubmitChanges(ConflictMode.ContinueOnConflict);
          allSaved = true;
        }
        catch (ChangeConflictException ex)
        {
          Console.WriteLine("Hit a conflict - fixing it!");

          foreach (ObjectChangeConflict conflict in ctx.ChangeConflicts)
          {
            conflict.Resolve(RefreshMode.KeepChanges);
          }
        }
        catch (Exception ex)
        {
          Console.WriteLine("Hit an unexpected exception [{0}]",
            ex.Message);
        }
      }
    }

When we hit the line of code marked HERE above, I go and run this piece of T-SQL;

update person set lastName='anything' where id=2
update person set lastName='else' where id=3

to cause concurrency problems for row number 2 and number 3 in my database.

What should happen;

  1. We read 3 entities with timestamps T1, T2, T3.
  2. We modify the surnames in our code.
  3. We use T-SQL to modify the timestamps T2, T3 in the database to ( say ) TM2 and TM3.
  4. We submit our changes.
  5. Our modifications for entities 2 and 3 will fail because T2!=TM2 and T3!=TM3. Our transaction is rolled back.
  6. We use Resolve in order to keep any changes that we have made in our code ( i.e. the surname modification ) but to refresh the other entity values from the DB.
  7. We now have in our program entities with timestamps T1, TM2, TM3
  8. We submit our changes.
  9. The code completes.

However, that’s not what happens when the code does. What happens is that we hit an unexpected exception at 8 above and my Console.WriteLine writes it out;

Hit an unexpected exception [Value of member ‘timestamp’ of an object of type ‘Person’ changed.

What’s going on here? I think what’s happening here is that when we hit the call to SubmitChanges we go to the database to update 3 entities. The first update works so we update the timestamp. The 2nd update fails and so does the 3rd so we rollback the transaction.

However, we’ve updated the timestamp on that 1st entity.

We then go and resolve the 2 concurrency violations we’ve got before calling SubmitChanges again. It takes a look at that first entity which didn’t have a concurrency problem but does now have a modified timestamp and still needs updating in the database ( because the transaction was rolled back ) and says “Hey, you’re not meant to change generated columns like this!!”.

Got it?

Option – Stop LINQ to SQL from Updating the TimeStamp Automatically on Update

The timestamp is only being updated because we have allowed the Auto-Sync property to be default to Always in the mapping information. That means “re-sync this column whenever we insert, update”.

We could change this so that we only do Auto-Sync on Insert by setting;

image

Now, our code as it originally stands is going to work just fine if I re-run it under the same conditions where I cause a concurrency violation at the line marked HERE because when we hit the initial call to SubmitChanges which throws a ChangeConflictException we catch that exception, call Resolve on the 2 rows with the problems and then we call SubmitChanges again and we’ve never updated the timestamp on row number 1.

However…this isn’t a free ride.

The problem with switching Auto-Sync to OnInsert means that whenever we actually do a successful update we won’t update the timestamp to reflect the latest value that we just generated in the database.

So, whenever we call SubmitChanges for subsequent updates we will see superfluous concurrency exceptions for every row that didn’t hit a concurrency exception the last time we called SubmitChanges.

That is, this code (which runs forever) will work;

    using (DemoDataContext ctx = new DemoDataContext())
    {
      var people = ctx.Persons.ToList();

      while (true)
      {
        Console.WriteLine("Enter a new surname");
        string surname = Console.ReadLine();

        // Cause some changes.
        foreach (Person p in people)
        {
          p.lastName = surname;
        }
        // HERE...
        Console.WriteLine("Now's the time to cause a concurrency violation");
        Console.ReadLine();

        bool allSaved = false;

        while (!allSaved)
        {
          try
          {
            ctx.SubmitChanges(ConflictMode.ContinueOnConflict);
            Console.WriteLine("Submitted changes");
            allSaved = true;
          }
          catch (ChangeConflictException ex)
          {
            Console.WriteLine("Hit a conflict - fixing it!");

            foreach (ObjectChangeConflict conflict in ctx.ChangeConflicts)
            {
              conflict.Resolve(RefreshMode.KeepChanges);
            }
          }
          catch (Exception ex)
          {
            Console.WriteLine("Hit an unexpected exception [{0}]",
              ex.Message);
          }
        }
      }
    }

and it’ll work regardless of whether I cause concurrency problems at the line marked HERE. However, once the loop has executed once we run the risk of hitting “false” concurrency problems at the call to SubmitChanges for any rows that have out of date timestamps because we did not update them the last time we did an update.

The other thing that comes to mind here is that this all depends on the lifetime of your DataContext. If you just use it for one “unit of work” then this isn’t really going to bite you in the same way because Auto-Sync doesn’t seem so relevant if you’re going to throw your DataContext away after you call SubmitChanges anyway.

And the last thing is that this won’t affect you at all unless you’re using generated columns like timestamps for concurrency checking.