Transactions are not safe or contained.

[raycmorgan]

Add support for transactions. To do this properly sqlbox needs to make use of client pools.

.save() is wrapped in a transaction. Currently it does not ensure that during the save process it is the only one using a given connection. This leads to other non-related queries happening between the BEGIN and COMMIT to be batched as part of the transaction. This is worsened when a transaction rolls back and those queries are lost.

[raycmorgan]

Hello,

https://github.com/rickharrison/sqlbox-test

I created a quick test project to demonstrate the issue. For some
reason, this isn't the exact same issue, but it is very similar. In
this particular app, I am doing a nested save from a models beforeSave
hook. However, only one of the models is actually being written to the
database.

The weird thing is that the callback is being called without an error
and the information for the row such as this:

{ name: 'Kyra',
id: 3,
createdAt: Mon Mar 03 2014 22:36:27 GMT-0800 (PST),
updatedAt: Mon Mar 03 2014 22:36:27 GMT-0800 (PST) }

Even though that row is output to console.log, it is not written to
the database. First, I wrote a test using these same exact models
alongside the rest of the tests of sqlbox. I wasn't able to replicate
the issue there. I don't know if there is some weird module versioning
issue that I am only having in a new app vs within the sqlbox tests or
if its something else. I've been going at this for hours and I am just
running into a brick wall so I'd love to see if you are seeing the
same issue when you run this code on your computer.

Thanks,

Rick

[raycmorgan]

From a chat session

raycmorgan:
so I know what is happening.. I don't know why
I think the outer transaction is getting committed too early, when the inner save finishes
which then leads to a "hanging" transaction
which gets rolled back when the connection is returned to the pool/app closes
which rolls back the outer object
and leads to the error that you were seeing when another save happens

[rickharrison]

Let me know if you want to do some pairing on this one night. I started messing around with converting the transaction piece to use any-db-transaction.