Início / dba / Perguntas / 60295
Accepted
Question Overflow
Asked: 2014-03-07 02:10:55 +0800 CST

Por que o incremento automático salta mais do que o número de linhas inseridas?

  • 772

Estou muito incomodado com esse comportamento estranho que estou vendo no auto_incrementvalor registrado no bidID de uma tabela de Bids após realizar a inserção em massa usando um procedimento armazenado:

INSERT INTO Bids (itemID, buyerID, bidPrice)
 SELECT itemID, rand_id(sellerID, user_last_id), FLOOR((1 + RAND())*askPrice)
 FROM Items
 WHERE closing BETWEEN NOW() AND NOW() + INTERVAL 1 WEEK ORDER BY RAND() LIMIT total_rows;

Por exemplo, se o auto_incrementvalor bidID for 101 no início e eu inserir 100 linhas, o valor final se tornará 213 em vez de 201. No entanto, os bidIDs dessas linhas inseridas são executados sequencialmente até um máximo de 201.

Verificando o seguinte,

SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

Eu não tenho idéia por que isso está acontecendo. O que poderia estar causando o salto no auto incrementvalor?

mysql innodb

2 respostas

  1. Best Answer

    Isso não é incomum e existem algumas causas. Às vezes, é devido a otimizações que o executor de consultas faz para reduzir problemas de contenção com o recurso de contador, melhorando a eficiência quando há atualizações simultâneas na tabela afetada. Às vezes, é devido a transações que foram revertidas explicitamente (ou revertidas implicitamente devido ao encontro de um erro).

    As únicas garantias de uma auto_incrementcoluna (ou IDENTITYem MSSQL, e os outros nomes pelos quais o conceito passa) é que cada valor será único e nunca menor que o anterior: então você pode confiar nos valores para ordenar, mas não pode confiar em que não tenham lacunas.

    Se você precisar que os valores da coluna não tenham lacunas, precisará gerenciar os valores você mesmo, em outra camada de lógica de negócios ou no banco de dados por meio de um gatilho (no entanto, tenha cuidado com possíveis problemas de desempenho com gatilhos), é claro se você vai ter que lidar com todos os problemas de simultaneidade/reversão/limpeza-após-exclusão/outros que os mecanismos de banco de dados contornam, permitindo lacunas).

    • 13

  2. Pode ter várias causas:

    1. Verifique se o valor de auto_increment na própria tabela tem o próximo valor mais alto.

    2. Lembre-se de que, se você tiver transações em que você INSERT uma linha e reverta a transação, esse valor de auto_increment será ignorado.

    Em todos os modos de bloqueio (0, 1 e 2), se uma transação que gerou valores de incremento automático for revertida, esses valores de incremento automático serão “perdidos”. Depois que um valor é gerado para uma coluna de incremento automático, ele não pode ser revertido, independentemente de a instrução “INSERT-like” ser concluída ou não, e se a transação que a contém é revertida ou não. Esses valores perdidos não são reutilizados. Assim, pode haver lacunas nos valores armazenados em uma coluna AUTO_INCREMENT de uma tabela.

    1. Você define a configuração geral innodb_autoinc_lock_mode=1(consecutiva) ou innodb_autoinc_lock_mode=2(intercalada). Nesse caso, instruções INSERT únicas e instruções INSERT em massa podem ser executadas umas pelas outras. Definido innodb_autoinc_lock_mode=0para garantir que as instruções sejam executadas em ordem.

    Com innodb_autoinc_lock_mode definido como 0 (“tradicional”) ou 1 (“consecutivo”), os valores de incremento automático gerados por qualquer instrução são consecutivos, sem lacunas, porque o bloqueio AUTO-INC no nível da tabela é mantido até o final do instrução, e apenas uma dessas instruções pode ser executada por vez.

    Com innodb_autoinc_lock_mode definido como 2 (“interleaved”), pode haver lacunas nos valores de incremento automático gerados por “inserções em massa”, mas somente se houver instruções “INSERT-like” em execução concorrente.

    Para os modos de bloqueio 1 ou 2, podem ocorrer intervalos entre instruções sucessivas porque, para inserções em massa, o número exato de valores de incremento automático exigidos por cada instrução pode não ser conhecido e a superestimação é possível.

    1. Digamos que seu valor atual de auto_increment em uma tabela seja 50 e você também tenha um id de 1000 em sua tabela. O próximo valor de auto_increment será 51. Mas quando você reiniciar o servidor MySQL, seu próximo id de auto_increment será 1001.

    No MySQL 5.7 e anteriores, o contador de incremento automático é armazenado apenas na memória principal, não no disco. Para inicializar um contador de incremento automático após uma reinicialização do servidor, o InnoDB executaria o equivalente da seguinte instrução na primeira inserção em uma tabela contendo uma coluna AUTO_INCREMENT.

    SELECT MAX(ai_col) FROM table_name FOR UPDATE;

    Todas as citações são de: https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html :

    • 1

relate perguntas