php - Mysql:在值更新之前锁定表以进行读取

Question

在我的数据库(MySQL)中，我有一个表(MyISAM)，其中包含一个名为 number 的字段。该字段的每个值要么是 0，要么是正数。非零值必须是唯一的。最后一件事是，该字段的值是根据该表中另一个字段(称为 isNew)的值在我的 php 代码中生成的。代码如下。

$maxNumber = $db->selectField('select max(number)+1 m from confirmed where isNew = ?', array($isNew), 'm');
$db->query('update confirmed set number = ? where dataid = ?', array($maxNumber, $id));

第一行代码选择数字字段的最大值并递增它。第二行通过设置新生成的编号来更新记录。

这段代码正在被数百个客户端同时使用，所以我注意到有时会出现数字字段的重复。据我了解，当两个客户端几乎同时读取数字字段的值时就会发生这种情况，并且这一事实会导致重复。

我已阅读有关 SELECT ... FOR UPDATE 语句的信息，但我不太确定它是否适用于我的情况。

所以问题是我应该将 FOR UPDATE 附加到我的 SELECT 语句中吗？或者创建一个存储过程来完成这项工作？或者也许完全改变数字的生成方式？

Answer 1

这绝对是可以做到的。 MyISAM 不提供事务锁定，因此请忘记诸如 FOR UPDATE 之类的内容。示例代码中的两个语句之间肯定存在竞争条件。从你实现它的方式来看，这个就像会说话的驴子。令人惊奇的是它确实有效，而不是说它工作得很糟糕! :-)

我不明白你用这个 SQL 做什么:

 select max(number)+1 m from confirmed where isNew = ?

number 的值在整个表中是唯一的，还是仅在 isNew 具有特定值的集合内唯一？如果 number 的值在整个表中是唯一的，它会起作用吗？这将更容易创建、调试和维护。

您需要一种多连接安全的方式来获取号码。

你可以试试这个SQL。它将在一条语句中设置最大数量。

 UPDATE confirmed 
    SET number = (SELECT 1+ MAX(number) FROM confirmed WHERE isNew = ?)
  WHERE dataid = ?

这会表现得很糟糕。如果没有 (isNew, number) 上的复合索引，并且没有将这些列声明为 NOT NULL，它将执行非常非常糟糕。

如果您可以使用整个表格中唯一的数字，我建议您为自己创建一个序列设置，该设置将在您每次使用时返回一个唯一的数字。您需要使用一系列连续的 SQL 语句来执行此操作。事情是这样的。

首先，当您创建表时，请为自己创建一个要使用的表，称为sequence(或任何您喜欢的名称)。这是一个单列表。

CREATE TABLE sequence (
     sequence_id INT NOT NULL AUTO_INCREMENT,
     PRIMARY KEY (`sequence_id`)
) AUTO_INCREMENT = 990000

这将使序列表从 990,000 开始发出数字。

其次，当您需要在应用程序中使用唯一编号时，请执行以下操作。

INSERT INTO sequence () VALUES ();
DELETE FROM sequence WHERE sequence_id < LAST_INSERT_ID();
UPDATE confirmed 
    SET number = LAST_INSERT_ID()
  WHERE dataid = ?

这是怎么回事？ MySQL 函数LAST_INSERT_ID() 返回最近自动增量生成的 ID 号的值。由于您在该 sequence 表中插入了一行，因此它会返回生成的 ID 号。 DELETE FROM 命令可防止该表占用磁盘空间；我们不关心旧的 ID 号。

LAST_INSERT_ID() 是连接安全的。如果与数据库的不同连接上的软件使用它，它们都会获得自己的值。

如果您需要知道最后插入的 ID 号，您可以发出以下 SQL:

SELECT LAST_INSERT_ID() AS sequence_id

你会得到它返回。

如果您使用 Oracle 或 PostgreSQL，而不是 MySQL，您会发现它们提供的 SEQUENCE 对象基本上可以完成此操作。

这是另一个类似问题的答案。

Fastest way to generate 11,000,000 unique ids