3月 062014
 

SQL标准定义了4类隔离级别,包括了一些具体规则,用来限定事务内外的哪些改变是可见的,哪些是不可见的。低级别的隔离级一般支持更高的并发处理,并拥有更低的系统开销。
Read Uncommitted(读取未提交内容)

       在该隔离级别,所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用,因为它的性能也不比其他级别好多少。读取未提交的数据,也被称之为脏读(Dirty Read)。
Read Committed(读取提交内容)

       这是大多数数据库系统的默认隔离级别(但不是MySQL默认的)。它满足了隔离的简单定义:一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读(Nonrepeatable Read),因为同一事务的其他实例在该实例处理其间可能会有新的commit,所以同一select可能返回不同结果。
Repeatable Read(可重读)

       这是MySQL的默认事务隔离级别,它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行。不过理论上,这会导致另一个棘手的问题:幻读 (Phantom Read)。简单的说,幻读指当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control)机制解决了该问题。

Serializable(可串行化) 
       这是最高的隔离级别,它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。在这个级别,可能导致大量的超时现象和锁竞争。

         这四种隔离级别采取不同的锁类型来实现,若读取的是同一个数据的话,就容易发生问题。例如:

         脏读(Drity Read):某个事务已更新一份数据,另一个事务在此时读取了同一份数据,由于某些原因,前一个RollBack了操作,则后一个事务所读取的数据就会是不正确的。

         不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致,这可能是两次查询过程中间插入了一个事务更新的原有的数据。

         幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致,例如有一个事务查询了几列(Row)数据,而另一个事务却在此时插入了新的几列数据,先前的事务在接下来的查询中,就会发现有几列数据是它先前所没有的。

         在MySQL中,实现了这四种隔离级别,分别有可能产生问题如下所示:


下面,将利用MySQL的客户端程序,分别测试几种隔离级别。测试数据库为test,表为tx;表结构:

id                               int

num

                              int

两个命令行客户端分别为A,B;不断改变A的隔离级别,在B端修改数据。

(一)、将A的隔离级别设置为read uncommitted(未提交读)

 在B未更新数据之前:

客户端A:

B更新数据:

客户端B:

客户端A:

        经过上面的实验可以得出结论,事务B更新了一条记录,但是没有提交,此时事务A可以查询出未提交记录。造成脏读现象。未提交读是最低的隔离级别。

(二)、将客户端A的事务隔离级别设置为read committed(已提交读)

 在B未更新数据之前:

客户端A:

B更新数据:

客户端B:

客户端A:

       经过上面的实验可以得出结论,已提交读隔离级别解决了脏读的问题,但是出现了不可重复读的问题,即事务A在两次查询的数据不一致,因为在两次查询之间事务B更新了一条数据。已提交读只允许读取已提交的记录,但不要求可重复读。

(三)、将A的隔离级别设置为repeatable read(可重复读)

 在B未更新数据之前:

客户端A:

B更新数据:

客户端B:

客户端A:

B插入数据:

客户端B:

客户端A:

       由以上的实验可以得出结论,可重复读隔离级别只允许读取已提交记录,而且在一个事务两次读取一个记录期间,其他事务部的更新该记录。但该事务不要求与其他事务可串行化。例如,当一个事务可以找到由一个已提交事务更新的记录,但是可能产生幻读问题(注意是可能,因为数据库对隔离级别的实现有所差别)。像以上的实验,就没有出现数据幻读的问题。

(四)、将A的隔离级别设置为 可串行化 (Serializable)

A端打开事务,B端插入一条记录

事务A端:

事务B端:

因为此时事务A的隔离级别设置为serializable,开始事务后,并没有提交,所以事务B只能等待。

事务A提交事务:

事务A端

事务B端

      

         serializable完全锁定字段,若一个事务来查询同一份数据就必须等待,直到前一个事务完成并解除锁定为止 。是完整的隔离级别,会锁定对应的数据表格,因而会有效率的问题。


附上查询和修改隔离级别语句:

用户可以用SET TRANSACTION语句改变单个会话或者所有新进连接的隔离级别。它的语法如下:

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

你可以用下列语句查询全局和会话事务隔离级别:

SELECT @@global.tx_isolation; 
SELECT @@session.tx_isolation; 
SELECT @@tx_isolation;

修改:

mysql> set tx_isolation=’read-committed’;
Query OK, 0 rows affected (0.00 sec)

+—————-+
| @@tx_isolation |
+—————-+
| READ-COMMITTED |
+—————-+
1 row in set (0.00 sec)

mysql> select @@session.tx_isolation;
+————————+
| @@session.tx_isolation |
+————————+
| READ-COMMITTED         |
+————————+
1 row in set (0.00 sec)



转自:http://xm-king.iteye.com/blog/770721


7月 112013
 

先更新一条数据

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;

如果使用node.js执行,控制台上会看到待执行的sql处于等待状态:
 

上面报错,是因为超时了。
如果执行commit后,再执行node.js脚本(执行的是一句更新sql–UPDATE user_info SET userName = ‘张一’ WHERE userId = 1)

 

这里主要用到了node-mysql-queues,它需要先安装node-mysql

可能因为版本问题吧,它的页面给出的操作方法已经失效了。正确的应该如下:

/**
 * 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();

 

 

 

 

 

 

 

 

 

它还支持一次执行多条sql,其实就是把sql语句放入一个数组,然后循环执行该数组内的每条SQL。

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. */

 
query方法的实现:
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);
};

如果第二个参数不是函数,调用format则转换sql,默认将?替换为数组参数的值(一一对应)
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);
};

默认的format方法实现如下:
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);
  });
};

也可以使用自定义的函数进行处理,在创建连接的时候,传入queryFormat参数即可。