Collin Dauphinee's questions

Eu tenho uma instância MySQL em execução no RDS da Amazon. Isso significa que não posso alterar nenhuma das configurações do log binário.

Eu tenho uma tabela source, criada assim, que contém cerca de 2 bilhões de linhas:

CREATE TABLE `source` (
   id INT PRIMARY KEY AUTO_INCREMENT,
   value1 VARCHAR(256),
   value2 VARCHAR(256)
);

Eu tenho outra tabela destination, com as mesmas colunas, mas idé uma BIGINT:

CREATE TABLE `destination` (
   id BIGINT PRIMARY KEY AUTO_INCREMENT,
   value1 VARCHAR(256),
   value2 VARCHAR(256)
);

A sourcetabela tem lacunas no ID que quero compactar. Eu gostaria de copiar todas as linhas sourcepara destination, sem a idcoluna , algo assim:

INSERT INTO `destination` (value1, value2)
SELECT value1, value2 FROM `source`; 

Como posso fazer isso, sem travar a sourcetabela? A cópia em si vai demorar muito, devido ao tamanho da tabela, e não posso bloqueá-la por tanto tempo.

Basicamente, quero executar a instrução acima com READ UNCOMMITTEDisolamento, mas isso não é possível devido à minha incapacidade de alterar as configurações de log binário.

Eu tenho uma tabela InnoDB cuja coluna de chave primária é definida da seguinte forma:

id INT NOT NULL AUTO_INCREMENT

O valor auto_increment desta coluna atingiu INT_MAX, portanto, todas as inserções estão falhando com um erro de chave duplicada. No entanto, esta tabela tem valores de ID não utilizados de 0 a 1,8 milhão, portanto, gostaria de redefinir o valor auto_increment para 0 para ganhar tempo para que uma correção de longo prazo seja implementada.

Não consigo encontrar nenhuma maneira de fazer isso sem incorrer em um tempo de inatividade significativo. A funcionalidade principal dos meus servidores de aplicativos é interrompida se eles não puderem ler esta tabela (mas não conseguir escrever, como agora, tudo bem).

Eu considerei as duas soluções a seguir, sem sucesso:

Configurando o valor auto_increment com ALTER TABLE. Isso não é possível porque reconstrói a tabela, o que leva muito tempo.

Criando uma nova tabela com esquema idêntico e usando RENAME TABLEpara trocar na nova tabela com a coluna começando em 0 e, em seguida, preenchendo-a com dados da tabela antiga. Isso também não é possível, porque o processo de preenchimento com clobber o valor auto_increment (e acredito que bloquearia as inserções do aplicativo durante o preenchimento).

Existe alguma outra abordagem que eu possa adotar aqui ou estou completamente sem sorte?

Estou encontrando deadlocks intermitentes no seguinte procedimento:

DELIMITER $$
CREATE PROCEDURE pr_set_user_password (IN user_id INT, IN algorithm INT, IN hash VARBINARY(256))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;
        UPDATE `UserPassword` SET `Active` = 0 WHERE `UserId` = user_id;
        INSERT INTO `UserPassword` (`UserId`, `Active`, `Algorithm`, `Hash`) VALUES (user_id, 1, algorithm, hash);
COMMIT;
END $$
DELIMITER ;

Esta tabela possui um índice composto em UserIde Active( IX_UserPassword_UserId_Active).

Alguém tentou executar um teste de carga que criou muitos usuários em paralelo e encontrou o impasse de forma muito consistente. Olhando os logs, vejo que o impasse ocorre quando esta proc roda com user_idvalores adjacentes.

Estou realmente procurando uma explicação real de por que isso resulta em um impasse. Eu tive várias respostas confusas e conflitantes, nenhuma das quais faz sentido, variando de porque uma transação tem o bloqueio e o outro bloqueio não pode adquiri-lo (mesmo que haja apenas um bloqueio em questão) para que seja assim que o MySQL funciona, lidar com isso .

Veja um exemplo de impasse abaixo:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-08-26 00:21:52 2b30caecc700
*** (1) TRANSACTION:
TRANSACTION 71925393848, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 4901, OS thread handle 0x2b30cc1d7700, query id 37117819 <server> update
INSERT INTO `UserPassword` (`UserId`, `Active`, `Algorithm`, `Hash`) VALUES ( NAME_CONST('user_id',72332427), 1,  NAME_CONST('algorithm',2),  NAME_CONST('hash',_binary'<data>' COLLATE 'binary'))
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 388 page no 117300 n bits 784 index `IX_UserPassword_UserId_Active` of table `users`.`UserPassword` trx id 71925393848 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 714 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 844fb48c; asc  O  ;;
 1: len 1; hex 81; asc  ;;
 2: len 4; hex 818106f9; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 71925393846, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 1
