How To Implement ACID Transactions in Loopback

Question

We've been trying to implement ACID transactions in Loopback without success. The only examples in the documentation use the 'create' method.We've tried completely overriding the events as well as multiple variations of Operation Hooks. We have only been able to get the create example to work.

Core Requirement: We need to be able to start a transaction in the create and update methods for a specific model and then update multiple tables in the transaction (either using Loopback's ORM functions or directly with SQL) with the ability to commit or rollback based on business rules. For example, we need the end-point to be able to accept an invoice with its header and detail transactions, validate it, update various tables (such as inventory and customer) and then save (or rollback) all of the changes in one single ACID transaction.

Using Database Transactions: https://docs.strongloop.com/display/public/LB/Using+database+transactions)

Here's the example from the documentation:

Post.create({title: 't1', content: 'c1'}, {transaction: tx}, function(err, post) {
  post.updateAttributes({content: 'c2', {transaction: tx}, function(err, newPost) {
    //
    newPost.reviews.create({content: 'r1'}, {transaction: tx}, function(err, newPost) {
    });
  }
});

Operation Hooks: https://docs.strongloop.com/display/public/LB/Operation+hooks

We were only able to successfully override a few of the core methods:

    Item.create = function(id, cb){
        console.log('create');  // Success!
    }
    //
    Item.find = function(id, cb){
        console.log('find'); // Success!
    }

    Item.findById = function(id, cb){
        console.log('findById'); // Success!
    }

    Item.getById = function(id, cb){
        console.log('updateAttributes'); // Did not work!
    }

    Item.all = function(id, cb){
        console.log('all'); // Did not work!
    };

    Item.findOrCreate = function(id, test, cb){
        console.log('findOrCreate'); // Did not work!
    }

    Item.replicate = function(id, test, cb){
        console.log('replicate'); // Did not work!
    }

    Item.save = function(id, test, cb){
        console.log('save'); // Did not work!
    }

    Item.updateAll = function(id, test, cb){
        console.log('updateAll'); // Did not work!
    }

    Item.upsert = function(id, test, cb){
        console.log('upsert'); // Did not work!
    }

    Item.updateAttribute = function(id, cb){
        console.log('updateAttribute'); // Did not work!
    };

    Item.updateById = function(id, test, cb){
        console.log('updateById'); // Did not work!
    }

Implementing save, update, etc in operational hooks did not work either:

Item.observe('before save', function(ctx, next) {

    console.log('before save');

    ctx.Model.beginTransaction({isolationLevel: ctx.Model.Transaction.READ_COMMITTED}, function(err, tx) {

        // Now we have a transaction (tx)
        console.log('begin transaction', err);

        //tx.commit(function(err) {
        //    console.log('commit', err);
        //})

        //tx.rollback(function(err) {
        //    console.log('rollback', err);
        //})

        next();
    });
})

Answer 1

I've managed to get a transaction working, but I don't know if this is the best way of doing it. I needed to check the "instance" because when the user is not found it does not return an error, but the instance is null. I did it using the built-in promises, because it would be really ugly using callbacks.

In the following example I had to find two users in order to update both of them. The transaction should succeed only if both values where modified. If one error occurred in the meantime, the transaction should be stopped (rolledback).

var firstUserInstance;
var secondUserInstance;

User.beginTransaction('READ COMMITTED', function(err, tx) {
      // find first user and pass it to first 'then'
      User.findById(firstUser.userId, {transaction: tx})
        .then(function(instance){
            if(instance){
                firstUserInstance = instance;
                //Pass second user instance to next 'then'
                return User.findById(firstUser.userId, {transaction: tx});
            }else{
                throw ({message: "User not found", status: 400});
            }
        })
        // Update first user
        .then(function(instance){
            if(instance){
                secondUserInstance = instance;
                //Update first user and pass result to next 'then'
                return firstUserInstance.updateAttribute("attribute", "newValue", {transaction: tx});
            }else{
                throw ({message: "User 'toUserId' not found", status: 400});
            }

        })
        // Update second user
        .then(function(instance){
            if(instance){
                //Update second user and pass result to next 'then'
                return secondUserInstance.updateAttribute("attribute", "newValue", {transaction: tx});
            }else{
                throw ({message: "Error updating", status: 401});
            }
        })
        .then(function(instance){
            if(instance){
                //Everything went OK - commit changes
                console.log(instance);
                tx.commit(function(err){});
            }else{
                throw ({message: "Error updating", status: 401});
            }

        })
        .catch(function(err){
            //Something happened - Any thrown errors will end here - rollback changes
            console.log(err);
            tx.rollback(function(err){});
        });

    });

I am not completely happy with this, I think there is still a better way of doing it, but I hope this helps!