In practice, multi-user concurrent access to the production environment inside, we often as possible to maintain data consistency. The most typical example of which is that we read data from a table inside, check the validation of the data changes, and then write back to the database. In the process of reading and writing, if the environment inside a multi-user concurrency, and other users have to modify the data you want to amend is the situation likely to occur, thus causing data inconsistency. Way to solve this, SQL SERVER proposed optimistic locking and pessimistic locking concept, below I have an example to illustrate how to use optimistic locking and pessimistic locking to solve this problem.
/ Bin / boot / dev / etc / home / lib / lost + found / media / misc / mnt / net / opt / proc / root / sbin / selinux / srv / sys / tmp / u01 / usr / var / vmware build test Table: Card, on behalf of a real card database for user registration. Users select from inside an unused card, which is F_Flag = 0 of the card, to the user registration: Update F_Name, F_Time, F_Flag field. If two users simultaneously update a card's situation is intolerable, that is inconsistent with what we call the data line. * /
create table Card (F_CardNO varchar (20), F_Name varchar (20), F_Flag bit, F_Time datetime)
Go
insert Card (F_CardNo, F_Flag) select''1111-1111'', 0
insert Card (F_CardNo, F_Flag) select''1111-1112'', 0
insert Card (F_CardNo, F_Flag) select''1111-1113'', 0
insert Card (F_CardNo, F_Flag) select''1111-1114'', 0
insert Card (F_CardNo, F_Flag) select''1111-1115'', 0
insert Card (F_CardNo, F_Flag) select''1111-1116'', 0
insert Card (F_CardNo, F_Flag) select''1111-1117'', 0
insert Card (F_CardNo, F_Flag) select''1111-1118'', 0
insert Card (F_CardNo, F_Flag) select''1111-1119'', 0
insert Card (F_CardNo, F_Flag) select''1111-1110'', 0
Go
- Here are our frequently updated program is as follows:
declare @ CardNo varchar (20)
Begin Tran
- Select an unused card
select top 1 @ CardNo = F_CardNo
from Card where F_Flag = 0
- 50 seconds delay to simulate concurrent access.
waitfor delay''000:00:50''
- To just select out the card to register.
update Card
set F_Name = user,
F_Time = getdate (),
F_Flag = 1
where F_CardNo = @ CardNo
commit
Question: If we are in the same window, perform the same piece of code, but remove the waitfor delay clause. On both sides of the implementation we found that although after the implementation of two registered, but only registered a card, that is, two people registered with a card.
Pessimistic locking solutions
- We are on top of the code as long as the changes do little pessimistic locking can be achieved.
declare @ CardNo varchar (20)
Begin Tran
- Select an unused card
select top 1 @ CardNo = F_CardNo
from Card with (UPDLOCK) where F_Flag = 0
- 50 seconds delay to simulate concurrent access.
waitfor delay''000:00:50''
- To just select out the card to register.
update Card
set F_Name = user,
F_Time = getdate (),
F_Flag = 1
where F_CardNo = @ CardNo
commit
Note the distinction between them do? With (updlock), Yes, we use the query time with (UPDLOCK) option in the query log when we update the record with a lock that we will be on the second record to be updated. NOTE update locks and shared locks are not in conflict, that is, other users can also search the contents of this table, but the exclusive and update locks and lock the conflict. so other users will be blocking the update. If we another window to do this code, the same clause without waifor delay. both sides finished, we found two cards successfully registered. Perhaps we have discovered the shortcomings of pessimistic locking: When a user transaction to update the time Other updates to be queued users, even if the user updates a record is not the same.
Optimistic locking solution
- First, we add in the Card table inside a F_TimeStamp column, the column is a varbinary (8) type. But when the update will automatically increase this value.
alter table Card add F_TimeStamp timestamp not null
- Pessimistic locking
declare @ CardNo varchar (20)
declare @ timestamp varbinary (8)
declare @ rowcount int
Begin Tran
- Get card number and the original timestamp value
select top 1 @ CardNo = F_CardNo,
@ Timestamp = F_TimeStamp
from Card
where F_Flag = 0
- 50 seconds delay to simulate concurrent access.
waitfor delay''000:00:50''
- Registration card, but to compare the time stamp is changed. If there is no change. Updated successfully. If a change occurs, the update fails.
update Card
set F_Name = user,
F_Time = getdate (),
F_Flag = 1
where F_CardNo = @ CardNo and F_TimeStamp = @ timestamp
set @ rowcount = @ @ rowcount
if @ rowcount = 1
begin
print''updated successfully!''
commit
end
else if @ rowcount = 0
begin
if exists (select 1 from Card where F_CardNo = @ CardNo)
begin
print''This card has been registered by another user! ''
rollback tran
end
else
begin
print''does not exist for this card!''
rollback tran
end
end
In another not waitfor a window inside the implementation of the code, registration is successful, return to the original window, we find that the time it shows hints of this card to register by another user tips. It is clear, so that we can to avoid two users register the card, the emergence of the phenomenon. Meanwhile, the use of this method is not used as an added bonus update lock, so concurrent processing systems to increase capacity.
On top I described in detail optimistic locking and pessimistic locking for use in the actual production environment inside, if the amount is not complicated, we can use the pessimistic locking approach, because this method is very convenient and easy to use. However, if the system very large complicated words, pessimistic locking will bring very large performance problem, so we should choose the method of optimistic locking.
If you find any article inside the wrong place, please remind me, are welcome to study and discuss together.