MySQL solution to prevent inventory oversold and less than 0 in high concurrency

Background:

I last time to apply for campaign PHP backstage, because the project on the line after some period of time to claim too many people, resulting in some counters inventory is negative (<0), fortunately the concurrency is not particularly large, Only in a small number of counters and is generally-1 of the situation, not particularly serious consequences, but still have to reflect on their own fault.

This time again there is a new claim campaign, I looked at the last code logic:

Body:

"Select After Update"

2. Begintranse (Open transaction)
4. Try {
6. $result = $DBCA->query (' Select amount from s_store where PostID = 12345 ');
8. if (Result->amount > 0) {
10. $DBCA->query (' Update s_store set amount = amount-1 where PostID = 12345 ');
12. }
14. }Catch($e Exception) {
16. RollBack (rollback)
18. }
20. Commit (COMMIT TRANSACTION)

The above code is the first time I have written, it seems that 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, due to high concurrency, there are currently three users A, B, c three users into the transaction, this time will generate a shared lock , so at the time of select, these three users found the inventory quantity is >=0.

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 commits, the other user waits for execution, commits, generates a new version, and after execution, the inventory is definitely negative. But according to the above description, we modify the code will not appear overbought phenomenon, the code is as follows:

"Update First select"

2. Begintranse (Open transaction)
4. Try {
6. $DBCA->query (' Update s_store set amount = amount-1 where PostID = 12345 ');
8. $result = $DBCA->query (' Select amount from s_store where PostID = 12345 ');
10. if (Result->amount < 0) {
12. Throw New Exception (' insufficient inventory ');
14. }
16. }Catch($e Exception) {
18. RollBack (rollback)
20. }
22. Commit (COMMIT TRANSACTION)

In addition, a more concise approach:

"Update & Select Merge"

1. Begintranse (Open transaction)
2. Try {
3. $DBCA->query (' Update s_store set amount = amount-1 where amount>=1 and PostID = 12345 ');
4. }Catch($e Exception) {
5. RollBack (rollback)
6. }
7. Commit (COMMIT TRANSACTION)

======================================== Supplement =============================================

1, 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 sell in seconds, for example, if you want to sell 10 items, only 100 requests are allowed 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.

2, 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 locking mechanism, optimistic lock or pessimistic lock.

pessimistic lock (pessimistic lock), as the name implies, is very pessimistic, every time to take the data when they think others will change, so every time when the data are locked, so that people want to take this data will block until it gets lock. Traditional relational database in the use of a lot of this locking mechanism, such as row locks, table locks, read locks, write locks, etc., are in operation before the lock.

optimistic Lock (Optimistic Lock), as the name implies, is very optimistic, every time to take the data when they think others will not be modified, so will not be locked, but in the update will be judged in the period when others have to update this data, you can use the version number and other mechanisms. Optimistic locking is useful for multi-read application types, which can improve throughput, such as the fact that a database provides an optimistic lock similar to the write_condition mechanism.

Two kinds of locks have advantages and disadvantages, can not simply define which is better than which. optimistic lock is more suitable for data modification less, read more frequent scenes , even if there are a small number of conflicts, so that also saves a lot of lock overhead, and therefore improve the system's throughput. However, if conflicts occur frequently (in the case of more written data), the upper application is not constantly retry, which in turn degrades performance, and it is more appropriate to use pessimistic locking for this situation.

3, 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.

======================================= Supplement ==============================================

Resources:

"MySQL handles high concurrency and prevents inventory oversold " http://blog.csdn.net/caomiao2006/article/details/38568825

MySQL solution to prevent inventory oversold and less than 0 in high concurrency