Mysql 事務與鎖機制

標籤：髒讀   需要   ble   支援   sam   隔離   show   alt   mic   

一. 事務四要素

　　資料庫事務正確執行的四個基本要素包括原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、持久性（Durability），簡稱ACID。目前要實現ACID主要有兩種方式：一種是Write ahead logging，也就是日誌式的方式(現代資料庫均基於這種方式)；另一種是Shadow paging。

• 原子性：整個事務中的所有操作，要麼全部完成，要麼全部不完成，不可能停滯在中間某個環節。事務在執行過程中發生錯誤，會被復原（Rollback）到事務開始前的狀態
• 一致性：事務執行前與執行後都必須始終保持系統處於一致的狀態
• 隔離性：並發事務之間不會相互幹擾，彼此獨立執行
• 持久性：在事務完成以後，該事務對資料庫所作的更改便持久的儲存在資料庫之中

二. MyISAM表鎖定

　　所有使用者行為最後執行的基本單位是單條操作命令(SELECT、INSERT、UPDATE、DELETE等)，它們都是原子操作。按照事務術語，MyISAM表總能高效地工作在AUTOCOMMIT=1模式下，原子操作通常能提供可比較的完整性以及更好的效能。這意味著，你能確信在每個特性更新執行的同時，其他使用者不能干涉它，而且不會出現自動復原（如果你不小心，對於事務性表，這種情況可能發生），MySQL伺服器還能保證不存在髒讀。

　　一般而言，所有由事務解決的完整性問題均能用LOCK TABLES或原子更新解決，從而確保了伺服器不會自動中斷，後者是事務性資料庫系統的常見問題。MyISAM只支援表級鎖定，允許多重讀或一次寫。LOCK TABLES可以鎖定用於當前線程的表，如果表被其它線程鎖定，則造成堵塞，直到可以擷取所有鎖定為止。UNLOCK TABLES可以釋放被當前線程保持的任何鎖定。當線程發布另一個LOCK TABLES時，或當與伺服器的串連被關閉時，所有由當前線程鎖定的表被隱含地解鎖。如果您想要確保在同一業務流中的SELECT和UPDATE之間沒有其它線程訪問修改資料，就必須使用LOCK TABLES。如果你獲得了對某一表的READ LOCAL鎖定（與寫鎖定相對），該表允許在表尾執行並行插入，當其他用戶端執行插入操作時，允許執行讀操作。新插入的記錄不會被有讀鎖定屬性的用戶端看到，直至解除了該鎖定為止。使用INSERT DELAYED，能夠將插入項置於本地隊列中，直至鎖定解除，不會讓用戶端等待插入完成。

三. InnoDB事務與鎖定

　　在 InnoDB 中，所有使用者行為都在事務內發生，事務是其執行的基本單位。如果自動認可模式被允許，即 AUTOCOMMIT = 1，每個SQL語句都將以一個單獨的事務來運行；如果自動認可模式被用 SET AUTOCOMMIT = 0 關閉，那麼我們可以認為一個使用者總是有一個事務開啟著，一個SQL COMMIT或ROLLBACK語句結束當前事務並且一個新事務開始，兩個語句都釋放所有在當前事務中被設定的InnoDB鎖定。

　　InnoDB除了表級鎖定之外，還支援更細粒度的行級鎖定，且行鎖和表鎖多重粒度可共存。

• Shared (S) Locks：一個共用鎖定允許事務擷取鎖來讀取一行(READ)。如果事務T1持有對行 r 的 S 鎖， 那麼另外一個事務T2對行 r 的鎖需求會如下處理，T2對S鎖的請求會被馬上授權，因此T1、T2都對r同時分別持有一個共用鎖定。但T2對r的X鎖請求不會被馬上授權，這看上去似乎READ優先於WRITE，以致WRITE請求被餓死
• Exclusive (X) Locks：一個獨佔鎖定允許事務擷取鎖來更新或刪除一行(WRITE)。如果事務T1持有一個r的X鎖, 那麼T2對r的任何鎖類型都無法被馬上授權
• Intention Locks：意圖鎖定在InnoDB中是表鎖，他表明S或X鎖將會在一個事務中對某一行使用。Intention shared (IS) 表明事務T打算設定S鎖到表t上，Intention exclusive (IX) 表明事務T打算設定X鎖到行上。意圖鎖定協議如下：

  1. 在一個事務擷取表t的某行的S鎖之前, 他必須擷取表t的一個IS鎖或更強的鎖2. 在一個事務擷取表t某行的X鎖之前, 他必須擷取一個t的IX鎖3. 這些規則可以總結為如下鎖類型相容矩陣： XIXSISXConflictConflictConflictConflictIXConflictCompatibleConflictCompatibleSConflictConflictCompatibleCompatibleISConflictCompatibleCompatibleCompatible
  
  4. 一個鎖如果和已經存在的鎖相容, 就可以授權給請求他的事務, 但如果和已存在的鎖不相容則不行

   一個事務必須等待直到衝突的鎖被釋放.如果一個鎖請求和一個已經存在的鎖衝突， 並且一直不能被授權， 就會造成死結。一旦死結發生，InnoDB會選擇其中一個報錯並釋放其持有的鎖，直至解除死結。意圖鎖定並不會阻塞任何事情，除非是對全表的請求(例如, LOCK TABLES ... WRITE)， IX和IS鎖的主要目的是表示有人正在或者準備鎖定一行

