. Net locks

This article describes how to handle the problem. net locks six methods. First, let's discuss the concurrency problem, and then discuss the three methods to deal with optimistic locks. Optimistic locks cannot solve the concurrency problem from the root, therefore, we will introduce pessimistic locks later. Finally, we will introduce how the isolation level helps us implement pessimistic locks. Examples are listed at each isolation level to make the concept clearer.

Why do we need a lock?

In a multi-user environment, multiple users may update the same records at the same time, which leads to conflicts. This is a famous concurrency problem.

Figure 1 parallelism comics

How to solve the concurrency problem?

The correct locking policy can solve the concurrency problem. After the resource is locked, other processes will be blocked if they want to access it.

What kind of conflicts Does concurrency cause?

Concurrency mainly causes four common problems. For details, see the table below.

Problem	Brief Description	Explanation
Dirty read	Dirty reading occurs when a transaction reads records of the other half of the transaction.	The values seen by user a and user B are 5. User B changes the value to 2. User A still sees the value 5, and dirty reading occurs.
Cannot be read repeatedly	Each time you read data, if the values you get are different, it indicates that you have encountered the problem of non-repeated reading.	The value that user a sees is 5. User B changes the value to 2. After user a refresh, the value is still 5, and it cannot be read repeatedly.
Unreal line	If the update and delete SQL statements do not affect the data, it is likely to encounter an illusory row problem.	User A changes all values from 5 to 2. User B inserts a new record with value 2 User A queries all records with a value of 2, but cannot find the record. In this case, an illusory line occurs.
Update loss	The update of a transaction overwrites the UPDATE results of other transactions.	User A updates all values from 5 to 2 User B updates all values from 2 to 5 User a lost his updates.

How can we solve the above conflicts?

The answer is to use optimistic or pessimistic locks, which will be further elaborated below.

Figure 2 optimistic and pessimistic locks

What is optimistic lock?

As the name implies, optimistic locks assume that multiple transactions do not affect each other. In other words, in optimistic locks mode, no locks are executed, and transactions only verify whether other transactions modify data, if yes, the transaction will be rolled back; otherwise, the transaction will be committed.

Figure 3 optimistic lock

How does optimistic lock work?

There are multiple ways to implement optimistic locks, but the basic principles are the same. There are always the following five steps:

• Record the current Timestamp

• Start to modify the value

• Check whether another person has updated the value before the update (by checking the New and Old timestamps)

• Roll back if they are not equal; otherwise, submit

Figure 4 principle of Optimistic Locking

Optimistic Lock solution

In. net, there are three ways to implement optimistic locks:

• Dataset: a dataset is the default method for Optimistic Locking. It checks the new and old values before updating.

• Timestamp: Create a timestamp data type in your table. When updating, check whether the old timestamp is equal to the new timestamp.

• Directly check the new and old values: when updating, check whether the old and new values are equal. If they are not equal, roll back. Otherwise, submit.

Solution 1: Dataset

As mentioned above, the dataset is the default method for processing optimistic locks. Below is a simple snapshot, and there is a debugging point on the update function of the adapter, when I remove a breakpoint to run the UPDATE function, it throws a parallel Exception error as shown in.

Figure 5 Error thrown during update function execution

If you run the backend analyzer, you will see the update statement to check whether the current value and old value are equal:

 Exec sp_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 ( 11  ), @ P2  Int  , @ P3
  Int  , @ P4 nvarchar (  4  ), @ P5  Int  , @ P6  Int  , @ P7  
Int  , @ P8 nvarchar (  18  ), @ 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  Old
Author  '  , @ P9 = 0, @ P10 = N  '  Kk  ' 

In this case, I try to change the value of the "authorname" field to "this is new", but the old value "this is old author" will be checked during the update ", below is the simplification of old valuesCodeSection:

 
, @ P8=N'This is old author'
 

Solution 2: Use the timestamp Data Type

SQL Server has a data type of Timestamp, which is another way to implement optimistic locks. Each time SQL server data is updated, the timestamp automatically generates a unique binary value, the timestamp data type can be used to verbose your record updates.

Figure 6 timestamp Data Type

To implement optimistic locks, you must first obtain the old timestamp value and check whether the old timestamp value is equal to the current timestamp when updating. For example:

 
Update tbl_itemsSetItemname=@ ItemnameWhereCurrenttimestamp=@ Oldtimestamp
 

Check whether the update operation has occurred. If no update has occurred, use the raiserror of SQL Server to generate a series of error messages.

If(@ Rowcount=0)
Begin
Raiserror ('Hello some else changed the value',16,10)
End
 

If a concurrency conflict occurs, when you call executenonquery as shown in, you should see the error message.

Figure 7 storage process error due to time stamp change

Solution 3: Check the old and new values

In many cases, we only need to check the consistency of the related field values, while other fields can be ignored. In the update statement, we can directly make this comparison.

Update tbl_itemsSetItemname=@ ItemnameWhereItemname=@ Olditemnamevalue
 

But the use of optimistic locks does not seem to completely solve the problem. The use of optimistic locks can only check the concurrency problem. To solve the concurrency problem from the root, we need to use pessimistic locks, so optimistic locks can play a preventive role, the pessimistic lock can be cured.

What is a pessimistic lock?

Pessimistic locks always assume that there will be concurrency/conflict issues, so the record will be locked first and then updated.

Figure 8 pessimistic lock

How to deal with pessimistic locks?

You can specify the isolationlevel (isolation level), ADO. Net level, or use the transaction range object to process pessimistic locks in the SQL server stored procedure.

What kind of locks can be obtained using pessimistic locks?

