proper usage of unlock_notify in multithreaded write scenarios

[n00kii]

i am trying to process and insert a lot of data across multiple threads, using multiple connections to a single database table. of course this can't happen without some form of concurrency primitive usage, and from what i have gathered, unlock_notify allows statements/queries to block until the database is able to be accessed again.

i enabled it and was still having issues in the form of:

SqliteFailure(
        Error {
            code: DatabaseBusy,
            extended_code: 5,
        },
        Some(
            "database is locked",
        ),
    ),

i found that if you make a transaction that first does a read, then tries to do a write, the process of upgrading the concurrency lock from read to write will immediately fail if the database gets an exclusive lock. so i tried changing my transaction behavior from Deferred (default) to Immediate.

this helped alot, but some statements are still failing with the same error. where am i going wrong?

[gwenn]

https://sqlite.org/rescode.html#busy

The SQLITE_BUSY result code differs from SQLITE_LOCKED in that SQLITE_BUSY indicates a conflict with a separate database connection, probably in a separate process, whereas SQLITE_LOCKED indicates a conflict within the same database connection (or sometimes a database connection with a shared cache).

[thomcc]

SQLITE_BUSY usually means you have multiple threads with write transactions open, and at least one of them holds the lock longer than the busy timeout. It's best to avoid this kind of thing by design, if possible, although doing so can be very difficult.

[n00kii]

alright so my problem then is conflating SQLITE_LOCKED with SQLITE_BUSY and the fact that unlock_notify is only for SQLITE_LOCKED scenarios. not only that, but now that i think about it, my initial implementation of throwing everything into its own thread is naive due to disk io contention on my part. ill think about it some more. cheers