如何進(jìn)行MySQL 5.5 隔離級(jí)別的測試

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

REPEATABLE READ

這是InnoDB默認(rèn)的隔離級(jí)別。對于一致性讀，REPEATABLE READ和READ COMMITTED有一個(gè)重要的區(qū)別：在同一個(gè)事務(wù)中，所有的一致性讀會(huì)從這個(gè)事務(wù)第一次讀取的快照中讀取數(shù)據(jù)。如果在相同事務(wù)中執(zhí)行多條相同的SELECT語句，這些SELECT查詢語句的返回結(jié)果是相同的。對于鎖定讀（例如SELECT .. FOR UPDATE 或 LOCK IN SHARE MODE），UPDATE, DELETE語句，鎖依賴語句是否使用unique索引掃描或范圍掃描。對于unique索引掃描，InnoDB只會(huì)鎖定找到的索引記錄。對于其他的搜索條件，InnoDB會(huì)鎖定范圍索引掃描到的記錄。

會(huì)話①向表插入數(shù)據(jù)并提交，會(huì)話②只有在執(zhí)行commit或rollback操作后，才可以查詢到會(huì)話①的插入數(shù)據(jù)，否則看到的是登錄時(shí)最初查詢到的快照中的數(shù)據(jù)。

會(huì)話①
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)

會(huì)話②
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)

會(huì)話①
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)

會(huì)話②
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對數(shù)據(jù)進(jìn)行范圍、條件掃描時(shí)，會(huì)對范圍內(nèi)也許并不存在的值進(jìn)行加鎖。

會(huì)話①
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)

會(huì)話②

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的事務(wù)隔離級(jí)別：每個(gè)一致性讀，及時(shí)在相同的事務(wù)中，會(huì)讀取自己事務(wù)最新的快照。對于鎖定讀（例如SELECT .. FOR UPDATE 或 LOCK IN SHARE MODE），UPDATE, DELETE語句，InnoDB只會(huì)鎖定索引記錄，允許向非鎖定的記錄插入新的記錄。

會(huì)話①刪除一條數(shù)據(jù)并執(zhí)行提交操作后，會(huì)話②會(huì)立刻查詢到會(huì)話①更新后的操作。

mysql> SET GLOBAL tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye

會(huì)話①
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)

會(huì)話②
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)

會(huì)話①
mysql> commit;
Query OK, 0 rows affected (0.12 sec)

會(huì)話②

mysql> select * from dept2;
+--------+-------------+
| deptno | dname       |
+--------+-------------+
|     10 | Research    |
|     20 | Maintenance |
|     30 | Leader      |
+--------+-------------+
3 rows in set (0.00 sec)

間隔鎖測試，在READ-COMMITTED隔離模式下，會(huì)話②不會(huì)受到會(huì)話①的影響。

會(huì)話①
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)

會(huì)話②

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語句允許以非鎖定的方式執(zhí)行，但是只有較早版本的行可以使用。因而，使用這個(gè)隔離級(jí)別，一些的讀操作不是一致性的。這個(gè)隔離級(jí)別也被稱為臟讀。

SERIALIZABLE

這個(gè)隔離級(jí)別類似REPEATABLE READ，但是如果autocommit沒有開啟的話，InnoDB會(huì)隱式將所有SELECT語句轉(zhuǎn)化為SELECT ... LOCK IN SHARE MODE。

關(guān)于如何進(jìn)行MySQL 5.5 隔離級(jí)別的測試就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到。