title: Transaction over two Tables with Postgres in Sails.js
body: <p>I am trying to implement a transaction in Node.js (Sails.js) with Postgresql and Sails Waterline.js ORM. </p>
  
  <p>I tried to adapt this <a href="https://stackoverflow.com/a/25100188/3037960">answer</a>. In that answer we have only one table. In my problem I have 2 tables which have to be locked till the transaction is over.</p>
  
  <p>What should this code do:</p>
  
  <ul>
  <li>find User with <code>:Id</code></li>
  <li>if this <code>User.coins &gt; 0</code>:    
  
  <ul>
  <li>Update <code>User.coins = 0</code>    </li>
  <li>Create a new record in the table MyTransaction</li>
  </ul></li>
  </ul>
  
  <p>But the snippet doesn't lock the transaction over both tables: </p>
  
  <pre><code>try {
    // Start the transaction
    sails.models.user.query("BEGIN", function (err) {
      if (err) {
        throw new Error(err);
      }
      // Find the user
      sails.models.user.findOne(currentUser.id).exec(function (err, user) {
        if (err) {
          throw new Error(err);
        }
        if (user.coins &gt; 0) {
          var params = {
            user_id: req.session.passport.user,
            publicAddress: bitcoin_address,
            amount: user.coins,
            wohnAdresse: wohnAdresse
          }
          // Update the user balance
          user.coins = 0;
          // Save the user
          user.save(function (err) {
            if (err) {
              throw new Error(err);
            }
  
  
              sails.models.myTransaction.create(params).exec(function (err, transaction) {
                if (err) {
                  payout = {success: false};
                  payout.transactionError = err;
                  console.log("ROLLBACK! ROLLBACK!")
                  return res.serverError(e);
                }
                // Commit the transaction
                 sails.models.user.query("COMMIT", function (err) {
                   if (err) {
                     throw new Error(err);
                   }
                     payout = {success: true};
                     console.log("PAYOUT PAYOUT", payout);
                     console.log("PAYOUT PAYOUT", transaction);
                     return res.json(payout);
              });
            });
          });
        } // END of if user.coins&gt;0
      });
    });
  }
    // If there are any problems, roll back the transaction
  catch (e) {
    User.query("ROLLBACK", function (err) {
      // The rollback failed--Catastrophic error!
      if (err) {
        return res.serverError(err);
      }
      // Return the error that resulted in the rollback
      return res.serverError(e);
    });
  }
  </code></pre>
  
  <p>So when I run a loop(5times) in the frontend console, then it creates 5 records in the myTransaction table. How Can I do this properly? </p>