How to pass closure connection object to Async method for Postgress Transaction rollback

Question

In Postgres, I have many SQL transactions which are executed from different threads and need to rollback all the transactions if anyone fails

code is like this -

 dbconn.transaction::<_, DieselError, _>(  |conn| {

  tokio::spawn( insert_salary_table(conn) );
  tokio::spawn( insert_user_table(conn) );
  tokio::spawn( insert_user_table(conn) );

}

The problem here is, that I am not. able to borrow the conn object out of the transaction closure block

Any suggestion will be really helpful.

Note: without tokio::spawn it is working, but my requirement as per the use case to use an async call. I tried calling these async functions from runtime.block_on() but facing the same error.

Answer 1

You can do async stuff in a transaction with Diesel by using the diesel-async crate:

use scoped_futures::ScopedFutureExt;
use diesel_async::AsyncConnection;

dbconn.transaction::<(), DieselError, _>(|conn| async move {
    insert_salary_table(conn).await?;
    insert_user_table(conn).await?;
    insert_user_table(conn).await?;

    Ok(())
}.scope_boxed()).await;

Consult the documentation on transaction for more info. Since your functions are async, they should be using the diesel-async version of RunQueryDsl inside them anyway to avoid blocking your executor.

---

... transactions which are executed from different threads and need to rollback all the transactions if anyone fails

This will not enable your transaction to be multi-threaded, and cannot be. The conn provided is an exclusive reference and thus can't be used in two places simultaneously.