MYSQL SELECT....FOR UPDATE

MYSQL SELECT...FOR UPDATE 的理解
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);

注意

顺带一提的是,
当选中某一个行的时候,如果是通过主键id选中的。那么这个时候是行级锁。
主键+普通索引/唯一索引/无索引行级锁
唯一索引+普通索引/无索引行级锁
普通索引+无索引行级锁
其他的行还是可以直接insert 或者update的。如果是通过其他的方式选中行,或者选中的条件不明确包含主键。这个时候会锁表。其他的事务对该表的任意一行记录都无法进行插入或者更新操作。只能读取。
如果明确指定主键，若查无此数据，则无lock
SELECT * FROM products WHERE id='-1' FOR UPDATE;

附上apache的ab测试并发的方法

ab -n 100 -c 100  127.0.0.1/test.php
//意思是同时有100人访问

最后编辑于：2019.01.29 17:46:17