Concorrência no SQLite e por que você deve levar isso em conta

• A estrutura baseada em arquivo do SQLite é simples, mas, ao executar várias operações de escrita ao mesmo tempo, podem ocorrer conflitos de bloqueio (locking)
• O Jellyfin usa SQLite há muito tempo, mas em alguns sistemas houve casos em que o aplicativo parava devido a erros de banco de dados bloqueado durante transações
• Aproveitando o recurso de interceptores (interceptors) do EF Core, foram implementadas três estratégias de bloqueio (No-Lock, Optimistic, Pessimistic) para mitigar o problema
• A abordagem Optimistic minimiza a perda de desempenho com base em retentativas, enquanto a abordagem Pessimistic aumenta a estabilidade, mas aceita uma redução de velocidade
• Essa abordagem tem uma estrutura que pode ser aplicada facilmente a outros aplicativos em EF Core, oferecendo uma alternativa prática para resolver problemas de concorrência no SQLite

---

Estrutura básica e limitações do SQLite

• O SQLite é um mecanismo de banco de dados relacional baseado em arquivo, executado dentro da própria aplicação
  • Ele armazena todos os dados em um único arquivo e não exige um aplicativo de servidor separado
• Como a aplicação gerencia completamente esse único arquivo, existe risco de conflito quando vários processos acessam ao mesmo tempo
• Por isso, aplicações que usam SQLite devem executar apenas uma operação de escrita por vez

Modo Write-Ahead-Log (WAL)

• O SQLite ameniza as limitações de concorrência por meio do recurso WAL (Write-Ahead-Log)
  • O arquivo WAL atua como um arquivo de journal que registra alterações no banco de dados
  • Ele enfileira várias operações de escrita em paralelo e, durante a leitura, aplica as mudanças registradas no WAL
• No entanto, o WAL também não é perfeito, e em certas situações ainda ocorrem conflitos de bloqueio

Problemas de transação no SQLite

• As transações são responsáveis por garantir a atomicidade das operações de modificação e por controlar o bloqueio de leitura
• Em alguns sistemas com Jellyfin, houve casos em que o SQLite retornava o erro “database is locked” durante uma transação e encerrava imediatamente
  • Esse problema foi relatado independentemente de sistema operacional, velocidade do disco ou uso de virtualização
  • Como é difícil reproduzi-lo e ele ocorre de forma irregular, identificar a causa é complicado

Como o Jellyfin usa SQLite e quais eram os problemas

• No ambiente recomendado (armazenamento não em rede, SSD), o problema é raro, mas, devido a um bug de limitação de tarefas paralelas em versões anteriores à 10.11, aconteceu o seguinte:
  • Tarefas de varredura da biblioteca eram executadas com paralelismo excessivo, gerando milhares de solicitações simultâneas de escrita
  • Isso ultrapassava os limites de retentativa e timeout do mecanismo SQLite, causando sobrecarga no banco de dados e erros
• Transações longas e consultas ineficientes também agravavam o problema

Solução baseada em EF Core

• Ao migrar a base de código para EF Core, o Jellyfin passou a ter controle estrutural sobre isso
• Usando os interceptores (Interceptors) do EF Core, foi possível interceptar a execução de todos os comandos e transações e implementar um controle de bloqueio transparente
• Foram adotadas três estratégias de bloqueio
  1. No-Lock: modo padrão, sem bloqueio adicional. Usado na maioria dos casos para evitar perda de desempenho
  2. Optimistic Locking: em caso de falha, faz retentativas com a biblioteca Polly
  3. Pessimistic Locking: antes de toda operação de escrita, aplica um bloqueio ao banco inteiro com ReaderWriterLockSlim

Como funciona o Optimistic Locking

• É uma abordagem que assume sucesso e faz nova tentativa em caso de falha
  • Se duas operações de escrita entrarem em conflito, uma delas falha, espera um certo tempo e tenta novamente
