We recently ran into a frustrating production issue: our Spring Boot application using SQLite would occasionally lock up, and the only way to recover was to restart the entire service. After some investigation and configuration changes, we not only fixed the locking issue but also saw dramatic performance improvements. Here's what we learned.
The Problem
Our production SQLite database would intermittently enter a locked state, causing requests to hang and eventually fail. The database would remain locked until we performed a manual restart; not exactly the kind of reliability we wanted in production.
The Root Cause
SQLite is designed differently from traditional client-server databases like PostgreSQL or MySQL. It's a file-based database that works best with single-connection access. However, Spring Boot's default connection pooling configuration doesn't account for SQLite's unique requirements, leading to connection conflicts and database locks.
The Solution
We made several targeted configuration changes to our application.properties to optimize Spring Boot for SQLite:
1. Single Connection Pool
spring.datasource.hikari.maximum-pool-size=1
spring.datasource.hikari.minimum-idle=0
SQLite performs best with a single connection. Multiple connections can lead to locking issues, so we configured HikariCP to maintain just one connection at a time.
2. Aggressive Connection Management
spring.datasource.hikari.connection-timeout=10000
spring.datasource.hikari.idle-timeout=10000
spring.datasource.hikari.max-lifetime=30000
These settings ensure connections don't linger unnecessarily, reducing the chance of locks.
3. Leak Detection
spring.datasource.hikari.leak-detection-threshold=5000
This helps us spot any connections that are held longer than expected, making debugging much easier.
4. Disable Open-in-View
spring.jpa.open-in-view=false
This was crucial. By default, Spring Boot keeps JPA connections open for the entire HTTP request lifecycle. We disabled this to ensure connections are released as soon as the database operation completes.
5. Manual Transaction Control
spring.datasource.hikari.auto-commit=false
Disabling auto-commit gives us explicit control over transactions, preventing unexpected commits that could cause locking issues.
The Results
The improvements were dramatic. We load-tested our API using k6 with identical test parameters:
Before the changes:
- Total requests in 5 minutes: 1,736
- Success rate: 76%
- Failed requests: 411
After the changes:
- Total requests in 5 minutes: 11,381
- Success rate: 99%
- Failed requests: 1
That's a 6.5x increase in throughput and virtually eliminated failures. More importantly, we haven't seen a single database lock since deploying these changes.
Key Takeaways
SQLite isn't PostgreSQL - Default Spring Boot configurations are optimized for client-server databases, not file-based ones.
Single connection is key - SQLite's architecture works best with one connection at a time.
Disable open-in-view - This JPA pattern is convenient but problematic with SQLite.
Monitor your connections - Leak detection helped us identify issues during development.
If you're using SQLite with Spring Boot, these configurations can save you from production headaches and give you some nice performance wins along the way.