Pessimistic locks can be used to obtain four types of locks: shared, exclusive, update, and intent. The first two are real locks, the latter two are the mixture of locks and tags.

	When to use	Read allowed	Write allowed
Shared lock	When you only want to read and do not want updates from other transactions	Yes	No
Exclusive lock	When you want to modify the data and do not want others to read it	No	No
Update lock	This is a hybrid lock. It is used when you perform multiple update operations.
	Read phase	Yes	No
	Operation Phase	Yes	No
	Update phase	No	No
Intention lock (request lock)	Intention locks are classified and used when you want to lock lower-level resources. For example, a shared intention lock on a table means that the shared locks are for rows in pages and tables,	Not applicable	Not applicable
Mode Lock	When you modify the table structure, use	No	No
Big data block update lock	Used when you update big data blocks	Table-level (NO)	Table-level (NO)

 

Detailed explanation of confusing update locks

Other locks are easy to understand, but update locks are confusing because they are mixed with locks and tags. Before update, we must first read records, and the locks are shared during read, in actual update, we need to have an exclusive lock. The update lock is very short.

Figure 9 Several locks used during update

Differences between different isolation levels and when to use them

There are four transaction isolation levels. The following table lists these four isolation levels and their usage time.

Isolation level	Read	Update	Insert
Read uncommitted	Read uncommitted data	Allow	Allow
Read committed (default)	Read submitted data	Allow	Allow
Repeated read	Read submitted data	Not Allowed	Allow
Serialization	Read submitted data	Not Allowed	Not Allowed

How do I specify the isolation level?

The isolation level is a function of relational databases. That is to say, it is basically only related to SQL Server, but also to ADO. net, EF, or LINQ does not matter, but you can set the isolation level on these components.

Figure 10 isolation level

Middle Layer: In the middle layer, you can use the transaction range object to specify the isolation level.

 
Transactionoptions transopt=New transactionoptions ();
Transopt. isolationlevel=System. Transactions. isolationlevel. readcommitted;
Using(Transactionscope scope Scope= New
Transactionscope (transactionscospontion. required, transoptions ))
{
 

Ado. Net: In ADO. net, you can use sqltransaction objects to specify the transaction isolation level.

Sqltransaction objtransaction=
Objconnection. begintransaction (system. Data. isolationlevel. serializable );
 

SQL Server: You can also use "set transacation isolation level" in tsql to specify the isolation level.

 
SET transaction isolation level read committed;
 

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

	Read committed	Repeated read	Serialization	Read uncommitted
Dirty read	Solution	Solution	Solution	No
Update loss	No	Solution	Solution	No
Non-repeated read	No	Solution	Solution	No
Fantasy line	No	No	Solution	No

Solution 4: Use "Read committed" to solve the "dirty read" Problem

Some key points about "Read committed:

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

• It only reads submitted data. In other words, it ignores any uncommitted data until the data is submitted. You can also see the update.

Figure 11 Analysis of Read committed Mode

If you want to see the situation, you only need to follow the steps below:

• Open two query windows and execute an update transaction, but do not submit it;

• When you execute a query in the second window, a message indicating that the query is blocked is displayed, as shown in.

Figure 12 the query is blocked and cannot be executed until the update transaction is committed.

Does the opposite of "Read committed" mean "read not committed?

Yes, the uncommitted read is the opposite of the committed read. When you set the read uncommitted transaction isolation level, the uncommitted data is also read.

Key Points of "Read committed:

• Uncommitted files are visible, so dirty reading is possible;

• No lock is captured;

• Locks are useful when they are not important, and more importantly, concurrency and throughput.

If you want to test it, try the following SQL statement to execute a new one and wait 20 seconds for rollback. During this period, if you perform a query, uncommitted data is returned, but after 20 seconds, you can query again and the returned old data will be returned because the submitted data has been rolled back.

 Set  Transaction isolation level read uncommitted
Begin tran

Update customer
  Set  Customername =  '  Changed  '  Where  Customercode  =  '  1001  '  
Waitfor Delay  '  000:00:20  '  
Rollback transet transaction isolation level read uncommitted
Select  * From customer  Where  Customercode  =  '  1001  ' 

Solution 5: Use duplicate read to solve loss of updates and non-duplicate read

After the isolation level is set for repeated reads, no one else can read and update data. The key points of the isolation level for repeated reads include:

• When the repetition transaction isolation level is set for the query, only committed data is read.

• When you select a record using repeated reads, other transactions cannot update the record, but the query is fine.

• If repeated transactions are set in the update query, you must wait until the transaction is completed to read and update the same records.

• When the selection and update queries are set to repeated reads, other transactions can insert new records. In other words, it is possible to create an illusory row.

If you want to test the isolation level, execute the following statements, and then try to query and update the query, they will be blocked, and you will be able to see the data in 50 seconds.

SetTransaction isolation level Repeatable read
Begin tran
Update customerSetCustomername='Changed'WhereCustomercode='1001'
Waitfor Delay'000:00:50'
Rollback tran
 

If you execute the following query statement in repeat read mode, you won't be able to do anything in 50 seconds until the transaction is completed.

SetTransaction isolation level Repeatable read
Begin tran
Select*From customerWhereCustomercode='1001'
Waitfor Delay'000:00:50'
Commit tran
 

Note: during this period, you can add a new record with customercode = '000000'. In other words, it is possible to add an illusory row.

Solution 6: Use the serialization isolation level to solve the problem of illusory rows

This is the highest isolation level. During this period, other transactions cannot update, query, and insert records. Some key points about serialized transactions include:

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

• Many blocking occurs, but all concurrency problems can be solved.