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!!!
