LINQ to SQL : Using Transaction
LINQ to SQL uses optimistic concurrency be default. LINQ to SQL is pure in memory operation, because you normally get the data from database and store them in .NET memory and perform some pure in memory update to those objects and finally when you are done with your modification you give those data back to the database. But what if someone changes the data in between; this is quite possible in network scenario. The default behavior in LINQ to SQL is that whoever hits the database wins the race. This is called optimistic concurrency. You can implement pessimistic concurrency with the new Transaction in .NET 3.0.
Let’s check it out
I have a table
Emp
Id int
Name varchar(50)
Let’s have some dummy data,
Id Name
1 C# 1.0
2 C# 2.0
3 C# 3.0
4 Orcas
Now I want to play with the Id = 4, and modify as per the condition.
Need this basic,
[Table(Name="Emp")]
public class Emp
{
[Column(IsPrimaryKey=true, IsDBGenerated=true)]
public int Id { get; set; }
[Column]
public string Name { get; set; }
}
public class TestDB : DataContext
{
public Table<Emp> Emps;
public TestDB(string s):base(s){}
}
Now using this code I will write an application to update data in the database.
static void Main(string[] args)
{
Console.Title = "LINQ to SQL Demo";
string sConn = @"Database connection string";
TestDB db = new TestDB(sConn);
ObjectDumper.Write(db.Emps);
//Get the element you want to modify
var query = db.Emps.First(e => e.Id == 4);
//Update the memory object
if (query.Name == "Orcas")
query.Name = "Visual Studio 2008 Beta 2";
else
query.Name = "Orcas";
//Just wait for other application to make changes
//this is intetional as we need to throw an error
Console.WriteLine("Ready to edit. Press any key..");
Console.Read();
//Update the database
db.SubmitChanges();
//Show the changed value
ObjectDumper.Write(db.Emps);
}
}
Now if you compile and run this app in two different command window and both of them will come and wail with the line, “Ready to Edit. Press any key to continue.. ”.
Whichever you click first will update the data and the second one will throw you and error.
Unhandled Exception: System.Data.Linq.ChangeConflictException: Row not found or changed.
This is optimictic concurrency, who executes first wins the race and other fails because there is a conflict in the actual source and the in memory data which you are modifying.
Now if you inplement the same code with TransactionScope class which is new in .NET Framework 2.0.
static void Main(string[] args)
{
Console.Title = "LINQ to SQL Demo";
string sConn = @"Database connection";
TestDB db = new TestDB(sConn);
ObjectDumper.Write(db.Emps);
//This new Transaction class in .NET Framework 3.0
using (TransactionScope ts = new TransactionScope())
{
//Get the element you want to modify
var query = db.Emps.First(e => e.Id == 4);
//Update the memory object
if (query.Name == "Orcas")
query.Name = "Visual Studio 2008 Beta 2";
else
query.Name = "Orcas";
//Just wait for other application to make changes
//this is intetional as we need to throw an error
Console.WriteLine("Ready to edit.Press any key..");
Console.Read();
//Update the database
db.SubmitChanges();
//Complete the Transaction
ts.Complete();
}
//Show the changed value
ObjectDumper.Write(db.Emps);
}
This again uses the same behavior but with managed scope. Especially when you have multiple update happening and the error is more tempting I must say,
Unhandled Exception: System.Data.SqlClient.SqlException: Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
No one likes to be a deadlock victim.
Namoskar!!!
Comments
Anonymous
August 06, 2007
LINQ to SQL uses optimistic concurrency be default. LINQ to SQL is pure in memory operation, becauseAnonymous
August 06, 2007
I'm sure TransactionScope was introduced in .NET 2.0 not 3.0Anonymous
August 07, 2007
Hi Des, Thanks for correcting me. I have changed. WrijuAnonymous
January 31, 2008
Don't know if you resolved this issue - but try wrapping the transaction scope just around the db.SubmitChanges(); statement and see if it works.Anonymous
January 31, 2008
Have I missed the point here the only difference seems to be the error message!Anonymous
February 01, 2008
Seems to me the only difference is the error message! Pessimistic would do a 'Select for update' and tell you at that point if you can't get the row which to my of thinking was necessary with databases that don't support transactions. (Old school!) DarylAnonymous
March 28, 2008
It seems like TransactionScope is not used by LINQ. If you remove the ts.Complete() statement, the changed are not rolled back.Anonymous
March 28, 2008
The reason it was not working for me was because I was using what VS2008 calls a "Local Database", i.e. SQL Server Compact Edition. Apperantly System.Transaction does not work with the Compact Edition. This is probably due to the fact that the Compact Edition does not support nested transactions.Anonymous
April 04, 2008
I have created a custom BaseDataContext that facilitates setting IsolationLevels for Transactions.Anonymous
May 26, 2008
How can you handle deletes that fail due to referential integrity? It seems there is no way to undo deleteonsubmit calls.Anonymous
July 30, 2008
LINQ is Language Integrated query. It is a integral part of visual studio 2008 and Microsoft .NET FrameworkAnonymous
August 20, 2008
To Tersius: You can use a call to InsertOnSubmit to compensate for the deleteAnonymous
January 14, 2009
IMPORTANT: to use using TransactionScope you need to add a Reference in your project to System.Transactions, while this would seem obvious it actually isn't and is worth notingAnonymous
February 07, 2009
So my question is on a sql database that does support transactions, will the transaction scope work?