Node-Firebird Invalid transaction handle

Question

I got an error when trying to commit in sequence with the same transaction. I am using Node-firebird lib. The first time is committed, but the second time I got the following message:

Error:

> invalid transaction handle (expecting explicit transaction start) at
> doCallback
> (C:\Ultra\PreVendaServerV2\node_modules\node-firebird\lib\index.js:1297:21)
> at
> C:\Ultra\PreVendaServerV2\node_modules\node-firebird\lib\index.js:3019:25
> at
> C:\Ultra\PreVendaServerV2\node_modules\node-firebird\lib\messages.js:151:25
> at search
> (C:\Ultra\PreVendaServerV2\node_modules\node-firebird\lib\messages.js:117:13)
> at
> C:\Ultra\PreVendaServerV2\node_modules\node-firebird\lib\messages.js:54:21
> at FSReqCallback.wrapper [as oncomplete] (fs.js:520:5) at
> FSReqCallback.callbackTrampoline (internal/async_hooks.js:126:14) {
> gdscode: 335544332, gdsparams: undefined

I am trying to insert the CHILDREN when I get the error message with the same transaction.

    public static async getTransaction(db: Database, ISOLATION_LEVEL?: number[]): Promise<Firebird.Transaction> {
        if (!db) throw "There is no Connection with the database!";
        const ISOLATION = ISOLATION_LEVEL ? ISOLATION_LEVEL : Firebird.ISOLATION_READ_COMMITED;

        return new Promise(
            (resolve, reject) => {
                db.transaction(ISOLATION, function (err, transaction) {
                    if (err && LOG_SQL) { console.log(err) };
                    if (err) reject(err);
                    resolve(transaction);
                });
            });
    }

    public static async asyncInsert(insertSQL: string, params?: any[], insertChildrenCallback?: any, originObject?: any, transaction?: Firebird.Transaction, commit?: boolean) {
        try {
            const db = await FirebirdPromise.attach();
            if (!transaction) transaction = await this.getTransaction(db);

            const insertedId = await this.execQueryWithTransaction(insertSQL, transaction, params, originObject);

            if (insertChildrenCallback) await insertChildrenCallback(insertedId, originObject, transaction, db);
            if (commit) await this.tryCommit(transaction, db);

            return new DatabaseResult({
                status: DatabaseResult.RESULT_OK, message: null, data: insertedId
            });
        } catch (error) {
            return new DatabaseResult({
                status: DatabaseResult.RESULT_ERROR, message: error, data: null
            });
        }
    }



public static async tryCommit(transaction: Firebird.Transaction, db: Database): Promise<Boolean> {
    return new Promise(
        (resolve, reject) => {
            transaction.commit(function (err) {
                if (err) {
                    if (LOG_SQL) console.log(err);
                    transaction.rollback();
                    reject(err);
                    throw err;
                }
                db.detach();
                resolve(true);
            });
        });
}

Show the code where you are creating the transaction. Specifically where the `transaction` variable is initialized. — tbking, Nov 24 '20 at 19:55
"*Trying to Commit in sequence with the same Transaction*" - I don't know firebase in particular, but I doubt that is possible in any database. — Bergi, Nov 24 '20 at 20:44
Please provide a [mre]. My guess is that you are trying to reuse the transaction handle after it has already been committed, but given you don't show the actual calls to those insert methods, etc, that is just a guess. If you commit a transaction, you need to obtain a new transaction handle. — Mark Rotteveel, Nov 25 '20 at 08:43
@Bergi there is `commit retaining` and `rollback retaining` operations in Firebird (NOT unrelated Google Firebase), but they surely are special case that is not used often. I don't think i personally ever used them, for example. Normal expectation without doubt is that ending a transaction is ending the transaction. — Arioch 'The, Nov 25 '20 at 12:11
@Arioch'The Commit retaining ends the transaction, it just retains the transaction handle and open cursors, and starts a new transaction on the same handle. — Mark Rotteveel, Nov 25 '20 at 13:10
@MarkRotteveel that is implementation details, for the application/lib it seems like the transaction is "still alive" under old handle. Your drift is probably that different transaction ID gets stored into OIT/OAT and record versions (also MON-tables and `CURRENT_TRANSACTION` perhaps)? But even if so, frankly, why would a regular client application even bother? However, it was just a minor comment to Bergi, this capability is hardly for typical use. — Arioch 'The, Nov 25 '20 at 14:12

score 1 · Answer 1 · edited Nov 25 '20 at 11:49

1

It seems like you are trying to reuse a transaction in multiple function calls. Try doing this:

const db = await FirebirdPromise.attach();
transaction = await this.getTransaction(db);

Instead of getting transaction from the argument, and committing it multiple times, you should generate the transaction every time and then commit it. For the next insert, generate another transaction.

edited Nov 25 '20 at 11:49

Mark Rotteveel

100,966
191
140
197

answered Nov 25 '20 at 10:24

tbking

8,796
2
20
33

Alternatively, the OP should generate a single transaction, and pass it for each insert, and only commit when all inserts are complete. – Mark Rotteveel Nov 28 '20 at 11:56
1

Agreed, the above code change is based on the code shared. I'm pretty sure there si a better way to do it given enough context. However I will leave it for OP to find the best way. – tbking Nov 28 '20 at 12:20

Node-Firebird Invalid transaction handle

1 Answers1