• Com a biblioteca Polly, apenas falhas causadas por bloqueio entram no fluxo de retentativa
• Em comparação com a abordagem Pessimistic, tem menos overhead e menor perda de desempenho

Como funciona o Pessimistic Locking

• Em toda operação de escrita, o banco de dados inteiro é bloqueado
  • Durante a escrita, todas as operações de leitura e escrita ficam bloqueadas
• Essa é a abordagem mais estável, mas também a mais lenta
  • Por exemplo, mesmo que fosse possível escrever na tabela “Bob” enquanto se lê a tabela “Alice”, isso não é permitido
• Com ReaderWriterLockSlim, múltiplas leituras são permitidas, mas apenas uma escrita por vez

Próximos passos: Smart Locking

• Está em análise a adoção de um Smart Locking que combine as abordagens Optimistic e Pessimistic
  • A ideia é reunir as vantagens das duas para buscar equilíbrio entre desempenho e estabilidade

Resultados e possibilidade de adoção

• Nos testes iniciais, ambos os modos de bloqueio se mostraram eficazes para resolver o problema
• A causa raiz do problema ainda não está clara, mas agora os usuários têm opções para usar o Jellyfin com estabilidade
• Também havia muitos relatos semelhantes na internet, mas não existia uma solução completa
• A implementação do Jellyfin tem uma estrutura baseada em interceptores do EF Core que pode ser copiada e aplicada com facilidade
  • Quem chama não precisa ter consciência do funcionamento interno do bloqueio
• Isso pode ser usado imediatamente também em outros aplicativos em EF Core que enfrentam o mesmo problema de concorrência no SQLite

[GN⁺]

Comentários no Hacker News

• Já tive um problema de bloqueio no SQLite, e a causa era a fragmentação do disco
  Usuários que usavam o app por 8 horas por dia durante anos em tablets Android antigos reclamavam de lentidão e erros de lock
  Quando eu copiava os dados para analisar, não conseguia reproduzir o problema, mas quando finalmente peguei o dispositivo em mãos e conferi diretamente, bastou fazer uma “desfragmentação” copiando o arquivo do DB para um novo local e depois restaurando o nome original, e o problema sumiu completamente
  Tive a mesma melhora de desempenho no banco de dados do Jellyfin

  • Isso provavelmente era degradação da memória flash mais do que fragmentação. Fico curioso se eram tablets baratos com armazenamento eMMC
  • Fico curioso se a função VACUUM do SQLite produziria o mesmo efeito
  • Caso interessante. Mas não dá para pedir que o usuário faça desfragmentação manualmente, então é preciso uma solução prática

• As transações do SQLite começam por padrão no modo “deferred”
  Ou seja, ele não adquire um write lock até realmente tentar fazer uma escrita
  O erro SQLITE_BUSY acontece quando uma transação de leitura tenta virar escrita e outra transação já está com o write lock
  A solução é configurar busy_timeout e iniciar no modo “immediate” as transações que incluem escrita
  A explicação está bem organizada neste post de blog

  • No começo eu também achei que isso era um problema de SQLITE_BUSY. Reuni casos relacionados aqui
  • Acho que SQLITE_BUSY é uma espécie de cheiro de arquitetura. Em modo WAL, eu separo o pool de conexões somente leitura e o pool de uma única conexão de escrita. Assim dá para saber claramente quem está segurando locks e projetar antecipadamente as situações de contenção
  • busy_timeout não se aplica neste caso. No modo WAL, como as páginas são anexadas a um único arquivo de log, se uma leitura tentar virar escrita o SQLite falha imediatamente para garantir a serialização. O modo “immediate” evita isso
  • No fim, quem usa SQLite vai acabar se queimando com esse problema pelo menos uma vez e vai gastar tempo rastreando a causa
  • Não vi menção a SQLITE_BUSY no post, então talvez tenha sido uma configuração ausente

