Блокировка InnoDB для параллельных транзакций INSERT/UPDATE

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

Вот MVP в псевдокоде, похожем на python, предполагается уровень изоляции по умолчанию:

sql('BEGIN')
rows = sql('SELECT `value` FROM table WHERE `id`=<id> FOR UPDATE')
if rows:
    old_value, = rows[0]
    process(old_value, new_value)
    sql('UPDATE table SET `value`=<new_value> WHERE `id`=<id>')
else:
    sql('INSERT INTO table (`id`, `value`) VALUES (<id>, <new_value>)')
sql('COMMIT')

Проблема в том, что FOR UPDATE приводит к блокировке IS, которая не препятствует выполнению двух транзакций. Это приводит к взаимоблокировке, когда обе транзакции пытаются выполнить UPDATE или INSERT.

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

sql('BEGIN')
rows_changed = sql('INSERT IGNORE INTO table (`id`, `value`) VALUES (<id>, <new_value>)')
if rows_changed == 0:
    rows = sql('SELECT `value` FROM table WHERE `id`=<id> FOR UPDATE')
    old_value, = rows[0]
    process(old_value, new_value)
    sql('UPDATE table SET `value`=<new_value> WHERE `id`=<id>')
sql('COMMIT')

Проблема в этом решении заключается в том, что сбой INSERT приводит к блокировке S, которая также не препятствует выполнению двух транзакций, как описано здесь: https://stackoverflow.com/a/31184293/710358.

Конечно, любое решение, требующее жестко запрограммированного ожидания или блокировки всей таблицы, не подходит для производственных сред.


concurrency innodb locking transaction-isolation
person M1L0U    schedule 26.01.2018    source источник
comment
Сколько времени занимает process(old_value, new_value)? Если это больше нескольких секунд, то вы напрашиваетесь на неприятности, удерживая транзакцию открытой так долго.   -  person Rick James    schedule 30.01.2018
comment
Это очень быстро (‹1 мс), но достаточно, чтобы наблюдать взаимоблокировки в prod.   -  person M1L0U    schedule 30.01.2018

Ответы (1)


arrow_upward
0
arrow_downward

Хак для решения этой проблемы заключается в использовании INSERT ... ON DUPLICATE KEY UPDATE ..., который всегда выдает блокировку X. Поскольку вам нужно старое значение, вы можете выполнить пустое обновление и продолжить, как во втором решении:

sql('BEGIN')
rows_changed = sql('INSERT INTO table (`id`, `value`) VALUES (<id>, <new_value>) ON DUPLICATE KEY UPDATE `value`=`value`')
if rows_changed == 0:
    rows = sql('SELECT `value` FROM table WHERE `id`=<id> FOR UPDATE')
    old_value, = rows[0]
    process(old_value, new_value)
    sql('UPDATE table SET `value`=<new_value> WHERE `id`=<id>')
sql('COMMIT')
person M1L0U    schedule 26.01.2018
comment
Используйте этот трюк, чтобы получить id: ... ON DUPLICATE KEY UPDATE id=last_insert_id(id), а затем SELECT last_insert_id(). - person Rick James; 11.11.2019