php – MySQL事务：SELECT INSERT

我正在构建Web应用程序 – 使用PHP和 mysql预订系统.系统将允许用户在某些设备上预留时间间隔(在该设备上工作的用户时间).

我将这些保留的时间间隔称为时隙.插槽存储在MysqL 数据库表中,如下所示：

CREATE TABLE IF NOT EXISTS `slot` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,`start` int(11) unsigned DEFAULT NULL,`end` int(11) unsigned DEFAULT NULL,`uid` int(11) unsigned DEFAULT NULL,`group` int(11) unsigned DEFAULT NULL,`message` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`devices_id` int(11) unsigned DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `start_2` (`start`),UNIQUE KEY `end_2` (`end`),KEY `index_foreignkey_slot_devices` (`devices_id`),KEY `start` (`start`),KEY `end` (`end`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=6997 ;

(这个表是由redbean orm自动创建的,我还没有优化它)

因此,当用户创建预订时,会在此表中插入一个新行.在专栏中
开始,结束我保留每个预订的开始和结束的unix时间戳.

另外要记住的是,应用程序允许不同的用户查看同一设备的不同时间表.例如：用户A有6分钟长的间隔,所以她可以看到空闲插槽(12：00 – 12:06)和免费插槽(12:06 – 12:12),但是用户B有4个小时间隔,所以他还有其他人看到插槽(12:04 – 12:08).每个用户或一组用户可以具有不同的间隔持续时间.因此,我必须确保当用户A和B都向这些插槽发送请求时,其中只有一个成功.这带给我交易和我的问题.

我是这样做的：
– 开始交易
– 选择当天的所有插槽
– 运行算法,检查所选保留时隙和请求时隙之间的时间冲突
– 如果没有冲突,则在槽表中插入新行,否则向用户发出信号错误
– 提交

现在你知道它同时运行时会发生什么.我是交易和MysqL的新手,但试图测试它,我有理由相信在这种情况下只是在交易中是不够的,但我不确定.

所以我的问题是：如何正确选择,检查冲突并在一个交易中存储预订.

谢谢

你需要的是锁定.确实“并非严格需要”交易.

您可以选择“悲观锁定”和“乐观锁定”.
关于这两种可能性中的哪一种取决于您并且必须进行评估的决定主要考虑：

>您拥有的并发级别
>数据库上必须原子操作的持续时间
>整个操作的复杂性

我将建议阅读这两个内容,以建立一个涉及的事情的想法：

> Optimistic vs. Pessimistic locking
> Optimistic locking in MySQL(这里有一些例子说明了如何不严格要求交易)

一个例子来解释得更好

这可能不是那么优雅,但它只是一个示例,显示了如何在没有事务的情况下完成所有操作(甚至没有UNIQUE约束).
需要做的是使用以下组合的INSERT SELECT statemet,并在执行后检查受影响的行数.
如果受影响的行数是1,那么它已经成功了(如果它是0)则发生了冲突而另一方已经获胜.

INSERT INTO `slot` (`start`,`end`,`uid`,`group`,`message`,`devices_id`)
SELECT @startTime,@endTime,@uid,@group,@message,@deviceId
FROM `slot`
WHERE NOT EXISTS (
    SELECT `id` FROM `slot`
    WHERE `start` <= @endTime AND `end` >= @startTime
    AND `devices_id` = @deviceId)
GROUP BY (1);

这是在没有事务和单个sql操作的情况下获得的乐观锁定的示例.

正如它所写的那样,它有一个问题,就是它必须在槽表中至少有一行才能工作(否则SELECT子句将始终返回一个空记录集,在这种情况下,如果没有,则不会插入任何内容evei碰撞.这有两种可能使它真正起作用：

>在表格中插入一个虚拟行,可能包含过去的日期
>重写所以主FROM子句引用任何至少有一行或更好的表创建一个小表(可能名为dummy),只有一列,只有一个记录,并重写如下(注意不再需要对于GROUP BY子句)

INSERT INTO `slot` (`start`,@deviceId
FROM `dummy`
WHERE NOT EXISTS (
    SELECT `id` FROM `slot`
    WHERE `start` <= @endTime AND `end` >= @startTime
    AND `devices_id` = @deviceId);

下面是一系列指令,如果您只是复制/粘贴显示实际的想法.我假设您将int字段上的日期/时间编码为一个数字,其中连接了日期和时间的数字.

INSERT INTO `slot` (`start`,`devices_id`)
VALUES (1008141200,1008141210,11,2,'Dummy Record',14)

INSERT INTO `slot` (`start`,`devices_id`)
SELECT 1408141206,1408141210,'Hello',14
FROM `slot`
WHERE NOT EXISTS (
    SELECT `id` FROM `slot`
    WHERE `start` <= 1408141210 AND `end` >= 1408141206
    AND `devices_id` = 14)
GROUP BY (1);

INSERT INTO `slot` (`start`,`devices_id`)
SELECT 1408141208,1408141214,14
FROM `slot`
WHERE NOT EXISTS (
    SELECT `id` FROM `slot`
    WHERE `start` <= 1408141214 AND `end` >= 1408141208
    AND `devices_id` = 14)
GROUP BY (1);

INSERT INTO `slot` (`start`,`devices_id`)
SELECT 1408141216,1408141220,14
FROM `slot`
WHERE NOT EXISTS (
    SELECT `id` FROM `slot`
    WHERE `start` <= 1408141220 AND `end` >= 1408141216
    AND `devices_id` = 14)
GROUP BY (1);

SELECT * FROM `slot`;

这显然是乐观锁定的极端示例,但最终效率非常高,因为所有操作都只使用一条sql指令,并且数据库服务器和PHP 代码之间的交互很少(数据交换).此外,实际上没有“真正的”锁定.

……或者悲观锁定

相同的代码可以成为一个很好的Pessimistc锁定实现,只需使用显式的表锁定/解锁指令：

LOCK TABLE slot WRITE,dummy READ;

INSERT INTO `slot` (`start`,@deviceId
FROM `dummy`
WHERE NOT EXISTS (
    SELECT `id` FROM `slot`
    WHERE `start` <= @endTime AND `end` >= @startTime
    AND `devices_id` = @deviceId);

UNLOCK TABLES;

当然在这种情况下(悲观锁定)可以分离SELECT和INSERT,并在其间执行一些PHP 代码.但是这段代码执行起来非常快(没有与PHP进行数据交换,没有中间的PHP 代码),因此悲观锁的持续时间最短.保持悲观锁定尽可能短是一个关键点,以避免应用程序的速度减慢.

无论如何,您需要检查受影响的记录返回值的数量,以便知道它是否成功,因为代码实际上是相同的,因此您以相同的方式获得成功/失败信息.

在这里http://dev.mysql.com/doc/refman/5.0/en/insert-select.html他们说“MysqL不允许INSERT … SELECT语句的并发插入”所以它不应该需要Pessimistic Lock但是无论如何这可能是一个很好的选择,如果你认为这将在MysqL的未来版本中发生变化.

我“乐观”,这不会改变;-)

php – MySQL事务：SELECT INSERT

猜你在找的PHP相关文章