Transactions doesn't work between multiple connections?

[azarus]

How to make it work?

[shamasis]

Assuming you meant multiple databases. No. It won't as of now. The following work is needed in this adapter to do it:

1. support multiple connections (originally this was written to support only one database)
2. create a transaction pool so that transaction spanning across multiple databases could behave like a single transaction

We are almost at a stage where this might get implemented in next few "months". Ya... I know... its late. But if I get a good PR, then it maybe earlier.

[azarus]

Let's say i have a code like this just to simulate my current situation:

for(var i=0;i<10;++i)
{
 Transaction.start(function (err, transaction)
{
    if (err)
    {
        // the first error might even fail to return a transaction object, so double-check.
        transaction && transaction.rollback();
        return callback(err);
    }  

    // Get the balance
    User.transact(transaction).findOne({userid: 1}).exec(function (err, UserInstance)
    {
        if (err)
        {
            transaction.rollback();
            return callback(err);
        }

        UserInstance.balance = UserInstance.balance - 10;
        UserInstance.save(function(err, UpdatedData)
        {
            if (err)
            {
                transaction.rollback();
                return callback(err);
            }
             // Commit
            transaction.commit();
            callback(err, UpdatedData);
        });
    });
});
}

Well when i set the transactionConnectionLimit to 1 it works just fine, but if i set it to 10 the transactions doesn't lock the table. So my balance only gets deducted by -10 instead of -100

[shamasis]

That's odd! Could it be because the underlying connection pool returns the same connection if requested too fast?

I will need to try this out and step through waterline and the adapter to see what is causing this.

PS: You have a misplaced semicolon at the end of for(var i=0;i<10;++i)

[azarus]

I think It's because the connection pool returns a different connection, for each transaction. And transactions are not shared between connections?

[shamasis]

yes. transactions cannot be shared between connections. but multiple connections work. I am using it myself. This has got to do something with starting transactions too fast in for loop (and something interfering with that.)

[azarus]

Any solution to the problem? :/

[shamasis]

Try creating the transactions slowly - with a timeout - at least if it works we will know the root cause.