四. 行鎖與表鎖優劣對比

　　行級鎖定的優點：

• 當在許多線程中訪問不同的行時只存在少量鎖定衝突
• 復原時只有少量的更改
• 可以長時間鎖定單一的行

　　行級鎖定的缺點：

• 比頁級或表級鎖定佔用更多的記憶體
• 當在表的大部分中使用時，比頁級或表級鎖定速度慢，因為你必須擷取更多的鎖
• 如果你在大部分資料上經常進行GROUP BY操作或者必須經常掃描整個表，比其它鎖定明顯慢很多
• 用進階別鎖定，通過支援不同的類型鎖定，你也可以很容易地調節應用程式，因為其鎖成本小於行級鎖定　　

　　MySQL表鎖定機制：當一個鎖定被釋放時，鎖定可被寫鎖定隊列中的線程得到，然後是讀鎖定隊列中的線程。這意味著，如果你在一個表上有許多更新，SELECT語句將等待直到沒有更多的更新。表更新通常情況認為比表檢索更重要，因此給予它們更高的優先順序，但這應確保更新一個表的活動不能“餓死”，即使該表上有很繁重的SELECT活動

　　對WRITE，MySQL使用的表鎖定方法原理如下：

• 如果在表上沒有鎖，在它上面放一個寫鎖
• 否則，把鎖定請求放在寫鎖定隊列中

　　對READ，MySQL使用的表鎖定方法原理如下：

• 如果在表上沒有寫鎖定，把一個讀鎖定放在它上面
• 否則，把鎖請求放在讀鎖定隊列中

　　在以下情況下，表鎖定優於行級鎖定：

• 表的大部分語句用於讀取
• 對嚴格的unique_key進行讀取和更新，你可以更新或刪除可以用單一的讀取的關鍵字來提取的一行：

  UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;DELETE FROM tbl_name WHERE unique_key_col=key_value;

• SELECT 結合并行的INSERT語句，並且只有很少的UPDATE或DELETE語句

• 在整個表上有許多掃描或GROUP BY操作，沒有任何寫操作

• 對於大表，對於大多數應用程式，表鎖定比行鎖定更好，但存在部分缺陷

　　表鎖定的注意事項：

• 使用SET LOW_PRIORITY_UPDATES=1語句可指定具體串連中的所有更新應使用低優先順序；
  或用LOW_PRIORITY屬性給與一個特定的INSERT、UPDATE或DELETE語句較低優先順序；
  或用HIGH_PRIORITY屬性給與一個特定的SELECT語句較高優先順序；
  或為max_write_lock_count系統變數指定一個低值來啟動mysqld來強制MySQL在具體數量的插入完成後臨時提高所有等待一個表的SELECT語句的優先順序；
  或用--low-priority-updates啟動mysqld，這將給所有更新(修改)一個表的語句以比SELECT語句低的優先順序

• 如果你有關於INSERT結合SELECT的問題，切換到使用新的MyISAM表，因為它們支援並發的SELECT和INSERT；
  如果你對同一個表混合插入和刪除，INSERT DELAYED將會有很大的協助；
  如果你對同一個表混合使用SELECT和DELETE語句出現問題，DELETE的LIMIT選項可以有所協助；
  對SELECT語句使用SQL_BUFFER_RESULT可以協助使表鎖定時間變短；
  可以更改mysys/thr_lock.c中的鎖代碼以使用單一的隊列，在這種情況下，寫鎖定和讀鎖定將具有相同的優先順序；
  如果不混合更新與需要在同一個表中檢查許多行的選擇，可以進行並行操作；
  可以使用LOCK TABLES來提高速度，因為在一個鎖定中進行許多更新比沒有鎖定的更新要快得多，另外將表中的內容切分為幾個表也可以有所協助

