La concurrencia en SQLite y por qué debes tenerla en cuenta

• La estructura basada en archivos de SQLite es simple, pero cuando se realizan varias operaciones de escritura al mismo tiempo pueden ocurrir conflictos de bloqueo (locking)
• Jellyfin ha usado SQLite durante mucho tiempo, pero en algunos sistemas se presentaba un problema donde la aplicación se detenía debido a errores de base de datos bloqueada durante una transacción
• Aprovechando la función de interceptores (interceptors) de EF Core, implementaron tres estrategias de bloqueo (No-Lock, Optimistic, Pessimistic) para mitigar el problema
• El enfoque Optimistic minimiza la pérdida de rendimiento mediante reintentos, mientras que el enfoque Pessimistic mejora la estabilidad a costa de menor velocidad
• Este enfoque ofrece una estructura fácil de aplicar también en otras aplicaciones con EF Core, y proporciona una alternativa práctica para resolver problemas de concurrencia en SQLite

---

Estructura básica y limitaciones de SQLite

• SQLite es un motor de base de datos relacional basado en archivos que se ejecuta dentro de la aplicación
  • Guarda todos los datos en un solo archivo y no requiere una aplicación de servidor separada
• Como la aplicación administra completamente un único archivo, existe riesgo de conflicto si varios procesos acceden al mismo tiempo
• Por eso, una aplicación que usa SQLite debe realizar solo una operación de escritura a la vez

Modo Write-Ahead-Log (WAL)

• SQLite alivia las restricciones de concurrencia mediante la función WAL (Write-Ahead-Log)
  • El archivo WAL actúa como un archivo de journal que registra los cambios en la base de datos
  • Permite encolar varias escrituras en paralelo y aplicar los cambios del WAL durante la lectura
• Sin embargo, WAL tampoco es perfecto y en ciertas situaciones siguen ocurriendo conflictos de bloqueo

Problemas de transacciones en SQLite

• Las transacciones se encargan de garantizar la atomicidad de los cambios y de controlar el bloqueo de lecturas
• En algunos sistemas con Jellyfin, durante una transacción SQLite devolvía el error “database is locked” y se detenía de inmediato
  • Este problema se reportó sin importar el sistema operativo, la velocidad del disco o si había virtualización
  • Como es difícil de reproducir y ocurre de forma irregular, resulta complicado identificar la causa

Cómo usa Jellyfin SQLite y dónde estaba el problema

• En entornos recomendados (almacenamiento no conectado por red, SSD), el problema es poco frecuente, pero debido a un bug de límite de tareas paralelas en versiones anteriores a 10.11
  • Las tareas de escaneo de bibliotecas se ejecutaban con demasiado paralelismo y generaban miles de solicitudes de escritura simultáneas
  • Esto superaba los límites de reintento y timeout del motor SQLite, provocando sobrecarga de la base de datos y errores
• Las transacciones largas y las consultas ineficientes también agravaban el problema

Solución basada en EF Core

• Jellyfin pudo tener un control estructural al migrar su base de código a EF Core
• Usando los interceptores (Interceptors) de EF Core, interceptaron toda la ejecución de comandos y transacciones para implementar un control de bloqueo transparente
• Se introdujeron tres estrategias de bloqueo
  1. No-Lock: modo predeterminado, sin bloqueo adicional. Se usa en la mayoría de los casos para evitar degradación de rendimiento
  2. Optimistic Locking: si falla, realiza reintentos con la biblioteca Polly
  3. Pessimistic Locking: antes de cada escritura, bloquea toda la base de datos con ReaderWriterLockSlim

Cómo funciona Optimistic Locking

• Parte de la suposición de que la operación tendrá éxito y, si falla, la reintenta
  • Si dos operaciones de escritura entran en conflicto, una falla, espera un tiempo y luego vuelve a intentar
• Con la biblioteca Polly, solo se reintentan los fallos causados por bloqueos
• Tiene menos overhead y menor pérdida de rendimiento que el enfoque Pessimistic

Cómo funciona Pessimistic Locking

• En cada operación de escritura, bloquea toda la base de datos
  • Mientras se escribe, se bloquean todas las operaciones de lectura y escritura
• Este método es el más estable, pero también el más lento
  • Por ejemplo, aunque sería posible escribir en la tabla “Bob” mientras se lee la tabla “Alice”, este enfoque no lo permite
• Usa ReaderWriterLockSlim para permitir múltiples lecturas, pero solo una escritura

Planes a futuro: Smart Locking

• Se está evaluando introducir Smart Locking, que combina los enfoques Optimistic y Pessimistic
  • La idea es integrar las ventajas de ambos para buscar un equilibrio entre rendimiento y estabilidad

Resultados y posibilidad de aplicación

• Según las pruebas iniciales, ambos modos de bloqueo fueron efectivos para resolver el problema
• Aunque la causa raíz sigue sin estar clara, ahora los usuarios tienen opciones para usar Jellyfin de forma estable
• En internet también había muchos reportes de errores similares, pero no existía una solución completa
• La implementación de Jellyfin está basada en interceptores de EF Core y tiene una estructura fácil de copiar y aplicar
  • Quien la use no necesita conocer el funcionamiento interno del bloqueo
• También puede aprovecharse de inmediato en otras aplicaciones con EF Core que sufran el mismo problema de concurrencia en SQLite

[GN⁺]

Opiniones en Hacker News

