EF + SQLSERVER control concurrent snatching of red packets minus balance (Improvement), sqlserver snatching of Red Packets

EF + SQLSERVER control concurrent snatching of red packets minus balance (Improvement), sqlserver snatching of Red Packets

In recent years, as soon as you have heard of the possibility of snatching red packets, you will pick up your mobile phone and click ~~~~ Then the problem arises...

How to control at the same time to ensure that there will be no error in deducting the red envelope balance in the database. Our previous practice was to directly lock the program. The downside is that the wait time is too long. Every time a thread goes in, it will go through the following processes.

The process is

1. query table

2. verify information

3. Sending Server

4. Waiting for feedback

5. Update the table

It is the next process after these processes are completed. I must wait until all the flowers are thankful ~

In addition, the sending server process time ranges from 0 s to 9 s. There will be a lot of idle time, And the CPU will generate a lot of idle time here. In this case, Server Load balancer cannot be continued. If multiple sites are deployed, database concurrency problems will occur.

If the database is released after the lock is updated before the query table, database concurrency is not generated. However, when the second thread enters the query, it will remain waiting, and its time consumption is similar to that of the lock program.

Improvement

This idea comes from the design of distributed transactions. We use the method of withholding the red envelope balance to ensure that we don't need to wait for server feedback, so that the next thread can continue to execute related tasks. When the server returns feedback, another transaction is started to change the transaction status. If the feedback result is FAIL, you need to restore the red envelope balance.

The test code simulating the actual environment is roughly written to simulate the red packet snatching action.

