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

(16)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的運行情況，後面的例子也是同樣的格式。


事務控制

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。


<喎?http://www.Bkjia.com/kf/ware/vc/" target="_blank" class="keylink">vcD4NCjxwPjxjb2RlIGNsYXNzPQ=="language-sql hljs ">舉例二
在鎖表期間，用start transaction命令開啟新事務，會造成隱含的unlock tables被執行。


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