PHP_MySQL高并发加锁事务处理

1、背景：

现在有这样的需求，插入数据时，判断test表有无username为‘mraz’的数据，无则插入，有则提示“已插入”，目的就是想只插入一条username为‘mraz’的记录。

2、一般程序逻辑如下：

$conn = mysqli_connect('127.0.0.1', 'root', '111111') or die(mysqli_error());  
  
mysqli_select_db($conn, 'mraz');  
  
$rs = mysqli_query($conn, 'SELECT count(*) as total FROM test WHERE username = "mraz" ');  
$row = mysqli_fetch_array($rs);  
if($row['total']>0){  
    exit('exist');  
}  
  
mysqli_query($conn, "insert into test(username) values ('mraz')");  
var_dump('error:'.mysqli_errno($conn));  
$insert_id = mysqli_insert_id($conn);  
  
echo 'insert_id：'.$insert_id.'<br>';  
  
mysqli_free_result($rs);  
mysqli_close($conn);

3、一般少量请求的时候，程序逻辑不会有问题。但是一旦高并发请求执行的话，程序并没有按预期执行，会插入多条username为‘mraz’的记录。

4、解决方案：利用mysql的FOR UPDATE 语句和事务的隔离性。注意的是FOR UPDATE仅适用于InnoDB，且必须在事务(BEGIN/COMMIT)中才能生效。

调整代码后如下：

$conn = mysqli_connect('127.0.0.1', 'root', '111111') or die(mysqli_error());  
  
mysqli_select_db($conn, 'mraz');  
mysqli_query($conn, 'BEGIN');  
$rs = mysqli_query($conn, 'SELECT count(*) as total FROM test WHERE username = "mraz" FOR UPDATE');  
$row = mysqli_fetch_array($rs);  
if($row['total']>0){  
    exit('exist');  
}  
  
mysqli_query($conn, "insert into test(username) values ('mraz')");  
var_dump('error:'.mysqli_errno($conn));  
$insert_id = mysqli_insert_id($conn);  
  
mysqli_query($conn, 'COMMIT');  
echo 'insert_id：'.$insert_id.'<br>';  
  
mysqli_free_result($rs);  
mysqli_close($conn);

5、再利用php的curl模拟高并发请求该php脚本，查看数据库会只有一条username为‘mraz’的记录。达到程序执行的预期结果

laravel 框架

<?php

namespace App\Http\Controllers;
use DB;
use Illuminate\Http\Request;

class HomeController extends Controller
{
    //测试锁
    public function test()
    {
       $username = '0233';
        // $username = '1001';
         DB::connection()->enableQueryLog(); // 开启查询日志
        $user = DB::table('user')->lockForUpdate()->where('username',$username)->first();
        $queries = DB::getQueryLog(); // 获取查询日志 
       print_r($user);
        print_r($queries);
       exit;
    }

