本文共 7093 字,大约阅读时间需要 23 分钟。
REPEATABLE READ 这是InnoDB默认的隔离级别。对于一致性读,REPEATABLE READ和READ COMMITTED有一个重要的区别:在同一个事务中,所有的一致性读会从这个事务第一次读取的快照中读取数据。如果在相同事务中执行多条相同的SELECT语句,这些SELECT查询语句的返回结果是相同的。对于锁定读(例如SELECT .. FOR UPDATE 或 LOCK IN SHARE MODE),UPDATE, DELETE语句,锁依赖语句是否使用unique索引扫描或范围扫描。对于unique索引扫描,InnoDB只会锁定找到的索引记录。对于其他的搜索条件,InnoDB会锁定范围索引扫描到的记录。 会话①向表插入数据并提交,会话②只有在执行commit或rollback操作后,才可以查询到会话①的插入数据,否则看到的是登录时最初查询到的快照中的数据。 会话① mysql> show variables like '%iso%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.09 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into dept2 values(10,'Research'); Query OK, 1 row affected (0.01 sec) mysql> commit; Query OK, 0 rows affected (0.12 sec) mysql> select * from dept2; +--------+----------+ | deptno | dname | +--------+----------+ | 10 | Research | +--------+----------+ 1 row in set (0.00 sec) 会话② mysql> show variables like '%iso%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.01 sec) mysql> select * from dept2; Empty set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.16 sec) mysql> select * from dept2; +--------+----------+ | deptno | dname | +--------+----------+ | 10 | Research | +--------+----------+ 1 row in set (0.00 sec) 会话① mysql> start transaction -> ; Query OK, 0 rows affected (0.00 sec) mysql> insert into dept2 values(20,'Maintenance'); Query OK, 1 row affected (0.04 sec) mysql> commit; Query OK, 0 rows affected (0.05 sec) mysql> select * from dept2; +--------+-------------+ | deptno | dname | +--------+-------------+ | 10 | Research | | 20 | Maintenance | +--------+-------------+ 2 rows in set (0.00 sec) 会话② mysql> select * from dept2; +--------+----------+ | deptno | dname | +--------+----------+ | 10 | Research | +--------+----------+ 1 row in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from dept2; +--------+-------------+ | deptno | dname | +--------+-------------+ | 10 | Research | | 20 | Maintenance | +--------+-------------+ 2 rows in set (0.00 sec) 间隔锁测试,在REPEATABLE-READ隔离模式下,MySQL对数据进行范围、条件扫描时,会对范围内也许并不存在的值进行加锁。 会话① mysql> select @@global.tx_isolation,@@tx_isolation; +-----------------------+-----------------+ | @@global.tx_isolation | @@tx_isolation | +-----------------------+-----------------+ | REPEATABLE-READ | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.00 sec) mysql> select * from dept2; +--------+-------------+ | deptno | dname | +--------+-------------+ | 10 | Research | | 20 | Maintenance | | 30 | Leader | +--------+-------------+ 3 rows in set (0.00 sec) mysql> select * from dept2 where deptno < 30 lock in share mode; +--------+-------------+ | deptno | dname | +--------+-------------+ | 10 | Research | | 20 | Maintenance | +--------+-------------+ 2 rows in set (0.02 sec) 会话② mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation; +-----------------------+-----------------+ | @@GLOBAL.tx_isolation | @@tx_isolation | +-----------------------+-----------------+ | REPEATABLE-READ | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into dept2 values(40,'Market'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction READ COMMITTED 类似Oracle的事务隔离级别:每个一致性读,及时在相同的事务中,会读取自己事务最新的快照。对于锁定读(例如SELECT .. FOR UPDATE 或 LOCK IN SHARE MODE),UPDATE, DELETE语句,InnoDB只会锁定索引记录,允许向非锁定的记录插入新的记录。 会话①删除一条数据并 执行提交操作后,会话②会立刻查询到 会话①更新后的操作。 mysql> SET GLOBAL tx_isolation='READ-COMMITTED'; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye 会话① mysql> use fire Database changed mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation; +-----------------------+----------------+ | @@GLOBAL.tx_isolation | @@tx_isolation | +-----------------------+----------------+ | READ-COMMITTED | READ-COMMITTED | +-----------------------+----------------+ 1 row in set (0.00 sec) mysql> select * from dept; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+ 4 rows in set (0.08 sec) mysql> select * from dept2; +--------+-------------+ | deptno | dname | +--------+-------------+ | 10 | Research | | 20 | Maintenance | | 30 | Leader | | 40 | Market | +--------+-------------+ 4 rows in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> delete from dept2 where deptno=40; Query OK, 1 row affected (0.09 sec) 会话② mysql> use fire Database changed mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation; +-----------------------+----------------+ | @@GLOBAL.tx_isolation | @@tx_isolation | +-----------------------+----------------+ | READ-COMMITTED | READ-COMMITTED | +-----------------------+----------------+ 1 row in set (0.00 sec) mysql> select * from dept2; +--------+-------------+ | deptno | dname | +--------+-------------+ | 10 | Research | | 20 | Maintenance | | 30 | Leader | | 40 | Market | +--------+-------------+ 4 rows in set (0.00 sec) 会话① mysql> commit; Query OK, 0 rows affected (0.12 sec) 会话② mysql> select * from dept2; +--------+-------------+ | deptno | dname | +--------+-------------+ | 10 | Research | | 20 | Maintenance | | 30 | Leader | +--------+-------------+ 3 rows in set (0.00 sec) 间隔锁测试,在READ-COMMITTED隔离模式下,会话②不会受到会话①的影响。 会话① mysql> select @@global.tx_isolation,@@tx_isolation; +-----------------------+----------------+ | @@global.tx_isolation | @@tx_isolation | +-----------------------+----------------+ | READ-COMMITTED | READ-COMMITTED | +-----------------------+----------------+ 1 row in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from dept2 where deptno < 30 lock in share mode; +--------+-------------+ | deptno | dname | +--------+-------------+ | 10 | Research | | 20 | Maintenance | +--------+-------------+ 2 rows in set (0.00 sec) 会话② mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation; +-----------------------+----------------+ | @@GLOBAL.tx_isolation | @@tx_isolation | +-----------------------+----------------+ | READ-COMMITTED | READ-COMMITTED | +-----------------------+----------------+ 1 row in set (0.00 sec) mysql> select * from dept2; +--------+-------------+ | deptno | dname | +--------+-------------+ | 10 | Research | | 20 | Maintenance | | 30 | Leader | +--------+-------------+ 3 rows in set (0.00 sec) mysql> insert into dept2 values(40,'Market'); Query OK, 1 row affected (0.11 sec) READ UNCOMMITTED SELECT语句允许以非锁定的方式执行,但是只有较早版本的行可以使用。因而,使用这个隔离级别,一些的读操作不是一致性的。这个隔离级别也被称为脏读。 SERIALIZABLE 这个隔离级别类似REPEATABLE READ,但是如果autocommit没有开启的话,InnoDB会隐式将所有SELECT语句转化为SELECT ... LOCK IN SHARE MODE。 来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26506993/viewspace-2109920/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26506993/viewspace-2109920/