五. 選擇MyISAM

　　一般而言，使用行級鎖定的儲存引擎，應看看應用程式做什麼並且混合使用什麼樣的select和update。例如，大多數Web應用程式執行許多select，而很少進行delete，只對key的值進行更新，並且只插入少量行。在MySQL中對於使用表級鎖定的儲存引擎，表鎖定時不會死結的，這通過總是在一個查詢開始時立即請求所有必要的鎖定並且總是以同樣的順序鎖定表來管理。

　　可以通過檢查table_locks_waited和table_locks_immediate狀態變數來分析系統上的表鎖定爭奪：

mysql> SHOW STATUS LIKE ‘Table%‘;+-----------------------+---------+| Variable_name         | Value   |+-----------------------+---------+| Table_locks_immediate | 1151552 || Table_locks_waited    | 15324   |+-----------------------+---------+

　　如果資料檔案不包含空塊(從表的中部刪除或更新的行可能導致空洞），INSERT語句不衝突，可以自由為MyISAM表混合并行的INSERT和SELECT語句而不需要鎖定，你可以在其它客戶正讀取MyISAM表的時候插入行，記錄總是插入在資料檔案的尾部；如果不能同時插入，為了在一個表中進行多次INSERT和SELECT操作，可以在暫存資料表中插入行並且立即用暫存資料表中的記錄更新真正的表，這也適合做批量延遲插入：

mysql> LOCK TABLES real_table WRITE, insert_table WRITE; mysql> INSERT INTO real_table SELECT * FROM insert_table; mysql> TRUNCATE TABLE insert_table; mysql> UNLOCK TABLES;

 　　

六. 選擇InnoDB

　　InnoDB使用行鎖定，可能存在死結。這是因為，在SQL語句處理期間，InnoDB自動獲得行鎖定，而不是在事務啟動時獲得。對於InnoDB和BDB(BerkeleyDB)表，如果你用LOCK TABLES顯式鎖定表，MySQL只使用表鎖定，建議不要使用LOCK TABLES，因為InnoDB使用自動行級鎖定而BDB使用頁級鎖定來保證事務隔離。

　　InnoDB支援外鍵約束。

　　一般說來，以多讀為主也附帶少量寫首選MyISAM，否則選擇InnoDB或其他引擎會更佳。

 

七. 悲觀鎖與樂觀鎖

　　悲觀鎖和樂觀鎖不是資料庫中的標準概念，而只是一種通俗說法。

• 悲觀鎖：悲觀鎖指對資料被意外修改持保守態度，依賴資料庫原生支援的鎖機制來保證當前交易處理的安全性，防止其他並發事務對目標資料的破壞或破壞其他並發交易資料，將在事務開始執行前或執行中申請鎖定，執行完後再釋放鎖定。這對於長事務來講，可能會嚴重影響系統的並發處理能力

  LOCK TABLES a WRITE;INSERT INTO a VALUES (1,23),(2,34),(4,33);INSERT INTO a VALUES (8,26),(6,29);UNLOCK TABLES;

  鎖定表可以加速用多個語句執行的INSERT操作，因為索引緩衝區僅在所有INSERT陳述式完成後重新整理到磁碟上一次。一般有多少INSERT語句即有多少索引緩衝區重新整理，如果能用一個語句插入所有的行，就不需要鎖定；對於事務表，應使用BEGIN和COMMIT代替LOCK TABLES來加快插入

• 樂觀鎖：樂觀鎖相對悲觀鎖而言，先假想資料不會被並行作業修改，沒有資料衝突，只在資料進行提交更新的時候，才會正式對資料的衝突與否進行檢測，如果發現衝突了，則宣告失敗，否則更新資料。這就要求避免使用長事務和鎖機制，以免導致系統並發處理能力降低，保障系統生產效率。下面將說明使用樂觀鎖時的大致業務處理流程

  首 步：執行一次查詢 select some_column as old_value from some_table where id = id_value (假設該值在當前業務處理過程中不會被其他並發事務修改)
  ...第n步：old_value參與中間業務處理，比如old_value被自己修改 new_value = f(old_value)。這期間可能耗時很長，但不會為持有 some_column 而申請所在的行或表鎖定，因此其他並發事務可以獲得該鎖
  ...尾 步：執行條件更新 update some_table set some_column = new_value where id = id_value and some_column = old_value (條件更新中檢查old_value是否被修改)

Mysql 事務與鎖機制