Can a mysql-PHP high-concurrency single-order transaction be solved? -Php Tutorial

A small example of placing an order (when the code is added, no transaction is added): {code ...} inventory default has 100: log table: Apacheab tool concurrency: ab-n1200-c1200-wlocalhostqueueindex.php & amp; gt; D: 1.html results out of concurrency issues (very...

A small example of placing an order (when the code is run and the transaction is not added ):

Class IndexController extends Controller {public function index () {$ stock = M ('stock'); $ log = M ('log '); $ condition ['id'] = 1; if ($ stock-> where ($ condition)-> getField ('stock _ left')> 0) {$ stock-> where ($ condition)-> setDec ("stock_left"); $ data ['OP'] = 1; $ log-> add ($ data );} else {echo "no more ";}}}

The default inventory quantity is 100:

Log table:

Apache AB tool concurrency:
AB-n 1200-c 1200-w http: // localhost/queue/index. php> D:/1.html

The result shows a concurrency problem (naturally ):

After the transaction control is added:

Class IndexController extends Controller {public function index () {$ stock = M ('stock'); $ log = M ('log '); $ condition ['id'] = 1; M ()-> startTrans (); if ($ stock-> where ($ condition) -> getField ('stock _ left')> 0) {$ res1 = $ stock-> where ($ condition)-> setDec ("stock_left "); $ data ['OP'] = 1; $ res2 = $ log-> add ($ data); if ($ res1! = False & $ res2) {M ()-> commit ();} else {M ()-> rollback ();}} else {echo "no more ";}}}

Test the concurrency:
AB-n 1200-c 1200-w http: // localhost/queue/index. php> D:/1.html
As a result, the output result is okay (but does it solve the concurrency problem ?) :

Many people have said that they use the Redis queue. I am still not quite clear about the specific implementation. please help me.

Reply content:

A small example of placing an order (when the code is run and the transaction is not added ):

Class IndexController extends Controller {public function index () {$ stock = M ('stock'); $ log = M ('log '); $ condition ['id'] = 1; if ($ stock-> where ($ condition)-> getField ('stock _ left')> 0) {$ stock-> where ($ condition)-> setDec ("stock_left"); $ data ['OP'] = 1; $ log-> add ($ data );} else {echo "no more ";}}}

The default inventory quantity is 100:

Log table:

Apache AB tool concurrency:
AB-n 1200-c 1200-w http: // localhost/queue/index. php> D:/1.html

The result shows a concurrency problem (naturally ):

After the transaction control is added:

Class IndexController extends Controller {public function index () {$ stock = M ('stock'); $ log = M ('log '); $ condition ['id'] = 1; M ()-> startTrans (); if ($ stock-> where ($ condition) -> getField ('stock _ left')> 0) {$ res1 = $ stock-> where ($ condition)-> setDec ("stock_left "); $ data ['OP'] = 1; $ res2 = $ log-> add ($ data); if ($ res1! = False & $ res2) {M ()-> commit ();} else {M ()-> rollback ();}} else {echo "no more ";}}}

Test the concurrency:
AB-n 1200-c 1200-w http: // localhost/queue/index. php> D:/1.html
As a result, the output result is okay (but does it solve the concurrency problem ?) :

Many people have said that they use the Redis queue. I am still not quite clear about the specific implementation. please help me.

Transactions have nothing to do with concurrency. You can only use transactions to ensure that the logic is successful or failed, but cannot control the logic of your program during concurrency.
To control concurrency, you still need to lock it. for example, mysql mentioned above implements optimistic locks.
For example, upadte table set a = a-1 where a = 5; only when a = 5, this update will actually modify the data, this method ensures that if the data version to be modified is different from the data version you expected, you do not need to perform any operations. you can determine whether the data version has been modified by affecting the number of rows, and then proceed with or exit the following operations.

The exclusive lock is also used. if a single machine can directly use flock to block the lock.
Or redis and memcache to implement the lock.
We recommend that you use redis and memcache for lock operations when there are many requests, or consider using message queues to process concurrency.

If you use 4000 concurrent tests, problems still occur:

Use transactions. do this when PS is using transactions.

SELECT * from table where a = x limit 1 for update;

Always use FOR UPDATE;

If you do not use for update, a negative number may occur.

You can also lock the data table (InnoDBThe engine uses row locks;MyISAMThe engine uses table locks)

The core is the database. you need to implement a pessimistic lock or optimistic lock.
On the surface, you have solved the problem. In fact, in special cases, the sales volume will still appear.

Transactions can be solved and committed in a unified manner, but the lock is needed to solve this problem.
If no lock is required, a queue is used, that is, queue insertion, and asynchronous synchronization. this is the safest way.

Refer to my answer.

Https://segmentfault.com/q/1010000005105041/a-1020000005106490

The queue method can be: a commodity inventory (or all commodities can run together with an order queue) has a script running in the background, and then the request is serialized. This solution will be promoted because of controllability. we can control the processing frequency based on system requirements.

The cache method is to regularly update the commodity inventory to the cache, and use the cached atomic read/write to perform the auto-reduction operation on the inventory in the cache. if the auto-reduction is greater than zero, you can leave the following order process (the order process still requires a complete transaction lock to ensure consistency). The purpose of the cache is to avoid traffic impact and only valid traffic enters the db.

Replace $ condition ['id'] = 1; in the first example with the equivalent condition of "id = 1 and stock_left> 0" to solve the problem and no transaction is required, transactions do not play any role at this time. The subsequent logic also needs to be adjusted, because setDec is certainly successful, but not necessarily some records have been modified, so the pseudo code example:

$ SQL = "update table set num = num-1 where num> 0"; $ updatedRows = get_updated_rows ($ db-> exec ($ SQL )); if ($ updatedRows> 0) {// success} else {// failure}