Fast Money payment with SQL Server optimistic lock and pessimistic lock

In the actual multi-user concurrent access production environment, we often want to maintain the consistency of the data as much as possible. The most typical example is that we read the data from the table, check the validation, modify the data, and then write back to the database. In the process of reading and writing, if in a multi-user concurrency environment, other users have changed the data you want to modify is very likely to occur, resulting in data inconsistencies.

Recently in the fast money to pay the time of the encounter this problem, the original code is as follows:
1. Structure of the table order:
OrderId int self-growth
Status nvarchar (10)//unhandled State "Wait"

2. Related SQL statements:
Select Status from order where orderid= @OrderID

Update Order Set Status = ' Y ' where [email protected]

3. Program Pseudo-code:
var status = Getorderstatus (OrderID); Get User Recharge Status
if (status= "Wait")//if the status is not processed
Updateorderstatus (OrderID);//update status is processed
Backend code to recharge the user

According to reason such code is no problem, because to the same user, there is no concurrency problem, there is no one payment two times the problem of recharge.

However, the fast Money processing method is the user through the payment, the fast money to re-transfer to our site, we receive the fast money to pay the successful request, to the user to recharge, and will redirect the page back to the fast money, fast money again directed to the payment success of the page.
The process is as follows: User--->GoToPay.aspx--> fast money-->afterpay.aspx--Fast money-->afterpaymessage.aspx.

Because the fast money uses the round-robin mechanism, accesses the afterpay.aspx every second, and therefore accesses the afterpay.aspx several times, the problem comes out:
var status = Getorderstatus (OrderID); Get User Recharge Status
if (status= "Wait")//if the status is not processed
Updateorderstatus (OrderID);//update status is processed
//Backstage to the user to recharge the code, will recharge two times

When the program has not yet updated the status, the second request has arrived, when using Getorderstatus, get the "wait", so will recharge two times.

Solution:
Way One:
Using the normal optimistic locking scheme
The table order contains a column timestamp column, which is the varbinary (8) type. However, this value will grow automatically at the time of the update.

Select Status,timestamp from order where orderid= @OrderID

--Update the state, but to compare whether the timestamp has changed. If there is no change, the number of rows affected is 1, and the update succeeds. If a change occurs, the number of rows affected is 0.
Update Order
Set status= "Y",
where [email protected] and [email protected]
Set @[email protected] @rowcount


Modification of the program
var status = Getorderstatus (orderid,out timestamp); Get User Recharge Status
if (status= "Wait")//if the status is not processed
int rowcount = updateorderstatus(Orderid,timestamp);
if (rowcount= 1)//Status not updated
Recharge
else//Fast money when the second time comes, the number of rows returned is 0
Return "already charged value"
endif

Way two:
Or optimistic locking scheme:
Because the status of the table order itself can be the same as the timestamp column effect, modified as follows:
Update Order
Set status= "Y",
where [email protected] and status= "Wait"
Set @[email protected] @rowcount

Modification of the program
var status = Getorderstatus (OrderID); Get User Recharge Status
if (status= "Wait")//if the status is not processed
int rowcount = updateorderstatus(OrderID);
if (rowcount= 1)//Status not updated
Recharge
else//Fast money when the second time comes, the number of rows returned is 0
Return "already charged value"
endif
This scenario is much simpler.

Programme III:
Using pessimistic locking, the SQL statement modified this time is not an update but a select
As follows:
Select Status from order with (UPDLOCK) where orderid= @OrderID

The program doesn't have to be modified at all:

Get the user recharge status, fast money the second time, if the first time has not been updated, the order line is still locked, so will wait for the first update to release the lock
var status = Getorderstatus (OrderID);
if (status= "Wait")//if the status is not processed
Updateorderstatus (OrderID);//update status is processed
Backend code to recharge the user

The simplest way to do this is to simply add the with (UPDLOCK) to the stored procedure without modifying the program at all.
The disadvantage is that it can be bad for a lot of concurrency and can cause deadlocks. Of course, for the top-up of such transactions, still can be more suitable.

This is the first technical article I wrote, perhaps many places not enough to speak, I hope you correct me.

Next article I want to provide a simpler solution to the concurrency problems caused by airline ticketing.