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.