none
new to linq to sql - atomic insert and update RRS feed

  • Question

  • Hello
    i need to perform the following operations on a db:
    1. insert a new record in table1
    2. get the newly generated record id (auto incremented)
    3. update a field of the record in table1 with a value of the kind id_somethingelse
    4. update table2 with userid and time of insert

    My question is how do i make this operations atomic in linq to sql?
    If two user try to insert a record on the same table at the same time , will datacontex generate the same id or or two different ids?
    The real problem is when i'm going to update the table2 , the last who has inserted the record in table1 must be the last who update table2

    I also need to use a pessimistic approach i some cases, for example i need to update a whole table, setting a flag to "in use" for all records, then lock another table, do a "batch insert" of records taken from another table and release it. Blocking other user to do the same operation while i'm doing it. There's a way to do that in linq to sql ?

    thank you in advance 

    Stefano
    Thursday, June 25, 2009 9:34 AM

Answers

  • Several questions in one there.

    1) Atomic operations - use TransactionScope:

    using (TransactionScope ts = Transactions.Serializable())
    {
       //do a pile of operations with L2S here - will be wrapped in a single transaction. (distributed/MSDTC/XA if necessary)
    }
    
    internal static class Transactions
    {
      internal static TransactionScope Serializable()
      {
        return new TransactionScope(
          TransactionScopeOption.Required,
            new TransactionOptions
              {
                IsolationLevel = IsolationLevel.Serializable
              });
      }
    }


    2) Identity columns - these are set by SQL Server, so if you're using those your records will get their own unique ids.

    3) Entire table locking - do by issuing a "select top 1 * from some_table with (tablockx)" using datacontext.ExecuteQuery within a transactionscope (see #1).

    4) L2S is not ideal for 'batch' operations as it works with one record at a time when inserting, updating, and deleting. This means a lot of data will travel over the wire compared to set-based operations. (This can be worked around with L2S, but it is not supported out-of-the-box...)
    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools, www.huagati.com/L2SProfiler
    Thursday, June 25, 2009 10:06 AM
    Answerer

All replies

  • Several questions in one there.

    1) Atomic operations - use TransactionScope:

    using (TransactionScope ts = Transactions.Serializable())
    {
       //do a pile of operations with L2S here - will be wrapped in a single transaction. (distributed/MSDTC/XA if necessary)
    }
    
    internal static class Transactions
    {
      internal static TransactionScope Serializable()
      {
        return new TransactionScope(
          TransactionScopeOption.Required,
            new TransactionOptions
              {
                IsolationLevel = IsolationLevel.Serializable
              });
      }
    }


    2) Identity columns - these are set by SQL Server, so if you're using those your records will get their own unique ids.

    3) Entire table locking - do by issuing a "select top 1 * from some_table with (tablockx)" using datacontext.ExecuteQuery within a transactionscope (see #1).

    4) L2S is not ideal for 'batch' operations as it works with one record at a time when inserting, updating, and deleting. This means a lot of data will travel over the wire compared to set-based operations. (This can be worked around with L2S, but it is not supported out-of-the-box...)
    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools, www.huagati.com/L2SProfiler
    Thursday, June 25, 2009 10:06 AM
    Answerer
  • Thanks - and don't forget to call ts.Complete();

     

    Saturday, December 4, 2010 11:02 AM