Início / dba / Perguntas / 283324
Accepted
Daan
Asked: 2021-01-16 07:42:37 +0800 CST

Como ATUALIZAR dados sem bloquear todos os registros que a pesquisa encontra?

  • 772

Temos as seguintes especificações de banco de dados:

version: 10.1.47-MariaDB-0ubuntu0.18.04.1
innodb_version: 5.6.49-89.0
tx_isolation: READ-COMMITTED
innodb_strict_mode: ON
sql_mode: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
autocommit: ON

Temos o problema de bloquear muitas entradas de dados na tabela, o que causa deadlocks. Aqui está o nosso exemplo.

A primeira consulta que executamos:

UPDATE a SET col4 = 3 WHERE col1 = 1 AND col2 = 2 AND col3 = 3;

Temos índices de coluna única em todas as colunas. De acordo com os documentos : isso definirá um bloqueio exclusivo da próxima tecla em todos os registros que a pesquisa encontrar.

Portanto, a próxima UPDATEinstrução estará aguardando a anterior, embora os registros de resultados finais correspondentes não estejam sobrepostos. Isso ocorre porque o índice em col1.

UPDATE a SET col4 = 2 WHERE col1 = 1 AND col2 = 9999;

Eu gostaria de bloquear apenas os registros do resultado final e não "todos os registros que a pesquisa encontrar". Dividir a consulta em uma SELECTe uma UPDATEchave primária por me dá esse resultado, mas uma condição de corrida pode acontecer entre o SELECTe o UPDATE. Combiná-los em uma transação também não ajuda, pois todo SELECT é uma leitura consistente .

Como você projetaria um processo como este, onde eu possa executar uma UPDATEinstrução, sem bloquear todos os registros que a pesquisa encontrar?

mysql database-design

1 respostas

  1. Best Answer
    UPDATE a SET col4 = 3 WHERE col1 = 1 AND col2 = 2 AND col3  = 3;

    Precisa deste índice composto:

    INDEX(col1, col2, col3)  -- in any order

    Então, se você começar com col1, col2(em qualquer ordem), esse índice também funcionará bem para

    UPDATE a SET col4 = 2 WHERE col1 = 1 AND col2 = 9999;

    Se, por "Temos índices em todas as colunas" você quer dizer índices de coluna única, então eles são desperdiçados e atrapalham. O MySQL usará apenas um índice. Ele irá escolher entre (col1), (col2), (col3) procurando o mais seletivo. Em seguida, ele verificará todas as linhas com, digamos, col1 = 1. É provável que haja muito mais linhas do que você precisa UPDATE.

    Com o índice composto que eu recomendo, ele irá direto para a(s) linha(s) que corresponde(m) a toda a WHEREcláusula. Isso será muito menos linhas, diminuindo assim a probabilidade de um impasse. Além disso, será muito mais rápido, o que também diminui a chance de um impasse.

    Independentemente disso, você deve verificar se há erros e executar novamente qualquer transação que atinja um impasse.

    • 2

relate perguntas