"Reprint". NET Lock 6 Big deal method pessimistic optimistic oneself grasps

Why do we need locks?

In a multiuser environment, there may be multiple users updating the same record at the same time, which creates a conflict, which is known as a concurrency problem.

Figure 1 The problem of parallelism comics

　How do I solve concurrency problems?

With the correct locking strategy, the concurrency problem can be solved, and when the resource is locked, other processes will be blocked if they want to access it.

　　What kind of conflict does concurrency cause?

Concurrency can lead to four common problems, see the table below for details.

Problem	Brief description	Explain
Dirty Read	Dirty reads occur when a transaction reads other records that complete half the transaction	Both User A and User B see a value of 5 User B modifies the value to 2 The value that user a sees is still 5, and dirty reads occur
Non-REPEATABLE READ	If you get a different value each time you read the data, it indicates that you are experiencing a non-repeatable read problem	User A sees a value of 5 User B changes the value to 2 When user a refreshes, the value is still 5, and a non-repeatable read occurs.
Unreal Line	If the update and delete SQL statements do not affect the data, you are likely to encounter unreal line problems	User A modifies all values from 5 to 2 User B Inserts a new record with a value of 2 User A queries all records with a value of 2, but cannot find them, and then an unreal line occurs.
Update lost	The update of one transaction overwrites the update result of other transactions, that is, the so-called update is lost	User A updates all values from 5 to 2 User B updates all values from 2 to 5 User A has lost his update

How to resolve the above conflict?

The answer is to use an optimistic or pessimistic lock, which is further elaborated below.

Figure 2 optimistic lock and pessimistic lock

　What is an optimistic lock?

As the name implies, optimistic locking assumes that multiple transactions do not affect each other, in other words, in optimistic lock mode, no lock operation is performed, the transaction simply verifies that there are other transactions to modify the data, if there is a transaction rollback, otherwise commits.

Figure 3 Optimistic Lock

How does an optimistic lock work?

There are many ways to achieve optimistic locking, but the basic principles are the same, with the following five steps:

• Record the current timestamp

• Start modifying values

• Before updating, check if someone else has updated the value (implemented by checking for new and old timestamps)

• Roll back if not equal, or commit

Figure 4 How optimistic locking works

Solutions for optimistic locking

In. NET, there are three main ways to implement optimistic locking:

• DataSet: A DataSet is the default way to implement optimistic locking, which checks for old and new values before updating.

• Timestamp data type (timestamp): Creates a timestamp data type in your table, checking whether the old timestamp equals the new timestamp when updating.

• Check the old and new values directly: When updating, check that the old and new values are equal and roll back if not equal, or commit.

　　Solution 1: Datasets

As mentioned earlier, the dataset is the default method for handling optimistic locks, here is a simple snapshot, there is a pilot on the adapter update function, and when I remove a breakpoint running the update function, it throws a parallel exception error as shown.

Figure 5 Exception errors thrown when the update function executes

If you run the back-end parser, you will see an UPDATE statement that checks whether the current and old values are equal:

execsp_executesql N'UPDATE [Tbl_items] SET [authorname] = @p1 WHERE ([Id] = @p2) and ((@p3 = 1 and [ItemName] is NULL) OR ([itemname] = @  P4) and ((@p5 = 1 and [type] is null) or ([type] = @p6)) and ((@p7 = 1 and [authorname] is null) or ([authorname] = @p8)) and (@p9 = 1 and [Vendor] is NULL) OR ([Vendor] = @p10) ))'N'@p1 nvarchar (one), @p2 int, @p3 int, @p4 nvarchar (4), @p5 int, @p6 int, @p7 int, @p8 nvarchar, @p9 int, @p10 nvarchar (2) 
    ',@p1=N'This is new',@p2=2,@p3=0,@p4=N'1001',@p5=0,@p6=3,@p7=0,@p8=N'This is oldauthor',@p9=0,@p10=N'KK'