    //模拟并发写入数据库
    public function save(Request $request)
    {
/***

select * from `user` where username in (select username from `user` group by username having count(username) >1 ) //查询重复用户的次数
SELECT * FROM `laravel`.`user` WHERE username = '0001';
INSERT INTO `laravel`.`user` ( `username`) VALUES ('0001');
UPDATE `laravel`.`user` SET username = '0001' WHERE id = '683';
//查询比写入和更新都要快
[SQL]SELECT * FROM `laravel`.`user` WHERE username = '0001';
受影响的行: 0
时间: 0.001s

[SQL]
INSERT INTO `laravel`.`user` ( `username`) VALUES ('0001');
受影响的行: 1
时间: 0.033s

[SQL]
UPDATE `laravel`.`user` SET username = '0001' WHERE id = '3270';
受影响的行: 0
时间: 0.035s

**/

        $username = $request->input('username');
       
         DB::beginTransaction();
       DB::connection()->enableQueryLog(); // 开启查询日志  
        /*$username = '1001';
        $user = DB::table('user')->lockForUpdate()->where('username',$username)->first();*/
        $username = ['1001','0233'];
       $user = DB::table('user')->lockForUpdate()->whereIn('username',$username)->first();
        sleep(30);
       print_r($user);
        $queries = DB::getQueryLog(); // 获取查询日志 
       print_r($queries);
        exit;
         file_put_contents('a.txt', '----begin'.$username.'----'.microtime()."\t\n",FILE_APPEND);
        if(!$username)
        {
            echo '用户名不能为空';exit;
        }

       /* if(rand(0,1))
            usleep(100000);//延迟300毫秒*/
        //拖慢相同用户名第一次往下处理的速度 方便后续的请求跟上来 加大重复用户名入库的概率
       /* if(!DB::table('all_user')->where('username',$username)->first())
        {
            DB::table('all_user')->insertGetId(['username'=>$username]);
        }*/

        $inId = DB::table('all_user')->insertGetId(['username'=>$username]);
        $loop = 0;
        while (1) 
        {
            //只等一次
            //有重复数据 或者循环达到10次 无限循环的结束条件
             if(DB::table('all_user')->where('username',$username)->where('id', '<>', $inId)->first() || $loop>100000)
             {
                break;
             }
             usleep(10000);
             $loop++;
        }
       

        DB::beginTransaction();
       DB::connection()->enableQueryLog(); // 开启查询日志  
        $user = DB::table('user')->lockForUpdate()->where('username',$username)->first();//for update
       $queries = DB::getQueryLog(); // 获取查询日志 
       print_r($queries);
       // file_put_contents('a.txt', '----begin'.$username.'----'.microtime()."select\t\n",FILE_APPEND);
        if($user)
        {
            echo '用户重复';exit;
        }
        $inId = DB::table('user')->insertGetId(['username'=>$username]);
       // file_put_contents('a.txt', '----begin'.$username.'----'.microtime()."insert\t\n",FILE_APPEND);
        if(!$inId)
        {
            DB::rollback();
            echo '写入用户失败';exit;
        }
        DB::commit();
        echo '用户ID'.$inId;
        exit;

        /*
        
        web 开发工具
        Mozilla/5.0 (iPhone; CPU iPhone OS 9_1 like Mac OS X) AppleWebKit/601.1.46 (KHTML, like Gecko) Version/9.0 Mobile/13B143 Safari/601.1 wechatdevtools/0.7.0 MicroMessenger/6.3.9 Language/zh_CN webview/0
        微信浏览网页
        Mozilla/5.0 (Linux; Android 5.1; m3 Build/LMY47I; wv) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/57.0.2987.132 MQQBrowser/6.2 TBS/043909 Mobile Safari/537.36 MicroMessenger/6.6.3.1260(0x26060339) NetType/WIFI Language/zh_CN
         */
        
        //var_dump($_SERVER['HTTP_USER_AGENT']);
        
        if (strpos($_SERVER['HTTP_USER_AGENT'], 'MicroMessenger') !== false && strpos($_SERVER['HTTP_USER_AGENT'], 'webview')) { 
               echo '禁止通过微信web工具访问';exit;
        }

        return view('welcome');
    }

    //模拟高并发写数据库
    public function create()
    {
        //echo 1;exit;
        file_put_contents('a.txt', '');//清空请求记录
        //清空旧数据
        DB::table('all_user')->delete();
        DB::table('user')->delete();
        set_time_limit(0);
        $chars = '0123';
        $url = 'lumen.103.waitkk.com/home/save?username=';
        $urls = [];
        $str = '';
        $count = 0;
        while ($count < 50) {
            //$urls[] = $chars[rand(0,3)].$chars[rand(0,3)].$chars[rand(0,3)].$chars[rand(0,3)].$chars[rand(0,3)];
            $str = $chars[rand(0,3)].$chars[rand(0,3)].$chars[rand(0,3)].$chars[rand(0,3)];
            $_url =  $url.$str;
            if(in_array($_url, $urls))
            {
                continue;
            }
            
            $urls[] = $_url;
            $urls[] = $_url;
            $count++;
        }
       // echo count($urls);exit;
       $this->Concurrent($urls);
       exit;

    }