Private void task () {for (int I = 0; I <50; I ++) {string tradeNo = Qxun. framework. utility. createOrderNo. dateTimeAndNumber (); try {using (var trans = new TransactionScope () {using (var dbContext = new ActivityDbContext () {// lock var model = dbContext. database. sqlQuery <Qxun. activity. contract. VIPPassRedBag013> (@ "select * from VIPPassRedBag013 with (updlock) where ActivitySceneID = 199 "). firstOrDefau Lt (); var mode = dbContext. database. sqlQuery <Qxun. activity. contract. VIPPassRedBag013Mode> (@ "select * from VIPPassRedBag013Mode with (updlock) where ActivitySceneID = 199 "). toList (); // simulate the verification delay Thread. sleep (5); // get the amount of the red packet VIPPassRedBag013Mode currentMode = null; foreach (var modeItem in mode) {if (modeItem. remainCount> 0) {currentMode = modeItem; break ;}/// determine whether the if (currentMode! = Null & model! = Null & model. redBagBalance> = currentMode. money) {VIPPassRedBag013Play currentPlayModel = new VIPPassRedBag013Play (); // the object of this participation record currentPlayModel. VIPPassRedBag013ModeID = currentMode. ID; currentPlayModel. weixinUserID = Thread. currentThread. managedThreadId; currentPlayModel. money = Convert. toInt32 (currentMode. money * 100); // The amount to be paid (saved to the table) currentPlayModel. tradeNumber = tradeNo; currentPlayModel. status = (int) TradeStatus. trading; currentPlayModel. VIPPassRedBag013ModeID = currentMode. ID; currentPlayModel. activitySceneID = 199; dbContext. insert <VIPPassRedBag013Play> (currentPlayModel); currentMode. remainCount-= 1; dbContext. update <VIPPassRedBag013Mode> (currentMode); model. redBagBalance-= currentMode. money; dbContext. update <Qxun. activity. contract. VIPPassRedBag013> (model); trans. complete ();} else {trans. complete () ;}}} catch (Exception ex) {}// submit to string returnCode = "SUCCESS"; Random ran = new Random (); int time = ran. next (100); if (time <= 1) {returnCode = "FAIL" ;}// simulate network latency Thread. sleep (time * 100); // set the number of retries bool retry = true; int retryCount = 0; do {Qxun. activity. contract. VIPPassRedBag013 model = null; VIPPassRedBag013Play playModel = null; VIPPassRedBag013Mode mode = null; try {using (var trans = new TransactionScope () {using (var dbContext = new ActivityDbContext ()) {// it is easy to obtain the exception model = dbContext. database. sqlQuery <Qxun. activity. contract. VIPPassRedBag013> (@ "select * from VIPPassRedBag013 with (updlock) where ActivitySceneID = 199 "). firstOrDefault (); playModel = dbContext. database. sqlQuery <Qxun. activity. contract. VIPPassRedBag013Play> (@ "select * from VIPPassRedBag013Play with (updlock) where TradeNumber = '" + tradeNo + "'"). firstOrDefault (); mode = dbContext. database. sqlQuery <Qxun. activity. contract. VIPPassRedBag013Mode> (@ "select * from VIPPassRedBag013Mode with (updlock) where ID =" + playModel. VIPPassRedBag013ModeID ). firstOrDefault (); if (returnCode = "SUCCESS") {playModel. status = (int) TradeStatus. success; playModel. remark = "retry =" + retryCount + ", success; time =" + DateTime. now. toString (); playModel. finishTime = DateTime. now; dbContext. update <VIPPassRedBag013Play> (playModel); trans. complete (); retry = false;} else {model. redBagBalance + = mode. money; dbContext. update <Qxun. activity. contract. VIPPassRedBag013> (model); playModel. status = (int) TradeStatus. fail; playModel. remark = "retry =" + retryCount + ", fail; time =" + DateTime. now. toString (); playModel. finishTime = DateTime. now; dbContext. update <VIPPassRedBag013Play> (playModel); mode. remainCount + = 1; dbContext. update <VIPPassRedBag013Mode> (mode); trans. complete (); retry = false ;}}} catch (Exception ex) {// if the previous thread is blocked when requesting the database // if the execution fails retryCount ++; retry = true;} if (retryCount> 5) {break ;}} while (retry );}}

 
Simulate 100 concurrent snatching of Red Packets

Public ActionResult Excute () {for (int I = 0; I <100; I ++) {Thread thread = new Thread (new ThreadStart (task); thread. start ();} return Content ("complete! ");}

 

The above Code also uses a retry variable to control the timeout caused by long waits, so that each order can be processed. However, when the number of threads is-, there will be 10 to 20 VIPPassRedBag013Play orders in the status of Trading. It becomes unstable when the number of threads is greater than 200. Why is it not found at present. Hope someone can give me some advice.

To solve this problem, I wrote a cycle in Global to find the VIPPassRedBag013Play 10 minutes ago, and the order status was Trading list (not processed after 10 minutes, ). Obtain the order number and check the red envelope transaction records. Update the Transaction Status of the database based on the feedback of red packets.

Public ActionResult Check () {using (var dbContext = new ActivityDbContext () {// query the order var playModel = dbContext in the transaction 10 minutes ago. database. sqlQuery <Qxun. activity. contract. VIPPassRedBag013Play> (@ "select * from VIPPassRedBag013Play with (nolock) where ActivitySceneID = 199 and [status] = 2 and DATEDIFF (MINUTE, CreateTime, GETDATE ()> 10 "). toList (); if (playModel! = Null & playModel. count> 0) {foreach (var item in playModel) {using (var trans = new TransactionScope () {// submit to query string returnCode = "SUCCESS "; random ran = new Random (); int time = ran. next (100); if (time <= 1) {returnCode = "FAIL" ;}// query the information of the red envelope // simulate the network delay Thread. sleep (time * 100); if (returnCode = "SUCCESS") {item. status = (int) TradeStatus. success; item. remark = "success; time =" + DateTime. now. toString (); item. finishTime = DateTime. now; dbContext. update <VIPPassRedBag013Play> (item); trans. complete ();} else {Qxun. activity. contract. VIPPassRedBag013 model = dbContext. database. sqlQuery <Qxun. activity. contract. VIPPassRedBag013> (@ "select * from VIPPassRedBag013 with (updlock) where ActivitySceneID = 199 "). firstOrDefault (); VIPPassRedBag013Mode mode = dbContext. database. sqlQuery <Qxun. activity. contract. VIPPassRedBag013Mode> (@ "select * from VIPPassRedBag013Mode with (updlock) where ID =" + item. VIPPassRedBag013ModeID ). firstOrDefault (); model. redBagBalance + = item. money; dbContext. update <Qxun. activity. contract. VIPPassRedBag013> (model); item. status = (int) TradeStatus. fail; item. remark = "fail; time =" + DateTime. now. toString (); item. finishTime = DateTime. now; dbContext. update <VIPPassRedBag013Play> (item); mode. remainCount + = 1; dbContext. update <VIPPassRedBag013Mode> (mode); trans. complete () ;}}}} return View ();}

PS: after such improvement, it should be much better than before. Of course, this is far from enough. I hope you can give me some advice. Thank you very much!