应用场景：PHP模拟购买,商品数量大于0才能购买
常见代码:
<?php
//连接数据库
$con=mysqli_connect("localhost","ihuohuo","927464cy","ihuohuo");
//查询商品数量是否大于0
$res=mysqli_fetch_assoc(mysqli_query($con,'SELECT total FROM shop WHERE id=1 LIMIT 1'));
if($res['total']>0){
mysqli_query($con,'UPDATE shop SET total=total-1 WHERE id=1');
}
unset($res);
mysqli_close($con);
?>以上代码在少量访问情况下不会出现问题，并发量较高会导致商品数量为负值
修正后代码:
<?php
$con=mysqli_connect("localhost","ihuohuo","927464cy","ihuohuo");
mysqli_query($con, 'BEGIN');
$res=mysqli_fetch_assoc(mysqli_query($con,'SELECT total FROM shop WHERE id=1 LIMIT 1 FOR UPDATE'));
if($res['total']>0){
mysqli_query($con,'UPDATE shop SET total=total-1 WHERE id=1 ');
}
mysqli_query($con, 'COMMIT');
unset($res);
mysqli_close($con);
?>解析:在mysql中通过 FOR UPDATE锁住本行记录，只有事务commit之后其他进程才能继续操作本条记录，一定要注意3点， FOR UPDATE锁表必须在事务begin和commit之间完成。
当然您可以通过文件锁完成，mysql行锁的开销还是比较大的，本站搜索文件锁。
