如何進行MySQL 5.5 隔離級別的測試

這篇文章將為大家詳細講解有關如何進行MySQL 5.5 隔離級別的測試，文章內容質量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。

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。

關于如何進行MySQL 5.5 隔離級別的測試就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。