Set transaction isolation level

[tgriesser]

It'd be nice to have a way to specify the isolation level on the transaction

Also mentioned by @llambda in #138, it'd be nice to have both a better api for injecting connection specific config default settings (currently it's possible with an afterCreate hook)

[rprieto]

This would definitely be nice 👍
AFAIK, the current workaround looks something like (for Postgres):

knex.transaction(function(trx) {
   return trx
   .raw('set transaction isolation level repeatable read;')
   .then(function() {
      return trx.select(/* ... */);
   });
})

Does this look correct to you?
And were you thinking of something like these?

knex.transaction({isolation: 'repeatable read'}, function(trx) { /* ... */ });
knex.transaction.repeatableRead(function(trx) { /* ... */ });

[bendrucker]

Your workaround looks correct. As for the new feature, I'd go with the first version but invert the arguments. Callback first, options second.

[rprieto]

Thanks! I suggested the callback as the last argument for CoffeeScript / ES6 support, where the (trx) => function syntax makes it much easier if there's nothing behind it. But you'd have to add argument type checking to keep it backwards-compatible. I guess otherwise we'll just extract that function and call knex.transaction(fn, {isolation: '...'}).

[cspotcode]

@rprieto's workaround won't work for MySQL, since "SET TRANSACTION ISOLATION LEVEL" applies to the next transaction, not the current one.

From: https://dev.mysql.com/doc/refman/5.0/en/set-transaction.html

Without any SESSION or GLOBAL keyword, the statement sets the isolation level for the next (not started) transaction performed within the current session. Subsequent transactions revert to using the SESSION isolation level.

[hendrul]

:( This is a blocker for me, and is sad cuz I really like bookshelf/knex combination. If there where some work around to this for mysql I'll be very happy to hear.

[elhigu]

@rprieto's suggestion for the backwards compatible API looks good to me. #581 (comment)

EDIT: deleted some "any news status update spam" if there are news, you can see them from this issue comments.

[kibertoad]

@honestserpent I could try working on it. Which databases are you interested in? Would you consider contributing sample SQLs to illustrate what expected output is?

[officer-rosmarino]

@kibertoad I'm working on Postgres.
As far as the sample SQL that I would expect is simply: begin transaction isolation level repeatable read;

[Recodify]

For mysql you can simply do:

 await knex.raw('SET TRANSACTION ISOLATION LEVEL READ COMMITTED;');
        await knex.transaction(async (trx) => {
              ...

This will set the transaction isolation level to READ COMMITTED for the next transaction only.
See the section 'Transaction Characteristic' on: https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html

[elhigu]

@Recodify That doesn't work unless you manage to make sure that those both queries are sent through same connection to DB server. Knex doesn't guarantee that even if pool size is 1 (it might automatically refresh broken/timed out connection).

[Recodify]

@elhigu you're right! If I print the current isolation level I can see that indeed it still prints the default

const isolationLevel = await this.knex.raw('SELECT @@tx_isolation as result;').transacting(trx);
            console.log(isolationLevel[0][0].result); // prints REAPTABLE READ

The strange thing is that it DOES change the behavior of the following code...which is what led me to believe it worked. I'm missing something, will continue to investigate

[elhigu]

With some luck it works, but it is not guaranteed to work. So it may fail for example if there are more concurrent queries executed at the same time. If it works it just means that knex did return the same connection for both of the queries.

[Recodify]

It has to be more than luck, I've just hit it with 5000 concurrent requests with connection pool settings of: pool: { min: 0, max: 20 } and it works as expected with the behavior clearly that of READ COMMITTED, removing the line that sets this isolation level results in a pretty fast fail due to REPEATABLE READ behavior.

[elhigu]

In next knex version you can setup listeners to pool internal operations and see why it returns always the same connection for both of the queries in that case. You can also try break it more by adding await sleep(10); between those two queries to simulate more extreme conditions. You can also check to which connection queries are sent from debug prints when you start knex with environment variable DEBUG=knex:* set.

[elhigu]

Anyways this is all really unrelated to this issues, which is about having real support by knex to set the isolation level.

[ayroblu]

Okay, so how do I do this with mysql, mssql? The set transaction isolation level must be done prior to the begin transaction step, and the knex.transaction() doesn't support a connection parameter to force it to be on the same connection.

[ayroblu]

Implemented as knex.transaction({isolationLevel: 'repeatable read'}) and similar FYI

[Venryx]

For PostgreSQL, I'm currently using this and it works fine:

await transaction.raw("SET CONSTRAINTS ALL DEFERRED;"); // call this before the other actions within the transaction

Is my code above equivalent to knex.transaction({isolationLevel: 'repeatable read'})?
If not, what are the differences?

[ayroblu]

For PostgreSQL, I'm currently using this and it works fine:

await transaction.raw("SET CONSTRAINTS ALL DEFERRED;"); // call this before the other actions within the transaction

Is my code above equivalent to knex.transaction({isolationLevel: 'repeatable read'})?
If not, what are the differences?

Isolations level are very specific ideas relating to what happens when other transactions try to reference the same data that you're using in your current transaction, basically from no checks to full table locks / aborts

The deferred constraint checking is whether checks occur immediately or at the end of the transaction

[jordaoqualho]

In case you need to specify an isolation level for your transaction, you can use a config parameter isolationLevel. Not supported by oracle and sqlite, options are read uncommitted, read committed, repeatable read, snapshot (mssql only), serializable.

const isolationLevel = 'read committed';
const trx = await knex.transaction({isolationLevel});
const result1 = await trx(tableName).select();
await knex(tableName).insert({ id: 1, value: 1 });
const result2 = await trx(tableName).select();
await trx.commit();

[remram44]

I came here from Google but it seems the feature is implemented by #4185 (knex.transaction({isolationLevel})). Perhaps this issue can be closed?

[julianCast]

Doing something like:

Causes this:

 TypeError: container is not a function

      at node_modules/knex/lib/execution/transaction.js:233:22

What am I doing wrong?

[remram44]

Where are you calling knex?

[julianCast]

Where are you calling knex?

Im passing it as a CB from the objection Model.
return DBModel.transaction((trx) => transactionSequence(trx));
whereas trx is a Knex.Transaction

[remram44]

You set the isolation level wherever you get that Transaction from knex.