Share connection between threads?

[hu6360567]

I'm trying to share connection between threads for executing callbacks of different queries.
Although, scoped thread is used to prevent 'static lifetime requirement for spawning.
However, scoped thread is not able to be spawned, which capture the reference of the connection.
Because, neither Connection nor Cursor is not Send marked.

The problem is Can connection handle multi queries concurrently?

The following code works as expected.

let env = Envirnoment::new().unwrap();
let conn = env.connect_with_connection_string(&conn_str).unwrap();

let r1 = conn.execute(&query1).unwrap();
let r2 = conn.execute(&query2).unwrap();

consume(r1);
consume(r2);

My expected concurrent code as below, so that consuming results from different queries with the same connection can be executed concurrently.

crossbeam_tuils::thread::scope(|scope|{
    let env = Envirnoment::new().unwrap();
    let conn = env.connect_with_connection_string(&conn_str).unwrap();

    {
        let conn = &conn;
        scope.spawn(move |_| {
            let r = conn.execute(&query1).unwrap();
           consume(r);
        });
    }

    {
        let conn = &conn;
        scope.spawn(move |_| {
            let r = conn.execute(&query2).unwrap();
           consume(r);
        });
    }
};

[pacman82]

Hello @hu6360567 ,

The problem is Can connection handle multi queries concurrently?

Short answer: They should be able to do that.

You can opt into making Connection Send.
See: https://docs.rs/odbc-api/0.29.1/odbc_api/struct.Connection.html#method.promote_to_send

According to the ODBC standard this should always work. According to your driver and system configuration it may not, and you may get race conditions. Tests are your friend here.

Hope that helps.

Cheers, Markus

[pacman82]

Just reading through this a second time. If you are only worried about consuming in different threads, why not just move the result (r) into threads, and call execute exclusively on the main branch?

If it turns out you need to use the same connection on different threads, you may want to use a Mutex around the connection, too. Connection is not Sync. So you can not send &Connection without it.

Depending on the use case you may want to think about just creating the connection within the thread, and enabling connection pooling.

Cheers, Markus

[hu6360567]

Hi @pacman82,

Just reading through this a second time. If you are only worried about consuming in different threads, why not just move the result (r) into threads, and call execute exclusively on the main branch?

What I'm consuming is the cursor, which retriving and consuming the result are taken place in scoped thread, but the cursor is not Send marked yet.

ConnectionPool is a better solution, I'll have a try.

[pacman82]

Hello @hu6360567 ,

just in case you have not spotted it yet: https://docs.rs/odbc-api/0.29.1/odbc_api/guide/index.html#connection-pooling

Tell me how it goes.

Cheers, Markus

[pacman82]

Hello @hu6360567 ,

how did it go? Could you achieve with this crate, what you set out to do?

In the meanwhile into_cursor has been added, which might give you an easier time with the borrow checker:

https://docs.rs/odbc-api/0.29.3/odbc_api/struct.Connection.html#method.into_cursor

Cheers, Markus

[pacman82]

@hu6360567 Closing this issue for now. Feel free to open a new one, if you feel the library should support this better.