使用SELECT FOR UPDATE和INSERT INTO

Question

使用SELECT FOR UPDATE和INSERT INTO

11

我正在制作一款抽奖游戏，有人建议我从MyISAM转换到InnoDB并开始使用FOR UPDATE，这样彩票（范围为1-16）就不会被重复售出。

现在我想知道，FOR UPDATE是如何工作的。

我在网上看到类似以下内容：

SELECT * FROM [table] WHERE [column] = [value] FOR UPDATE
UPDATE [table] SET [column] = [new_value]

然而，我的问题是关于以下内容的：它是否也适用于 INSERT？

我的数据库设计：

CREATE TABLE IF NOT EXISTS `Lottery` (
  `id` varchar(50) NOT NULL,
  `preferedLotteryId` varchar(50) NOT NULL,
  `winningTicketId` int(11) NOT NULL DEFAULT '-1',
  `createdOn` datetime NOT NULL DEFAULT '1001-00-00 00:00:00',
  `startedOn` datetime NOT NULL DEFAULT '1001-00-00 00:00:00',
  `finishedOn` datetime NOT NULL DEFAULT '1001-00-00 00:00:00',
  `active` tinyint(1) NOT NULL,
  `deliveredOn` datetime NOT NULL DEFAULT '1001-00-00 00:00:00',
  `preferedDeliverMethod` int(2) NOT NULL DEFAULT '-1',
  `deliveredMethod` int(2) NOT NULL DEFAULT '-1',
  `deliveredByAccountId` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `LotteryBid` (
  `bidId` varchar(50) NOT NULL,
  `accountId` varchar(50) NOT NULL,
  `auctionId` varchar(50) NOT NULL,
  `ticketId` int(50) NOT NULL,
  `datetime` datetime NOT NULL DEFAULT '1001-00-00 00:00:00',
  PRIMARY KEY (`bidId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

这意味着这个设计需要类似于 FOR UPDATE 的东西：

SELECT * FROM Lottery WHERE id = [id] FOR UPDATE
INSERT INTO LotteryBids SET [LotteryBids.values & Lottery.id]

或者

SELECT * FROM Lottery WHERE id = [id] FOR UPDATE
INSERT INTO LotteryBids, Lottery SET [LotteryBids.values] WHERE Lottery.id = [id]

然而，我不知道使用 FOR UPDATE 的方式是否可行。

我对这种大规模数据交互相当陌生，不知道该从哪里开始。

希望你们中的一些人能够帮助我。

顺颂时祺， Larssy1

我认为以下想法可能会转化为解决方案：

想法1：

一个用户正在购买彩票，其彩票ID为5。目前此彩票已售出3张，因此我想锁定此彩票的行数，直到我完成此插入操作为止。

想法2：

一个用户正在购买彩票，其彩票ID为5。目前此拍卖没有彩票与彩票ID 5相对应，因此我想预留一个类似拍卖ID和彩票ID的条目，并防止进一步添加，直到此查询完成。

使用的查询

选择一个彩票（包括附加信息）：

SELECT au.*, asp.* FROM Lottery au, LotteryPrefered asp
WHERE au.preferedAuctionId = asp.id AND au.id = '" . $_auctionId . "'

或（不包括额外信息）

SELECT au.* FROM Lottery au
WHERE au.id = '" . $_auctionId . "'

购买门票：

INSERT INTO LotteryBids (bidId, accountId, auctionId, ticketId, datetime)
VALUES ('" . $guid . "', '" . $_accountId . "', '" . $_auctionId . "',
  '" . $_ticketId . "', NOW())

- larssy1

我猜你的INSERT语句是伪代码，但是WHERE子句在INSERT语句中会起到什么作用？ - Marcus Adams

这基本上只是为了给 FOR UPDATE 发送一个小的“敲击”，表示已经发生了“更新”。但我不知道它实际上是如何工作的。 - larssy1

2个回答

0

您可以尝试使用一种带有备用表的锁/互斥体：

请参见http://blog.udby.com/archives/14（实现简单的数据库信号量）

在开始处理给定类型之前，您可以通过在锁定表中进行INSERT + DELETE来锁定“类型”。

- Bruno

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Marcus Adams · Accepted Answer

使用 UPDATE 的 SELECT ... FOR UPDATE

在使用 InnoDB（关闭自动提交）的事务时，SELECT ... FOR UPDATE 允许一个会话暂时锁定特定的记录（或多个记录），以便其他会话无法更新它。然后，在同一事务中，该会话可以实际执行对同一记录的 UPDATE 并提交或回滚事务。这将使您能够锁定记录，以便其他会话不能更新它，同时您可能需要执行一些其他业务逻辑。

这是通过加锁实现的。InnoDB 利用索引来锁定记录，因此锁定现有记录似乎很容易 - 只需锁定该记录的索引即可。

使用 INSERT 的 SELECT ... FOR UPDATE

然而，要使用 SELECT ... FOR UPDATE 与 INSERT，如何锁定尚不存在的记录的索引？如果您使用默认的隔离级别 REPEATABLE READ，则 InnoDB 还将利用间隙锁定。只要您知道要锁定的 id（甚至是 id 范围），那么 InnoDB 就可以锁定该间隙，从而使没有其他记录可以插入该间隙，直到我们完成为止。

如果您的 id 列是自动增量列，则使用 INSERT INTO 的 SELECT ... FOR UPDATE 会存在问题，因为您不知道新的 id 是什么，直到您插入它。但是，由于您知道要插入的 id，所以 SELECT ... FOR UPDATE 与 INSERT 将起作用。

示例

查看拍卖 12 中是否有可用的 ticketid 5：

SELECT * FROM LotteryBids
WHERE auctionid = 12 AND ticketid = 5
FOR UPDATE

如果没有返回任何行，则表示它不存在。请插入它：

INSERT INTO LotteryBids (bidId, accountId, auctionId, ticketId, datetime)
VALUES ('abcd-efgh-ijkl-mnop', 100, 12, 5, NOW())

保持简单

处理并发问题可能难以理解，我经常看到人们过于复杂化事情。我建议提出一个分开的问题，详细说明你想要实现什么，提供你的解决方案，并寻求建议。