In this case, I try to modify the "AuthorName" field value to "This is new", but when the update checks the old value "This is a", the following is a thin code snippet that compares the old values:

,@p8=N'This is an oldAuthor'

　

　　Solution 2: Use the timestamp data type

SQL Server has a data type of timestamp, which is another way to implement optimistic locking, and each time the SQL Server data is updated, the timestamp automatically produces a unique binary value, and the timestamp data type can be used to version your record update.

Figure 6 Timestamp data type

To achieve an optimistic lock, you first need to get the old timestamp value, and when updating, check that the old timestamp value is equal to the current timestamp, such as:

Update set itemname=@itemnamewhere currenttimestamp=@ Oldtimestamp

Then check if the update operation has occurred, and if the update does not occur, use SQL Server RAISERROR to produce a series of error messages.

 

if (@ @rowcount=0) begin RAISERROR ('Hello Some else changed the value',[+] End

 

If a concurrency conflict occurs, you should see error propagation when you call ExecuteNonQuery as shown.

Figure 7 The timestamp changes and the stored procedure produces an error

Solution 3: Check for old and new values

Many times, we only need to check the consistency of the relevant field values, other fields can be ignored, in the UPDATE statement, we can directly do this comparison.

Update set itemname=@itemnamewhere itemname=@OldItemNameValue

But using optimistic locking does not seem to solve the problem completely, using optimistic locking can only check concurrency problems, in order to solve the problem of concurrency from the root cause, we need to use pessimistic lock, so optimistic lock can play a preventive role, and pessimistic lock can be cured.

　What is a pessimistic lock?

Pessimistic locks always assume that concurrency/conflict issues occur, so the records are locked before being updated.

Figure 8 Pessimistic lock

How do I handle pessimistic locks?

We can specify IsolationLevel (Isolation level) in SQL Server stored procedures, ADO. NET level or use transaction scope objects to handle pessimistic locks.

What kind of lock can I get with pessimistic locks?

There are four types of locks that can be obtained using pessimistic locks: shared, exclusive (Exclusive), updated (update) and intent (Intent), the first two are real locks, and the next two are the blending of locks and tokens.

	When to use	Allow read	Allow Write
Shared locks	When you want to read only and do not want other transactions to be updated	Is	Whether
Exclusive lock	When you want to modify the data and do not want others to be able to read it until you have finished updating	Whether	Whether
Update lock	This is a hybrid lock that is used when you perform an update operation that has multiple steps
	Reading stage	Is	Whether
	Operation phase	Is	Whether
	Update phase	Whether	Whether
Intent Lock (Request lock)	Intent locks are hierarchical and used when you want to lock down subordinate resources, for example, a shared intent lock on a table means that shared locks are for rows in the page and table.	Not applicable	Not applicable
Mode lock	When you modify the table structure, use the	Whether	Whether
Big Data Block Update lock	When you perform a big data block update, use the	Table level (NO)	Table level (NO)

　　

　Detailed and confusing update lock

Other locks are well understood, but the update lock is confusing, because it mixes the lock and the tag, before the update we have to read the record, the lock is shared during the read, and in the real update, we need exclusive lock, update the lock is very short.

Figure 9 Several locks used in the update

　　Differences between different isolation levels and when to use them

There are 4 types of transaction isolation levels, and the following table lists the 4 isolation levels and their time of use.

Isolation level	Read	Update	Insert
Read non-committed	READ UNCOMMITTED data	Allow	Allow
Read Committed (default)	Read the submitted data	Allow	Allow
Repeat Read	Read the submitted data	Not allowed	Allow
Serialization of	Read the submitted data	Not allowed	Not allowed

　How do I specify the isolation level?

The isolation level is a feature of a relational database, that is, it is basically only related to SQL Server, and it has nothing to do with Ado.net,ef or LINQ, but you can set the isolation level on those components.

Figure 10 Isolation Level

Middle tier: In the middle tier, you can use transaction scope objects to specify the isolation level.

Transactionoptions transopt ==usingnew  TransactionScope ( Transactionscopeoption.required, transoptions)) {

Ado. NET: In ADO, you can use the SqlTransaction object to specify the transaction isolation level.

SqlTransaction objtransaction = objconnection.begintransaction (System.Data.IsolationLevel.Serializable);

SQL Server: You can also specify the isolation level in TSQL using "SET transacation isolation Levels".

SET TRANSACTION Isolation  Level READ COMMITTED;

　　The correspondence between the transaction isolation level and the concurrency problem it can solve

	Read the Submitted	Repeat Read	Serialization of	Read non-committed
Dirty Read	Can solve	Can solve	Can solve	No
Missing updates	No	Can solve	Can solve	No
Non-REPEATABLE READ	No	Can solve	Can solve	No
Fantasy Line	No	No	Can solve	No

　　

　Solution 4: Use Read Committed to resolve dirty read issues

Some key points about Read Committed:

• It is the default transaction isolation level for SQL Server.

• It reads only the submitted data, in other words, any uncommitted data is ignored until the data is submitted, explained in detail, and you can see the update.

Figure 11 Read Committed mode parsing

If you want to see the situation described, just follow the steps below to do it:

• Open two query Windows, perform an update transaction, but do not commit;

• When you execute a query in the second window, a prompt that looks like the query being blocked is displayed.

Figure 12 The query is blocked until the transaction submission is updated to execute

　　Does the "Read Committed" opposite be "READ UNCOMMITTED"?

Yes, read UNCOMMITTED is the opposite of the Read Committed, and when you set the READ UNCOMMITTED transaction isolation level, uncommitted data is also read.

About "Read Committed" key points:

• Uncommitted is visible, so dirty reads are possible;

• No lock is caught;

• Useful when the lock is not important, and more importantly, concurrency and throughput.

If you also want to test, try the following SQL statement, it performs an update, and after waiting for 20 seconds to roll back, during this time if you execute the query, return the uncommitted data, but after 20 seconds, you query again, the returned will be the old data, because the submitted data has been rolled back.

Set Transaction Isolation  Level Read UncommittedBegin TranUpdateCustomerSetCustomerName='Changed' whereCustomercode='1001'WAITFORDELAY'000:00:20'rollback TranSet Transaction Isolation  Level Read UncommittedSelect *  fromCustomerwhereCustomercode='1001'

　Solution 5: Use duplicate read to resolve lost and non-repeatable reads

After you set the isolation level for repeat read, other people cannot read and update the data, and the key points about the duplicate read isolation level include:

• When you set the duplicate transaction isolation level for a query, only the submitted data is read.

• When you use repeat read to select a record, other transactions will not be able to update the record, but the query is possible.

• If you set up a repeatable transaction in an update query, you must wait until the transaction completes to read and update the same record.

• When the Select and update queries are set to repeatable reads, other transactions can insert new records, in other words, unreal rows are possible.

If you want to test this isolation level, execute the following statement, and then try to query and update the query, they will all be blocked and you won't see the data for 50 seconds.

Set Transaction Isolation  Level Repeatable ReadBegin TranUpdateCustomerSetCustomerName='Changed' whereCustomercode='1001'WAITFORDELAY'000:00:50'rollback Tran

If you execute the following query in repeat-read mode, you will not be able to do anything in 50 seconds until you get the query results after the transaction is completed.

Set Transaction Isolation  Level Repeatable Read begin Tran Select *  from where Customercode='1001'WAITFOR'000:00:50  'commitTran

Note that during this time you can add a new record of customercode= ' 1001 ', in other words the Unreal line is possible.

Solution 6: Use the serialization isolation level to resolve unreal line issues

This is the highest level of isolation, during which other transactions cannot be updated, queried and inserted into records, and some key points about serialized transactions include:

• When the isolation level is serialized, no other transaction can be inserted, updated, deleted, or queried.

• There will be a lot of blocking, but all concurrency issues can be resolved.

"Reprint". NET Lock 6 Big deal method pessimistic optimistic oneself grasps