Как улучшить INSERT INTO... SELECT поведение блокировки

Ответ 1

Теперь ответ на этот вопрос намного проще: - Используйте репликацию на основе строк и прочитайте уровень изоляции.

Зафиксированная вами блокировка исчезает.

Более длинное объяснение: http://harrison-fisk.blogspot.com/2009/02/my-favorite-new-feature-of-mysql-51.html

Ответ 2

Вы можете установить формат binlog следующим образом:

SET GLOBAL binlog_format = 'ROW';

Измените my.cnf, если вы хотите сделать, если он постоянный:

[mysqld]
binlog_format=ROW

Установите уровень изоляции для текущего сеанса перед выполнением запроса:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
INSERT INTO t1 SELECT ....;

Если это не поможет, попробуйте установить уровень изоляции уровня сервера, а не только для текущего сеанса:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Измените my.cnf, если вы хотите сделать, если он постоянный:

[mysqld]
transaction-isolation = READ-UNCOMMITTED

Вы можете изменить READ-UNCOMMITTED для READ-COMMITTED, который является лучшим уровнем изоляции.

Ответ 3

Каждый, кто использует таблицы Innodb, вероятно, получил пользу от факта Innodb таблицы выполняют неблокирующие чтения, то есть, если вы не используете некоторые модификаторы, такие как LOCK IN SHARE MODE или FOR UPDATE, SELECT не будет блокировать любые строки во время работы.

Это, как правило, правильно, однако есть заметное исключение - INSERT INTO table1 SELECT * FROM table2. Этот оператор будет выполнять блокировку чтения (общие блокировки) таблицы table2. Это также относится к аналогичным таблицам с предложением where и join. Это важно для таблиц, которые читаются как Innodb, даже если записи выполняются в таблице MyISAM.

Итак, почему это было сделано, что было довольно плохо для производительности MySQL и concurrency?

Причина - репликация. В MySQL до 5.1 репликация основана на утверждении, что означает, что операторы, ответившие на мастер, должны вызывать тот же эффект, что и на ведомом. Если Innodb не будет блокировать строки в исходной таблице, другая транзакция может изменить строку и зафиксировать до транзакции, в которой выполняется инструкция INSERT.. SELECT. Это сделало бы эту транзакцию примененной к подчиненному устройству до инструкции INSERT... SELECT и, возможно, приведет к разным данным, чем к master. Блокировка строк в исходной таблице при их чтении защищает от этого эффекта, поскольку другая транзакция изменяет строки до того, как INSERT... У SELECT был шанс получить к ней доступ, он также будет изменен в том же порядке на ведомом. Если транзакция пытается изменить строку после ее доступа и заблокирована с помощью INSERT... SELECT, транзакции придется ждать, пока оператор не будет завершен, чтобы убедиться, что он будет выполнен на подчиненном устройстве в правильном порядке. Получается довольно сложно? Ну, все, что вам нужно знать, должно было быть сделано для повторной репликации, чтобы работать прямо в MySQL до 5.1.

В MySQL 5.1 это, как и несколько других проблем, должно решаться репликацией на основе строк. Im, однако, еще не дал настоящие стресс-тесты, чтобы увидеть, насколько хорошо он выполняет:)

Еще одна вещь, которую нужно учесть: INSERT... SELECT фактически выполняет чтение в режиме блокировки и поэтому частично обходит управление версиями и извлекает последние исправленные строки. Поэтому, даже если вы работаете в режиме REPEATABLE-READ, эта операция будет выполняться в READ-COMMITTED способ, потенциально дающий разные результаты по сравнению с тем, что даст чистый SELECT. Это, кстати, относится к SELECT.. LOCK IN SHARE MODE и SELECT... FOR UPDATE.

Один мой вопрос, что это, если я не использую репликацию и отключил мой двоичный журнал? Если репликация не используется, вы можете включить опцию innodb_locks_unsafe_for_binlog, которая будет ослаблять блокировки, которые Innodb устанавливает на выполнение оператора, что обычно дает лучший concurrency. Однако, поскольку имя говорит, что делает блокировки небезопасными для повторной репликации и указывает время восстановления, поэтому используйте опцию innodb_locks_unsafe_for_binlog с осторожностью.

