LINQ experience (10) -- open concurrency control and transactions for LINQ to SQL statements

Continue this series from today. The good news is that Microsoft released the Simplified Chinese version of Visual Studio Team System February 1 Team Suite on April 9, 2008. You can download the 90-day trial version of Visual Studio Team System 2008 Team Suite Simplified Chinese version here. Today, I will briefly learn about open concurrency control and transaction content. For more information, see MSDN.

Simultaneous Changes open Concurrency Control

The following table describes the terms related to open concurrency in the LINQ to SQL document:

Terms	Description
Concurrency	When two or more users attempt to update the same database row at the same time.
Concurrency conflict	When two or more users attempt to submit conflicting values to one or more columns of a row at the same time.
Concurrency Control	It is used to solve concurrency conflicts.
Open Concurrency Control	Check whether other transactions have changed the value in the row before committing the changed technology. In contrast, implicit concurrency control prevents concurrent conflicts by locking records. It is called open control because it treats one transaction as unlikely to interfere with another transaction.
Conflict Resolution	Refresh conflicting items by re-querying the database, and then coordinate the difference process. When an object is refreshed, The LINQ to SQL change tracker retains the following data: The new database value obtained from the database and used to update the check value through subsequent queries. Then, you can determine whether the corresponding object conflicts (that is, whether one or more of its member values have been changed ). If this object is conflicted, the next step of LINQ to SQL will determine which members of the object are conflicted. Any member conflicts discovered by LINQ to SQL are added to the conflict list.

In the LINQ to SQL object model, when both of the following conditions are met, an "open concurrency conflict" occurs: the client attempts to submit changes to the database; one or more Update check values in the database have been updated since the client last read them. The process of resolving this conflict includes finding out which members of the object conflict and then deciding how you want to handle it.

Optimistic Concurrency)

Note: In this example, before you read the data, another user has modified and submitted and updated the data, so there will be no conflict.

// We open a new connection to simulate another user, NorthwindDataContext otherUser_db = new NorthwindDataContext (); var otherUser_product = otherUser_db.Products.First (p => p. productID = 1); otherUser_product.UnitPrice = 999.99 M; otherUser_db.SubmitChanges (); // The current connection var product = db. products. first (p => p. productID = 1); product. unitPrice = 777.77 M; try {db. submitChanges (); // The current connection is successfully executed} catch (ChangeConflictException ){}

Note: After we read the data, another user obtains and submits the updated data. When we update the data, a concurrency conflict occurs. The system rolls back, allowing you to retrieve new data from the database and decide how to continue with your own updates.

// The current user var product = db. products. first (p => p. productID = 1); // we open a new connection to simulate another user, NorthwindDataContext otherUser_db = new NorthwindDataContext (); var otherUser_product = otherUser_db.Products.First (p => p. productID = 1); otherUser_product.UnitPrice = 999.99 M; otherUser_db.SubmitChanges (); // The current user modifies the product. unitPrice = 777.77 M; try {db. submitChanges ();} catch (ChangeConflictException) {// exception occurred !}

Transactions transaction

LINQ to SQL supports three transaction models:

• Explicit local Transaction: if the Transaction attribute is set to Transaction when SubmitChanges is called, SubmitChanges is called in the context of the same Transaction. After the transaction is successfully executed, you must submit or roll back the transaction. The connection to the transaction must match the connection used to construct the DataContext. If other connections are used, an exception is thrown.
• Explicit distributable transactions: You can call the LINQ to SQL API (including but not limited to SubmitChanges) in the current scope of Transaction ). LINQ to SQL detects that the call is within the scope of the transaction, so no new transaction is created. In this case, <token> vbtecdlinq </token> also avoids closing the connection. You can perform queries and SubmitChanges operations in the context of such transactions.
• Implicit transactions: When you call SubmitChanges, LINQ to SQL checks whether the call is within the scope of Transaction or whether the Transaction attribute is set to a local Transaction started by the user. If none of these two transactions are found, the LINQ to SQL starts the local transaction and uses this transaction to execute the generated SQL command. When all SQL commands have been successfully executed, LINQ to SQL submits local transactions and returns.

1. Implicit (Implicit)

Note: This example implicitly uses transactions when performing the SubmitChanges () operation. When the inventory quantity of the two products is updated, the inventory quantity of the second product is negative, which violates the CHECK constraints on the server. As a result, all products fail to be updated, and the system rolls back to the initial status of this operation.

Try {Product prod1 = db. products. first (p => p. productID = 4); Product prod2 = db. products. first (p => p. productID = 5); prod1.UnitsInStock-= 3; prod2.UnitsInStock-= 5; // error: the unit of inventory Quantity cannot be negative. // either all succeeded or all failed databases. submitChanges ();} catch (System. data. sqlClient. sqlException e) {// execute Exception Handling}

2. Explicit (Explicit)

Note: This example uses explicit transactions. Explicit transactions can provide more protection by adding read operations to transactions to prevent open concurrency exceptions. As in the previous query, updating the UnitsInStock field of prod2 sets this field to a negative value, which violates the CHECK constraints in the database. As a result, the transactions of the two products fail to be updated, and all changes will be rolled back.

Using (TransactionScope ts = new TransactionScope () {try {Product prod1 = db. products. first (p => p. productID = 4); Product prod2 = db. products. first (p => p. productID = 5); prod1.UnitsInStock-= 3; prod2.UnitsInStock-= 5; // error: the unit of inventory Quantity cannot be negative db. submitChanges ();} catch (System. data. sqlClient. sqlException e) {// execute Exception Handling }}

This series of links: navigation to the LINQ Series

Recommended resources of LINQ

Special topics: http://kb.cnblogs.com/zt/linq/ on all aspects of the entry, advanced, in-depth articles on LINQ.
LINQ group: a good place to ask questions or questions about http://space.cnblogs.com/group/linq/ Learning

This article is based on the signature 2.5 mainland China license agreement. You are welcome to reprint, interpret, or use it for commercial purposes. However, you must keep this article's signature Li yongjing (including the link). For more information, see here. If you have any questions or authorization negotiation, please leave a message for me.