MySQL handles high concurrency, preventing inventory oversold

In fact, MySQL processing high concurrency, prevent inventory oversold problem, at the time of last year, Wang has mentioned, but unfortunately, even when everyone understood, but in the reality of development, still do not have this aspect of consciousness. Today, some of my understanding, to tidy up the problem, and hope that the future of such courses can be more points.

First of all, the issue of inventory oversold description: General e-commerce sites will encounter such as group purchase, second Kill, specials and other activities, and such activities have a common feature is the surge in traffic, thousands or even tens of thousands of people snapping up a commodity. However, as an active commodity, inventory is certainly very limited, how to control inventory does not allow overbought, in order to prevent unnecessary loss is a lot of e-commerce website Programmers Headache problem, this is also the most basic problem.

From the technical perspective, many people will certainly think of business, but the transaction is to control the inventory oversold requirements, but not sufficient necessary conditions.

Example:

Total Stock: 4 items

Requester: A, a commodity B, 2 commodities C, 3 items

The procedure is as follows:

begintranse (open transaction) Try {    = $dbca->query (' Select amount from s_store where PostID = 12345 ');     if (Result->amount > 0) {        //Quantity The amount of inventory that was lost for the request        $DBCA->query (' Update s_ Store Set amount = amount-quantity where PostID = 12345 ');}    } Catch ($e Exception) {    rollBack (rollback)}commit (COMMIT transaction)

The above code is our usual control inventory write code, most people will write this, seemingly the problem is not big, in fact, hidden a huge loophole. Access to the database is actually access to the disk files, the table in the database is actually saved on disk files, even a file contains multiple tables. For example, because of high concurrency, there are currently three users A, B, c three users into the transaction, this time will generate a shared lock, so in the Select, the three users found in the inventory amount is 4, but also note that MySQL InnoDB found that the results are version-controlled, Before other users update without a commit (that is, before the new version is generated), the current user will find the results are still the version;

Then update, if the three users at the same time to update here, this time the UPDATE statement will be the concurrent serialization, that is, to arrive at the same time there are three users order, one to execute, and generate an exclusive lock, before the current UPDATE statement commit, Other users wait to execute, commit, generate a new version, so after execution, the inventory must be negative. But according to the above description, we modify the code will not appear overbought phenomenon, the code is as follows:

begintranse (open transaction) Try {    //Quantity The quantity of inventory to be dropped    $dbca->query (' update s_store set amount = Amount-quantity where PostID = 12345 ');    $result = $DBCA->query (' Select amount from s_store where PostID = 12345 ');     if Throw New Exception (' insufficient inventory '); }}catch($e Exception) {rollBack (rollback)} commit (COMMIT TRANSACTION)

In addition, a more concise approach:

begintranse (open transaction) Try {    //Quantity The quantity of inventory to be dropped    $dbca->query (' update s_store set amount = Amount-quantity where amount>=quantity and PostID = 12345 ');} Catch ($e Exception) {    rollBack (rollback)}commit (COMMIT transaction)

1, in the case of the second kill, must not be so high frequency to read and write the database, will seriously cause performance problems
You must use the cache to put items that need to be killed in the cache and use locks to handle their concurrency. When the user second kill submit order, the number of goods (locking/unlocked) before the processing of other aspects, processing failure in the data increment 1 (locking/unlock), otherwise the transaction is successful.
When the number of items is reduced to 0 o'clock, it means that the product is finished in seconds, rejecting requests from other users.

2, this certainly cannot directly operate the database, will hang. The direct read Library write library is too stressful for the database to be cached.
Put the items you want to sell, such as 10 items into the cache, and then set a counter in the Memcache to record the number of requests, which you can base on the number of items you want to kill to sell, for example, if you want to sell 10 items, and only allow 100 requests to come in. That when the counter reaches 100, the back comes to show the end of the second kill, which can alleviate the pressure on your server. Then according to these 100 requests, the first payment of the first after payment of the prompt merchandise in seconds to kill.

3, first, multi-user concurrent modification of the same record, it is definitely after the submission of the user will overwrite the former submitted results.

This can be solved directly using the lock mechanism, optimistic lock or pessimistic lock.

optimistic lock , that is, in the database design a version number of the field, each modification to make it +1, so that at the time of submission than the version number before committing to know is not concurrent commit, but there is a drawback is only the application control, if there are cross-application to modify the same data optimistic lock can not be done, This time can consider pessimistic lock.

pessimistic lock , is directly at the database level to lock the data, similar to Oralce in the use of the select xxxxx from the XXXX where xx=xx for update, so that other threads will not be able to submit data.

In addition to the locking method can also use the way to receive locks, the idea is to design a state identity bit in the database, the user before modifying the data, the status identifier is the state that is being edited so that other users to edit this record when the system will find that other users are editing, the request to reject their edits, Similar to your operating system in which a file is being executed, and then you want to modify the file, the system will remind you that the file is not editable or deleted.

4, it is not recommended to lock at the database level, we recommend the memory lock through the service side (lock the primary key). When a user to modify the data of an ID, the ID to be modified into memcache, if other users trigger the modification of this ID data, read memcache has the value of this ID, it prevents the user to modify.

5, the actual application, not to let MySQL to face the large concurrent read and write, will be the use of "external forces", such as caching, the use of master-slave library to achieve read and write separation, table, using queue writing methods to reduce concurrent read and write.

This article links http://blog.csdn.net/caomiao2006/article/details/38568825

More reference content: Http://www.roncoo.com/article/index?tn=Mysql

MySQL handles high concurrency, preventing inventory oversold