Замечание об отключении двоичных журналов недостаточно для запуска ослабленных блокировок. Вы необходимо также установить innodb_locks_unsafe_for_binlog = 1. Это делается так включение двоичного журнала не вызывает неожиданных изменений в блокировке поведения и производительности. Вы также можете использовать этот параметр с помощью иногда, если вы действительно знаете, что вы делаете. я бы не рекомендуется, если это действительно необходимо, поскольку вы, возможно, не знаете какие другие замки будут смягчены в будущих версиях и как это будет влияют на вашу репликацию.

Ответ 4

Возможно, вы могли бы использовать команду Create View (см. Создать синтаксис просмотра). Например,

Create View temp as SELECT FROM HighlyContentiousTableInInnoDb WHERE allKindsOfComplexConditions are true

После этого вы можете использовать инструкцию insert в этом представлении. Что-то вроде этого

INSERT INTO TemporaryTable (SELECT * FROM temp)

Это только мое предложение.

Ответ 5

Отказ от ответственности: я не очень разбираюсь в базах данных, и я не уверен, что эта идея работоспособна. Пожалуйста, поправьте меня, если это не так.

Как настроить вторичную эквивалентную таблицу HighlyContentiousTableInInnoDb2 и создание AFTER INSERT и т.д. триггеров в первой таблице, которые поддерживают обновление новой таблицы с теми же данными. Теперь вы можете заблокировать HighlyContentiousTableInInnoDb2 и только замедлить триггеры первичной таблицы, а не все запросы.

Потенциальные проблемы:

• 2 x сохраненных данных
• Дополнительная работа для всех вставок, обновлений и удалений
• Не может быть транзакционным.

Ответ 6

Если вы можете разрешить некоторые аномалии, вы можете изменить УРОВЕНЬ ИЗОЛЯЦИИ до наименее строгой - ЗАЧИТАЙТЕ НЕОПРЕДЕЛЕННЫЙ. Но в это время кому-то разрешено читать из таблицы назначения ur. Или вы можете заблокировать таблицу назначения вручную (я предполагаю, что mysql предоставляет эту функцию?).

Или, альтернативно, вы можете использовать READ COMMITTED, который также не должен блокировать исходную таблицу. Но он также блокирует вставленные строки в таблице назначения до фиксации.

Я бы выбрал второй.

Ответ 7

Причина блокировки (readlock) заключается в том, чтобы ваша транзакция чтения не считывала "грязные" данные, которые в настоящее время может выполнять параллельная транзакция. Большинство СУБД предлагают настройки, которые пользователи могут устанавливать и отменить блокировку чтения и записи вручную. Это может быть интересно для вас, если чтение грязных данных не является проблемой в вашем случае.

Я думаю, что нет безопасного способа чтения из таблицы без каких-либо блокировок в DBS с несколькими транзакциями.

Но следующий мозговой штурм: если пространство не является проблемой, вы можете подумать о запуске двух экземпляров одной и той же таблицы. HighlyContentiousTableInInnoDb2 для вашей транзакции постоянного чтения и записи и HighlyContentiousTableInInnoDb2_shadow для вашего пакетного доступа. Возможно, вы можете заполнить теневую таблицу, автоматизированную с помощью триггеров/подпрограмм внутри вашей СУБД, которая быстрее и умнее, чем дополнительная транзакция записи.

Еще одна идея - вопрос: нужно ли всем транзакциям получить доступ ко всей таблице? В противном случае вы можете использовать представления для блокировки только необходимых столбцов. Если непрерывный доступ и ваш пакетный доступ не пересекаются относительно столбцов, возможно, они не блокируют друг друга!

Ответ 8

Я не знаком с MySQL, но, надеюсь, есть эквивалент уровней изоляции транзакций Snapshot и Read committed snapshot в SQL Server. Использование любого из них должно решить вашу проблему.

Ответ 9

Я столкнулся с той же проблемой, используя CREATE TEMPORARY TABLE ... SELECT ... с SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction.

Исходя из вашего первоначального запроса, моя проблема была решена путем блокировки HighlyContentiousTableInInnoDb перед запуском запроса.

LOCK TABLES HighlyContentiousTableInInnoDb READ;
INSERT INTO TemporaryTable
    (SELECT FROM HighlyContentiousTableInInnoDb
    WHERE allKindsOfComplexConditions are true)
UNLOCK TABLES;