mysql - Can high concurrency orders in PHP be solved using transactions?

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

<code>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 "已经没剩余了";
        }
    }
}</code>

The default inventory is 100:

Log table:

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

The result is a concurrency problem (very natural):

Then after adding transaction control:

<code>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 "已经没剩余了";
        }
    }
}</code>

Test the concurrency again:
ab -n 1200 -c 1200 -w http://localhost/queue/index.php >> D:/1.html
What about the results, the output results No problem (but does it really solve the concurrency problem?):

Many people have said to use Redis queue to do it. I am still not sure about the specific implementation. Please help

Reply content:

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

<code>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 "已经没剩余了";
        }
    }
}</code>

The default inventory is 100:

Log table:

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

The result is a concurrency problem (very natural):

Then after adding transaction control:

<code>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 "已经没剩余了";
        }
    }
}</code>

Test the concurrency again:
ab -n 1200 -c 1200 -w http://localhost/queue/index.php >> D:/1.html
What about the results, the output results No problem (but does it really solve the concurrency problem?):

Many people have said to use Redis queue to do it. I am still not sure about the specific implementation. Please help

Transactions have nothing to do with concurrency. Your use of transactions can only guarantee the success or failure of this piece of logic, but cannot guarantee that you can control your program logic during concurrency.
Locks are still needed to control concurrency. For example, mysql implements optimistic locking as mentioned above.
For example, upadte table set a = a - 1 where a = 5; only when a=5, this update will actually modify the data. Using this method can ensure that the data version to be modified is as you expected. is different, no operation will be performed, and the number of affected rows will be used to determine whether there are modifications, and then continue the following operations or exit.

There is also the use of exclusive locks. If a single machine can directly use flock to achieve the purpose of blocking locks.
Or redis and memcache to implement locks.
When there are many requests, it is recommended to use redis and memcache for lock operations, or consider using message queues to handle concurrency.

I tested it with 4000 concurrency, but problems still occur:

Use transactions. When PS uses transactions, please be sure to do this

SELECT * FROM TABLE WHERE A=X LIMIT 1 FOR UPDATE;

务必使用FOR UPDATE;

If you don’t use for update, negative numbers will definitely appear.

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

The core is still in the database. You need to make a pessimistic lock or optimistic lock.
On the surface, it seems that you have solved the problem, but in fact, oversold may still occur under special circumstances.

Transactions can be solved by submitting them uniformly, but generally we have to rely on locks to solve this problem.
If you don’t use locks, you have to use queues, which are queued insertions, and change asynchronous to synchronous. This is the safest way.

You can refer to my answer.

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

队列的方法可以是，一个商品库存（也可以所有商品一起，跑一个下单队列）在后台有一个脚本在跑，然后把请求变成串行。这个方案会被推崇是因为可控性，我们可以根据系统需要控制处理的频率。

缓存的做法是，定时将商品库存更新到缓存里面去，利用缓存的原子读写，对缓存里的库存进行自减操作，如果自减后大于零，就可以走后面的下单流程（下单流程仍然需要完整的事务加锁来保证一致性），缓存的目的在于，避免流量冲击，只有有效流量进入db。

把第一个例子中的$condition['id'] = 1;换成"id=1 and stock_left > 0"的等效条件就解决问题了，不需要事务，事务在这个时候起不到什么作用。后面的逻辑当然也要相应调整，因为setDec肯定成功，但是不一定真有记录被修改了，所以伪码示例：

<code>$sql = "update table set num = num - 1 where num > 0";
$updatedRows = get_updated_rows($db->exec($sql));
if ($updatedRows > 0) {
    //成功
} else {
    //失败
}</code>