Eu tenho alguma lógica em meu aplicativo que acho que resulta nas seguintes chamadas do MySQL; no entanto, quando duas delas são executadas em alguns milissegundos, recebo duas linhas filhas incompatíveis.
- Inicie a transação com isolamento de leitura repetível.
- Buscar e bloquear o objeto Conta
SELECT ... FROM Account WHERE id = ? FOR UPDATE - Leia os destinatários existentes vinculados à conta
SELECT ... FROM Address WHERE account_id = ? - Se o endereço existente estiver marcado como principal, atualize-o.
UPDATE Addresses SET primary = false WHERE id = ? - Insira o novo endereço com primário = verdadeiro.
- Transação completa.
Se esse processo for executado duas vezes rapidamente, recebo duas linhas de endereço com primário = true e account_id definido para a conta que me surpreendeu porque pensei que bloquear a conta na etapa 2 impediria que várias transações fossem executadas simultaneamente. Não quero mudar o comportamento de uma forma que limite muito meu rendimento.
Eu me perguntei se preciso mudar o nível de isolamento para "Serializable" [sic], mas não tenho certeza se uma leitura "FOR SHARE" na etapa 3 realmente resolveria o problema.
Minha estrutura de dados é provavelmente óbvia pelo que foi dito acima, mas se parece com isto:
Account:
idPC
outros dados de conta irrelevantes
Address:
idPC,
account_id- não é uma chave estrangeira real, apenas um ID que corresponde ao PK da conta.
primary- booleano, deve ter no máximo um endereço primário por conta, mas isso não é aplicado no MySQL porque complicaria um pouco o banco de dados ter uma coluna gerada para permitir isso, já que o MySQL não suporta índices parciais.
outros dados de endereço irrelevantes
O que está acontecendo é que a segunda transação não vê o UPDATE realizado pela primeira transação, por causa do REPEATABLE READ. Ele vê apenas a versão das linhas na tabela de endereços como estavam quando a transação 2 foi iniciada (antes da atualização ser confirmada pela transação 1).
Isso é conhecido como "atualização perdida". Algumas linhas foram atualizadas pela transação 1, mas a transação 2 não consegue vê-las e acredita que pode atualizar as linhas novamente. Então ele faz sua atualização e nunca vê a alteração da transação 1.
Para resolver isso, você poderia usar o nível de isolamento READ COMMITTED, para que a transação 2 faça um SELECT na tabela de endereços e veja a alteração confirmada mais recente.
Como alternativa, você pode transformar o SELECT da transação 2 na tabela de endereços em uma consulta de bloqueio. Tanto SELECT FOR UPDATE quanto SELECT FOR SHARE funcionariam para isso. Da forma como o InnoDB implementa o bloqueio de leituras, eles sempre visualizam a versão confirmada mais recentemente de uma linha, como se você tivesse usado o nível de isolamento READ COMMITTED (mesmo que a transação tenha sido iniciada como REPEATABLE READ).