Las transacciones de SQLite

• En los últimos años, SQLite ha ganado mucha atención como un motor de base de datos SQL en proceso, altamente confiable, para usarse como backend de procesos de servidor
• Su popularidad se ha disparado a pesar de que los desarrolladores de SQLite casi desaconsejan activamente su uso para este propósito, en lugar de su rol tradicional como aplicación cliente o de edge

Razones principales por las que me interesé en SQLite:

• Simplicidad conceptual: imagina un árbol B de filas/tuplas particionado por clave primaria. Ha sido probado extensamente para persistirse de forma confiable en disco, y encima se le agrega una capa de interacción SQL
• Con Litestream se puede establecer una estrategia de respaldo práctica. Respalda el WAL a una ubicación remota y lo replica continuamente. Los respaldos pueden restaurarse automáticamente al inicio con un comando simple.
• Sigo prefiriendo un entorno de desarrollo completo que pueda ejecutarse sin conexión
• Con file::memory: se puede trabajar en memoria, así que el código de prueba puede iniciar y cerrar instancias fácilmente cuando sea necesario

Límite de single-writer

• Los desarrolladores de SQLite han documentado bien las "limitaciones de SQLite en el servidor" y han analizado la mejor configuración posible del lado del servidor. Pero la limitación más evidente son los sitios web de alto tráfico, es decir, los sitios con muchas escrituras
• En modo WAL, SQLite usa por diseño un único writer. Esto permite como máximo 1 transacción de escritura al mismo tiempo y múltiples transacciones de solo lectura
• Este diseño hace que el cuello de botella de los sitios web con mucho tráfico orientado a escritura quede en la gestión del throughput de ese único writer. Esto nos devuelve a uno de los componentes centrales de la tecnología moderna

SQLite

• SQLite ofrece por defecto transacciones con aislamiento estricto SERIALIZABLE. Este es el nivel más fuerte de garantía de aislamiento
• Al usar un único writer, SQLite emplea una forma de control de concurrencia pesimista que puede garantizar fácilmente que los datos subyacentes no hayan cambiado mientras una transacción de escritura está en curso

Postgres

• Postgres en realidad difiere del valor predeterminado SERIALIZABLE definido en el estándar SQL y elige el más relajado READ COMMITTED (a pesar de un control de concurrencia multiversión mucho más complejo)
  • Esta menor rigurosidad implica el riesgo de non-repeatable reads; es decir, incluso dentro de la misma transacción, ejecutar varias veces la misma consulta de lectura puede devolver resultados distintos si otra transacción COMMITTED cambió el valor en segundo plano
  • Al elegir este nivel de aislamiento, Postgres deja abierta la posibilidad de que una transacción opere sobre datos obsoletos. El desarrollador debe tenerlo presente
• Si se configura como SERIALIZABLE, Postgres usa un esquema de optimistic-concurrency control para rastrear los datos accedidos durante la transacción y verificar antes del commit que no hayan cambiado
  • Postgres hace esto basándose en bloqueos a nivel de fila o de página según la transacción, para gestionar el uso de memoria
  • Este patrón se llama optimistic porque se espera que los datos subyacentes no cambien, ya que cuanto más granular sea la información monitoreada por la transacción al momento del commit, menos probable es que cambie

FoundationDB

• Las transacciones no se limitan a las bases de datos relacionales. También se usa optimistic concurrency control para lograr garantías SERIALIZABLE en almacenes distribuidos de clave-valor
• Cuando apareció NoSQL, no era común encontrar almacenes distribuidos NoSQL con garantías ACID. FoundationDB publicó un manifiesto sobre transacciones para destacar que los desarrolladores pueden beneficiarse enormemente de las garantías ACID
• FoundationDB ofrece orientación sobre cómo y cuándo escribir código para optimistic concurrency control, y sobre el hecho de que a veces los datos cambian por conflictos entre transacciones concurrentes y la transacción se reintenta automáticamente

Idempotencia

