Операционная система MySQL: SELECT + INSERT

Вам нужна блокировка . Сделки действительно «не нужны».

Вы можете выбирать между «пессимистической блокировкой» и «оптимистичной блокировкой». Решение о том, какая из этих двух возможностей зависит от вас, и должна оцениваться в основном с учетом:

• уровень параллелизма, который у вас есть
• длительность операций атома-атома в базе данных
• сложность всей операции

Я рекомендую прочитать эти два, чтобы создать представление о вовлеченных вещах:

• Оптимистическая и пессимистическая блокировка
• Оптимистическая блокировка в MySQL (вот некоторые примеры, показывающие, как транзакция не требуется)

Пример для лучшего объяснения

Возможно, это не так элегантно, но это всего лишь пример, показывающий, как можно делать все без транзакции (и даже без ограничений UNIQUE). Что нужно сделать, так это использовать следующую объединенную статграмму INSERT + SELECT и после ее выполнения проверить количество затронутых строк. Если количество затронутых строк равно 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`, `end`, `uid`, `group`, `message`, `devices_id`) SELECT @startTime, @endTime, @uid, @group, @message, @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`, `end`, `uid`, `group`, `message`, `devices_id`) VALUES (1008141200, 1008141210, 11, 2, 'Dummy Record', 14) INSERT INTO `slot` (`start`, `end`, `uid`, `group`, `message`, `devices_id`) SELECT 1408141206, 1408141210, 11, 2, '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`, `end`, `uid`, `group`, `message`, `devices_id`) SELECT 1408141208, 1408141214, 11, 2, 'Hello', 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`, `end`, `uid`, `group`, `message`, `devices_id`) SELECT 1408141216, 1408141220, 11, 2, 'Hello', 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`; 

Это, безусловно, крайний пример Optimistic Locking, но очень эффективен в конце, потому что все выполняется только с одной инструкцией SQL и с низким взаимодействием (обмен данными) между сервером базы данных и php-кодом. Кроме того, практически нет «реальной» блокировки.

… или с пессимистическим блокированием

Один и тот же код может стать хорошей реализацией Pessimistc Locking, просто связанной с явными инструкциями по блокировке / разблокировке таблицы:

 LOCK TABLE slot WRITE, dummy READ; INSERT INTO `slot` (`start`, `end`, `uid`, `group`, `message`, `devices_id`) SELECT @startTime, @endTime, @uid, @group, @message, @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», поэтому не нужно пессимистично Lock, но в любом случае это может быть хорошим вариантом, если вы считаете, что это изменится в будущих версиях MySQL.

Я «Оптимистичен», что это не изменится 😉