Sqlx connection pool timeout issue with tokio

help
1

Hi guys, I met one DB pool issue with SeaORM/LazyLock/tokio.

This is the demo code:

#[cfg(test)]
mod tests {
    use std::sync::LazyLock;

    use sea_orm::{ConnectionTrait, Database, DatabaseBackend, DatabaseConnection, Statement};
    use tokio::runtime::Runtime;
    use tracing::{debug, error, info};

    // this will block
    static DB_CONNECTION: LazyLock<DatabaseConnection> = LazyLock::new(|| {
        dbg!("in db connecting");
        let re = Runtime::new().unwrap().block_on(async {
            Database::connect("postgres://test_user:test_password@localhost:5432/test_db")
                .await
                .expect("db connect error")
        });
        dbg!("connected");
        re
    });

    #[test]
    fn test() {
        tracing_subscriber::fmt()
            .with_max_level(tracing::Level::DEBUG)
            .with_test_writer()
            .init();

        LazyLock::force(&DB_CONNECTION);

        let rt = Runtime::new().unwrap();

        rt.block_on(async {
            dbg!("in here?");
            let re = DB_CONNECTION
                .execute(Statement::from_string(
                    DatabaseBackend::Postgres,
                    "DELETE FROM test_table;",
                ))
                .await
                .unwrap();
            dbg!(re);
            dbg!("done");
        })
    }
}

Then I run env RUST_LOG="debug" cargo test. The stdout is

---- tests::test stdout ----
[src/main.rs:15:9] "in db connecting" = "in db connecting"
[src/main.rs:21:9] "connected" = "connected"
[src/main.rs:55:13] "in here?" = "in here?"
2025-01-11T17:54:21.109897Z DEBUG sea_orm::driver::sqlx_postgres: DELETE FROM test_table;

thread 'tests::test' panicked at src/main.rs:62:18:
called `Result::unwrap()` on an `Err` value: ConnectionAcquire(Timeout)
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

After some source code check, the debug log is here. I also check the acquire(), it in sqlx

I run some code change try to get more details.

Rather than use the static, I create it inside test():

    #[test]
    fn test() {
        tracing_subscriber::fmt()
            .with_max_level(tracing::Level::DEBUG)
            .with_test_writer()
            .init();

        // this also timeout
        let DB_CONNECTION: LazyLock<DatabaseConnection> = LazyLock::new(|| {
            dbg!("in db connecting");
            let re = Runtime::new().unwrap().block_on(async {
                Database::connect("postgres://test_user:test_password@localhost:5432/test_db")
                    .await
                    .expect("db connect error")
            });
            dbg!("connected");
            re
        });
        LazyLock::force(&DB_CONNECTION);

        let rt = Runtime::new().unwrap();
        rt.block_on(async {
            dbg!("in here?");
            let re = DB_CONNECTION
                .execute(Statement::from_string(
                    DatabaseBackend::Postgres,
                    "DELETE FROM test_table;",
                ))
                .await
                .unwrap();
            dbg!(re);
            dbg!("done");
        })
    }

It still timeout, same issue as use static.

If I don't use the LazyLock:

        let rt = Runtime::new().unwrap();

        // code below is ok
        let DB_CONNECTION = rt.block_on(async {
            Database::connect("postgres://test_user:test_password@localhost:5432/test_db")
                .await
                .expect("db connect error")
        });

        rt.block_on(async {
            dbg!("in here?");
            let re = DB_CONNECTION
                .execute(Statement::from_string(
                    DatabaseBackend::Postgres,
                    "DELETE FROM test_table;",
                ))
                .await
                .unwrap();
            dbg!(re);
            dbg!("done");
        })

It works well.

I don't know which part causes this issue. Does anyone have some ideas?

2

I also use the lazy_static re-write it:

#[cfg(test)]
mod tests {
    use lazy_static::lazy_static;
    use std::sync::LazyLock;

    use sea_orm::{ConnectionTrait, Database, DatabaseBackend, DatabaseConnection, Statement};
    use tokio::runtime::Runtime;
    use tracing::{debug, error, info};

