mysql事務隔離的級別

這篇文章主要介紹“mysql事務隔離的級別”，在日常操作中，相信很多人在mysql事務隔離的級別問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”mysql事務隔離的級別”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

ANSI SQL標準定義了4中隔離級別：
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

ANSI隔離級別
隔離級別                        臟讀         不可重復讀       幻像讀
READ UNCOMMITTED      允許          允許               允許
READ COMMITTED          不允許       允許               允許
REPEATABLE READ          不允許       不允許           允許
SERIALIZABLE                不允許       不允許           不允許

以下給出測試的例子。

1.READ UNCOMMITTED

session 1:
mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values (2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

session 2:
mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> set tx_isolation = 'READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

==>隔離級別設置為READ-UNCOMMITTED后，可以看到未提交的數據。

2、READ-COMMITTED

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

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

session 2:
mysql>  SET tx_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

session 1:
mysql> insert into t values (2);
Query OK, 1 row affected (0.02 sec)

mysql> update t set id=3 where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t;
+------+
| id   |
+------+
|    3 |
|    2 |
+------+
2 rows in set (0.00 sec)

session2:
mysql> select * from t;
+------+
| id   |
+------+
|    3 |
|    2 |
+------+
2 rows in set (0.00 sec)

==>session1的數據提交后，session2的事務中能夠讀到最新的數據。

3、REPEATABLE READ

session1:
mysql> SET tx_isolation = 'REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    3 |
|    2 |
+------+
2 rows in set (0.00 sec)

session2:
mysql> SET tx_isolation = 'REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    3 |
|    2 |
+------+
2 rows in set (0.00 sec)

session 1:
mysql> update t set id=1  where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from t where id=3;
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

session 2:

mysql>  select * from t;
+------+
| id   |
+------+
|    3 |
|    2 |
+------+
2 rows in set (0.00 sec)

==>這里查出的數據還是事務開始時的數據，而不是最新的數據。
這里幻象讀的現象被解決了，是因為InnoDb引擎通過間隙鎖（nex-key locking)策略防止幻讀。
這里和標準的REPEATABLE READ隔離方式的現象有點出入。

4、SERIALIZABLE 

session 1:
mysql> SET tx_isolation = 'SERIALIZABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

session 2:
mysql> SET tx_isolation = 'SERIALIZABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

==>這里會給表t中的記錄加鎖（即加鎖讀），通過下列查詢可以看到：

mysql> SELECT trx_id, trx_state, trx_rows_locked, trx_rows_modified FROM information_schema.INNODB_TRX;
+--------+-----------+-----------------+-------------------+
| trx_id | trx_state | trx_rows_locked | trx_rows_modified |
+--------+-----------+-----------------+-------------------+
| 9598   | RUNNING   |               3 |                 0 |
+--------+-----------+-----------------+-------------------+

session 1:
mysql> update t set id=2 where id =1;
Query OK, 1 row affected (27.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

==》這里時間用了27.05 sec，是因為在等待session2釋放鎖，直到session 1發出commit或者rollback命令后，才能獲得鎖。

總結：
1、REPEATABLE READ為mysql默認的隔離級別方式,不允許重復讀和幻象讀。
2、InnoDb引擎通過間隙鎖（nex-key locking)策略防止幻讀。
3、SERIALIZABLE 隔離方式下會加鎖讀，類似于select … for update,而其他的隔離級別不會。

到此，關于“mysql事務隔離的級別”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！