Sqlite3 Locks

[japananh]

Problem

Today, I ran into an issue with Sqlite3 locks.

We used Sqlite3 for unit testing and ProgreSQL for running services. One of the main reasons for using Sqlite3 for unit testing is that it is a lightweight database management system, and the data is stored in a file. This makes running multiple unit tests in a separate database easier without needing a dedicated server or complex setup.

But it has a disadvantage due to its simplicity. When I added unit tests for a function that created some go routines to increase test coverage, the tests failed due to an error database table is locked.

The root cause was that my function created many go routines that select/update to DB, and Sqlite3 didn't support well for concurrent requests. In dev/staging/prod environment, we didn't see that issue due to

Solution

There are some solutions that I tried to apply:

• Check whether if dialect Sqlite3 then set numbers of go routine = 1: Most effective! Solved my problem. But if the requests increase, we must replace sqlite3 with another database.
• Enable WAL: WAL helps reduce the table lock by writing data changes to a file called WAL (write-ahead log) and then from WAL to the database. I enable it but see no change.
• Enable recursive triggers: I enable it but see no change.

References

• https://sqldocs.org/sqlite/sqlite-locks/
• https://sqldocs.org/sqlite/sqlite-database-is-locked/