    lazy_static! {
        static ref DB_CONNECTION: DatabaseConnection = {
            dbg!("in db connecting");
            let re = Runtime::new().unwrap().block_on(async {
                Database::connect("postgres://test_user:test_password@localhost:5432/test_db")
                    .await
                    .expect("db connect error")
            });
            dbg!("connected");
            re
        };
    }

    fn use_connection(db: &DatabaseConnection) {}

    #[test]
    fn test() {
        tracing_subscriber::fmt()
            .with_max_level(tracing::Level::DEBUG)
            .with_test_writer()
            .init();

        let rt = Runtime::new().unwrap();
        use_connection(&DB_CONNECTION);

        rt.block_on(async {
            dbg!("in here?");
            let re = DB_CONNECTION
                .execute(Statement::from_string(
                    DatabaseBackend::Postgres,
                    "DELETE FROM test_table;",
                ))
                .await
                .unwrap();
            dbg!(re);
            dbg!("done");
        })
    }
}

It has the same TimeOut issue. So I guess it isn't the LazyLock issue.

3

The issue is not related to LazyLock. In the working example you use the same Runtime::new().unwrap() both for Database::connect and DB_CONNECTION.execute and that's a key:

lazy_static! {
    static ref RUNTIME: Runtime = Runtime::new().unwrap();
    static ref DB_CONNECTION: DatabaseConnection = {
        let re = RUNTIME.block_on(...);
    }
    ...
}

fn test() {
    ...
    RUNTIME.block_on(async {
        ...
    }
    ...
}
4

Thanks for your reply. I tried your example and it works well (with the use_connection(&DB_CONNECTION); for initialization before entry the block_on).

If I understand correct, in test(), let rt = Runtime::new().unwrap(); and the initialization in lazy_static! are the different runtime instances of current thread.

Then, in the DB_CONNECTION.execute(), DB_CONNECTION is initialized and try to wait the outside rt.block_on release the lock for current thread (even the different runtime instances, but they block on thread), it causes the deadlock. Right?

But the dbg! I used in code for printing shows DB_CONNECTION is connected when reached use_connection(&DB_CONNECTION);. Why the execute() will affect the Runtime in lazy_static?

5

After several tries, there are more informations:

I run execute twice:

            let re = DB_CONNECTION
                .execute(Statement::from_string(
                    DatabaseBackend::Postgres,
                    "DELETE FROM test_table;",
                ))
                .await;
            dbg!(re);

            let re = DB_CONNECTION
                .execute(Statement::from_string(
                    DatabaseBackend::Postgres,
                    "DELETE FROM test_table;",
                ))
                .await;

And the second time look like it runs well.

6

Actually, I recreated the problem and figured out that blocking occurs because you created Runtime in such way Runtime::new().unwrap().block_on()

Instance of the Runtime immediatly deconstructs after the block_on invocation, it leads shutting down all Runtime resources.

Because the DB_CONNECTION was created in the deconstructed Runtime, the second Runtime "loses" control because the asynchronous communication resources are no longer maintained.

The runtime provides an I/O driver, task scheduler, [timer] (tokio::time - Rust), and blocking pool, necessary for running asynchronous tasks. <...> Once the runtime has been dropped, any outstanding I/O resources bound to it will no longer function. Calling any method on them will result in an error.

But if we won't shutdown Runtime we still be able to use it's resources, following code works fine:

let rt1 = Runtime::new().unwrap();
let connection = rt1.block_on(async {
    Database::connect("postgres://postgres:postgres@localhost:5432/postgres")
        .await
        .expect("db connect error")
});

Runtime::new().unwrap().block_on(async {
    connection.execute( ... ) // No problems
})

And following code will lead a timeout:

let connection = Runtime::new().unwrap().block_on(async {
    Database::connect("postgres://postgres:postgres@localhost:5432/postgres")
        .await
        .expect("db connect error")
});

Runtime::new().unwrap().block_on(async {
   connection.execute( ... ) // /!\ Timeout 
})

After all, tokio developers advise us not to use Runtime objects, but use #[tokio::main]

However, most users will use the #[tokio::main] annotation on their entry point instead.

1 Like
7

Thanks, that's make sense.