Answered by:
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
StefanoThursday, 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- Proposed as answer by Joe AlbahariMVP, Editor Thursday, June 25, 2009 10:37 AM
- Marked as answer by Bit01 Thursday, June 25, 2009 12:24 PM
Thursday, June 25, 2009 10:06 AMAnswerer
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- Proposed as answer by Joe AlbahariMVP, Editor Thursday, June 25, 2009 10:37 AM
- Marked as answer by Bit01 Thursday, June 25, 2009 12:24 PM
Thursday, June 25, 2009 10:06 AMAnswerer -
Thanks - and don't forget to call ts.Complete();
Saturday, December 4, 2010 11:02 AM