If secrets are rotated while a connection to RDS is currently open, will that connection still be able to query the database, or will it become inactive?
If you rotate the password for a user account, users will be unable to initiate new connections to the database until they fetch the new password. Existing connection will continue to work.
A common strategy is to have two user accounts (user1 and user2) and rotate their passwords on a staggered schedule. The credentials for user1 will still work while the clients detect user2 and start using it. Note for this to be effective the clients will have to check for updated credentials periodically.
https://docs.aws.amazon.com/secretsmanager/latest/userguide/rotating-secrets-two-users.html