php – MySQL事务：SELECT INSERT

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

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

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

> 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的未来版本中发生变化.

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