new to linq to sql - atomic insert and update

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

Answer 1

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

Answer 2

Thanks - and don't forget to call ts.Complete();