mariano/node-db-mysql — Issue #45: MySQL Transactions
Author: pertinky
Created: 2011-12-04 17:06:18+00:00
State: open

Hey Mariano,

I've being doing a bit of ground work for transactional support in the database layer (sorry for not getting back to you time has not been forthcoming recently). Unfortunately I've run into a bit of a problem and I am struggling to come up with a solution. I'm not sure whether or not the issue lies in the driver, but I'll explain and we can see.

Consider the following scenario:

Table A and table B, where B references A through B.ref_id

As part of an atomic function (i.e. I need a transaction); insert 2 rows into A (lets call them A1 and A2), and 4 rows into B (2 for A1, 2 for A2).

Within my application I am successfully opening a connection and starting a transaction. I iterate over my A-tuples, inserting them as you'd expect using code similar to this:

```
myATuples.forEach(function(aTuple, index, aTuples) {
//Insert A tuple (returns a promise)
//When promise is fulfilled
myBTuplesForATuple.forEach(bTuple, index, bTuples) {
//Promise.resolve holds the query result (insert ID)
//Set B.ref_id to the insert ID of A
//Insert B tuple
});
});
```
Nearly every time the inserts for A1 and A2 are sequential and before the inserts for the B tuples. However, that means that my A1 query result gets gobbled as the query result is always the preceeding query. So when I insert B1 I actually reference A2.

Unfortunately there is no way to say what query result belongs to what query object that would allow me to consolidate and map to results correctly.