中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

溫馨提示×

溫馨提示×

您好,登錄后才能下訂單哦!

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

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

發布時間:2021-11-16 15:41:09 來源:億速云 閱讀:97 作者:柒染 欄目:MySQL數據庫

這篇文章將為大家詳細講解有關如何進行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 隔離級別的測試就分享到這里了,希望以上內容可以對大家有一定的幫助,可以學到更多知識。如果覺得文章不錯,可以把它分享出去讓更多的人看到。

向AI問一下細節

免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。

AI

格尔木市| 任丘市| 奉化市| 合川市| 毕节市| 尖扎县| 宁陕县| 都匀市| 西华县| 宁波市| 家居| 共和县| 卢氏县| 安泽县| 清原| 常州市| 苍梧县| 博客| 分宜县| 五家渠市| 泗阳县| 扶沟县| 屏山县| 黄平县| 毕节市| 孝感市| 宣汉县| 汝阳县| 博兴县| 松原市| 修文县| 定安县| 浦东新区| 苏尼特右旗| 营山县| 浦县| 普陀区| 天祝| 叙永县| 江西省| 福泉市|