MySQL thread id 4909, OS thread handle 0x2b30caecc700, query id 37117815 <server> update
INSERT INTO `UserPassword` (`UserId`, `Active`, `Algorithm`, `Hash`) VALUES ( NAME_CONST('user_id',72332426), 1,  NAME_CONST('algorithm',2),  NAME_CONST('hash',_binary'<data>' COLLATE 'binary'))
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 388 page no 117300 n bits 784 index `IX_UserPassword_UserId_Active` of table `users`.`UserPassword` trx id 71925393846 lock_mode X locks gap before rec
Record lock, heap no 714 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 844fb48c; asc  O  ;;
 1: len 1; hex 81; asc  ;;
 2: len 4; hex 818106f9; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 388 page no 117300 n bits 784 index `IX_UserPassword_UserId_Active` of table `users`.`UserPassword` trx id 71925393846 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 714 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 844fb48c; asc  O  ;;
 1: len 1; hex 81; asc  ;;
 2: len 4; hex 818106f9; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

Tenho um cenário em uma aplicação muito antiga e muito grande, onde tenho uma tabela representando um tipo de recurso:

CREATE TABLE resource (resource_id INT, name NVARCHAR(4000))

Essa tabela é selecionada em centenas de locais diferentes, incluindo procedimentos armazenados e SQL dinâmico no código do aplicativo.

Uma equipe atualizou recentemente o nome deste recurso para ser localizado, e sua abordagem é bastante direta. Há uma nova tabela contendo os nomes localizados e um ID de idioma 'padrão' na resourcetabela, para quando o nome não estiver localizado para o idioma solicitado:

-- Foreign keys omitted
ALTER TABLE resource ADD default_language_id INT
CREATE TABLE resource_local (resource_id INT, language_id INT, name NVARCHAR(4000))

A maioria dos procs tem um @user_language_idparâmetro, portanto, a lógica para escolher o nome a ser retornado é simples: pegue resource_local.namecorrespondência language_id = @user_language_idse existir, caso contrário, resource_local.namecorresponda language_id = resource.default_language_idse existir, caso contrário pegue resource.name.

Infelizmente, isso transforma a lógica para selecionar o nome correto em algo assim:

SELECT ISNULL(ISNULL(exact.name, default.name), res.name)
FROM resource res
LEFT JOIN resource_local exact ON exact.resource_id = res.resource_id 
    AND exact.language_id = @user_language_id
LEFT JOIN resource_local default ON default.resource_id = res.resource_id
    AND default.language_id = res.default_language_id
WHERE res.resource_id = @resource_id

Todas as centenas de locais que tentam selecionar resource.nameestão tendo que ser atualizadas com essa lógica, o que tornou esse projeto um esforço massivo de toda a organização, pois cada equipe precisa atualizar seu SQL para usar essa lógica. Isso também causa problemas de manutenção, pois qualquer novo desenvolvedor que lida com essa tabela precisa saber que não pode usar apenas a namecoluna.

Agora é tarde demais, mas estou curioso: existe alguma maneira melhor de abordar isso, de modo que selecionar a namecoluna de resourceapenas 'faça a coisa certa' com base na @user_language_idvariável (se existir)?

Estou tentando diagnosticar uma consulta que está demorando muito tempo intermitentemente. Eu suspeito que pode ser bloqueado tentando adquirir um bloqueio. Não tenho permissão para usar um criador de perfil no ambiente com o problema.

Existe alguma maneira de obter estatísticas sobre quanto tempo essa consulta individual está bloqueada, sem usar um criador de perfil externo?

Eu tenho uma tabela bem básica:

CREATE TABLE [obj_local] (
    [obj_id]     INT             NOT NULL,
    [value]      NVARCHAR (1000) NOT NULL
);

Esta tabela armazena muitos dados e preciso pesquisar na valuecoluna uma sequência de caracteres:

SELECT [obj_id] FROM [obj_local] WHERE [value] LIKE '%{substring}%'

Isso é extremamente lento. Meu entendimento é que um índice não vai me ajudar devido aos curingas de ambos os lados, e um índice de texto completo também não vai me ajudar, porque não estou procurando tokens (palavras).

Há algo que eu possa fazer para otimizar essa pesquisa?

Eu estava procurando um impasse causado por duas instruções UPDATE simultâneas:

UPDATE [table] 
SET [column] = 0 
WHERE [unindexed_column] = @id

Meu entendimento é que, como a coluna WHEREem que se baseia não é indexada, uma verificação completa da tabela é executada. Para cada linha, um bloqueio de atualização é adquirido. Se corresponder à WHEREcláusula, ele atualizará para um bloqueio exclusivo e o manterá até que a instrução seja concluída.

O impasse ocorre quando a sessão A tem um bloqueio exclusivo na linha 2 e está tentando adquirir um bloqueio de atualização na linha 1, enquanto a sessão B tem um bloqueio exclusivo na linha 1 e está tentando adquirir um bloqueio de atualização na linha 2.

O motivo do impasse faz sentido, mas não entendo exatamente como é realizada a varredura da tabela que torna esse cenário possível. Se as duas consultas executarem a varredura na mesma ordem, parece que o pior cenário é que uma das consultas seja bloqueada ao adquirir um bloqueio de atualização até que a outra consulta seja concluída e libere seus bloqueios.

Como uma varredura de tabela é realizada? A ordem em que as linhas da tabela são verificadas é inconsistente? Se a instrução de atualização falhar em adquirir um bloqueio de atualização, ela irá para a próxima linha e tentará a linha anterior novamente mais tarde? O que, exatamente, torna esse impasse possível?