如何在php中锁定mysql表 [英] How to lock mysql tables in php

问题描述

如何在php中锁定mysql表?我目前有此代码:

How do I lock mysql tables in php? I currently have this code:

$db->query("LOCK TABLES tbl_othercharge WRITE");
for($x=0;$x<=500; $x++){
    $id = get_max();
    $db->query("INSERT INTO tbl_othercharge SET tblocID = '$id', assessmentID='lock1'");
}

$db->query("UNLOCK TABLES");

这是get_max()函数，如果上面的脚本同时执行，显然会失败.

Here's the get_max() function, which obviously will fail if the script above executes simultaneously.

 <?php
    function get_max(){
        global $db;
        $max = $db->get_var("SELECT MAX(tblocNumber) FROM tbl_othercharge");
        if($max == null){
            $max = 1;
        }else if($max >= 1){
            $max = $max + 1;
        }
        return 'OC'.$max;
    }
    ?>

我试图通过在2个浏览器上执行相同的脚本来测试是否仍然存在并发问题. 上面的脚本插入了400多个记录，而不是999个记录.在表中插入内容时如何正确锁定表.

I'm trying to test if there are still concurrency problems by executing the same script on 2 browsers. The script above inserts 400+ records instead of 999 records. How do I properly lock the table while I'm inserting something into it.

我想锁定表以防止发生这种情况:

I want to lock the table to prevent something like this to happen:

如您所见，在其前缀为"OC"的字段上应有一个等于自动递增主键的数字.

As you can see the field with the prefix 'OC' on it should have a number which is equal to the auto-increment primary key.

推荐答案

唯一可靠的解决方案是使用虚拟值进行插入，获取最后的插入id，并将行更新为正确的值.

The only reliable solution is to do an insert with a dummy value, getting the last insert id, and updating the row to the correct value.

mysql_query("INSERT INTO table (field) VALUES (dummy);");
$id = mysql_last_insert_id();
mysql_query("UPDATE table SET field='OC{$id}' WHERE id={$id} LIMIT 1;");

这篇关于如何在php中锁定mysql表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！