如何进行MySQL 5.5 隔离级别的测试

本文将详细解释如何测试MySQL 5.5隔离级别。这篇文章的内容质量很高，所以边肖会分享给大家参考。希望你看完这篇文章后有所了解。

REPEATABLE READ

这是InnoDB的默认隔离级别。对于一致读取，REPEATABLE READ和READ COMMITTED之间有一个重要的区别：在同一个事务中，所有一致读取都将在该事务中首次从快照读取中读取数据。如果在同一事务中执行多个相同的SELECT语句，这些SELECT查询语句的返回结果是相同的。对于锁定读取(如选择.对于UPDATE或LOCK IN SHARE MODE)、UPDATE、DELETE语句，无论依赖于锁的语句是使用唯一索引扫描还是范围扫描。对于唯一索引扫描，InnoDB将只锁定找到的索引记录。对于其他搜索条件，InnoDB将锁定范围索引扫描的记录。会话向表插入数据并提交，会话只有在执行commit或rollback操作后，才可以查询到会话的插入数据，否则看到的是登录时最初查询到的快照中的数据。

对话

mysql显示像“%iso%”这样的变量；

- -

|变量名|值|

- -

| tx_isolation | REPEATABLE-READ |

- -

1行一组(0.09秒)

mysql begin

查询正常，0行受影响(0.00秒)

mysql插入dept2值(10，‘Research’)；

查询正常，1行受影响(0.01秒)

mysql提交；

查询正常，0行受影响(0.12秒)

mysql从dept2中选择*；

- -

| deptno | dname |

- -

| 10 |研究|

- -

1行一组(0.00秒)

对话

mysql显示像“%iso%”这样的变量；

- -

|变量名|值|

- -

| tx_isolation | REPEATABLE-READ |

- -

1行一组(0.01秒)

mysql从dept2中选择*；

空集(0.00秒)

mysql提交；

查询正常，0行受影响(0.16秒)

mysql从dept2中选择*；

- -

| deptno | dname |

- -

| 10 |研究|

- -

1行一组(0.00秒)

对话

mysql启动事务

- ;

查询正常，0行受影响(0.00秒)

mysql插入dept2值(20，‘维护’)；

查询正常，1行受影响(0.04秒)

mysql commi

t;
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。

关于如何进行MySQL 5.5 隔离级别的测试就分享到这里了，希望