MySQL handles high concurrency, preventing inventory oversold

Excerpt from: http://blog.csdn.net/caomiao2006/article/details/38568825

today, Wang also gave us a lesson, 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 inventory: 4 Items requested by: A, a commodity B, 2 commodities C, 3 commodity procedures are as follows: Begintranse (open transaction)Try{$result= $DBCA->query ('Select amount from s_store where PostID = 12345'); if(Result->amount >0){        //Quantity Inventory quantity that is 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 of 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, No other user update before the commit (that is, before the new version), the current user is still the results of the version, and then update, if the three users at the same time to update here, this time the UPDATE statement will be concurrent serialization, That is, to get here at the same time is three user order, one to execute, and generate an exclusive lock, before the current UPDATE statement commit, the other users wait for execution, commit, after the new version is executed, 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 Inventory quantity that is lost for the request$DBCA->query ('Update S_store Set amount = amount-quantity where PostID = 12345'); $result= $DBCA->query ('Select amount from s_store where PostID = 12345'); if(Result->amount <0){       Throw NewException ('Insufficient Inventory'); }}Catch($e Exception) {rollBack (rollback)}commit (COMMIT transaction) In addition, the more concise method: Begintranse (open transaction)Try{    //Quantity Inventory quantity that is lost for the request$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 must use the cache, will need to kill the product into the cache, and use the lock to handle its concurrency. The number of items is reduced first when the user commits the order by the second kill (locking/unlock), processing fails to increment the data by 1 (locking/unlocked), 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 manipulate 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, when multiple users modify the same record concurrently, it is certain that the user who commits the post will overwrite the result submitted by the former. This can be solved directly using the lock mechanism, optimistic lock or pessimistic lock. An optimistic lock is a field in which a version number is designed in the database, and each modification makes it+1, so that at the time of submission than the pre-commit version number will know is not concurrent commit, but there is a drawback is only the application control, if there is a cross-application to modify the same data optimistic lock can not be done, this time may consider pessimistic lock. Pessimistic lock, which is to lock the data directly at the database level, similar to the use of select xxxxx in Oralce fromXxxxwhereXx=xx forupdate 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, it is recommended to pass the memory lock on the service side (lock 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, sub-table, using queue writing methods to reduce concurrent read and write.

MySQL handles high concurrency, preventing inventory from oversold