• Parte da explicação do texto parece estar errada
  O SQLite faz seu próprio gerenciamento de locks, então a aplicação não precisa controlar diretamente o acesso ao arquivo
  Além disso, WAL não permite várias escritas paralelas. Ele apenas permite leitura e uma única escrita ao mesmo tempo

  • Eu também adoro SQLite, mas este texto erra desde os conceitos básicos de concorrência, então não consigo recomendá-lo

• SQLite é um banco de dados excelente, mas é uma pena que os valores padrão (defaults) sejam conservadores demais
  Para uso real em produção, é preciso ajustar várias PRAGMAs

  • Queria saber quais PRAGMAs valeria a pena ativar por padrão
  • Numa situação dessas, talvez fosse melhor fazer um fork e definir novos padrões

• Quando o novo recurso hctree do SQLite estiver estável, pretendo usar só SQLite
  O hc no nome provavelmente significa High Concurrency
  Link para a documentação oficial

• Quando leio textos assim, fico com a impressão de que param em soluções paliativas em vez de fazer uma análise da causa raiz do problema
  Descobrir a causa exata com debugging e pesquisa mais profundos parece um compartilhamento realmente valioso

  • Talvez o autor tenha investigado só parcialmente e compartilhado uma solução incompleta. Pode até ter sido para provocar respostas melhores no HN. Como diz o ditado, “postar a resposta errada faz a correta aparecer mais rápido”

• Parece que também não entenderam que o modo WAL continua sendo uma estrutura de uma escrita, várias leituras
  Escritas paralelas não são possíveis; ele apenas evita que transações de leitura sejam bloqueadas por escritas
  Seria ótimo ter MVCC completo, mas a estrutura atual já funciona muito bem quando se entende o princípio

• Também tive um problema parecido no Jellyfin
  Normalmente funciona bem, mas em certas situações o DB fica travado com lock
  O log só registra “database is locked”, e no fim a única solução é reiniciar o contêiner Docker
  Isso acontece principalmente quando se aperta vários botões rapidamente na interface da TV

• Mudando um pouco de assunto, se você usa um DB in-memory do SQLite para grandes volumes de insert/delete, o uso de memória vai crescendo aos poucos
  Por exemplo, se você inserir e apagar 100 mil linhas a cada 5 minutos por vários dias, no macOS a memória pode chegar a 1 GB
  Fico curioso se há alguma configuração que ajudaria a ajustar isso

  • Vale verificar se você roda VACUUM periodicamente e se auto_vacuum está ativado
    Documentação do VACUUM
  • Também pode ser o comportamento normal de buffers sendo ajustados dinamicamente ao padrão de uso
  • Se o caso é apagar todas as linhas, então simplesmente dropar a tabela e criá-la de novo pode ser mais eficiente

• SQLite é excelente, mas às vezes, vendo problemas assim, dá vontade de pensar que talvez fosse melhor usar Postgres
  Se não for por portabilidade de arquivo único ou uso embarcado, o Postgres resolve concorrência de forma mais simples

  • Mas o Jellyfin é um servidor de mídia self-hosted, então exigir Postgres complica a instalação e a manutenção. SQLite é mais adequado
  • Na maioria dos casos, o Jellyfin roda em ambientes domésticos de usuário único, então SQLite basta. Só parece que a configuração atual não é a ideal
  • Ignorar as vantagens do SQLite e mandar usar Postgres é como dizer “você quer acampar, então construa uma cabana”
  • Com Postgres, além da instalação, você também precisa cuidar de migrações em upgrades de versão. Com SQLite esse peso não existe
  • O Jellyfin reescreveu recentemente o código do banco com Entity Framework, então está se preparando para tornar a escolha de banco mais flexível no futuro

[ndrgrd]

Ué? Tinha uma parte que me deixou com uma pulga atrás da orelha, então fui direto conferir os comentários, e como esperado...