Как обновлять разные строки одновременно в Mysql

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

Есть ли способ обновить разные строки одновременно?

Я использую Django в бэкэнде для выполнения операции обновления. Я могу переключиться на Postgres, но это поможет или все еще будет проблема блокировки?

Обновление: добавлен фрагмент кода:

def process_calculate_training(base_dept, exch_dept):
    # First initialize training to None, this is not happening concurrently.
    Emp.objects.filter(exch_dept=exch_dept, base_dept=base_dept).update(training=None)
    # Compute training and store result
    pass


def start_calculating_training():
    thread_func_args = [['MECH', 'COE'], ['MECH', 'ECE'], ['MECH', 'ICE'], ['MECH', 'IT']]
    with ThreadPool(4) as p:
        p.starmap(process_calculate_training, thread_func_args)

Обновление: выполнено 'показывать состояние innodb движка;'

Обнаружено, что Django автоматически установил уровень изоляции "READ-COMMITTED". Следовательно, MySql блокирует только те строки, которые необходимо обновить. Но все же обновление не происходит одновременно, так как кажется, что 2 потока ожидают блокировки, даже если им приходится обновлять разные строки.

я сделал show engine innodb status; во время одновременных операций обновления. Вот результаты:

TRANSACTIONS
------------
Trx id counter 2446659
Purge done for trx's n:o < 2446655 undo n:o < 0 state: running but idle
History list length 32
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421137001004688, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421137001002848, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421137001003768, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 2446658, ACTIVE 11 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4601 lock struct(s), heap size 1024208, 7281 row lock(s), undo log entries 2165
MySQL thread id 427, OS thread handle 139661736408832, query id 651475 localhost root updating
UPDATE `sdk_emp` SET `training` = NULL WHERE (`sdk_emp`.`base_dept` = 'MECH' AND `sdk_emp`.`exch_dept` = 'COE')
------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 345 page no 5238 n bits 240 index PRIMARY of table `empdb`.`sdk_emp` trx id 2446658 lock_mode X locks rec but not gap waiting
Record lock, heap no 135
------------------
---TRANSACTION 2446657, ACTIVE 11 sec fetching rows
mysql tables in use 1, locked 1
13971 lock struct(s), heap size 2351312, 15525 row lock(s), undo log entries 6307
MySQL thread id 424, OS thread handle 139661737813760, query id 651473 localhost root updating
UPDATE `sdk_emp` SET `training` = NULL WHERE (`sdk_emp`.`base_dept` = 'MECH' AND `sdk_emp`.`exch_dept` = 'ECE')
---TRANSACTION 2446656, ACTIVE 11 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 879 lock struct(s), heap size 270544, 1985 row lock(s), undo log entries 411
MySQL thread id 426, OS thread handle 139661736810240, query id 651471 localhost root updating
UPDATE `sdk_emp` SET `training` = NULL WHERE (`sdk_emp`.`base_dept` = 'MECH' AND `sdk_emp`.`exch_dept` = 'ICE')
------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 345 page no 1078 n bits 240 index PRIMARY of table `empdb`.`sdk_emp` trx id 2446656 lock_mode X locks rec but not gap waiting
Record lock, heap no 125
------------------
---TRANSACTION 2446655, ACTIVE 11 sec fetching rows
mysql tables in use 1, locked 1
13984 lock struct(s), heap size 2318544, 15263 row lock(s), undo log entries 6308
MySQL thread id 425, OS thread handle 139661737010944, query id 651469 localhost root updating
UPDATE `sdk_emp` SET `training` = NULL WHERE (`sdk_emp`.`base_dept` = 'MECH' AND `sdk_emp`.`exch_dept` = 'IT')

И снова запускаем команду через некоторое время:

TRANSACTIONS
------------
Trx id counter 2446659
Purge done for trx's n:o < 2446655 undo n:o < 0 state: running but idle
History list length 32
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421137001004688, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421137001002848, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421137001003768, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 2446658, ACTIVE 31 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4601 lock struct(s), heap size 1024208, 7281 row lock(s), undo log entries 2165
MySQL thread id 427, OS thread handle 139661736408832, query id 651475 localhost root updating
UPDATE `sdk_emp` SET `training` = NULL WHERE (`sdk_emp`.`base_dept` = 'MECH' AND `sdk_emp`.`exch_dept` = 'COE')
------- TRX HAS BEEN WAITING 29 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 345 page no 5238 n bits 240 index PRIMARY of table `empdb`.`sdk_emp` trx id 2446658 lock_mode X locks rec but not gap waiting
Record lock, heap no 135
------------------
---TRANSACTION 2446657, ACTIVE 31 sec updating or deleting
mysql tables in use 1, locked 1
27466 lock struct(s), heap size 4120784, 25388 row lock(s), undo log entries 12691
MySQL thread id 424, OS thread handle 139661737813760, query id 651473 localhost root updating
UPDATE `sdk_emp` SET `training` = NULL WHERE (`sdk_emp`.`base_dept` = 'MECH' AND `sdk_emp`.`exch_dept` = 'ECE')
---TRANSACTION 2446656, ACTIVE 31 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 879 lock struct(s), heap size 270544, 1985 row lock(s), undo log entries 411
MySQL thread id 426, OS thread handle 139661736810240, query id 651471 localhost root updating
UPDATE `sdk_emp` SET `training` = NULL WHERE (`sdk_emp`.`base_dept` = 'MECH' AND `sdk_emp`.`exch_dept` = 'ICE')
------- TRX HAS BEEN WAITING 30 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 345 page no 1078 n bits 240 index PRIMARY of table `empdb`.`sdk_emp` trx id 2446656 lock_mode X locks rec but not gap waiting
Record lock, heap no 125
------------------
---TRANSACTION 2446655, ACTIVE 31 sec fetching rows
mysql tables in use 1, locked 1
27489 lock struct(s), heap size 4169936, 25817 row lock(s), undo log entries 12692
MySQL thread id 425, OS thread handle 139661737010944, query id 651469 localhost root updating
UPDATE `sdk_emp` SET `training` = NULL WHERE (`sdk_emp`.`base_dept` = 'MECH' AND `sdk_emp`.`exch_dept` = 'IT')

Я не могу понять, почему 2 потока не могут получить блокировки, поскольку они будут обновлять разные строки? И какое решение для этого?

Источник

1 ответ

  1. Убедитесь, что ваша таблица хорошо проиндексирована и ваш запрос на обновление не выполняет полное сканирование таблицы или полное сканирование индекса. Таким образом, ваше утверждение может потребовать меньше блокировки. Обновление по первичному ключу или уникальному ключу, если это возможно.

Выполнение EXPLAIN для вашего заявления поможет вам узнать эффективность вашего запроса.

  1. Уровень изоляции MySQL по умолчанию - REPEATABLE-READ, и если вы используете транзакцию, он сохранит блокировку строки для строк, к которым он прикоснулся, до завершения транзакции, даже если строка не совпадает.

Изменение уровня изоляции на READ-COMMITTED может решить эту проблему, если блокировка строки будет немедленно снята, если она не совпадает.

Источник
Другие вопросы по тегам