node-mysql操作transactions的实例

UPDATE user_info SET userName = 'kk' WHERE userId = 1;

SELECT * FROM user_info

SET AUTOCOMMIT=0;

START TRANSACTION;
SELECT * FROM user_info FOR UPDATE;
COMMIT;

/**
 * Created with JetBrains WebStorm.
 */
var mysql = require('mysql');

var connection = mysql.createConnection({
    host : 'localhost',
    port : 3306,
    user : 'root',
    password : '123456',
    database : 'test',
    charset : 'UTF8_GENERAL_CI',
    debug : false
});

connection.connect();

//connection.query('CALL proc_simple(1, @a, @b);', function(err, rows, fields) {
//    if (err) {
//       throw err;
//    }
//
//    var results = rows[0];
//    var row = results[0];
//    console.log("userName：",row.uName, "　　count：", row.totalCount);
//});

var queues = require('mysql-queues');
const DEBUG = true;
queues(connection, DEBUG);

var trans = connection.startTransaction();
trans.query("UPDATE user_info SET userName = ? WHERE userId = ?", ["张一", 1], function(err, info) {
   if (err) {
       throw err;
       trans.rollback();
   } else {
       trans.commit(function(err, info) {
           console.log(info);
       });
   }
});
trans.execute();
console.log('execute');
//connection.end();

var q = client.createQueue();
q.query(...); 
q.query(...);
q.execute();

var mysql = require('mysql');
var client = mysql.createConnection({//createClient(已失效) -> createConnection
    user: 'root',
    password: 'root'
});
//Enable mysql-queues
var queues = require('mysql-queues');
const DEBUG = true;
queues(client, DEBUG);
//Start running queries as normal...
client.query(...);

//Now you want a separate queue?
var q = client.createQueue();
q.query(...); 
q.query(...);
q.execute();

client.query(...); //Will not execute until all queued queries (and their callbacks) completed.

//Now you want a transaction?
var trans = client.startTransaction();
trans.query("INSERT...", [x, y, z], function(err, info) {
    if(err)
        trans.rollback();
    else
        trans.query("UPDATE...", [a, b, c, info.insertId], function(err) {
            if(err)
                trans.rollback();
            else
                trans.commit();
        });
});
trans.execute();
//No other queries will get executed until the transaction completes
client.query("SELECT ...") //This won't execute until the transaction is COMPLETELY done (including callbacks)

//Or... as of version 0.3.0, you can do this...
var trans = client.startTransaction();
function error(err) {
    if(err && trans.rollback) {trans.rollback(); throw err;}
}
trans.query("DELETE...", [x], error);
for(var i = 0; i < n; i++)
    trans.query("INSERT...", [ y[i] ], error);
trans.commit(); //Implictly calls resume(), which calls execute()
/* In the case written above, COMMIT is placed at the end of the Queue, yet the
entire transaction can be rolled back if an error occurs. Nesting these queries
was not required. */

Connection.prototype.query = function(sql, values, cb) {
  this._implyConnect();

  var options = {};

  if (typeof sql === 'object') {
    // query(options, cb)
    options = sql;
    cb      = values;
    values  = options.values;

    delete options.values;
  } else if (typeof values === 'function') {
    // query(sql, cb)
    cb          = values;
    options.sql = sql;
    values      = undefined;
  } else {
    // query(sql, values, cb)
    options.sql    = sql;
    options.values = values;
  }

  options.sql = this.format(options.sql, values || []);

  if (!('typeCast' in options)) {
    options.typeCast = this.config.typeCast;
  }

  return this._protocol.query(options, cb);
};

Connection.prototype.format = function(sql, values) {
  if (typeof this.config.queryFormat == "function") {
    return this.config.queryFormat.call(this, sql, values, this.config.timezone);
  }
  return SqlString.format(sql, values, this.config.timezone);
};

SqlString.format = function(sql, values, timeZone) {
  values = [].concat(values);

  return sql.replace(/\?/g, function(match) {
    if (!values.length) {
      return match;
    }

    return SqlString.escape(values.shift(), false, timeZone);
  });
};