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

Feature Request: Transactions #755

Closed
Zigur opened this Issue Dec 8, 2014 · 34 comments

Comments

@Zigur

Zigur commented Dec 8, 2014

Sorry if I open a new thread, but I could'nt find an old one on the subject...
Are transactions on the Sails/Waterline Roadmap yet? I remembered reading some discussions on them 6 months ago or so, but no updates afterwards. I don't know about the community, but for me at least they would be extremely beneficial (aka sorely needed).
I tried a workaround to use transactions in sails with postgres, using Model.query as it was suggested here:
http://stackoverflow.com/questions/17794784/transactional-sql-with-sails-js
but I got this issue as that I documented on stackoverflow (no ansers yet):
http://stackoverflow.com/questions/27291992/sails-js-postgres-issue-with-transactions
Has anyone a suggestion / personal workarounds while working with with transactions in Waterline?
Thanks a lot for your help,
Massimiliano

@Batornator

This comment has been minimized.

Show comment
Hide comment
@Batornator

Batornator Jan 7, 2015

Contributor

I'm also having the issue above (http://stackoverflow.com/questions/27291992/sails-js-postgres-issue-with-transactions). Transactions are currently a must have in our application, any workaround available?

Contributor

Batornator commented Jan 7, 2015

I'm also having the issue above (http://stackoverflow.com/questions/27291992/sails-js-postgres-issue-with-transactions). Transactions are currently a must have in our application, any workaround available?

@Zigur

This comment has been minimized.

Show comment
Hide comment
@Zigur

Zigur Jan 9, 2015

In the end, since transaction support doesn't seem to come soon, I used knex (http://knexjs.org/) to implement the transactional operations I needed.

https://github.com/biolab-unige/xtens-transact/blob/master/lib/KnexStrategy.js

It works fine, but obviously, you have to access the underlying association tables (if you have associations).

Zigur commented Jan 9, 2015

In the end, since transaction support doesn't seem to come soon, I used knex (http://knexjs.org/) to implement the transactional operations I needed.

https://github.com/biolab-unige/xtens-transact/blob/master/lib/KnexStrategy.js

It works fine, but obviously, you have to access the underlying association tables (if you have associations).

@l1br3

This comment has been minimized.

Show comment
Hide comment
@l1br3

l1br3 Feb 1, 2015

Hello,

