點擊下載
本文文檔

當前位置：首頁 - 科技 - 知識百科 - 正文

MySQL事宜學習-&amp;gt;隔離級別

來源：懂視網責編：小采時間：2020-11-09 14:03:15

MySQL事宜學習-&gt;隔離級別

MySQL事宜學習->隔離級別:MySQL事務學習--隔離級別 6 事務的隔離級別設置的目的在數據庫操作中，為了有效保證并發讀取數據的正確性，提出的事務隔離級別。數據庫是要被廣大客戶所共享訪問的，那么在數據庫操作過程中很可能出現以下幾種不確定情況。更新丟失兩個事務都同時更新

推薦度：

點擊下載本文 文檔為doc格式

導讀MySQL事宜學習->隔離級別:MySQL事務學習--隔離級別 6 事務的隔離級別設置的目的在數據庫操作中，為了有效保證并發讀取數據的正確性，提出的事務隔離級別。數據庫是要被廣大客戶所共享訪問的，那么在數據庫操作過程中很可能出現以下幾種不確定情況。更新丟失兩個事務都同時更新

MySQL事務學習--隔離級別 6 事務的隔離級別設置的目的在數據庫操作中，為了有效保證并發讀取數據的正確性，提出的事務隔離級別。數據庫是要被廣大客戶所共享訪問的，那么在數據庫操作過程中很可能出現以下幾種不確定情況。更新丟失兩個事務都同時更新

MySQL事務學習-->隔離級別

6 事務的隔離級別

設置的目的

在數據庫操作中，為了有效保證并發讀取數據的正確性，提出的事務隔離級別。

數據庫是要被廣大客戶所共享訪問的，那么在數據庫操作過程中很可能出現以下幾種不確定情況。

更新丟失

兩個事務都同時更新一行數據，但是第二個事務卻中途失敗退出，導致對數據的兩個修改都失效了。這是因為系統沒有執行任何的鎖操作，因此并發事務并沒有被隔離開來。

臟讀

一個事務開始讀取了某行數據，但是另外一個事務已經更新了此數據但沒有能夠及時提交。這是相當危險的，因為很可能所有的操作都被回滾。

不可重復讀（Non-repeatable Reads）

一個事務對同一行數據重復讀取兩次，但是卻得到了不同的結果。它包括以下情況：

（1）事務T1讀取某一數據后，事務T2對其做了修改，當事務T1再次讀該數據時得到與前一次不同的值。

（2）幻讀（Phantom Reads）：事務在操作過程中進行兩次查詢，第二次查詢的結果包含了第一次查詢中未出現的數據或者缺少了第一次查詢中出現的數據（這里并不要求兩次查詢的SQL語句相同）。這是因為在兩次查詢過程中有另外一個事務插入數據造成的。

解決方法

為了避免上面出現的幾種情況，在標準SQL規范中，定義了4個事務隔離級別，不同的隔離級別對事務的處理不同。

未授權讀取

也稱為讀未提交（Read Uncommitted）：允許臟讀取，但不允許更新丟失。如果一個事務已經開始寫數據，則另外一個事務則不允許同時進行寫操作，但允許其他事務讀此行數據。該隔離級別可以通過“排他寫鎖”實現。

授權讀取

也稱為讀提交（Read Committed）：允許不可重復讀取，但不允許臟讀取。這可以通過“瞬間共享讀鎖”和“排他寫鎖”實現。讀取數據的事務允許其他事務繼續訪問該行數據，但是未提交的寫事務將會禁止其他事務訪問該行。

可重復讀取

可重復讀取（Repeatable Read）：禁止不可重復讀取和臟讀取，但是有時可能出現幻影數據。這可以通過“共享讀鎖”和“排他寫鎖”實現。讀取數據的事務將會禁止寫事務（但允許讀事務），寫事務則禁止任何其他事務。

序列化

序列化（Serializable）：提供嚴格的事務隔離。它要求事務序列化執行，事務只能一個接著一個地執行，但不能并發執行。如果僅僅通過“行級鎖”是無法實現事務序列化的，必須通過其他機制保證新插入的數據不會被剛執行查詢操作的事務訪問到。

