Open concurrency control and transactions for LINQ to SQL statements (13)

Simultaneous changes open concurrency control

The following table describes the terms that are involved in optimistic concurrency in a LINQ to SQL document:

Terminology	Description
Concurrent	A scenario in which two or more users try to update the same database row at the same time.
concurrency conflicts	A scenario in which two or more users attempt to commit conflicting values to one or more columns in a row at the same time.
concurrency control	The technique used to resolve concurrency conflicts.
Optimistic concurrency control	First investigate whether other transactions have changed the values in the row, and then allow the technology to commit the changes. In contrast, pessimistic concurrency control avoids concurrency conflicts by locking records. This is called open control because it treats one transaction as less likely to interfere with another transaction.
Conflict resolution	Refreshes the conflicting items by re-querying the database, and then coordinates the process of the differences.  When you refresh an object, the LINQ to SQL change Tracker retains the following data: The value of the new database that was originally fetched from the database and used for the update check for subsequent queries. LINQ to SQL then determines whether the object is conflicting (that is, if one or more of its member values have changed). If this object conflicts, LINQ to SQL next determines which of its members conflict. Any member conflicts found by LINQ to SQL are added to the conflict list.

In the LINQ to SQL object model, an "optimistic concurrency violation" occurs when the following two conditions are met: The client attempts to commit changes to the database, and one or more update check values in the database have been updated since the client last read them. The resolution of this conflict involves identifying which members of the object conflict and then deciding how you want to handle it.

Optimistic concurrency (optimistic Concurrency)

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

//we open a new connection to simulate another userNorthwindDataContext otheruser_db =NewNorthwindDataContext ();varOtheruser_product =otheruser_db. Products.first (P= = P.productid = =1); otheruser_product. UnitPrice=999.99M; otheruser_db. SubmitChanges ();//We are currently connectedvarProduct = db. Products.first (p = P.productid = =1);p roduct. UnitPrice=777.77M;Try{db. SubmitChanges ();//Current connection execution succeeded}Catch(changeconflictexception) {}

Note: After we read the data, another user gets and submits the updated data, and when we update the data, a concurrency conflict occurs. The system is rolled back, allowing you to retrieve newly updated data from the database and decide how to proceed with your own updates.

//Current UservarProduct = db. Products.first (p = P.productid = =1);//we open a new connection to simulate another userNorthwindDataContext otheruser_db =NewNorthwindDataContext ();varOtheruser_product =otheruser_db. Products.first (P= = P.productid = =1); otheruser_product. UnitPrice=999.99M; otheruser_db. SubmitChanges ();//Current User ModificationsProduct. UnitPrice =777.77M;Try{db. SubmitChanges ();}Catch(changeconflictexception) {//An exception occurred! }

Transactions transactions

LINQ to SQL supports three transaction models, namely:

• Explicit local transaction: When calling SubmitChanges, if the Transaction property is set to a transaction, the SubmitChanges call is executed in the context of the same transaction. After a transaction has been successfully executed, you are either committing or rolling back the transaction. The connection corresponding to the transaction must match the connection used to construct the DataContext. If you use a different connection, an exception is thrown.
• Explicit distributable transactions: You can invoke the LINQ to SQL API (including but not limited to submitchanges) in the scope of the current Transaction. LINQ to SQL detects that the call is within the scope of the transaction and therefore does not create a new transaction. In this case, the,<token>vbtecdlinq</token> also avoids closing the connection. You can perform query and submitchanges operations in the context of such transactions.
• Implicit transactions: When you call SubmitChanges, LINQ to SQL checks whether this call is within the scope of Transaction or whether the Transaction property is set to a user-initiated local transaction. If neither of these transactions is found, LINQ to SQL initiates a local transaction and uses this transaction to execute the generated SQL command. When all SQL commands have been successfully executed, LINQ to SQL submits the local transaction and returns.

1.Implicit (implicit)

Description: This example implicitly uses the transaction when performing the SubmitChanges () operation. Because the Second product inventory quantity is negative when the inventory quantity for 2 products is updated, the CHECK constraint on the server is violated. This causes the update product to fail completely, and the system rolls back to the initial state of the operation.

Try {    4);     5 );     3 ;     5; // Error: The unit of inventory quantity cannot be    a negative number // either all succeed or all fail     db. SubmitChanges ();} Catch (System.Data.SqlClient.SqlException e) {    // perform exception handling }

2.Explicit (Explicit)

Description: This example uses an explicit transaction. Explicit transactions can provide more protection by adding read data to the transaction to prevent optimistic concurrency exceptions. As in the previous query, updating the UnitsInStock field of prod2 causes the field to be negative, which violates the CHECK constraint in the database. This causes the transaction to update the two products to fail, and all changes are rolled back at this time.

using(TransactionScope ts =NewTransactionScope ()) {    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 a negative numberdb.    SubmitChanges (); }    Catch(System.Data.SqlClient.SqlException e) {//performing exception Handling    }}

Open concurrency control and transactions for LINQ to SQL statements (13)