(16)mysql 中的表鎖定及事務控制

林灣村龍貓發表於2017-01-20
MySql

概述

MySQL支援三種鎖級別：頁級、表級、行級。MyISAM和MEMORY儲存引擎採用的是表級鎖（table-level locking）；BDB儲存引擎採用的是頁面鎖（page-level locking），但也支援表級鎖；InnoDB儲存引擎既支援行級鎖（row-level locking），也支援表級鎖，但預設情況下是採用行級鎖。在預設情況下，表鎖和行鎖都是自動獲取的，不需要額外的命令。三種鎖級別優缺點如下：

  • 表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的概率最高,併發度最低。
  • 頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間；會出現死鎖；鎖定粒度界於表鎖和行鎖之間，併發度一般。
  • 行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低,併發度也最高。

表鎖定

1.語法

#鎖定表
LOCK TABLES 
    tb_name1 [AS alias] {READ[LOCAL]|[LOW_PRIORITY] WRITE}
    tb_name2 [AS alias] {READ[LOCAL]|[LOW_PRIORITY] WRITE}
    ...
#釋放表鎖定
UNLOCK TABLES;複製程式碼
  • lock tables 可以鎖定用於當前執行緒（會話）的表。如果被其他執行緒鎖定，則當前執行緒會等待，直到可以獲取所有鎖定為止。
  • unlock tables釋放當前執行緒（會話）獲得的任何鎖定。
  • read(讀鎖/共享鎖)：當表不存在 WRITE 寫鎖時 READ 讀鎖被執行,這該狀態下,當前執行緒不可以修改(insert,update,delete),其他執行緒的修改操作進入列隊,噹噹前執行緒釋放鎖,其他執行緒修改被執行。
  • read local：read local和read之間的區別是，read local允許在鎖定被保持時，執行非衝突性INSERT語句（同時插入）。但是，如果您正打算在MySQL外面運算元據庫檔案，同時您保持鎖定，則不能使用read local。對於InnoDB表，read local與read相同。
  • write(寫鎖/排它鎖)：除了當前使用者被允許讀取和修改被鎖表外，其他使用者的所有訪問（讀/寫）被完全阻止。注意的是在當前執行緒當WRITE被執行的時候,即使之前加了READ沒被取消,也會被取消。
  • low_priority write：降低優先順序的write,預設write的優先順序高於read.假如當前執行緒的low_priority write在列隊裡面,在未執行之前其他執行緒傳送一條read,那麼low_priority write繼續等待.
  • InnoDB行鎖是通過給索引上的索引項加鎖來實現的，這一點MySQL與Oracle不同，後者是通過在資料塊中對相應資料行加鎖來實現的。InnoDB這種行鎖實現特點意味著：只有通過索引條件檢索資料，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！
  • 併發插入：原則上資料表有一個讀鎖時，其它程式無法對此表進行更新操作，但在一定條件下，MyISAM表也支援查詢和插入操作的併發進行。MyISAM儲存引擎有一個系統變數concurrent_insert，專門用以控制其併發插入的行為，其值分別可以為0、1或2。
    • 當concurrent_insert設定為0時，不允許併發插入。
    • 當concurrent_insert設定為1時，如果MyISAM表中沒有空洞（即表的中間沒有被刪除的行），MyISAM允許在一個程式讀表的同時，另一個程式從表尾插入記錄。這也是MySQL的預設設定。
    • 當concurrent_insert設定為2時，無論MyISAM表中有沒有空洞，都允許在表尾併發插入記錄。

2.舉例

這是一個獲取表鎖及釋放表鎖的例子。其中session1和session2表示兩個同時開啟的session(連線/執行緒)，表格中的每一行表示同一時刻兩個session的執行情況，後面的例子也是同樣的格式。

(16)mysql 中的表鎖定及事務控制
表鎖舉例

事務控制

MySQL通過set autocommit、start transaction、commit、rollback等語句支援本地事務。

1.語法

START TRANSACTION |BEGIN [WORK]
COMMIT [WORK][AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK][AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT={0|1}複製程式碼
  • 預設情況下，MySQL是自動提交的，如果需要通過明確的Commit和Rollback來提交和回滾事務，那麼就需要通過明確的事務控制命令來開始事務。
  • start transaction 或begin語句開始一項新的事務。
  • commit和rollback用來提交或者回滾事務。
  • chain和release子句分別用來定義在事務提交或者回滾之後的操作，chain會立即啟動一個新事物，並且和剛才的事務具有相同的隔離級別，release則會斷開和客戶端的連線。
  • set autocommit可以修改當前連線的提交方式，如果設定了set autocommit=0，則設定之後的所有事務都需要通過明確的命令進行提交或者回滾。

2.舉例

舉例一
使用start transaction和commit and chain。

(16)mysql 中的表鎖定及事務控制
事務控制1-1

(16)mysql 中的表鎖定及事務控制
事務控制1-2

(16)mysql 中的表鎖定及事務控制
事務控制1-3

舉例二
在鎖表期間，用start transaction命令開啟新事務，會造成隱含的unlock tables被執行。

(16)mysql 中的表鎖定及事務控制
事務控制2-1

(16)mysql 中的表鎖定及事務控制
事務控制2-2

舉例三
在事務中可以通過定義savepoint，指定回滾事務的一個部分，但是不能指定提交事務的一個部分。對於複雜的應用，可以定義多個不同的savepoint，滿足不同的條件時，回滾不同的savepoint。需要注意的是，如果定義了相同名字的savepoint，則後面定義的savepoint會覆蓋之前的定義。對於不再需要使用的savepoint，可以通過release savepoint命令刪除savepoint，刪除後的savepoint，不能再執行rollback to savepoint命令。
(16)mysql 中的表鎖定及事務控制
事務控制3-1

(16)mysql 中的表鎖定及事務控制
事務控制3-2

參考

blog.csdn.net/xiao7ng/art…
www.cnblogs.com/huangye-dre…
c.biancheng.net/cpp/html/14…

相關文章