模拟一个生成流水号的操作

准备工作

z_serial 表 num 字段初始值为 0 ，记录当前流水号。

image.png

z_data 表，用于记录生成的编号，存入code字段

image.png

生成流水号功能

$this->Db->transtart();
try{
    //获取当前序号
    $s = $this->Db->getOneRow('select * from z_serial;');
    //当前序号+1
    $no = $s['num'] + 1;
    //随机sleep 0-1秒，模拟其他业务逻辑处理的耗时，便于并发测试
    $t = random_int(1,100)/100;
    sleep($t); 
    //生成的序号写入z_data
    $this->Db->exec("insert into z_data(code) values ($no);");
    //z_serial表里的当前序号+1
    $this->Db->exec("update z_serial set num = num + 1 where id = 1;");
    //提交事务
    $this->Db->transcommit();
}catch (Exception $e){
    $this->Db->transrollback();
}
$this->echoSuccess();

暴露接口为 http://simple_admin/Home/test

并发模拟

使用 apache 自带的 ab 工具

c:\wamp64\bin\apache\apache2.4.23\bin
λ ab -n 1000 -c 100 http://simple.test/Home/test
This is ApacheBench, Version 2.3 <$Revision: 1748469 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking simple.test (be patient)
Completed 100 requests
Completed 200 requests
Completed 300 requests
Completed 400 requests
Completed 500 requests
Completed 600 requests
Completed 700 requests
Completed 800 requests
Completed 900 requests
Completed 1000 requests
Finished 1000 requests

-n 1000 表示发起共1000次请求， -c 100 表示并发量为100

结果

1. z_serial 表中，num 值为1000，正确。印证了事务的隔离性和持久性。

2. z_data 表中，code存在重复值，出现了并发问题，同时印证了事务的一致性（即是可重复读的）

   
   image.png

解决方案

如果熟悉了 Mysql 的事务/锁 的机制与原理，解决这个问题其实很简单

$this->Db->transtart();
try {
    $this->Db->exec("update z_serial set num = num + 1 where id = 1;");
    $s = $this->Db->getOneRow('select * from z_serial;');
    $no = $s['num'];
    $t = random_int(1, 100) / 100;
    sleep($t); 
    $this->Db->exec("insert into z_data(code) values ($no);");
    $this->Db->transcommit();
} catch (Exception $e) {
    $this->Db->transrollback();
}
$this->echoSuccess();