      //模拟并发更新数据库
    public function update(Request $request)
    {
         $username = $request->input('username');
         if(!$username)
         {
            echo '用户名不能为空';exit;
         }
         DB::beginTransaction();
         $userInfo = DB::table('user')->lockForUpdate()->where('username',$username)->first();
         if(!$userInfo)
         {
            DB::rollback();
            echo '用户不存在';exit;
         }
         DB::table('user')->where('id',$userInfo->id)->update(['count'=>$userInfo->count+1]);
         DB::commit();
         echo '更新用户ID'.$userInfo->id.'完成';exit;

/*DB::transaction(function () {
    DB::table('users')->update(['votes' => 1]);

    DB::table('posts')->delete();
}, 5);*/
    }

    public function edit()
    {
         $url = 'lumen.103.waitkk.com/home/update?username=';
         $urls = [];
        $data = DB::table('user')->get()->toArray();
        if(!$data)
        {
            echo '数据为空';exit;
        }
       // print_r($data);
        foreach ($data as $k => $v) 
        {
            $urls[] = $url.$v->username;
            $urls[] = $url.$v->username;
        }
        $this->Concurrent($urls);
        exit;
    }


    private function Concurrent(array $urls,array $data=[])
    {
        asort($urls);
        /*echo count($username);
         print_r($username);exit;*/
        $mh = curl_multi_init();  
          
        foreach($urls as $k=>$url)  
        {  
                $conn[$k] = curl_init();  
                curl_setopt ( $conn[$k] , CURLOPT_URL, $url);    
                curl_setopt ( $conn[$k] , CURLOPT_HEADER , 0 ) ;  
                curl_setopt ( $conn[$k], CURLOPT_CONNECTTIMEOUT,60);     
                curl_setopt ( $conn[$k], CURLOPT_RETURNTRANSFER,true);   
                curl_multi_add_handle ($mh, $conn[$k]);   
        }  
          
        do {     
                curl_multi_exec($mh,$active);     
        } while ($active);  
          
        foreach($urls as $k=>$v)  
        {  
                $res = curl_multi_getcontent($conn[$k]);  
          
                var_dump($res);
                echo "<br />";  
        }  
          
        foreach($urls as $k=>$v)  
        {  
                curl_multi_remove_handle($mh,$conn[$k]);     
                curl_close($conn[$k]);   
        }  
          
        curl_multi_close($mh);  


                /*$connomains = array(
                'http://localhost/demo/client_1.php',
                'http://localhost/demo/client_2.php',
                'http://localhost/demo/client_1.php',
                'http://localhost/demo/client_2.php',
                );
                $mh = curl_multi_init();
                foreach ($connomains as $i => $url) {
                     $conn[$i]=curl_init($url);
                      curl_setopt($conn[$i],CURLOPT_RETURNTRANSFER,1);
                      curl_multi_add_handle ($mh,$conn[$i]);
                }
                do { 
                    $n=curl_multi_exec($mh,$active); 
                } while ($active);
                foreach ($connomains as $i => $url) {
                      $res[$i]=curl_multi_getcontent($conn[$i]);
                      curl_close($conn[$i]);
                }
                print_r($res);*/
    }

}

