Почему MySQL InnoDB может обрабатывать одновременные обновления, а PostgreSQL не может?

Давайте представим, что у вас есть таблица с этим определением:

CREATE TABLE public.positions
(
   id serial,
   latitude numeric(18,12),
   longitude numeric(18,12),
   updated_at timestamp without time zone
)

И у вас есть 50 000 строк в такой таблице. теперь для целей тестирования вы запустите обновление следующим образом:

update positions
set  updated_at = now()
where latitude between 234.12 and 235.00;

этот оператор обновит 1000 строк из 50 000 (в этом конкретном наборе данных)

если вы запустите такой запрос в 30 разных потоках, MySQL innodb завершится успешно, а postgres завершится с большим количеством взаимоблокировок.

Почему?


mysql postgresql deadlock isolation-level
person SDReyes    schedule 09.10.2016    source источник

Ответы (1)


arrow_upward
5
arrow_downward

Обычная удача, я бы сказал.

Если тридцать потоков захотят обновить одни и те же 1000 строк, они могут получить доступ к этим строкам в одном и том же порядке (в этом случае они будут блокировать друг друга и делать это последовательно) или в разных порядках (в этом случае они заблокируются). ).

То же самое для InnoDB и PostgreSQL.

Чтобы проанализировать, почему в вашем случае все работает иначе, следует начать со сравнения планов выполнения. Возможно, тогда вы поймете, почему PostgreSQL не обращается ко всем строкам в одном и том же порядке.

Не имея этой информации, я предполагаю, что вы столкнулись с добавленной функцией в версии 8.3, которая ускоряет параллельное последовательное сканирование:

  • Параллельные большие последовательные сканирования теперь могут разделять операции чтения с диска (Джефф Дэвис)

    Это достигается запуском нового последовательного сканирования в середине таблицы (где уже выполняется другое последовательное сканирование) и переходом к началу для завершения. Это может повлиять на порядок возвращаемых строк в запросе, в котором не указано ORDER BY. Параметр конфигурации synchronize_seqscans можно использовать для отключения этого при необходимости.

Проверьте, использует ли ваш план выполнения PostgreSQL последовательное сканирование, и посмотрите, поможет ли изменение synchronize_seqscans избежать взаимоблокировок.

person Laurenz Albe    schedule 09.10.2016
comment
ты прав. вы можете решить это в postgres, используя: UPDATE.... WHERE id in (SELECT... FROM... WHERE... order by id ДЛЯ ОБНОВЛЕНИЯ). - person SDReyes; 09.10.2016
comment
Я думаю, что правильным решением было бы не иметь 30 потоков, обновляющих одни и те же строки. - person Laurenz Albe; 10.10.2016
comment
если только мы не нагружаем БД. кем мы являемся, потому что это часть эталона! хД - person SDReyes; 10.10.2016
comment
Хорошая идея: проверьте, использует ли ваш план выполнения PostgreSQL последовательное сканирование, и посмотрите, позволяет ли изменение synchronize_seqscans избежать взаимоблокировок. - person SDReyes; 10.10.2016
comment
@SDReyes Если все потоки обновляют одни и те же строки, это плохой стресс-тест, потому что все потоки, кроме одного, ничего не сделают (ждут блокировки). Попросите их обновить разные строки, тогда вы получите гораздо большую нагрузку. - person Laurenz Albe; 11.10.2016
comment
что, если я скажу вам, что MySQL каким-то образом справился с этим тестом. в то время как pg нет. - person SDReyes; 11.10.2016
comment
Вы уже сказали это. А я говорил вам, что это удача, что одна СУБД думает так, а другая иначе. Все, что я пытался указать вам в своем комментарии, это то, что ваш стресс-тест, похоже, вызывает больше блокировок, чем стресса, как в MySQL, так и в PostgreSQL. - person Laurenz Albe; 12.10.2016
comment
ооооо, да, мне нужно проверить это. спасибо Лауренсу. вернется с дополнительными данными об этом случае - person SDReyes; 15.10.2016