Lock SQLite connection in multi-thread context (Diesel ORM)

help
1

Not sure is it correct, but already found first issue with following implementation:

for stream in listener.incoming() {
    match stream {
        Ok(stream) => {
            thread::spawn({
                let acceptor = acceptor.clone();
                let database = SqliteConnection::establish(&argument.database).unwrap();
                move || {
                    connection::handle(acceptor.accept(stream).unwrap(), database);
                }
            });
        }
        // ..

When I'm delegating new 'raw' database connection to the new thread, everything work until another user (thread) starting insert/update records in the shared database.

So how can I lock the database properly - by using transactions or maybe with shared memory pools like deadpool or r2d2 crates? Or maybe with RwLock..

p.s. I expected that new db connection will work for new socket connection by SQLite 'server side', but nope, seems I should manage it.

2

for locking

sql_query("
BEGIN;
//OPERATION GOES HERE
COMMIT;
").execute(&connection)

this might useful for locking

1 Like
3

Thanks, is this transaction begin/commit on example or something else?

I'm learning Diesel ORM, found some notes about it have issues in multi-thread/sqlite combination.

Also tried:

  • wrap connection to Arc/RwLock and got error that sqlite-sys does not support trait with multi-thread implementation
  • implemented r2d2 pool with hope it has any automanagement for threads lock from the box, but nope, and of course the main goal of this pool is just single connection for multiple threads (to not open lot of connections)
  • tried to use transactions with Diesel, and now the thread panics on another thread locked it

I don't know what to do, have some stupid ideas to drop database from server and use in-memory storage only. All data storage move to single-thread client side, or just switch to Postgres - as remember there is locking auto-management for every client connected.

Before, I've locked server-side threads with semaphores, but now trying to implement multi-player server, with user registration feature - lock the database when somebody get started new registration and give read access to everyone when nobody registering at this moment. Found this subject much harder than expected)

4

there is problem using dissel in transaction because in single threaded enviriment trasaction is completed in the stack but in multi threaded the transaction cant be done in parallel so change to any other environment or use raw pg like postgres crate to hanlde mutliple thread

else use arc mutex to lock the state until completes but this would look like single threaded

or use bb8 crate

1 Like
5

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.