High concurrent access to MySQL problem (i): Inventory over-reduction

If you do not take any precautions when updating a row, you will be prone to negative inventory errors when you are accessing multiple threads.

Here are examples of PHP, Mysql,apache AB tools:

MySQL table structure

CREATE TABLE' yxt_test_concurrence ' (' ID ')int( One) not NULLauto_increment, ' value 'int( One) not NULLCOMMENT'Stock',  PRIMARY KEY(' id ')) ENGINE=InnoDB auto_increment=2 DEFAULTCHARSET=UTF8 COLLATE=Utf8_unicode_ci COMMENT='Inventory Table';

CREATE TABLE' YXT_TEST_PV ' (' ID ')int(Ten) unsigned not NULLauto_increment, ' Val 'int(255)DEFAULT NULLCOMMENT'The number of inventory that the thread has read',  PRIMARY KEY(' id ')) ENGINE=MyISAM auto_increment=351 DEFAULTCHARSET=UTF8 COLLATE=Utf8_unicode_ci COMMENT='access the Record table, add a record for each visit, and record the number of stocks on this visit';

Save 500 in the inventory table for a simulated inventory.

Here, for convenience, PHP uses the TP framework:

 Public functionTC () {$this->TC = M ("test_concurrence");//simulate the remaining quantity of a product        $this->PV = M ("TEST_PV");//number of simulated visits        $res=$this->tc->field (' value ')->find (1);//The remaining quantity found        $value=$res[' Value ']; if($value>0) {//if it is greater than 0, the following logic is        $this->pv->data (Array(' val ' = =$value))->add ();//This is used to record the number of visits,and record the number of stocks at the time of this visit.

        Execute ("UPDATE ' yxt_test_concurrence ' SET ' value ' = ' value '-1 WHERE ' id ' = 1"//  Number of goods minus 1
        
       }

Simulate concurrent access using the AB tool:

c:\users\chenhui>ab-c 50-n http://study.com/course/Course/tc/This is Apachebench, Version 2.3<$Revision:1554214 $>Copyright 1996 Adam Twiss, Zeus technology LTD, http://www.zeustech.net/Licensed to the Apache software Foundation, http:/ /www.apache.org/benchmarking studyyxtcmf.com (Be patient) completed requestscompleted 300 requestscompleted requestscompleted requestsfinished requestsserver software:apache/2.4.9server Hostna Me:studyyxtcmf.comServer port:80document Path:/course/course/tc/document length:2578        6 bytesconcurrency Level:50time taken for tests:60.035 Secondscomplete requests:500failed requests: (connect:0, receive:0, length:450, exceptions:0) Total transferred:12973630 byteshtml transferred:12       785130 bytesrequests per second:8.33 [#/sec] (mean) time per request:6003.543 [MS] (mean) time per request: 120.071 [MS] (mean, across all concurrent requests) Transfer rate:211.03 [Kbytes/sec] receivedconnection times ( ms) Min Mean[+/-sD] Median maxconnect:0 1 2.1 1 34processing:781 5915 1578.6 5996 12272waiting:765 59 1581.8 5983 12261total:783 5916 1578.4 5997 12272Percentage of the requests served within a certain Tim e (ms) 50% 5997 66% 6385 75% 6707 80% 6850 90% 7387 95% 8402 98% 9734 99% 10300 100% 12272 (longes T request)

To view database records:

SELECT *  fromYXT_TEST_PV;--Intercept a record (on the left is the first few visits, the right side is the inventory that is seen on the second visit)| 338 |  164 || 339 |  164 || 340 |  163 || 341 |  162 || 342 |  162 || 343 |  162 || 344 |  162 || 345 |  157 || 346 |  156 || 347 |  156 || 348 |  153 || 349 |  155 ||  - |  151 |

You can find that the inventory quantity is the same in 341-343 reads, and there is no problem when there is a lot of inventory: because the logic of reducing inventory in the program is the current inventory minus 1. But when inventories are limited, there is a high likelihood of problems, such as having only one inventory, At this time there are more than one thread query at this time there is also a stock, because 1>0 meet the conditions, so inventory minus 1, multiple threads of the current inventory minus 1, and finally more inventory, negative, which is not allowed.

So be sure to take measures.

In my opinion, the general principle is that only one session will be allowed to modify the inventory for a given moment. To satisfy this condition. There are two options:

1. For a given moment of inventory, only one session is allowed to read (lock mechanism). After the lock is released, other sessions can read inventory.

2. For a certain moment of inventory, set the version (that is, add a version of the field for comparison. My understanding of the version is a tick), update the inventory to determine whether the version has changed, if not changed, update the inventory at the same time, update the version number. If the version changes when you update your inventory, That must be another thread has already modified the inventory, and in this case discard the modification.

Select 1. Use the MySQL lock mechanism. (Pessimistic Lock)

 Public function TC () {        $this->tc = M ("test_concurrence");   The remaining quantity of the simulated goods        $this->PV = M ("TEST_PV");   Analog Access        //Lock the table, note that if the lock process to operate more than one table, you have to lock these tables, or you will get an error
mysql> Lock Table Yxt_test_concurrence read;--only a single table was locked

Query OK, 0 rows Affected (0.00 sec)

Mysql> SELECT * from yxt_test_pv;--read table not locked

Error 1100 (HY000): Table ' YXT_TEST_PV ' is not locked with lock tables--error, prompted to query the tables are not locked

M ()->execute ("Lock tables yxt_test_concurrence WRITE,YXT_TEST_PV write;"); $res=$this->tc->field (' value ')->find (1);//The remaining quantity found        $value=$res[' Value ']; if($value>0) {//if it is greater than 0, the following logic is            $this->pv->data (Array(' val ' = =$value))->add ();//This is used to record the number of visits.M ()Execute ("UPDATE ' yxt_test_concurrence ' SET ' value ' = ' value '-1 WHERE ' id ' = 1"); //number of goods minus 1        }        //UnlockM ()->execute ("Unlock Tables"); }

With the lock mechanism, the change of inventory quantity can be controlled strictly, but the use of locks will increase the cost of the database.

Option 2. Version control (optimistic lock)

Optimistic locking is the assumption that transactions do not interfere with each other, and transactions do not acquire locks when accessing data, but before committing, each transaction ensures that the other transaction does not modify the data that he reads. If the data is updated when other transactions have been modified, the commit is rolled back. Optimistic locks are often used for " Low contention data structure. When the conflict is particularly small, transactions can be completed without the overhead of managing locks and waiting for other transactions to release locks, which can lead to higher throughput rates. However, if contention for data is particularly frequent, the overhead of re-opening a new transaction can significantly affect performance.

It is generally assumed that other concurrency control methods, in which case there will be better performance, however, based on pessimistic locking methods, can lead to poor performance. Because even deadlocks can be avoided, "locks" still greatly affect concurrency performance. (I think it should be because the session is blocked, which results in a serial access to the database only)

The above definition is excerpted from Wiki:https://en.wikipedia.org/wiki/optimistic_concurrency_control

In this case, if you have concurrent access, the chances of a modification failure are higher.

Example: In a hot product scenario, it is easy to purchase a failure. This is not good for the user experience. Because it means trying again.

Summary: Which kind of lock should be taken, according to the actual situation to weigh the pros and cons, if the update is very frequent, that should use pessimistic lock. The question to consider now is how to solve concurrency problems. If you rarely update, it's easier to use optimistic locking.

High concurrent access to MySQL problem (i): Inventory over-reduction