Sequelize doesn't recognize `COMMIT PREPARED :id` and 'ROLLBACK PREPARED :id` statements to close database connections.

[Mamun724]

Issue Creation Checklist

• [x] I understand that my issue will be automatically closed if I don't fill in the requested information
• [x] I have read the contribution guidelines

Bug Description

We had created an issue reporting that sequelize is giving 'SequelizeConnectionAcquireTimeoutError' error after executing 5 prepared transactions. After some further analysis, we have found that we are not committing or rolling back the transaction explicitly and that's why the connection is not closed. But we are running raw queries to commit the prepared transaction or roll back the prepared transaction. This should handle the connection-closing task.

if I commit or rollback the transaction(involving prepared transaction), then the connection get's closed. is the following snippet valid for a prepared transaction scenario?

// sequelize1 & sequelize2 are two different sequelize instances, connected to different databases.
const sequelize1 = db1.connection;
const sequelize2 = db2.connection;

const id = crypto.randomUUID();
let firstTransactionPrepared = false;

const transaction1 = await sequelize1.transaction(); // In Sequelize 7, this method is called unmanagedTransaction()
try {
    const transaction2 = await sequelize2.transaction();

    //Start of actual task to do
    try {
        await test_table_1.create(
            {
                first_name: user.first_name
            },
            {
                transaction: transaction1
            }
        );

        await test_table_2.create(
            {
                last_name: user.last_name
            },
            {
                transaction: transaction2
            }
        );
        //End of actual task to do

        // Prepare & commit
        await sequelize1.query('PREPARE TRANSACTION :id', {
            replacements: { id },
            transaction: transaction1,
        });
        firstTransactionPrepared = true;

        await transaction2.commit();

        await sequelize1.query('COMMIT PREPARED :id', {
            replacements: { id },
            transaction: transaction1,
        });
        await transaction1.commit(); //is the usage of this line correct?
    } catch (error) {
        await transaction2.rollback();
        throw error;
    }
} catch (error) {
    if (firstTransactionPrepared) {
        await sequelize1.query('ROLLBACK PREPARED :id', {
            replacements: { id },
            transaction: transaction1,
        });
        await transaction1.rollback(); //is the usage of this line correct?
    } else {
        await transaction1.rollback();
    }

    throw error;
}

Reproducible Example

Here is the link to the SSCCE for this issue. Please update the database URLs in the .env file at the root before testing.

Here is the link to the SSCCE for this issue:

What do you expect to happen?

Sequelize should detect Commit prepared and rollback prepared statements and close the database connection.

What is actually happening?

Sequelize is not closing a database connection after a 'commit prepared' or 'rollback prepared' statement.

Environment

• Sequelize version: v6.32.1
• Node.js version: v16.20.1
• If TypeScript related: TypeScript version: not used
• Database & Version: PostgreSQL 14
• Connector library & Version: [email protected]

Would you be willing to resolve this issue by submitting a Pull Request?

• [ ] Yes, I have the time and I know how to start.
• [x] Yes, I have the time but I will need guidance.
• [ ] No, I don't have the time, but my company or I are supporting Sequelize through donations on OpenCollective.
• [ ] No, I don't have the time, and I understand that I will need to wait until someone from the community or maintainers is interested in resolving my issue.

---

Indicate your interest in the resolution of this issue by adding the 👍 reaction. Comments such as "+1" will be removed.

[ephys]

We don't try to determine what someone is doing by reading the query, so we can't mark the transaction as finished on our end like this

We'd have to support prepared transactions out of the box

In the meantime, you could use sequelize.withConnection (v7): It lets you access a connection with no associated transaction. You'd have to manage the entire transaction part yourself. See #15851

If that's fine by you, we can turn this bug report into a feature request for prepared transactions