codeIngiter

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Welcome extends CI_Controller {
    function __construct() 
    { 
        parent::__construct(); 
        $this->load->database(); 
    } 
    /**
     * Index Page for this controller.
     *
     * Maps to the following URL
     *         http://example.com/index.php/welcome
     *    - or -
     *         http://example.com/index.php/welcome/index
     *    - or -
     * Since this controller is set as the default controller in
     * config/routes.php, it's displayed at http://example.com/
     *
     * So any other public methods not prefixed with an underscore will
     * map to /index.php/welcome/<method_name>
     * @see https://codeigniter.com/user_guide/general/urls.html
     */
    public function index()
    {
        $this->load->view('welcome_message');
    }
     //模拟并发更新数据库
    public function update()
    {
         $username = $this->input->get('username');
       // $username = '1001 or id <> 0';
         if(!$username)
         {
            echo '用户名不能为空';exit;
         }
         $this->db->trans_begin();

         //$userInfo = $this->db->where('username',$username)->get('user')->row();
         $sql = sprintf("select * from `user` where username = '%s' FOR UPDATE",$username);//原生sql防止注入
         $userInfo = $this->db->query($sql)->row();
        /* echo $this->db->last_query();
         print_r($userInfo);exit;*/
         if(!$userInfo)
         {
           $this->db->trans_rollback();
            echo '用户不存在';exit;
         }
         $this->db->where('id',$userInfo->id)->update('user', ['count'=>$userInfo->count+1]);
         $this->db->trans_commit();
         echo '更新用户ID'.$userInfo->id.'完成';exit;

    }

    public function edit()
    {
         $url = 'ci.103.waitkk.com/welcome/update?username=';
         $urls = [];
         $this->db->update('user', ['count'=>0]);
        $data =  $this->db->get('user')->result();
       // print_r($data);exit;
        if(!$data)
        {
            echo '数据为空';exit;
        }
       // print_r($data);
        foreach ($data as $k => $v) 
        {
            $urls[] = $url.$v->username;
            $urls[] = $url.$v->username;
        }
        $this->Concurrent($urls);
        exit;
    }
    private function Concurrent(array $urls,array $data=[])
    {
        asort($urls);
        /*echo count($username);
         print_r($username);exit;*/
        $mh = curl_multi_init();  
          
        foreach($urls as $k=>$url)  
        {  
                $conn[$k] = curl_init();  
                curl_setopt ( $conn[$k] , CURLOPT_URL, $url);    
                curl_setopt ( $conn[$k] , CURLOPT_HEADER , 0 ) ;  
                curl_setopt ( $conn[$k], CURLOPT_CONNECTTIMEOUT,60);     
                curl_setopt ( $conn[$k], CURLOPT_RETURNTRANSFER,true);   
                curl_multi_add_handle ($mh, $conn[$k]);   
        }  
          
        do {     
                curl_multi_exec($mh,$active);     
        } while ($active);  
          
        foreach($urls as $k=>$v)  
        {  
                $res = curl_multi_getcontent($conn[$k]);  
          
                var_dump($res);
                echo "<br />";  
        }  
          
        foreach($urls as $k=>$v)  
        {  
                curl_multi_remove_handle($mh,$conn[$k]);     
                curl_close($conn[$k]);   
        }  
          
        curl_multi_close($mh);  


                /*$connomains = array(
                'http://localhost/demo/client_1.php',
                'http://localhost/demo/client_2.php',
                'http://localhost/demo/client_1.php',
                'http://localhost/demo/client_2.php',
                );
                $mh = curl_multi_init();
                foreach ($connomains as $i => $url) {
                     $conn[$i]=curl_init($url);
                      curl_setopt($conn[$i],CURLOPT_RETURNTRANSFER,1);
                      curl_multi_add_handle ($mh,$conn[$i]);
                }
                do { 
                    $n=curl_multi_exec($mh,$active); 
                } while ($active);
                foreach ($connomains as $i => $url) {
                      $res[$i]=curl_multi_getcontent($conn[$i]);
                      curl_close($conn[$i]);
                }
                print_r($res);*/
    }
}

posted on 2018-03-09 18:14 小乔流水人家阅读(381) 评论(0) 收藏举报

刷新页面返回顶部

PHP_MySQL高并发加锁事务处理

导航

公告