resultSet.fetch() hangs if I execute read query before insert query is committed (insert and read must use same attachment)

[mreis1]

export async function run({log, connect}) {
    try {
        const attachment = await connect() as Attachment;
        const transaction = await attachment.startTransaction({
            accessMode: 'READ_ONLY',
            autoCommit: false,
            isolation: TransactionIsolation.READ_COMMITTED,
        });
        const transaction2 = await attachment.startTransaction({
            accessMode: 'READ_WRITE'
        });

        // >> INSERT SOMETHING
        let query = `INSERT INTO TEST_TABLE(ID, NAME) VALUES (?, ?)`;
        let r = await attachment.executeReturning(
            transaction2,
            query,
            [Date.now(), 'TEST_NAME']
        );

        // >> COMMIT 2s LATER
        setTimeout(async () => {
            try {
                log('Running transaction2.commitRetaining()');
                await transaction2.commit();
                log('Committed transaction that was hanging');
            } catch (err) {
                log('Error failed to commit parallel transaction');
            }
        }, 2000);

        // READ AS SOON AS POSSIBLE (while previous commit didn't took place)
        query = `SELECT COUNT(*) FROM TEST_TABLE`;
        log('Reading as soon as possible: ' + query);
        let resultSet3 = await attachment.executeQuery(
            transaction, query);
        log('Read completed. Fetching results....');
        let rows3 = await resultSet3.fetch();
        log('Fetch completed', rows3);
        log('Closing resultSet3', rows3);
        await resultSet3.close();
        log('Closed resultSet3');

        log(`ResultSet3: `, {resultSet3: JSON.stringify(rows3)});
        await transaction.commit();

        // DISCONNECT Later
        setTimeout(async () => {
            log('Disconnecting...');
            await attachment.disconnect();
            log('Disconnected!!');
        }, 3000)

    } catch (err) {
        log(err);
    }
}

Outputs:

DB2_005 Reading as soon as possible: SELECT COUNT(*) FROM TEST_TABLE [] DB2_005 Read completed. Fetching results.... [] DB2_005 Running transaction2.commitRetaining() []

If I run the same thing but in different attachments, it works:

DB2_005 Reading as soon as possible: SELECT COUNT(*) FROM TEST_TABLE [] DB2_005 Read completed. Fetching results.... [] DB2_005 Running transaction2.commitRetaining() [] DB2_005 Committed transaction that was hanging [] DB2_005 Fetch completed [ [ [ 16 ] ] ] DB2_005 Closing resultSet3 [ [ [ 16 ] ] ] DB2_005 Closed resultSet3 [] DB2_005 ResultSet3: [ { resultSet3: '[[16]]' } ] Finish... DB2_005 Disconnecting... [] DB2_005 Disconnected!! []

[asfernandes]

Looks like way Firebird works. Try transaction options:

    readCommittedMode?: 'NO_RECORD_VERSION' | 'RECORD_VERSION';
    waitMode?: 'NO_WAIT' | 'WAIT';

[mreis1]

Thank you Adriano.

Switching the transaction to 'NO_WAIT' will throw a deadlock (line 37) which make sense since I realized that readCommitedMode had a default value of 'NO_RECORD_VERSION' and by the moment the read query is executed the insert was not yet committed.

So updating line 4 to:

const transaction = await attachment.startTransaction({
            accessMode: 'READ_ONLY',
            autoCommit: false,
            isolation: TransactionIsolation.READ_COMMITTED,
            waitMode: 'NO_WAIT',
            readCommittedMode: 'RECORD_VERSION'
}); 

was enough to prevent the hanging but still.... I wasn't expecting this behaviour.

Let me explain:

This is my console output

DB2_005    Reading as soon as possible: SELECT COUNT(*) FROM TEST_TABLE []
DB2_005    Read completed. Fetching results.... []
DB2_005    Fetch completed [ [ [ 23 ] ] ]
DB2_005    Closing resultSet3 [ [ [ 23 ] ] ]
DB2_005    Closed resultSet3 []
DB2_005    ResultSet3:  [ { resultSet3: '[[23]]' } ]
Finish...
DB2_005    Running transaction2.commitRetaining() []
DB2_005    Committed transaction that was hanging []
DB2_005    Disconnecting... []
DB2_005    Disconnected!! []

And here's what I was expecting:

DB2_005    Reading as soon as possible: SELECT COUNT(*) FROM TEST_TABLE []

// Hang starts here because transaction2 is not yet committed
DB2_005    Running transaction2.commitRetaining() []
DB2_005    Committed transaction that was hanging []

// Commit took place, line 37 would get resolved. <<<

DB2_005    Read completed. Fetching results.... []
DB2_005    Fetch completed [ [ [ 23 ] ] ]
DB2_005    Closing resultSet3 [ [ [ 23 ] ] ]
DB2_005    Closed resultSet3 []
DB2_005    ResultSet3:  [ { resultSet3: '[[23]]' } ]
Finish...
DB2_005    Disconnecting... []
DB2_005    Disconnected!! []

In my opinion there are 2 options:

Option 1) After line 26 is executed line 37 should be resolved (hanging forever doesn't seem a good thing. am I wrong?)

Option 2) Line 37 should thrown an error

What do you think?

[asfernandes]

I still believe this is standard Firebird behaviour, it could be tested with ISQL to see if there is difference.