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