DLINQ(LINQ to SQL) with TransactionScope


  • Hi, all. How can I use the update lockhint(UPDLOCK) optimization in DLINQ?

    I knew that the LINQ to SQL is designed primarily for optimistic concurrency(http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1344034&SiteID=1), but I want to use the pessimistic concurrency in some scenario.


    For example, how can I use the UPDLOCK in the below code? 

    Code Snippet
    using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
        PubsDataContext db = new PubsDataContext();
        // SELECT title_id, title, price FROM titles WHERE title_id = 'BU1032'   <- I want to add UPDLOCK!
        var titles = (from t in db.titles
                      where t.title_id == "BU1032"
                      select t).First();
        titles.price = titles.price + 1;



    Nobuyuki Akama


    Wednesday, April 18, 2007 10:39 AM

All replies

  • LINQ to SQL does not support pessimistic concurrency.  It does not generate UPDLOCK or FOR UPDATE in the queries.
    Wednesday, April 18, 2007 11:51 PM
  • Thanks, Matt.

    Is there any plan to support pessimistic concurrency scenario?


    Even if the main scenario of DLINQ is optimistic concurrency (I agree), I think that the pessimistic option is required in order to avoid the conversion deadlock. For example, high-volume transactional system (such as order-entry or ticketing system, i.e., the collisions of transactions are the common-case) requires the pessimistic concurrency control.


    Why does the LINQ to SQL "only" support the optimistic concurrency?

    Or is there any plan (such as DLINQ v2.0, etc.) or workaround?

    Or do we have to use the traditional ADO.NET 2.0 and T-SQL(such as TableAdapter) for the high-volume transactional system?




    I think the main cause of this problem is that the LINQ have to support muptiple datasources, such as Entities, Objects, DataSets, XML, Database, and so on.

    But these datasources are not the same - for example, the DataSet is basically single-user-use. But the RDBMS is basically multiple-user-use. So I think that the LINQ to Multi-User-DataSource (such as RDBMS) should support the (pessimistic) concurrency control similar to T-SQL.

    Thursday, April 19, 2007 5:43 AM