As transações do SQLite

• Nos últimos anos, o SQLite vem ganhando destaque como um engine de banco de dados SQL em-processo, altamente confiável, para uso como backend de processos de servidor
• A popularidade disparou apesar de os desenvolvedores do SQLite quase desaconselharem ativamente esse tipo de uso, em vez do papel tradicional de cliente ou aplicação de edge

Principais motivos pelos quais passei a me interessar pelo SQLite:

• Simplicidade conceitual: imagine uma B-tree de linhas/tuplas particionadas por chave primária. Isso foi amplamente testado para persistir de forma confiável em disco, com uma camada de interação SQL adicionada
• É possível estabelecer uma estratégia prática de backup com o Litestream. Ele faz backup do WAL para um local remoto e o replica continuamente. O backup pode ser restaurado automaticamente na inicialização com um comando simples.
• Ainda gosto de ter um ambiente de desenvolvimento completo que possa rodar offline
• Com file::memory: é possível trabalhar em memória, então o código de teste pode iniciar e encerrar instâncias facilmente quando necessário

Limitação de single-writer

• As “limitações do SQLite no servidor” estão bem documentadas pelos desenvolvedores do SQLite, e as melhores configurações no lado do servidor foram analisadas. Mas a limitação que mais se destaca são sites de alto tráfego, ou seja, sites com muitas escritas
• No modo WAL, o SQLite usa, por design, um único Writer. Isso permite no máximo 1 transação de escrita ao mesmo tempo e várias transações somente leitura
• Esse design coloca o gargalo de sites com muito tráfego e muitas escritas na gestão da vazão desse único Writer. Isso nos leva de volta a um dos componentes centrais da tecnologia moderna

SQLite

• O SQLite oferece, por padrão, transações estritamente isoladas em SERIALIZABLE. Esse é o nível mais forte de garantia de Isolation
• Ao usar um único Writer, o SQLite adota uma forma de controle pessimista de concorrência que pode garantir facilmente que os dados subjacentes não foram alterados durante uma transação de escrita

Postgres

• O Postgres, na prática, difere do padrão SERIALIZABLE definido no padrão SQL e escolhe o mais permissivo READ COMMITTED (apesar do controle de concorrência multiversão, muito mais complexo)
  • Essa redução de rigor traz o risco de non-repeatable reads. Ou seja, mesmo dentro da mesma transação, executar a mesma consulta de leitura várias vezes pode retornar resultados diferentes se outra transação COMMITTED alterar valores em segundo plano
  • Ao escolher esse nível de Isolation, o Postgres deixa aberta a possibilidade de a transação operar sobre dados desatualizados. O desenvolvedor precisa ter isso em mente
• Quando configurado como SERIALIZABLE, o Postgres usa um esquema de optimistic-concurrency control para rastrear os dados acessados durante a transaction e verificar antes do commit se eles não foram alterados
  • O Postgres faz isso com base em locks no nível de row ou de page, dependendo da transaction, para gerenciar o uso de memória
  • Esse padrão é chamado de optimistic porque se espera que os dados subjacentes não mudem, já que quanto mais granulares forem os dados monitorados pela transaction no momento do commit, menor a chance de terem sido alterados

FoundationDB

• Transações não se limitam a bancos de dados relacionais. Em um armazenamento distribuído de chave-valor, usa-se optimistic concurrency control para alcançar garantias SERIALIZABLE
• Quando o NoSQL surgiu, armazenamentos NoSQL distribuídos com garantias ACID não eram comuns. O FoundationDB publicou um manifesto sobre transações para destacar como os desenvolvedores podem se beneficiar muito dessas garantias ACID
• O FoundationDB oferece orientações sobre como escrever código para optimistic concurrency control e sobre o fato de que, às vezes, por conflitos entre transações concorrentes, os dados mudam e a transação é automaticamente tentada novamente

Idempotência

• Uma transação idempotente é aquela que produz o mesmo efeito ao ser confirmada uma vez ou duas vezes
• O FoundationDB fornece padrões para tornar transações idempotentes e evitar problemas quando é preciso repetir uma transação várias vezes por causa de conflitos

Então, com tudo isso em mente, que opções o SQLite oferece?

BEGIN …

O SQLite oferece várias formas de indicar ao engine como a transação deve se comportar, na forma das palavras-chave IMMEDIATE, EXCLUSIVE e DEFERRED, que no modo WAL acabam se resumindo a DEFERRED vs IMMEDIATE

DEFERRED

• A transação começa em modo READ, podendo executar ao mesmo tempo que outras transações de leitura ou escrita
• Só é promovida para uma transação READ-WRITE bloqueante quando uma consulta que modifica o estado do DB (INSERT, UPDATE, DELETE) é executada
• Se, na promoção, o DB estiver bloqueado por outra transação, retorna erro SQLITE_BUSY. O cliente precisa tratar isso

IMMEDIATE

• A transação já começa imediatamente em modo READ-WRITE
• Se já houver uma transação de escrita em andamento, retorna SQLITE_BUSY imediatamente
• O cliente precisa decidir como lidar com isso

CONCURRENT

• O SQLite tem um ponto experimental que move as transações de um modelo pessimista para um modelo otimista limitado
• É limitado porque o locking otimista funciona no nível de page do DB (4096 bytes por padrão), e não no nível de linha/tupla
• No modo CONCURRENT, o SQLite pode manter várias transações de escrita ativas ao mesmo tempo, mas antes do commit verifica se as pages acessadas durante a transação não foram alteradas desde o início dela
• Se não houver conflito, as mudanças são confirmadas em sequência e alcançam garantias estritas de SERIALIZABLE. Se houver conflito, retorna SQLITE_BUSY

HC-Tree

• Outro branch experimental do SQLite é o [HC-Tree], um trabalho em andamento que busca oferecer locking otimista no nível de linha/tupla. Um dos resultados interessantes é que ele também fornece um excelente conjunto de benchmarks mostrando os ganhos de desempenho desse design em comparação com o branch BEGIN CONCURRENT

E se pegarmos a abordagem de benchmarking deles e a executarmos com as opções padrão?

Benchmarking

nUpdate=1, nScan=0

• Essa transação somente de escrita mostra claramente a vantagem de IMMEDIATE sobre DEFERRED. O locking acontece imediatamente e a transação não sofre o custo da promoção
• O CONCURRENT mostra aumento de throughput à medida que o número de threads cresce e os conflitos aumentam

nUpdate=10, nScan=0

• Como esperado, agrupar escritas em lote ajuda bastante no número de linhas atualizadas com 16 threads. O CONCURRENT sobe de ~12k/sec para ~19k/sec
• IMMEDIATE vs DEFERRED se torna menos importante. O custo das próprias atualizações passa a ser mais relevante que o custo de promover a transação

nUpdate=1, nScan=10

• Essa transação deve expor a fraqueza do locking CONCURRENT em nível de page por causa das leituras aleatórias
• Isso mostra imediatamente por que usar IMMEDIATE para transações que vão atualizar é mais importante do que o custo da promoção em DEFERRED
• No caso do CONCURRENT, esses resultados são bastante robustos porque os conflitos de base, na prática, não aumentaram muito

nUpdate=0, nScan=10

• Essa transação em lote somente leitura mostra o impacto do controle pessimista de concorrência
• Mostra por que não se deve definir IMMEDIATE como padrão para todas as transações
• CONCURRENT vs IMMEDIATE indica uma pequena desvantagem ao usar o modo CONCURRENT. “Em todos os casos, o desempenho é um pouco pior”
  • Ainda assim, CONCURRENT seria uma boa opção padrão