• Una transacción idempotente es una transacción que tiene el mismo efecto si se confirma una vez o dos veces
• FoundationDB ofrece patrones para hacer que las transacciones sean idempotentes, evitando problemas si es necesario reintentarlas varias veces debido a conflictos

Entonces, teniendo todo esto en mente, ¿qué opciones ofrece SQLite?

BEGIN …

SQLite ofrece varias formas para que los desarrolladores indiquen al motor cómo debe comportarse una transacción, mediante las palabras clave IMMEDIATE, EXCLUSIVE y DEFERRED, que en modo WAL pueden reducirse básicamente a DEFERRED vs IMMEDIATE

DEFERRED

• La transacción comienza en modo READ, por lo que puede ejecutarse al mismo tiempo que otras transacciones de lectura o escritura
• Solo se actualiza a una transacción bloqueante READ-WRITE si se ejecuta una consulta que modifica el estado de la BD (INSERT, UPDATE, DELETE)
• Si al actualizarse la BD está bloqueada por otra transacción, devuelve un error SQLITE_BUSY. El cliente debe manejarlo

IMMEDIATE

• La transacción comienza inmediatamente en modo READ-WRITE
• Si ya hay una transacción de escritura en ejecución, devuelve SQLITE_BUSY de inmediato
• El cliente debe decidir cómo manejarlo

CONCURRENT

• SQLite tiene una rama experimental que mueve las transacciones de un enfoque pesimista a uno optimista limitado
• Es limitado porque el optimistic locking opera a nivel de página de BD (4096 bytes por defecto), no a nivel de fila/tupla
• En modo CONCURRENT, SQLite puede mantener activas varias transacciones de escritura al mismo tiempo, pero antes del commit verifica que las páginas accedidas durante la transacción no hayan cambiado desde que esta comenzó
• Si no hay conflictos, los cambios se confirman secuencialmente y se logran garantías estrictas SERIALIZABLE. Si hay conflicto, devuelve SQLITE_BUSY

HC-Tree

• Otra rama experimental de SQLite es [HC-Tree], un trabajo en curso que busca ofrecer locking optimista a nivel de fila/tupla. Uno de sus resultados interesantes es que proporciona un excelente conjunto de benchmarks que muestra las ventajas de rendimiento de ese diseño frente a la rama BEGIN CONCURRENT

¿Qué tal si tomamos su enfoque de benchmarking y lo ejecutamos sobre las opciones estándar?

Benchmarking

nUpdate=1, nScan=0

• Esta transacción de solo escritura muestra claramente la ventaja de IMMEDIATE frente a DEFERRED. El locking ocurre de inmediato y la transacción no se ve afectada por el costo de actualizarse de modo
• CONCURRENT muestra un aumento de throughput conforme crece el número de hilos y aumentan los conflictos

nUpdate=10, nScan=0

• Como era de esperarse, agrupar escrituras ayuda mucho con 16 hilos en términos del número de filas actualizadas. CONCURRENT aumenta de ~12k/sec a ~19k/sec
• IMMEDIATE vs DEFERRED se vuelve menos importante. El costo de las propias actualizaciones pasa a ser más importante que el costo de actualizar la transacción de modo

nUpdate=1, nScan=10

• Esta transacción debería exponer la debilidad del locking CONCURRENT a nivel de página debido a las lecturas aleatorias
• Muestra enseguida por qué usar IMMEDIATE para una transacción que va a actualizar es más importante que el costo de actualización desde DEFERRED
• En el caso de CONCURRENT, estos resultados son muy sólidos porque los conflictos subyacentes en realidad no aumentaron mucho

nUpdate=0, nScan=10

• Esta transacción por lotes de solo lectura muestra el impacto del control de concurrencia pesimista
• Muestra por qué no conviene establecer IMMEDIATE como valor predeterminado para todas las transacciones
• CONCURRENT vs IMMEDIATE indica que hay una pequeña desventaja al usar el modo CONCURRENT. "El rendimiento cae un poco en todos los casos"
  • Aun así, CONCURRENT sería una buena opción predeterminada