`PgAdvisoryLock::acquire` is not cancel safe (undocumented footgun)

[bonsairobo]

I have found these related issues/pull requests

I only saw a few issues related to PgAdvisoryLock and none of them mentioned cancellation safety.

But there is this other cancel safety issue: #2054

Description

If the PgAdvisoryLock::acquire future is dropped/cancelled, then it's possible it sent a lock query without actually creating a PgAdvisoryLockGuard. Because it's this guard object that issues the unlock query on Drop, the connection may be returned to the pool without ever unlocking. Now this lock remains alive in the pool, potentially causing deadlocks with other connections.

I'm not sure if this problem with the PgAdvisoryLock API can be fixed. My solution was to manually use pg_advisory_xact_lock (transaction-level locks) instead of session locks. This way the locks should get dropped on ROLLBACK.

But after doing a little digging, I'm now concerned the same issue is possible with begin, and it seems like there is a relevant issue: #2054

Reproduction steps

In my particular case:

Call PgAdvisoryLock::acquire inside of futures_util::try_join!, which cancels futures when any of them fails.

SQLx version

0.8.6

Enabled SQLx features

"macros", "migrate", "postgres", "runtime-tokio", "uuid"

Database server and version

Postgres

Operating system

MacOS 15.6

Rust version

1.93.1

[abonander]

We fixed begin in #3980 by just constructing the guard before the call. If the future is dropped it'll queue a rollback to be sent asynchronously. We can do the same thing here.