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

創(chuàng)新互聯(lián)主營(yíng)蒲城網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營(yíng)網(wǎng)站建設(shè)方案,app軟件開發(fā)公司,蒲城h5微信平臺(tái)小程序開發(fā)搭建,蒲城網(wǎng)站營(yíng)銷推廣歡迎蒲城等地區(qū)企業(yè)咨詢

REPEATABLE READ

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

間隔鎖測(cè)試，在REPEATABLE-READ隔離模式下，MySQL對(duì)數(shù)據(jù)進(jìn)行范圍、條件掃描時(shí)，會(huì)對(duì)范圍內(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ù)最新的快照。對(duì)于鎖定讀（例如SELECT .. FOR UPDATE 或 LOCK IN SHARE MODE），UPDATE, DELETE語(yǔ)句，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)

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

SERIALIZABLE

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

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

網(wǎng)站題目：如何進(jìn)行MySQL5.5隔離級(jí)別的測(cè)試
文章分享：http://aaarwkj.com/article40/peshho.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供域名注冊(cè)、軟件開發(fā)、標(biāo)簽優(yōu)化、搜索引擎優(yōu)化、品牌網(wǎng)站設(shè)計(jì)、營(yíng)銷型網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)