在 PHP 中锁定 MySQL INNODB 行

Question

我有一个名为 meta 的表，其中有两列 name 和 value。

在由许多客户端同时调用的 php 脚本中，我这样做：-

$mysqli->multi_query("SELECT id FROM links WHERE id > (SELECT value FROM meta WHERE name='scan') LIMIT 1000;UPDATE meta SET value=value+1000 WHERE name='scan';");

或者这样：-

$mysqli->multi_query("SELECT id FROM links WHERE id > (SELECT value FROM meta WHERE name='scan' FOR UPDATE) LIMIT 1000;UPDATE meta SET value=value+1000 WHERE name='scan';");

不幸的是，这并不似乎可以正常工作，因为客户端最终会得到重复的 id。数据库负载很重，SELECT 需要几秒钟的时间。

Answer 1

$mysqli->autocommit(FALSE);
$mysqli->query("BEGIN;");
$mysqli->multi_query("SELECT id FROM links WHERE id > (SELECT value FROM meta WHERE name='scan' FOR UPDATE) LIMIT 1000;UPDATE meta SET value=value+1000 WHERE name='scan';");
$mysqli->commit();

这是一个复杂的问题；锁定和事务级别，但上面的神奇之处在于 BEGIN 语句。如果没有它，每个语句都会在自己的事务级别中运行，并且 FOR UPDATE 锁会过早解锁。

Answer 2

首先尝试下面的实验，然后尝试根据您的要求进行映射。

创建表：

CREATE TABLE `t1` (                       
  `id` int(11) NOT NULL AUTO_INCREMENT,                 
  `notid` int(11) DEFAULT NULL,                         
  PRIMARY KEY (`id`)                                    
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

然后创建：

create rowlocking.php

<?php
    require_once('connectvars.php');
    // Connect to the database 
    $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

    $query = "START TRANSACTION";
    $data = mysqli_query($dbc, $query);

    $query = "SELECT * FROM t1 WHERE id=5 FOR UPDATE";
    $data = mysqli_query($dbc, $query);

    if (mysqli_num_rows($data) != 0) {
        $row = mysqli_fetch_array($data);
        echo $row['id'];
        echo $row['notid'];
    }

    //$query = "COMMIT";
    //$data = mysqli_query($dbc, $query);
    sleep(10);
    echo "After 10 seconds";
?>

上面的脚本将访问 id=5 的行并锁定其他事务直到 10 秒休眠时间。

创建 rowlocking1.php

  <?php
    require_once('connectvars.php');
    // Connect to the database 
    $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

    $query = "START TRANSACTION";
    $data = mysqli_query($dbc, $query);

    $query = "SELECT * FROM t1 WHERE id=5 FOR UPDATE";
    $data = mysqli_query($dbc, $query);

    if (mysqli_num_rows($data) != 0) {
        $row = mysqli_fetch_array($data);
        echo $row['id'];
        echo $row['notid'];
    }
    //sleep(10);
    //$query = "COMMIT";
    //$data = mysqli_query($dbc, $query);
    //echo "After 10 seconds";
?>

上面的脚本尝试访问 id=5 的同一行。

现在，如果您运行脚本 rowlocking，并且在 10 秒睡眠时间内运行 rowlocking1，它将无法访问行 id=5，直到通过行锁定释放为止。一旦超过行锁定 10 秒的睡眠时间就能够访问行 id=5。

尝试将这个概念与您的脚本映射，您将获得 innoDB 行级锁定。如果您需要详细解释，请发表评论。

Answer 3

这是您要找的吗？

query("SELECT id FROM links WHERE id > (SELECT value FROM meta WHERE name='scan' LOCK IN SHARE MODE) LIMIT 1000 LOCK IN SHARE MODE;
UPDATE meta SET value=value+1000 WHERE name='scan';");

我不确定这是否是您正在寻找的，但如果是这样，请记住在必要时/必要时解锁表，并确保您使用的帐户具有 LOCK 权限。

有关 INNODB 读锁的 MySQL 文档