Toolroom Tech Blog

Devlopers Digest

Linq to Sql: Row not found or changed

Finding a way back from LINQ to SQL cuncurrency hell.

Yesterday I was trapped in concurrency hell and had no idea why.

Background

The Windows Phone App I am currently working on, synchronizes data between a local database and a REST service. When the app receives an update for a row, I select the row to be updated from the local database via LinqToSql, update its values with the data from the service and save it.

The issue

As you can see in the snippet below, I select a record, update it ... and a few lines later the update crashes with a lovely System.Data.Linq.ChangeConflictException saying "Row not found or changed". How is that possible? I'm sure that nobody modifies or deletes the data in the meantime.

private void updatePerson(Person person)
{
	var localPerson = DataStore.Data.Persons.Where(p => p.Id == person.Id);
	if(localPerson == null)
	{
		//person is new, so create it
		DataStore.Data.Persons.InsertOnSubmit(person);
	}
	else
	{
		//update existing person
		localPerson.Name = person.Name;
		localPerson.CustomerId = person.CustomerId;
		localPerson.DepartmentId = person.DepartmentId;
		localPerson.IsActive = person.IsActive;
	}
	
	DataStore.Data.SubmitChanges(); //crash boom bang --> Row not found or changed.
}

Aha! LINQ to SQL uses Optimistic Concurrency

Hahaaa, that's it! When LINQ to SQL selects a record, it does not lock any data. When it is updating a record, it compares all columns with their original values.

So what does this mean? The update query that I expected from LINQ to SQL was:

UPDATE Persons
SET Name = 'Mr. Buggy',
	CustomerId = null,
	DepartmentId = 6,
	IsActive = true
WHERE
	Id = 9999

But the real world is different; due to optimistic concurrency it compared all columns:

UPDATE Persons
SET Name = 'Mr. Buggy',
	CustomerId = null,
	DepartmentId = 6,
	IsActive = true
WHERE
	Id = 9999 AND
	Name = 'Mr. Buggy' AND
	CustomerId = 1234 AND
	DepartmentId = 1 AND
	IsActive = true

This still does neither explain nor solve my issue, since my original values did not change in the meantime. But with the knowledge of Optimistic Concurrency and a few more minutes of debugging, I found (my) fault: The database field for CustomerId is not nullable ... but the REST Service returned null.

I fixed this and everyone (that's me) is happy!

Loading