Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Parallel transactions #1806

Open
nicholaswmin opened this issue Nov 28, 2016 · 4 comments
Open

Parallel transactions #1806

nicholaswmin opened this issue Nov 28, 2016 · 4 comments

Comments

@nicholaswmin
Copy link

nicholaswmin commented Nov 28, 2016

Is there any way to get a transaction working in parallel?

Here's the problem

A transaction needs to execute all it's queries on the same connection, thus calls to Promise.all() are fast when executed not as part of a transaction, and slow when executed as part of a transaction

// can be called as part of a transaction, or not
getAllData: function(trx, idCustomer) {
  return new Promise((resolve, reject) => {
    let data = {};
    
    Promise.all([
      getFoo(trx); // knex query using `trx` as the knex instance
      getBar(trx); // knex query using `trx` as the knex instance
    ]).then((result) => {
      data.foo = result[0];
      data.bar = result[1];
      resolve(data);
    }).catch((err) => {
      reject(err);
    })
  });
});

I could use knex.raw() and do actual multiple-statements queries in one go but that would couple the DB calls with the DB,thus eliminating knex's added value of being a DB abstraction layer

@nicholaswmin nicholaswmin changed the title Parallel transactions in knex Parallel transactions Nov 28, 2016
@elhigu
Copy link
Member

elhigu commented Nov 28, 2016

If you mean by "making transaction parallel", that multiple queries are sent to the same transaction using different connections, it is not possible. It is limitation of the database server itself.

If you are referring that multiple queries are sent to DB as fast as possible without waiting query response for each query before sending the next one... At least postgres driver doesn't seem to support that brianc/node-postgres#896.

Most probably Promise.all doesn't help in this case and just uses more resources / buffers when node is trying to do parallel stuff that will be sent to DB one by one.

@nicholaswmin
Copy link
Author

nicholaswmin commented Nov 28, 2016

@elhigu

If you are referring that multiple queries are sent to DB as fast as possible without waiting query response for each query before sending the next one

Isn't there any way to handle that on knex-level?

Not sure if I'm missing something but maybe the trx object could accumulate the queries itself and make the decision when to actually initiate the DB call, no?

@elhigu
Copy link
Member

elhigu commented Nov 28, 2016

@nicholaswmin not really, it is not knex responsibility to decide how communication between driver and database is handled. In theory with mysql driver it might be possible to implement (since the driver supports sending multiple queries in one statement), but it would also disable support for sending queries as prepared statements, which is pretty important part of preventing sql-injections.

If your performance requirements are really that high, then you can just use mysql driver directly for those parts of the application.

In db driver level this kind of functionality should be easier to implement. One just would need to send all new queries straight away to DB without waiting for result and add some bookkeeping (maybe just fifo) to be able to associate each result to correct query callback (or on the other hand it might be really tricky, since I haven't looked much into DB driver codes).

@nicholaswmin
Copy link
Author

nicholaswmin commented Nov 29, 2016

@elhigu Thanks again, I've asked on tedious about this which seems to be the underlying driver of node-mssql which is what I use. Let's see what tds people have t say here: tediousjs/tedious#318

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants