SqliteError 262 database table is locked

[apexys]

Hi!

I'm trying to use sqlx with an SqlitePool as a caching backend for an actix web application.

Several worker threads compute data, which can take a long time to produce and then store it into a table.

When this data is requested, the worker thread first queries the database before computing, to see if the result already has been computed, to save time. If the data isn't in the database, we recompute it and store it in the DB before sending it out.

The DB layout is basically just one table cache with key TEXT, data BLOB and I just use normal SELECT data from CACHE where key = ? prepared statements to retrieve and normal inserts to put the data into the DB.

I had hoped that the pool feature would just make my multithreading worries go away, but sadly I still got error 262 database table is locked.

Reading up a bit online I found documentation that usually enabling WAL should make the problem go away (it's already enabled) or that multithreading could cause it, which I thought unlikely, since that's sort of what a connection pool is for.

Anyhow, I wrapped the Pool in a Mutex and tried using that and the error still occured.

Any ideas what I am doing wrong? Do I need to use transactions for this?

[apexys]

I also tried adapting the SqliteConnection creation to

let pool = block_on(SqlitePool::builder().max_size(1).build("sqlite:cache.db")).unwrap();

but I still get the error.

[abonander]

Wrapping the pool in a Mutex effectively does very little as it has its own internal synchronization.

The error code directly suggests this is due to our use of SQLITE_OPEN_SHAREDCACHE which makes write transactions mutually exclusive between connections sharing the cache.

We should remove this flag.

In the mean time, you can prevent this error by setting the pool size to 1 when you build it. That effectively makes it operate as a mutex over a single connection internally.

[dignifiedquire]

fyi https://stackoverflow.com/questions/57123453/how-to-use-diesel-with-sqlite-connections-and-avoid-database-is-locked-type-of

[apexys]

So the error happens much less with Pool Size = 1, but it still comes up from time to time. The data size I write is around 1MB, so it could be that maybe some things are not flushed yet?

For what was suggested in the stackoverflow answer, I think sqlx is already using the pragmas
PRAGMA synchronous = NORMAL; PRAGMA journal_mode = WAL;, see https://github.com/launchbadge/sqlx/blob/master/sqlx-core/src/sqlite/connection.rs#L126 and #296

Thanks a lot for your answers. This database layer is really making my life a lot easier at the moment and I'm optimistic the few remaining problems will vanish soon!

[mehcode]

It's possible for SQLx to handle the locked notification like a mutex and unlock when we can. Hopefully we can improve on that soon.

@abonander is spot on though. We should have always been using private cache over a shared cache.

[dignifiedquire]

Still getting this error with poolsize 1 :/

[alun]

One thing I've noticed to solve this is cursor should be fully evaluated even when querying a single row:

async fn last_uid() -> commons::AsyncResult<Option<String>> {
  let mut cursor = sqlx::query(r#"SELECT uid FROM trades ORDER BY time DESC, batch_idx LIMIT 1"#)
    .fetch(get_pool().await);
  let result: Option<String> = if let Some(row) = cursor.next().await? {
    Some(row.get::<String, _>(0))
  } else {
    None
  };
  // THIS IS MANDATORY otherwise getting "blocked" error with some of the calls
  while let Some(_) = cursor.next().await? {}
  Ok(result)
}

[mehcode]

This should be fully fixed on master. SQLITE_LOCKED was caused by us naively using a shared cache. SQLITE_BUSY is caused for various reasons ( too many to list, see docs ) and we now enable SQLite's busy timeout ( which runs in a worker thread or blocking IO queue depending on the runtime ).

[apexys]

So I just tried the code from the master branch and apparently this still happens.
I initialize the database with
let pool = SqlitePool::builder().max_size(num_cpus::get() as u32).build("sqlite:cache.db").await.unwrap();
and then create a table and an index on that table.
I only use straight insert and select queries, no transactions (or.. do I need to use those? data consistency is not really that important, just using it as a cache and I have a method of checking for broken data externally) and I still get that code 517 database is locked error on pretty much every insert.

What could cause this?