深入淺出MySQL交易處理和鎖機制

深入淺出MySQL交易處理和鎖機制

1.      交易處理和並發性

1.1.        基礎知識和相關概念

1 ）全部的表類型都可以使用鎖，但是只有 InnoDB 和 BDB 才有內建的事務功能。

2 ）使用 begin 開始事務，使用 commit 結束事務，中間可以使用 rollback 復原事務。

3 ）在預設情況下， InnoDB 表支援一致讀。

SQL 標準中定義了 4 個隔離等級： read uncommited ， read commited ， repeatable read ， serializable 。

read uncommited 即髒讀，一個事務修改了一行，另一個事務也可以讀到該行。

如果第一個事務執行了復原，那麼第二個事務讀取的就是從來沒有正式出現過的值。 ?

read commited 即一致讀，試圖通過唯讀取提交的值的方式來解決髒讀的問題，但是這又引起了不可重複讀取的問題。

一個事務執行一個查詢，讀取了大量的資料行。在它結束讀取之前，另一個事務可能完成了對資料行的更改。當第一個事務試圖再次執行同一個查詢，伺服器就會返回不同的結果。

repeatable read 即可重複讀，在一個事務對資料行執行讀取或寫入操作時鎖定了這些資料行。

但是這種方式又引發了幻想讀的問題。

因為只能鎖定讀取或寫入的行，不能阻止另一個事務插入資料，後期執行同樣的查詢會產生更多的結果。

serializable 模式中，事務被強製為依次執行。這是 SQL 標準建議的預設行為。

4 ）如果多個事務更新了同一行，就可以通過復原其中一個事務來解除死結。

5 ） MySQL 允許利用 set transaction 來設定隔離等級。

6 ）事務只用於 insert 和 update 語句來更新資料表，不能用於對錶結構的更改。執行一條更改表結構或 begin 則會立即提交當前的事務。

7 ）所有表類型都支援表級鎖，但是 MyISAM 只支援表級鎖。

8 ）有兩種類型的表級鎖：讀鎖和寫鎖。

讀鎖是共用鎖定，支援並發讀，寫操作被鎖。

寫鎖是獨佔鎖，上鎖期間其他線程不能讀表或寫表。

8 ）如果要支援並發讀寫，建議採用 InnoDB 表，因為它是採用行級鎖，可以獲得更多的更新效能。

9 ）很多時候，可以通過經驗來評估什麼樣的鎖對應用程式更合適，不過通常很難說一個鎖比別的更好，這全都要依據應用程式來決定，不同的地方可能需要不同的鎖。當前 MySQL 已經支援 ISAM, MyISAM, MEMORY (HEAP) 類型表的表級鎖了， BDB 表支援頁級鎖， InnoDB 表支援行級鎖。

10 ） MySQL 的表級鎖都是寫鎖優先，而且是採用排隊機制，這樣不會出現死結的情況。對於 InnoDB 和 BDB 儲存引擎來說，是可能產生死結的。這是因為 InnoDB 會自動捕獲行鎖， BDB 會在執行 SQL 陳述式時捕獲頁鎖的，而不是在事務的開始就這麼做。

1.2.        不同鎖的優缺點及選擇

行級鎖的優點及選擇 ：

1 ）在很多線程請求不同記錄時減少衝突鎖。

2 ）交易回復時減少改變資料。

3 ）使長時間對單獨的一行記錄加鎖成為可能。

行級鎖的缺點 ：

1 ）比頁級鎖和表級鎖消耗更多的記憶體。

2 ）當在大量表中使用時，比頁級鎖和表級鎖更慢，因為他需要請求更多的所資源。

3 ）當需要頻繁對大部分資料做 GROUP BY 操作或者需要頻繁掃描整個表時，就明顯的比其它鎖更糟糕。

4 ）使用更高層的鎖的話，就能更方便的支援各種不同的類型應用程式，因為這種鎖的開銷比行級鎖小多了。