• Antes tuve un problema de bloqueo en SQLite y la causa fue la fragmentación del disco
  Usuarios que usaban la app en tablets Android viejas durante 8 horas diarias por años empezaron a quejarse de lentitud y errores de bloqueo
  Cuando copiábamos los datos para revisarlos no podíamos reproducir el problema, pero al final recibimos los dispositivos y confirmamos que, al “desfragmentar” el archivo de la DB copiándolo a otra ubicación y luego devolviéndolo a su nombre original, el problema desaparecía por completo
  También vi una mejora de rendimiento con el mismo método en la DB de Jellyfin

  • Eso suena más a degradación de memoria flash que a fragmentación. Me pregunto si eran tablets económicas con almacenamiento eMMC
  • Me pregunto si se puede lograr el mismo efecto con la función VACUUM de SQLite
  • Es un caso interesante. Pero no puedes pedirle al usuario que desfragmente manualmente, así que hace falta una solución realista

• Las transacciones de SQLite comienzan por defecto en modo “deferred”
  Es decir, no toman el write lock hasta intentar hacer una escritura real
  El error SQLITE_BUSY ocurre cuando una transacción de lectura intenta convertirse en escritura y otra transacción ya tiene el write lock
  La solución es configurar busy_timeout e iniciar en modo “immediate” las transacciones que incluyan escrituras
  La explicación está bien resumida en esta entrada de blog

  • Yo también al principio pensé que esto era un problema de SQLITE_BUSY. Reuní casos relacionados aquí
  • Creo que SQLITE_BUSY es una especie de mal olor arquitectónico. En modo WAL diseño por separado un pool de conexiones de solo lectura y un único pool de conexiones de escritura. Así queda claro quién tiene el lock y se puede diseñar de antemano la contención
  • busy_timeout no aplica en este caso. En modo WAL las páginas se agregan a un único archivo de log, así que si intentas pasar de lectura a escritura, SQLite falla de inmediato para garantizar la serialización. El modo “immediate” evita eso
  • Al final, cualquiera que use SQLite tarde o temprano se va a quemar con este problema y perderá tiempo rastreando la causa
  • Veo que la entrada del blog no mencionaba SQLITE_BUSY; probablemente faltó esa configuración

• Parte de la explicación del artículo parece incorrecta
  SQLite maneja los locks por sí mismo, así que la aplicación no necesita controlar directamente el acceso al archivo
  Además, WAL no permite varias escrituras en paralelo. Solo permite lectura y una sola escritura al mismo tiempo

  • A mí también me encanta SQLite, pero este artículo está mal incluso en conceptos básicos de concurrencia, así que no puedo recomendarlo

• SQLite es una gran base de datos, pero sus valores predeterminados son demasiado conservadores
  Para usarla en producción de verdad, hay que ajustar varias PRAGMA

  • Me gustaría saber cuáles PRAGMA conviene activar por defecto
  • En una situación así, casi parecería mejor hacer un fork y definir nuevos valores por defecto

• Cuando la nueva función hctree de SQLite se estabilice, pienso usar solo SQLite
  Supongo que hc significa High Concurrency
  Enlace a la documentación oficial

• Este tipo de artículos me da la impresión de que se quedan en soluciones temporales en lugar de analizar la causa raíz del problema
  Sería mucho más valioso compartir una investigación y depuración más profunda para identificar con precisión el origen real

  • Probablemente el autor solo investigó una parte y compartió una solución incompleta. Tal vez quería provocar mejores respuestas en HN. Como dice el dicho: “si publicas una respuesta equivocada, obtienes la correcta más rápido”

• Parece que tampoco entendió que el modo WAL sigue siendo una estructura de una sola escritura y múltiples lecturas
  La escritura paralela no es posible; solo evita que las transacciones de lectura queden bloqueadas por una escritura
  Sería bueno tener MVCC completo, pero incluso la estructura actual funciona bastante bien si entiendes cómo opera

• Yo también tuve un problema parecido en Jellyfin
  Normalmente funciona bien, pero en ciertas situaciones la DB se queda bloqueada
  En los logs solo aparece “database is locked”, y al final la única solución es reiniciar el contenedor de Docker
  Suele pasar al presionar varios botones rápidamente en la UI de la TV

• Cambiando un poco de tema, si usas una DB in-memory de SQLite para muchas operaciones de insert/delete, el uso de memoria sigue creciendo
  Por ejemplo, si insertas y eliminas 100 mil filas cada 5 minutos durante varios días, en macOS la memoria sube hasta 1 GB
  Me pregunto si hay alguna configuración que valga la pena ajustar en esos casos

  • Recomiendo revisar si estás ejecutando VACUUM periódicamente y si auto_vacuum está activado
    Documentación de VACUUM
  • También podría ser un comportamiento normal donde el búfer se ajusta dinámicamente al patrón de uso
  • Si vas a borrar todas las filas, puede ser más eficiente simplemente eliminar la tabla y volver a crearla

• SQLite es excelente, pero viendo este tipo de problemas a veces da la impresión de que sería mejor usar Postgres
  Si no necesitas portabilidad en un solo archivo o un caso embebido, Postgres resuelve la concurrencia con menos complicaciones

  • Pero Jellyfin es un servidor multimedia self-hosted, así que exigir Postgres complica la instalación y el mantenimiento. SQLite encaja mejor
  • Jellyfin suele usarse en entornos domésticos de un solo usuario, así que SQLite basta. Aunque su configuración actual no parece la ideal
  • Ignorar las ventajas de SQLite e irse a Postgres es como decir: “quieres ir de campamento, mejor construye una cabaña”
  • Con Postgres no solo hay que instalarlo, también hay que ocuparse de las migraciones al actualizar versiones. SQLite no tiene esa carga
  • Jellyfin recientemente reescribió el código de la DB con Entity Framework, así que se está preparando para permitir una elección de base de datos más flexible en el futuro

[ndrgrd]

¿Eh? Había una parte que me hizo ruido, así que fui directo a revisar los comentarios, y efectivamente...