隔離級別越高，越能保證數據的完整性和一致性，但是對并發性能的影響也越大。對于多數應用程序，可以優先考慮把數據庫系統的隔離級別設為Read Committed。它能夠避免臟讀取，而且具有較好的并發性能。盡管它會導致不可重復讀、虛讀和第二類丟失更新這些并發問題，在可能出現這類問題的個別場合，可以由應用程序采用悲觀鎖或樂觀鎖來控制。

在MySQL中設置事務隔離級別有2種方法:

1 在my.cnf中設置，在mysqld選項中如下設置

[mysqld]

transaction-isolation = READ-COMMITTED

2 在mysql窗口用set命令重置

mysql> set global tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.01 sec)
 
mysql>

查詢當前的會話事務級別，可以使用：

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)
 
mysql>

查詢全局的事務隔離級別，可以使用

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED |
+-----------------------+
1 row in set (0.00 sec)
 
mysql>

在Serializable模式下。

mysql> system cat /usr/local/mysql56m2/my.cnf |grep transaction-isolation
transaction-isolation = READ-COMMITTED
mysql>

復制二進制與隔離級別的關系

在SERIALIZABLE模式下，Innodb存儲引擎會對每個select語句自動加Lock in sharedmode，給每一個讀操作加共享鎖。因此在這個隔離級別下，讀占用鎖了，一致性的非鎖定讀不再予以支持。因為Innodb存儲引擎在repeatable read 模式下就已經達到了3度的隔離，所以一般不在本地事務中使用serializable隔離級別，serializable的事務隔離級別主要用于innodb存儲引擎的分布式事務。

在Read committed的隔離模式下，除了唯一性約束檢查以及外鍵約束檢查需要Gap lock，innodb存儲引擎不會使用gap lock的鎖算法。不過使用read committed隔離級別需要注意一些問題，mysql5.1中，Read committed的事務隔離級別默認只能在replication的二進制為row格式下，如果二進制默認在statement模式下，則會報如下錯誤：

mysql> select @@version;
+-------------+
| @@version |
+-------------+
| 5.5.25a-log |
+-------------+
1 row in set (0.00 sec)
 
mysql>
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT |
+-----------------+
1 row in set (0.00 sec)
 
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
 
mysql> set tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)
 
mysql> use test;
Database changed
mysql> create table a (b int, primary key (b)) engine=innodb;
ERROR 1050 (42S01): Table 'a' already exists
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)
 
mysql> begin
 -> ;
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into a select 100000;
ERROR 1665 (HY000): Cannotexecute statement: impossible to write to binary log since BINLOG_FORMAT =STATEMENT and at least one table uses a storage engine limited to row-basedlogging. InnoDB is limited to row-logging when transaction isolation level isREAD COMMITTED or READ UNCOMMITTED.

ERROR 1665 (HY000): Cannotexecute statement: impossible to write to binary log since BINLOG_FORMAT =STATEMENT and at least one table uses a storage engine limited to row-basedlogging. InnoDB is limited to row-logging when transaction isolation level isREAD COMMITTED or READ UNCOMMITTED.

[Note]：在mysql5.1以及mysql5.6模式下實驗過都是如此。也許可以知道通過將innodb_locks_unsafe_for_binlog設置為1，來可以使binlog日志在statement下使用readcommitted的事務隔離級別：

mysql> select @@innodb_locks_unsafe_for_binlog;
+----------------------------------+
| @@innodb_locks_unsafe_for_binlog |
+----------------------------------+
| 0 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> set global innodb_locks_unsafe_for_binlog=1;
ERROR 1238 (HY000): Variable 'innodb_locks_unsafe_for_binlog' is a readonly variable
mysql>

此參數是只讀模式，需要修改my.cnf重新啟動才行。

在my.cnf里面的[mysqld]添加

[mysqld]

innodb_locks_unsafe_for_binlog = 1

然后重啟，然后去check模仿一個事務操作，如下所示：

mysql> select @@innodb_locks_unsafe_for_binlog;
+----------------------------------+
| @@innodb_locks_unsafe_for_binlog |
+----------------------------------+
| 1 |
+----------------------------------+
1 row in set (0.00 sec)
 
mysql>
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into t select 15;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
 
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t;
+--------+
| id |
+--------+
| 1 |
| 12 |
| 15 |
| 11111 |
| 111110 |
+--------+
5 rows in set (0.00 sec)

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com