Как атомарно перемещать строки из одной таблицы в другую?

Я собираю показания с нескольких тысяч датчиков и сохраняю их в базе данных MySQL. Есть несколько сотен вставок в секунду. Чтобы улучшить производительность вставки, я сначала сохраняю значения в буферной таблице MEMORY. Раз в минуту я запускаю хранимую процедуру, которая перемещает вставленные строки из буфера памяти в постоянную таблицу.

В основном я хотел бы сделать следующее в моей хранимой процедуре, чтобы переместить строки из временного буфера:

INSERT INTO data SELECT * FROM data_buffer;
DELETE FROM data_buffer;

К сожалению, предыдущий вариант неприменим, потому что процессы сбора данных вставляют дополнительные строки в «data_buffer» между INSERT и DELETE выше. Таким образом, эти строки будут удалены без вставки в таблицу «данные».

Как я могу сделать операцию атомарной или сделать оператор DELETE для удаления только тех строк, которые были выбраны и вставлены в предыдущем операторе?

Я бы предпочел сделать это стандартным способом, который, если возможно, работает на разных механизмах баз данных.

Я бы предпочел не добавлять какие-либо дополнительные столбцы «id» из-за накладных расходов на производительность и требований к хранилищу.

Я бы хотел, чтобы в стандартном SQL был оператор SELECT_AND_DELETE или MOVE или что-то подобное...


mysql sql atomic
person snap    schedule 03.08.2011    source источник
comment
Не могли бы вы предоставить структуру таблицы data_buffer?   -  person Darius Kucinskas    schedule 03.08.2011
comment
Конечно: CREATE TABLE data_buffer ( time int(11) NOT NULL, sensor smallint(6) NOT NULL, value float NOT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1;   -  person snap    schedule 03.08.2011
comment
На самом деле у меня есть одно конкретное решение для MySQL, но, похоже, мне не разрешено публиковать его в разделе ответов до истечения 8 часов. Я действительно ненавижу эти ограничения в stackoverflow...   -  person snap    schedule 03.08.2011
comment
Как насчет идентификатора строки, получить максимальное значение перед вставкой, а затем удалить записи ‹= max(id)   -  person niktrs    schedule 03.08.2011
comment
@niktrs, это умная идея! Будут некоторые дополнительные накладные расходы из-за ненужного в противном случае столбца идентификатора, но, с другой стороны, это должно быть возможно на любом механизме БД таким же образом (кроме синтаксиса определения столбца AUTO_INCREMENT). Жаль, что вы не разместили его в разделе ответов. :)   -  person snap    schedule 03.08.2011

Ответы (6)


arrow_upward
1
arrow_downward

Возможный способ избежать всех этих проблем, а также оставаться быстрым, состоит в том, чтобы использовать две таблицы data_buffer (назовем их data_buffer1 и data_buffer2); пока процессы сбора вставляются в data_buffer2, вы можете выполнять insert и delete на data_buffer2; чем вы переключаетесь, поэтому собранные данные попадают в data_buffer2, а данные вставляются + удаляются из data_buffer1 в data.

person Erich Kitzmueller    schedule 03.08.2011
comment
В конце концов я реализовал вариант этого решения: решение RENAME TABLE, о котором я написал в своем собственном ответе. Однако, поскольку он основан на идее этого ответа, я выбираю этот ответ в качестве принятого ответа. Использование чередующихся таблиц — лучшее решение в моем случае, потому что нет лишних накладных расходов на ведение записей столбцов id и вставки с сенсоров не будут блокироваться из-за блокировок. - person snap; 08.08.2011

arrow_upward
3
arrow_downward

Я верю, что это сработает, но будет блокироваться до тех пор, пока вставка не будет завершена.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
INSERT INTO data (SELECT * FROM data_buffer FOR UPDATE); 
DELETE FROM data_buffer; 
COMMIT TRANSACTION;
person Sherif elKhatib    schedule 03.08.2011
comment
Неа. SELECT FOR UPDATE блокирует только выбранные строки, но не предотвращает вставку новых строк. - person snap; 03.08.2011
comment
Извини. На самом деле это работает с таблицами MEMORY (если они объединены в одну транзакцию), потому что в настоящее время они поддерживают только блокировки на уровне таблицы. Я бы предпочел не зависеть от текущих характеристик механизма хранения, кто знает, реализует ли следующая версия MySQL блокировки на уровне строк для таблиц памяти, или кто-то решит поместить временную таблицу в таблицу памяти NDB (которая поддерживает блокировки на уровне строк). - person snap; 03.08.2011
comment
Ага. Спасибо! Отредактированная версия работает и не зависит от ограничений механизма хранения MySQL. Однако он не идеален, поскольку блокирует все новые вставки в data_buffer во время выполнения процедуры. - person snap; 03.08.2011
comment
да, он блокирует, но не полностью .. просто блокирует коммиты, поэтому вставки работают правильно - person Sherif elKhatib; 03.08.2011

arrow_upward
1
arrow_downward

Как насчет того, чтобы иметь идентификатор строки, получить максимальное значение перед вставкой, сделать вставку, а затем удалить записи ‹= max(id)

person niktrs    schedule 03.08.2011

arrow_upward
1
arrow_downward

Это похожее решение на ответ @ammoQ. Разница в том, что вместо того, чтобы процесс INSERT определял, в какую таблицу производить запись, вы можете прозрачно поменять местами таблицы в запланированной процедуре.

Используйте RENAME в запланированной процедуре для замены таблиц:

CREATE TABLE IF NOT EXISTS data_buffer_new LIKE data_buffer;
RENAME TABLE data_buffer TO data_buffer_old, data_buffer_new TO data_buffer;
INSERT INTO data SELECT * FROM data_buffer_old;
DROP TABLE data_buffer_old;

Это работает, потому что оператор RENAME меняет местами таблицы атомарно, поэтому процессы INSERTing не завершатся ошибкой с «таблица не найдена». Однако это специфично для MySQL.

person snap    schedule 04.08.2011

arrow_upward
0
arrow_downward

Я предполагаю, что таблицы идентичны, с одинаковыми столбцами и первичными ключами? Если это так, вы можете выбрать select внутри предложения where... что-то вроде этого:

DELETE FROM data_buffer 
WHERE primarykey IN (SELECT primarykey FROM data)
person Andrew    schedule 03.08.2011
comment
Это сработало бы, если бы был первичный ключ. В настоящее время нет, потому что он не нужен для других целей. Однако я думаю, что это будет очень медленно, потому что в таблице data огромное количество строк. - person snap; 03.08.2011

arrow_upward
0
arrow_downward

Это специальное решение для MySQL. Вы можете использовать блокировку, чтобы предотвратить добавление новых строк процессами INSERT во время их перемещения.

Процедура перемещения строк должна быть следующей:

LOCK TABLE data_buffer READ;
INSERT INTO data SELECT * FROM data_buffer;
DELETE FROM data_buffer;
UNLOCK TABLE;

Код, который ВСТАВЛЯЕТ новые строки в буфер, следует изменить следующим образом:

LOCK TABLE data_buffer WRITE;
INSERT INTO data_buffer VALUES (1, 2, 3);
UNLOCK TABLE;

Очевидно, что процесс INSERT будет заблокирован, пока блокировка на месте.

person snap    schedule 04.08.2011
comment
Похоже, что это решение неверно, поскольку невозможно использовать LOCK TABLES в хранимых процедурах, как описано в (dev.mysql.com/doc/refman/5.1/en/). К сожалению, я не могу понизить свой собственный ответ. :) - person snap; 10.08.2011