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。
這是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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL的隔離級別MySql
- MySQL事務隔離級別MySql
- MySQL 事務隔離級別MySql
- [Mysql]事務/隔離級別MySql
- 測試一下MySQL四種隔離級別都做了什麼MySql
- mysql中read commited與repeatable read兩種隔離級別的測試MySqlMIT
- 面試問爛的 MySQL 四種隔離級別面試MySql
- MySQL事務的隔離級別MySql
- MySQL 的隔離級別 自理解MySql
- 理解MySQL事務隔離級別MySql
- MySQL的事務隔離級別MySql
- 淺析MySQL InnoDB的隔離級別MySql
- Mysql資料庫的隔離級別MySql資料庫
- MySQL事務隔離級別和MVCCMySqlMVC
- 理解mysql的事務隔離級別MySql
- 啥是 MySQL 事務隔離級別？MySql
- mysql如何修改事務隔離級別MySql
- MySQL之事務隔離級別和MVCCMySqlMVC
- mysql事務隔離級別和鎖MySql
- Mysql鎖與事務隔離級別MySql
- Mysql 四種事務隔離級別MySql
- MySQL 的四種事務隔離級別MySql
- MySQL 事務隔離級別實現原理MySql
- MySQL 事務的隔離級別初窺MySql
- MySQL的事務處理及隔離級別MySql
- 徹底搞懂 MySQL 事務的隔離級別MySql
- Mysql事務隔離級別與鎖機制MySql
- MySQL的事務隔離級別是什麼？MySql
- MySQL 事務隔離級別解析和實戰MySql
- 四個案例看懂 MySQL 事務隔離級別MySql
- 面試官問你：MYSQL事務和隔離級別，該如何回答面試MySql
- 面試被吊打系列 - 事務隔離級別面試
- 論 MySQL 之事務隔離級別 | 資料庫篇MySql資料庫
- MySQL資料庫中的四種隔離級別MySql資料庫
- MySQL資料庫引擎、事務隔離級別、鎖MySql資料庫
- 如何理解mysql 的事務隔離級別 repeatable readMySql
- MySQL RC隔離級別下罕見的gap lockMySql
- 資料庫隔離級別資料庫
- Mariadb之事務隔離級別