Tools: How We Fixed Sqlite Database Locks In Spring Boot (and Got A 5x...

Posted on Jan 30

• Originally published at paleblueapps.com on Jan 30

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.

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.

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.

We made several targeted configuration changes to our application.properties to optimize Spring Boot for SQLite:

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.

These settings ensure connections don't linger unnecessarily, reducing the chance of locks.

This helps us spot any connections that are held longer than expected, making debugging much easier.

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.

Disabling auto-commit gives us explicit control over transactions, preventing unexpected commits that could cause locking issues.

Source: Dev.to