I have a multithreaded app that uses a SQLite DB for persistent storage. The application uses one persistent connection. It prepares the statements at startup and after each rollback. It's also using journal_model=WAL
to help with the concurrent access.
I'm finding that when my listUsers()
function is called concurrently that occasionally the results seem to leak between calls. I know there's 100 users but sometimes the results are 100+ and I think it's because the sqlite3_step
on one thread is pulling results from the sqlite3_step
that could have been called on another thread.
Is that possible?
Also, I've tried wrapping the listUsers()
in a transaction but occasionally it will throw errors that a transaction is being started inside another transaction. Again, I think this is because two threads are sharing the same DB connection.
Is there a common solution to this issue? One connection per thread? I'm not so concerned with the occasional locked database or long waits, it's just invalid results that are worrisome.
If it's helpful, my listUsers()
looks like
std::vector<User> listUsers()
{
char* err = 0;
auto users = std::vector<User>();
sqlite3_reset(list_user_stmt);
beginTransaction();
while (SQLITE_ROW == sqlite3_step(list_user_stmt))
{
auto id = sqlite3_column_int(list_user_stmt, 0);
auto name = sqlite3_column_text(list_user_stmt, 1);
users.push_back(User{id, name});
}
commitTransaction();
return users;
}
You can not use the same connection from multiple threads simultaneously. SQLite the database is safe for concurrent access, but a single connection is not (well as far as I know it will be 'thread safe' in the sense that it's not actually going to corrupt any data, but it will not behave like you'd want in this case – in particular there is no transaction isolation across a single connection, as that doesn't really make any sense). If you do need to access the thread from multiple threads at the same time, you either put a mutex on the connection and only have one thread access the database at any one time (usually not needed for SQLite, but if e.g. you cache some results at application level you might need to do that to keep your cached view consistent), have a connection per thread or use a connection pool as a reasonable middle ground if you have a lot of threads.