5 ）可以用應用程式級鎖來代替行級鎖，例如 MySQL 中的 GET_LOCK() 和 RELEASE_LOCK() 。但它們是勸告鎖（原文： These are advisory locks ），因此只能用於安全可信的應用程式中。

6 ）對於 InnoDB 和 BDB 表， MySQL 只有在指定用 LOCK TABLES 鎖表時才使用表級鎖。在這兩種表中，建議最好不要使用 LOCK TABLES ，因為 InnoDB 自動採用行級鎖， BDB 用頁級鎖來保證事務的隔離。

表鎖的優點及選擇：

1 ）很多操作都是讀表。

2 ）在嚴格條件的索引上讀取和更新，當更新或者刪除可以用單獨的索引來讀取得到時： UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;DELETE FROM tbl_name WHERE unique_key_col=key_value;

3 ） SELECT 和 INSERT 語句並發的執行，但是只有很少的 UPDATE 和 DELETE 語句。

4 ）很多的掃描表和對全表的 GROUP BY 操作，但是沒有任何寫表。

表鎖的缺點：

1 ）一個用戶端提交了一個需要長時間啟動並執行 SELECT 操作。

2 ）其他用戶端對同一個表提交了 UPDATE 操作，這個用戶端就要等到 SELECT 完成了才能開始執行。

3 ）其他用戶端也對同一個表提交了 SELECT 請求。由於 UPDATE 的優先順序高於 SELECT ，所以 SELECT 就會先等到 UPDATE 完成了之後才開始執行，它也在等待第一個 SELECT 操作。

1.3.        如何避免鎖的資源競爭

1 ）讓 SELECT 速度盡量快，這可能需要建立一些摘要表。

2 ）啟動 mysqld 時使用參數 --low-priority-updates 。這就會讓更新操作的優先順序低於 SELECT 。

這種情況下，在上面的假設中，第二個 SELECT 就會在 INSERT 之前執行了，而且也無需等待第一個 SELECT 了。

3 ）可以執行 SET LOW_PRIORITY_UPDATES=1 命令，指定所有的更新操作都放到一個指定的連結中去完成。

4 ）用 LOW_PRIORITY 屬性來降低 INSERT ， UPDATE ， DELETE 的優先順序。

5 ）用 HIGH_PRIORITY 來提高 SELECT 語句的優先順序。

6 ）從 MySQL 3.23.7 開始，可以在啟動 mysqld 時指定系統變數 max_write_lock_count 為一個比較低的值，它能強制臨時地提高表的插入數達到一個特定值後的所有 SELECT 操作的優先順序。它允許在 WRITE 鎖達到一定數量後有 READ 鎖。

7 ）當 INSERT 和 SELECT 一起使用出現問題時，可以轉而採用 MyISAM 表，它支援並發的 SELECT 和 INSERT 操作。

8 ）當在同一個表上同時有插入和刪除操作時， INSERT DELAYED 可能會很有用。

9 ）當 SELECT 和 DELETE 一起使用出現問題時， DELETE 的 LIMIT 參數可能會很有用。

10 ）執行 SELECT 時使用 SQL_BUFFER_RESULT 有助於減短鎖表的期間。

11 ）可以修改原始碼 `mysys/thr_lock.c' ，只用一個所隊列。這種情況下，寫鎖和讀鎖的優先順序就一樣了，這對一些應用可能有協助。

--------------------------------------分割線 --------------------------------------

Ubuntu 14.04下安裝MySQL

《MySQL權威指南(原書第2版)》清晰中文掃描版 PDF

Ubuntu 14.04 LTS 安裝 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主從伺服器

Ubuntu 12.04 LTS 構建高可用分布式 MySQL 叢集

Ubuntu 12.04下原始碼安裝MySQL5.6以及Python-MySQLdb

MySQL-5.5.38通用二進位安裝

--------------------------------------分割線 --------------------------------------

本文永久更新連結地址：