August, 2007

  • Wriju's BLOG

    LINQ to SQL : Using Transaction

    • 14 Comments

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

  • Wriju's BLOG

    S. Somasegar (Soma) and Scott Guthrie on Orcas Beta 2

    • 1 Comments

    Channel 9 video at http://channel9.msdn.com/showpost.aspx?postid=329443

    Namoskar!!!

  • Wriju's BLOG

    I love Visual Studio 2008

    • 2 Comments

    I am using Visual Studio 2008 Beta 2

    VS2008

    Namoskar!!!

  • Wriju's BLOG

    C# 3.0 Specification is available

    • 2 Comments

    The 507 page specification is available at MSDN. Download from http://download.microsoft.com/download/3/8/8/388e7205-bc10-4226-b2a8-75351c669b09/CSharp%20Language%20Specification.doc

    Namoskar!!!

Page 1 of 1 (4 items)