the problem with this issue (http://stackoverflow.com/questions/27291992/sails-js-postgres-issue-with-transactions) is that each query is done by a different connexion so transaction is impossible. I've just found this post 10 minutes ago, I did not try it yet but it seems to work.

http://blog.evizija.si/sails-js-waterline-mysql-transactions/

l1br3 commented Feb 1, 2015

Hello,

the problem with this issue (http://stackoverflow.com/questions/27291992/sails-js-postgres-issue-with-transactions) is that each query is done by a different connexion so transaction is impossible. I've just found this post 10 minutes ago, I did not try it yet but it seems to work.

http://blog.evizija.si/sails-js-waterline-mysql-transactions/

@elennaro

This comment has been minimized.

Show comment
Hide comment
@elennaro

elennaro Apr 17, 2015

Contributor

Are you planning to implement transaction somehow soon?

Contributor

elennaro commented Apr 17, 2015

Are you planning to implement transaction somehow soon?

@dmarcelino

This comment has been minimized.

Show comment
Hide comment
@dmarcelino

dmarcelino Apr 17, 2015

Member

@elennaro, it's not slated for 0.11 (next big release) and it's not trivial, check #862 (comment) and #885 (comment). So it may take a few more months. Having said that, any help is much appreciated!

Member

dmarcelino commented Apr 17, 2015

@elennaro, it's not slated for 0.11 (next big release) and it's not trivial, check #862 (comment) and #885 (comment). So it may take a few more months. Having said that, any help is much appreciated!

@l1br3

This comment has been minimized.

Show comment
Hide comment
@l1br3

l1br3 Apr 17, 2015

Just to give you my feedback on the link i referenced above (http://blog.evizija.si/sails-js-waterline-mysql-transactions/) -> it is perfectly working for MySQL !

l1br3 commented Apr 17, 2015

Just to give you my feedback on the link i referenced above (http://blog.evizija.si/sails-js-waterline-mysql-transactions/) -> it is perfectly working for MySQL !

@elennaro

This comment has been minimized.

Show comment
Hide comment
@elennaro

elennaro Apr 17, 2015

Contributor

@dmarcelino @Prisonier thanks for the answers.

Contributor

elennaro commented Apr 17, 2015

@dmarcelino @Prisonier thanks for the answers.

@jyoungrok

This comment has been minimized.

Show comment
Hide comment
@jyoungrok

jyoungrok Apr 22, 2015

@dmarcelino
Thanks for your comment on the issue(#960 (comment))
On this link, you suggested that Model.query() method.

Actually, i tried that method for transaction a few months ago.
Unfortunately, it seems that Model. query() doesn't support multiple queries.
What I mean is that if i implement transaction method like below, it won't work adequately.
Model.query("begin transaction",function(err,result){
Model.query( another query.....
Model.query( "rollback" ....
});

because of that, "rollback" method doesn't work......

so as you told me, i can make new connection.
However, i'm worrying if new connection will make some problems (because there will be 2 connections)

jyoungrok commented Apr 22, 2015

@dmarcelino
Thanks for your comment on the issue(#960 (comment))
On this link, you suggested that Model.query() method.

Actually, i tried that method for transaction a few months ago.
Unfortunately, it seems that Model. query() doesn't support multiple queries.
What I mean is that if i implement transaction method like below, it won't work adequately.
Model.query("begin transaction",function(err,result){
Model.query( another query.....
Model.query( "rollback" ....
});

because of that, "rollback" method doesn't work......

so as you told me, i can make new connection.
However, i'm worrying if new connection will make some problems (because there will be 2 connections)

@l1br3

This comment has been minimized.

Show comment
Hide comment
@l1br3

l1br3 Apr 22, 2015

@jyoungrok just use the method given in the blog i referenced.

You will setup ONE connection and do everything with this one.

l1br3 commented Apr 22, 2015

@jyoungrok just use the method given in the blog i referenced.

You will setup ONE connection and do everything with this one.

@jyoungrok

This comment has been minimized.

Show comment
Hide comment
@jyoungrok

jyoungrok Apr 22, 2015

@Prisonier Thanks for your comment
i will try that and comment again

jyoungrok commented Apr 22, 2015

@Prisonier Thanks for your comment
i will try that and comment again

@jyoungrok

This comment has been minimized.

Show comment
Hide comment
@jyoungrok

jyoungrok Apr 22, 2015

http://blog.evizija.si/sails-js-waterline-mysql-transactions/
In this blog, i got the news that new release of waterline will involve transaction implementation
Can i know when this implementation will be released?

jyoungrok commented Apr 22, 2015

http://blog.evizija.si/sails-js-waterline-mysql-transactions/
In this blog, i got the news that new release of waterline will involve transaction implementation
Can i know when this implementation will be released?

@jyoungrok

This comment has been minimized.

Show comment
Hide comment
@jyoungrok

jyoungrok Apr 22, 2015

Actually, when i commented, i didn't know about connection pool in detail
I've caught the point and if i implement like blog @Prisonier refered, there won't be much overhead
because connection pool is just cache of connections for re-use and i will create only one connection and close it after using that.

Actually, i did it a few months ago but i didn't know in detail
Anyway, thanks for your comment @Prisonier
and finally, i'd like to knwo when the new version will be released :)

jyoungrok commented Apr 22, 2015

Actually, when i commented, i didn't know about connection pool in detail
I've caught the point and if i implement like blog @Prisonier refered, there won't be much overhead
because connection pool is just cache of connections for re-use and i will create only one connection and close it after using that.

Actually, i did it a few months ago but i didn't know in detail
Anyway, thanks for your comment @Prisonier
and finally, i'd like to knwo when the new version will be released :)

@dmarcelino

This comment has been minimized.

Show comment
Hide comment
@dmarcelino

dmarcelino Apr 22, 2015

Member

@jyoungrok this feature is assigned to milestone WL_Next which will be released after 0.11, so this should take several months. Having said that, waterline is an open source project developed by the community for the community so you if you want to help implementing this feature your contribution will be appreciated.

Member

dmarcelino commented Apr 22, 2015

@jyoungrok this feature is assigned to milestone WL_Next which will be released after 0.11, so this should take several months. Having said that, waterline is an open source project developed by the community for the community so you if you want to help implementing this feature your contribution will be appreciated.

@dmarcelino

This comment has been minimized.

Show comment
Hide comment
@dmarcelino

dmarcelino Apr 29, 2015

Member

It's probably a good idea to look at sails-mysql-transactions and knex when building our own API for transactions, we shouldn't reinvent the wheel.

And this will likely be supported by adapters through a new API... Transactable?

Member

dmarcelino commented Apr 29, 2015

It's probably a good idea to look at sails-mysql-transactions and knex when building our own API for transactions, we shouldn't reinvent the wheel.

And this will likely be supported by adapters through a new API... Transactable?

@dmarcelino

This comment has been minimized.

Show comment
Hide comment
@dmarcelino

dmarcelino Jun 4, 2015

Member

For reference, there is already a spec for this in #62 written by @mikermcneil:

tldr;

0.5) Add transaction support for uniqueness constraints (already done probably, since we're sending that down as part of the schema)

  1. Add support for same-adapter transactions, using built-in stuff for the database (e.g. mysql transactions). The API looks like User.transaction().find(....), instance.rollback(cb) and instance.commit(cb). If either of those fail, we're in deep trouble. See the very bottom of this issue.

  2. Add transaction support (using our new transaction API described above) on top of:

    • aggregate (createEach(), plural usage of update(), plural usage of destroy())
    • and composite queries (findOrCreate(), and findOrCreateEach())
  3. Add increment() and decrement(), which are just shortcuts on top of update. Only thing is they need to be transactional. Using the same new functionality, of course.

  4. Figure out how to handle commit() and rollback() errors. This may already be worked out at the adapter level.

  5. Add support for instance.attempts(), which will reattempt a waterline call n times until it works (with incremental back-off). We can probably leverage async for this.

  6. Figure out how to do cross-adapter transactions in a user-friendly way. This is explored in a NOT-user friendly way at the bottom of this issue.

Member

dmarcelino commented Jun 4, 2015

For reference, there is already a spec for this in #62 written by @mikermcneil:

tldr;

0.5) Add transaction support for uniqueness constraints (already done probably, since we're sending that down as part of the schema)

  1. Add support for same-adapter transactions, using built-in stuff for the database (e.g. mysql transactions). The API looks like User.transaction().find(....), instance.rollback(cb) and instance.commit(cb). If either of those fail, we're in deep trouble. See the very bottom of this issue.

  2. Add transaction support (using our new transaction API described above) on top of:

    • aggregate (createEach(), plural usage of update(), plural usage of destroy())
    • and composite queries (findOrCreate(), and findOrCreateEach())
  3. Add increment() and decrement(), which are just shortcuts on top of update. Only thing is they need to be transactional. Using the same new functionality, of course.

  4. Figure out how to handle commit() and rollback() errors. This may already be worked out at the adapter level.

  5. Add support for instance.attempts(), which will reattempt a waterline call n times until it works (with incremental back-off). We can probably leverage async for this.

  6. Figure out how to do cross-adapter transactions in a user-friendly way. This is explored in a NOT-user friendly way at the bottom of this issue.

@atiertant

This comment has been minimized.

Show comment
Hide comment
@atiertant

atiertant Jun 15, 2015

@dmarcelino does this spec are definitive ? is there any Transactable adapter interface spec ?

atiertant commented Jun 15, 2015

@dmarcelino does this spec are definitive ? is there any Transactable adapter interface spec ?

@dmarcelino

This comment has been minimized.

Show comment
Hide comment
@dmarcelino

dmarcelino Jun 15, 2015

Member

No, I don't think so. Nope, at least not yet.

Member

dmarcelino commented Jun 15, 2015

No, I don't think so. Nope, at least not yet.

@atiertant

This comment has been minimized.

Show comment
Hide comment
@atiertant

atiertant Jun 15, 2015

@dmarcelino could be a good starting point ! so if i understood @mikermcneil ,transaction should work like this in waterline :

var petTransaction = pet.transaction();
petTransaction.create()
petTransaction.find()
petTransaction.update()
petTransaction.destroy()
//async
if (err) {
  petTransaction.rollback(function (err) { ... });
}
else {
  petTransaction.commit(function (err) { ... });
}

am i correct?
what about populate and cross adapter?
if adapter doesn't support transaction what should waterline do on rollback? (trying to restore last data?)

so adapter need to implements transaction,rollback and commit methods
rollback and commit are easy they just take a callback as argument to handle errors.
transaction i more complex:
waterline' transaction method should return a transaction specific collection instance ?
so adapter' transaction method should return a transaction specific adapter instance ?

atiertant commented Jun 15, 2015

@dmarcelino could be a good starting point ! so if i understood @mikermcneil ,transaction should work like this in waterline :

var petTransaction = pet.transaction();
petTransaction.create()
petTransaction.find()
petTransaction.update()
petTransaction.destroy()
//async
if (err) {
  petTransaction.rollback(function (err) { ... });
}
else {
  petTransaction.commit(function (err) { ... });
}

am i correct?
what about populate and cross adapter?
if adapter doesn't support transaction what should waterline do on rollback? (trying to restore last data?)

so adapter need to implements transaction,rollback and commit methods
rollback and commit are easy they just take a callback as argument to handle errors.
transaction i more complex:
waterline' transaction method should return a transaction specific collection instance ?
so adapter' transaction method should return a transaction specific adapter instance ?

@devinivy

This comment has been minimized.

Show comment
Hide comment
@devinivy

devinivy Jun 15, 2015

Contributor

I like that idea as a start. One issue to think about is how to deal with transactions within callbacks. Typically in a callback to one of these methods you get a model instance. Will changes to this model instance implicitly be part of the same transaction?

Contributor

devinivy commented Jun 15, 2015

I like that idea as a start. One issue to think about is how to deal with transactions within callbacks. Typically in a callback to one of these methods you get a model instance. Will changes to this model instance implicitly be part of the same transaction?

@atiertant

This comment has been minimized.

Show comment
Hide comment
@atiertant

atiertant Jun 16, 2015

Will changes to this model instance implicitly be part of the same transaction?
@devinivy i think it should be the same transaction

the problem with this way is that we can't have a transaction with multiple models...
it could be :

var trans = new Transaction();
pet.transaction(trans).create()
pet.transaction(trans).find()
user.transaction(trans).update()
user.transaction(trans).destroy()
//async
if (err) {
  trans.rollback(function (err) { ... });
}
else {
  trans.commit(function (err) { ... });
}

in this way we could choose who is a part of the transaction

atiertant commented Jun 16, 2015

Will changes to this model instance implicitly be part of the same transaction?
@devinivy i think it should be the same transaction

the problem with this way is that we can't have a transaction with multiple models...
it could be :

var trans = new Transaction();
pet.transaction(trans).create()
pet.transaction(trans).find()
user.transaction(trans).update()
user.transaction(trans).destroy()
//async
if (err) {
  trans.rollback(function (err) { ... });
}
else {
  trans.commit(function (err) { ... });
}

in this way we could choose who is a part of the transaction

@iyn

This comment has been minimized.

Show comment
Hide comment
@iyn

iyn Sep 11, 2015

is there a timeline for transaction support?

iyn commented Sep 11, 2015

is there a timeline for transaction support?

@jyoungrok

This comment has been minimized.

Show comment
Hide comment
@jyoungrok

jyoungrok Sep 11, 2015

NAVER - http://www.naver.com/

jyr3357@naver.com 님께 보내신 메일 <Re: [waterline] Feature Request: Transactions (#755)> 이 다음과 같은 이유로 전송 실패했습니다.


받는 사람이 회원님의 메일을 수신차단 하였습니다.


jyoungrok commented Sep 11, 2015

NAVER - http://www.naver.com/

jyr3357@naver.com 님께 보내신 메일 <Re: [waterline] Feature Request: Transactions (#755)> 이 다음과 같은 이유로 전송 실패했습니다.


받는 사람이 회원님의 메일을 수신차단 하였습니다.


@nodecode

This comment has been minimized.

Show comment
Hide comment
@nodecode

nodecode Sep 24, 2015

+1 for transaction support

nodecode commented Sep 24, 2015

+1 for transaction support

@formula1

This comment has been minimized.

Show comment
Hide comment
@formula1

formula1 Sep 26, 2015

TLDR: An Example how to implement it in psuedocode (AKA its not that simple)

Transactions aren't really that simple as this is cross Database. Heres a possible Work around (however probably not the best)

  1. First we need to know if there may be a transaction to roll back
    • Specify all documents (model and id) that will be involved in the transaction
    • Specify the properties that will be changed in each
  2. We need to know our place in case we do need to roll back
    • This can be done by leaving a watermark on each document "I was here"
    • In the case where a database crashes
      • We see our transaction still exists in the transaction table
      • We go to the first doc and see our watermark is there
      • We go to the next doc and see our watermark is there
      • Etc...
      • We find a doc without a watermark, we continue from there
  3. We need to clean ourselves up after everything is done
    • This can be done by Deleting each watermark and the pending transaction that exists
    • The pending transaction would have to be deleted first before anything happens
  4. In the case part of the transaction fails (Which should be done by specifying a query in a way that prevents finding of the document)
    • We Go backward from our current point
      • We remove our watermark and undo the modification we made

This is similar to how MongoDB explains best practices for two phase commits in their database

Inorder for waterline to have something like this available in an easy to use manner

  1. User will need to specify a Model for Transaction
  2. User will need to specify Models, arguments to be used in a Query/Validator and operation, Query/Validator for those models and Operation to perform
  3. Waterline needs to perform something like the above

Its also possible to ensure that no two transactions modify the same document at the same time

  • Before each transaction is made, Check if any other transaction is using our current documents
  • If some are found, Find the last added, and set current transaction's "waitingFor" equal to an array of transactions, each being the last
  • In the case a transaction ends - Get all "waitingFor" the ending transaction and remove that dependency. If the dependencies is all resolved for each transaction, resolve the transaction.
  • In the case a transaction is canceled
    • waitingFor = Get all "waitingFor" the canceled transaction
    • canceled.waitingFor = get all documents the document was waiting for
    • For each document the canceled document was waiting for
      • props in common = compare(canceled, canceled.waitingFor[i])
      • For each waitingFor
        • if(waitingFor[i].has(props in common)) waitingFor.push(canceled.waitingFor[i]);
    • For each waitingFor
      • waitingFor[i].waitingFor.remove(canceled)
      • save

formula1 commented Sep 26, 2015

TLDR: An Example how to implement it in psuedocode (AKA its not that simple)

Transactions aren't really that simple as this is cross Database. Heres a possible Work around (however probably not the best)

  1. First we need to know if there may be a transaction to roll back
    • Specify all documents (model and id) that will be involved in the transaction
    • Specify the properties that will be changed in each
  2. We need to know our place in case we do need to roll back
    • This can be done by leaving a watermark on each document "I was here"
    • In the case where a database crashes
      • We see our transaction still exists in the transaction table
      • We go to the first doc and see our watermark is there
      • We go to the next doc and see our watermark is there
      • Etc...
      • We find a doc without a watermark, we continue from there
  3. We need to clean ourselves up after everything is done
    • This can be done by Deleting each watermark and the pending transaction that exists
    • The pending transaction would have to be deleted first before anything happens
  4. In the case part of the transaction fails (Which should be done by specifying a query in a way that prevents finding of the document)
    • We Go backward from our current point
      • We remove our watermark and undo the modification we made

This is similar to how MongoDB explains best practices for two phase commits in their database

Inorder for waterline to have something like this available in an easy to use manner

  1. User will need to specify a Model for Transaction
  2. User will need to specify Models, arguments to be used in a Query/Validator and operation, Query/Validator for those models and Operation to perform
  3. Waterline needs to perform something like the above

Its also possible to ensure that no two transactions modify the same document at the same time

  • Before each transaction is made, Check if any other transaction is using our current documents
  • If some are found, Find the last added, and set current transaction's "waitingFor" equal to an array of transactions, each being the last
  • In the case a transaction ends - Get all "waitingFor" the ending transaction and remove that dependency. If the dependencies is all resolved for each transaction, resolve the transaction.
  • In the case a transaction is canceled
    • waitingFor = Get all "waitingFor" the canceled transaction
    • canceled.waitingFor = get all documents the document was waiting for
    • For each document the canceled document was waiting for
      • props in common = compare(canceled, canceled.waitingFor[i])
      • For each waitingFor
        • if(waitingFor[i].has(props in common)) waitingFor.push(canceled.waitingFor[i]);
    • For each waitingFor
      • waitingFor[i].waitingFor.remove(canceled)
      • save
@devinivy

This comment has been minimized.

Show comment
Hide comment
@devinivy

devinivy Sep 26, 2015

Contributor

@formula1 thanks for thinking about this– I look forward to digging into your proposed solution.

Contributor

devinivy commented Sep 26, 2015

@formula1 thanks for thinking about this– I look forward to digging into your proposed solution.

@sailsbot

This comment has been minimized.

Show comment
Hide comment
@sailsbot

sailsbot Oct 27, 2015

Thanks for posting, @Zigur. I'm a repo bot-- nice to meet you!

It has been 30 days since there have been any updates or new comments on this page. If this issue has been resolved, feel free to disregard the rest of this message. On the other hand, if you are still waiting on a patch, please:

  • review our contribution guide to make sure this submission meets our criteria (only verified bugs with documented features, please; no questions, commentary, or bug reports about undocumented features or unofficial plugins)
  • create a new issue with the latest information, including updated version details with error messages, failing tests, and a link back to the original issue. This allows GitHub to automatically create a back-reference for future visitors arriving from search engines.

Thanks so much for your help!

sailsbot commented Oct 27, 2015

Thanks for posting, @Zigur. I'm a repo bot-- nice to meet you!

It has been 30 days since there have been any updates or new comments on this page. If this issue has been resolved, feel free to disregard the rest of this message. On the other hand, if you are still waiting on a patch, please:

  • review our contribution guide to make sure this submission meets our criteria (only verified bugs with documented features, please; no questions, commentary, or bug reports about undocumented features or unofficial plugins)
  • create a new issue with the latest information, including updated version details with error messages, failing tests, and a link back to the original issue. This allows GitHub to automatically create a back-reference for future visitors arriving from search engines.

Thanks so much for your help!

@sailsbot sailsbot closed this Oct 27, 2015

@elennaro

This comment has been minimized.

Show comment
Hide comment
@elennaro

elennaro Oct 27, 2015

Contributor

Hey! No way! :D
First bot kills the task we are all waiting for, like I don't know, like a new heart in a hospital!
And then spam? What's going on here :)

Contributor

elennaro commented Oct 27, 2015

Hey! No way! :D
First bot kills the task we are all waiting for, like I don't know, like a new heart in a hospital!
And then spam? What's going on here :)

@devinivy

This comment has been minimized.

Show comment
Hide comment
@devinivy

devinivy Oct 27, 2015

Contributor

This issue should remain open until a PR is made to the roadmap file.

Contributor

devinivy commented Oct 27, 2015

This issue should remain open until a PR is made to the roadmap file.

@devinivy devinivy reopened this Oct 27, 2015

@khkiley

This comment has been minimized.

Show comment
Hide comment
@khkiley

khkiley Nov 5, 2015

I’m new to Waterline, and a heavy MS SQL server user. Transactions are pretty much required for every application I write. (ERP Systems)

I read the solution that @formula1 presented and while it is thorough, I think it overly complicates what is required for a first pass implementation of transactions in Waterline.

I don’t think Waterline itself should be required to handle the intricacies of a transaction, that should all be pushed down to the adapter. Most of the SQL based solutions already support transactions, and Waterline itself should remain transaction agnostic.

I did some digging into the transaction solution for MySql, and I think that the approach is solid, it could be made generic so other adapters can be modified to click into a known transactional interface.

My rough understanding of the how the transactional MySql adapter works:

  1. A transaction id (integer) is requested from the adapter.
  2. I think this starts the transaction and sets aside a connection
  3. A custom version of Waterline is required that passes the transaction Id around internally.
  4. The transaction Id is buried inside the parameters that are passed to the adapter (options, query, data ? etc.)
  5. The modified adapter sniffs out the parameters and if it finds a transaction Id it calls the official MySql adapter with a specific connection.
  6. Commit or Rollback is called on the specific transaction

Let’s build on the work already done and build multi-adapter/connection support. The requirements are simple, multiple transactions can be active at any time, multiple collections can join a transaction, adapters are free to ignore the transaction if they do not support transaction. Waterline involvement or understanding of the actual transaction is light and limited to passing around a transaction object between the collections/models and the methods on the adapters, and then finally calling the adapter’s commit or rollback method at the appropriate time.

  1. Waterline will need a new method, beginTransaction which returns transaction objects. No adapters are notified at this point, the only thing that happens is a new transaction object is created.

  2. The transaction object will have 4 properties: transactionId : Integer, transactionTimeout:Integer, XACT_ABORT:Boolean (This is equivalent to SQL Sever’s XACT_ABORT setting which will automatically abort a transaction on any SQL error, this is for discussion) and finally an adapter object which adapters can use to store connection specific transaction information.

  3. Collections will have a new method, joinTransaction. The transaction object will be passed to the method and associated with the collection. Any models returned will be associated with the transaction object as well.

  4. Cross collection queries would inherit this transaction object from the original collection. (I am in the dark about how simple or complicated this would be with populate/deep population) This appears to be what is happening with the custom version of Waterline used with MySql Transactions.

  5. With any interaction with the adapter, this transaction object needs to be passed. I assume there is some difficulty with this as the MySql Transactional adapter went through the trouble of modifying core waterline, but stopped at actually passing the transaction Id to the adapter. It ends up getting buried in the parameters to calls to the adapter and then sniffed out later.
    The Mysql Adapter seems to have added a non-standard calling parameter (connection) to support the transactional adapter, i.e.:

    find: function(connectionName, collectionName, options, cb, connection)

    Vs the standard in other adapters:

    find: function (connection, collection, options, cb)

    going forward the call would look like this:

    find: function(connectionName, collectionName, options, cb, transaction)

    This would be less likely to break existing adapters, and I think the MySql adapter could be fixed without too much trouble.

  6. Now is when the interesting stuff starts. When an adapter is called it will check to see if the transaction object is passed, if so it checks transaction.adapter[connectionName] object to see if it has already started a transaction and if not then it fires one up, and then stores its transactional information back in transaction.adapter[connectionName]. Waterline is opaque to what is stored in that object, it will be adapter/connection specific.

  7. If the adapter has already fired up the transaction, it will operate within that transaction context.

  8. Within the adapter, there needs to be a way to indicate something went sour with the transaction. I was thinking the adapter could emit a rollback event passing the transaction object and waterline could listen to all the adapters for that event. There could be something better. Whatever happens, there needs to be a communication path from the adapter to Waterline that the transaction needs to be rolled back once the adapter is finished with the call.

  9. Waterline will have two new methods, commitTransaction and rollbackTransaction. The transaction object will be passed and Waterline will cycle through the each adapter registered in the transaction object calling commit or rollback methods with the transaction.

  10. For the commit method, there needs to be a way to call the adapters in a specific sequence to maximize integrity. Something could go wrong during a adapters transaction commit with the possibility of leaving the transaction half baked. More about that a little later.

So, ensuring commits across multiple adapters/connections is a big task. For example, in the Microsoft world there is a product call MSDTC (Microsoft Distributed Transaction Coordinator) specific for this task. I think that it is too much to expect Waterline to perform this type of transaction coordination, and my proposed solution is “good enough”. If an application needs this functionality it could let a third party application perform the heavy lifting. For example, if I need to coordinate database updates between multiple SQL servers and an oracle database I can let SQL server do all the work and Waterline is none the wiser.

With my proposed solution the adapters would be validating any calls (I say calls instead of insert because an adapter might not be a database) Transactional databases perform validations on the fly, and if you can insert a record without it erroring out then you know that your commit is probably going to be ok, and the biggest thing to worry about is a hardware failure.

If we want to transactionalize a non transaction entity, say an email adapter, we can validate the data and queue it up for the later commit. For example, I may be inserting multiple order records, and with each insert I also call an adapter that sends an email to the customer acknowledging each order. Well, I might not want the emails to actually be sent until all the order records are validated, inserted and committed. So I would write an email adapter to accept the parameters and perform a pre-validation to ensure that when I DO send the email it can be sent, minimalizing the chance it fails during the commit. If during the validation of the email an error occurs, the email adapter would emit a rollback event and Waterline would catch it and rollback all of the transactions, which would include the database inserts.

When the Waterline commitTransaction method is called, it will go through the adapters and call the commit method with the transaction object. I would want to make sure the commit method on my database handler was called FIRST because in the slim chance it fails, I don’t want the emails going out. If the commit method was called on the email adapter first, the emails would go, and then if I commit the database transactions and then they fail I’m in a bind because I already sent the emails to the customer. I don't know if this shoud be done with a parameter on the connection, or if its something that needs to be transaction specific

So if anyone made it all the way down here, thanks for taking the time to read this! Its a long winded response to a solution I think could be fairly simple.

Kurt

khkiley commented Nov 5, 2015

I’m new to Waterline, and a heavy MS SQL server user. Transactions are pretty much required for every application I write. (ERP Systems)

I read the solution that @formula1 presented and while it is thorough, I think it overly complicates what is required for a first pass implementation of transactions in Waterline.

I don’t think Waterline itself should be required to handle the intricacies of a transaction, that should all be pushed down to the adapter. Most of the SQL based solutions already support transactions, and Waterline itself should remain transaction agnostic.

I did some digging into the transaction solution for MySql, and I think that the approach is solid, it could be made generic so other adapters can be modified to click into a known transactional interface.

My rough understanding of the how the transactional MySql adapter works:

  1. A transaction id (integer) is requested from the adapter.
  2. I think this starts the transaction and sets aside a connection
  3. A custom version of Waterline is required that passes the transaction Id around internally.
  4. The transaction Id is buried inside the parameters that are passed to the adapter (options, query, data ? etc.)
  5. The modified adapter sniffs out the parameters and if it finds a transaction Id it calls the official MySql adapter with a specific connection.
  6. Commit or Rollback is called on the specific transaction

Let’s build on the work already done and build multi-adapter/connection support. The requirements are simple, multiple transactions can be active at any time, multiple collections can join a transaction, adapters are free to ignore the transaction if they do not support transaction. Waterline involvement or understanding of the actual transaction is light and limited to passing around a transaction object between the collections/models and the methods on the adapters, and then finally calling the adapter’s commit or rollback method at the appropriate time.

  1. Waterline will need a new method, beginTransaction which returns transaction objects. No adapters are notified at this point, the only thing that happens is a new transaction object is created.

  2. The transaction object will have 4 properties: transactionId : Integer, transactionTimeout:Integer, XACT_ABORT:Boolean (This is equivalent to SQL Sever’s XACT_ABORT setting which will automatically abort a transaction on any SQL error, this is for discussion) and finally an adapter object which adapters can use to store connection specific transaction information.

  3. Collections will have a new method, joinTransaction. The transaction object will be passed to the method and associated with the collection. Any models returned will be associated with the transaction object as well.

  4. Cross collection queries would inherit this transaction object from the original collection. (I am in the dark about how simple or complicated this would be with populate/deep population) This appears to be what is happening with the custom version of Waterline used with MySql Transactions.

  5. With any interaction with the adapter, this transaction object needs to be passed. I assume there is some difficulty with this as the MySql Transactional adapter went through the trouble of modifying core waterline, but stopped at actually passing the transaction Id to the adapter. It ends up getting buried in the parameters to calls to the adapter and then sniffed out later.
    The Mysql Adapter seems to have added a non-standard calling parameter (connection) to support the transactional adapter, i.e.:

    find: function(connectionName, collectionName, options, cb, connection)

    Vs the standard in other adapters:

    find: function (connection, collection, options, cb)

    going forward the call would look like this:

    find: function(connectionName, collectionName, options, cb, transaction)

    This would be less likely to break existing adapters, and I think the MySql adapter could be fixed without too much trouble.

  6. Now is when the interesting stuff starts. When an adapter is called it will check to see if the transaction object is passed, if so it checks transaction.adapter[connectionName] object to see if it has already started a transaction and if not then it fires one up, and then stores its transactional information back in transaction.adapter[connectionName]. Waterline is opaque to what is stored in that object, it will be adapter/connection specific.

  7. If the adapter has already fired up the transaction, it will operate within that transaction context.

  8. Within the adapter, there needs to be a way to indicate something went sour with the transaction. I was thinking the adapter could emit a rollback event passing the transaction object and waterline could listen to all the adapters for that event. There could be something better. Whatever happens, there needs to be a communication path from the adapter to Waterline that the transaction needs to be rolled back once the adapter is finished with the call.

  9. Waterline will have two new methods, commitTransaction and rollbackTransaction. The transaction object will be passed and Waterline will cycle through the each adapter registered in the transaction object calling commit or rollback methods with the transaction.

  10. For the commit method, there needs to be a way to call the adapters in a specific sequence to maximize integrity. Something could go wrong during a adapters transaction commit with the possibility of leaving the transaction half baked. More about that a little later.

So, ensuring commits across multiple adapters/connections is a big task. For example, in the Microsoft world there is a product call MSDTC (Microsoft Distributed Transaction Coordinator) specific for this task. I think that it is too much to expect Waterline to perform this type of transaction coordination, and my proposed solution is “good enough”. If an application needs this functionality it could let a third party application perform the heavy lifting. For example, if I need to coordinate database updates between multiple SQL servers and an oracle database I can let SQL server do all the work and Waterline is none the wiser.

With my proposed solution the adapters would be validating any calls (I say calls instead of insert because an adapter might not be a database) Transactional databases perform validations on the fly, and if you can insert a record without it erroring out then you know that your commit is probably going to be ok, and the biggest thing to worry about is a hardware failure.

If we want to transactionalize a non transaction entity, say an email adapter, we can validate the data and queue it up for the later commit. For example, I may be inserting multiple order records, and with each insert I also call an adapter that sends an email to the customer acknowledging each order. Well, I might not want the emails to actually be sent until all the order records are validated, inserted and committed. So I would write an email adapter to accept the parameters and perform a pre-validation to ensure that when I DO send the email it can be sent, minimalizing the chance it fails during the commit. If during the validation of the email an error occurs, the email adapter would emit a rollback event and Waterline would catch it and rollback all of the transactions, which would include the database inserts.

When the Waterline commitTransaction method is called, it will go through the adapters and call the commit method with the transaction object. I would want to make sure the commit method on my database handler was called FIRST because in the slim chance it fails, I don’t want the emails going out. If the commit method was called on the email adapter first, the emails would go, and then if I commit the database transactions and then they fail I’m in a bind because I already sent the emails to the customer. I don't know if this shoud be done with a parameter on the connection, or if its something that needs to be transaction specific

So if anyone made it all the way down here, thanks for taking the time to read this! Its a long winded response to a solution I think could be fairly simple.

Kurt

@kevinburkeshyp

This comment has been minimized.

Show comment
Hide comment
@kevinburkeshyp

kevinburkeshyp Nov 16, 2015

Contributor

fwiw - if you need to implement these in Postgres, here's a library for doing so - https://github.com/Shyp/pg-transactions.

(If it counts at all I think the approach currently taken by sails-postgresql with respect to unique constraints is great! enforce the constraint at the database level rather than via a transaction means you only need to do 1 database query instead of 4 to validate the constraint - furthermore the SELECT */transaction-based approach to uniqueness constraints leads to incorrect behavior with the default READ COMMITTED database settings! I wrote a little bit more about this here https://kev.inburke.com/kevin/faster-correct-database-queries/)

Contributor

kevinburkeshyp commented Nov 16, 2015

fwiw - if you need to implement these in Postgres, here's a library for doing so - https://github.com/Shyp/pg-transactions.

(If it counts at all I think the approach currently taken by sails-postgresql with respect to unique constraints is great! enforce the constraint at the database level rather than via a transaction means you only need to do 1 database query instead of 4 to validate the constraint - furthermore the SELECT */transaction-based approach to uniqueness constraints leads to incorrect behavior with the default READ COMMITTED database settings! I wrote a little bit more about this here https://kev.inburke.com/kevin/faster-correct-database-queries/)

@sailsbot

This comment has been minimized.

Show comment
Hide comment
@sailsbot

sailsbot Dec 17, 2015

Thanks for posting, @Zigur. I'm a repo bot-- nice to meet you!

It has been 30 days since there have been any updates or new comments on this page. If this issue has been resolved, feel free to disregard the rest of this message. On the other hand, if you are still waiting on a patch, please:

  • review our contribution guide to make sure this submission meets our criteria (only verified bugs with documented features, please; no questions, commentary, or bug reports about undocumented features or unofficial plugins)
  • create a new issue with the latest information, including updated version details with error messages, failing tests, and a link back to the original issue. This allows GitHub to automatically create a back-reference for future visitors arriving from search engines.

Thanks so much for your help!

sailsbot commented Dec 17, 2015

Thanks for posting, @Zigur. I'm a repo bot-- nice to meet you!

It has been 30 days since there have been any updates or new comments on this page. If this issue has been resolved, feel free to disregard the rest of this message. On the other hand, if you are still waiting on a patch, please:

  • review our contribution guide to make sure this submission meets our criteria (only verified bugs with documented features, please; no questions, commentary, or bug reports about undocumented features or unofficial plugins)
  • create a new issue with the latest information, including updated version details with error messages, failing tests, and a link back to the original issue. This allows GitHub to automatically create a back-reference for future visitors arriving from search engines.

Thanks so much for your help!

@sailsbot sailsbot closed this Dec 17, 2015

@randallmeeker

This comment has been minimized.

Show comment
Hide comment
@randallmeeker

randallmeeker Dec 17, 2015

Contributor

@sailsbot strikes again

Contributor

randallmeeker commented Dec 17, 2015

@sailsbot strikes again

@randallmeeker randallmeeker reopened this Dec 17, 2015

@particlebanana

This comment has been minimized.

Show comment
Hide comment
@particlebanana

particlebanana Dec 17, 2015

Contributor

@randallmeeker thanks, we need to add this to the Roadmap so that this can be closed.

Contributor

particlebanana commented Dec 17, 2015

@randallmeeker thanks, we need to add this to the Roadmap so that this can be closed.

@particlebanana

This comment has been minimized.

Show comment
Hide comment
@particlebanana

particlebanana Jan 16, 2016

Contributor

This has been added to the roadmap. There are some interesting projects out there now for doing this including @kevinburkeshyp's project and Sails MySQL Transactions from the Postman team.

It's obviously a big challenge and requires bringing connections up to the user level in Waterline. I have some ideas of how this could be done and have some working examples. It should be scheduled for development in the near future but I don't have a timeframe to put on this.

Contributor

particlebanana commented Jan 16, 2016

This has been added to the roadmap. There are some interesting projects out there now for doing this including @kevinburkeshyp's project and Sails MySQL Transactions from the Postman team.

It's obviously a big challenge and requires bringing connections up to the user level in Waterline. I have some ideas of how this could be done and have some working examples. It should be scheduled for development in the near future but I don't have a timeframe to put on this.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment