START TRANSACTION, COMMIT和ROLLBACK語法

start transaction, commit和rollback語法
start transaction | begin [work]commit [work] [and [no] chain] [[no] release]rollback [work] [and [no] chain] [[no] release]set autocommit = {0 | 1}start transaction或begin語句可以開始一項新的事務。commit可以提交當前事務，是變更成為永久變更。rollback可以 回滾當前事務，取消其變更。set autocommit語句可以禁用或啟用默認的autocommit模式，用於當前連接。

自選的work關鍵詞被支持，用於commit和release，與chain和release子句。chain和release可以被用於對事務完成進行附加控制。completion_type系統變量的值決定了默認完成的性質。請參見5.3.3節，“服務器系統變量”。

and chain子句會在當前事務結束時，立刻啟動一個新事務，並且新事務與剛結束的事務有相同的隔離等級。release子句在終止了當前事務後，會讓服務器斷開與當前客戶端的連接。包含no關鍵詞可以抑制chain或release完成。如果completion_type系統變量被設置為一定的值，使連鎖或釋放完成可以默認進行，此時no關鍵詞有用。

默認情況下，mysql教程采用autocommit模式運行。這意味著，當您執行一個用於更新（修改）表的語句之後，mysql立刻把更新存儲到磁盤中。

如果您正在使用一個事務安全型的存儲引擎（如innodb, bdb或ndb簇），則您可以使用以下語句禁用autocommit模式：

set autocommit=0;通過把autocommit變量設置為零，禁用autocommit模式之後，您必須使用commit把變更存儲到磁盤中，或著如果您想要忽略從事務開始進行以來做出的變更，使用rollback。

如果您想要對於一個單一系列的語句禁用autocommit模式，則您可以使用start transaction語句：

start transaction;select @a:=sum(salary) from table1 where type=1;update table2 set summary=@a where type=1;commit;使用start transaction，autocommit仍然被禁用，直到您使用commit或rollback結束事務為止。然後autocommit模式恢復到原來的狀態。

begin和begin work被作為start transaction的別名受到支持，用於對事務進行初始化。start transaction是標准的sql語法，並且是啟動一個ad-hoc事務的推薦方法。begin語句與begin關鍵詞的使用不同。begin關鍵詞可以啟動一個begin...end復合語句。後者不會開始一項事務。請參見20.2.7節，“begin ... end復合語句”。

您也可以按照如下方法開始一項事務：

start transaction with consistent snaps教程hot;with consistent snapshot子句用於啟動一個一致的讀取，用於具有此類功能的存儲引擎。目前，該子句只適用於innodb。該子句的效果與發布一個start transaction，後面跟一個來自任何innodb表的select的效果一樣。請參見15.2.10.4節，“一致的非鎖定讀”。

開始一項事務會造成一個隱含的unlock tables被執行。

為了獲得最好的結果，事務應只使用由單一事務存儲引擎管理的表執行。否則，會出現以下問題：

·         如果您使用的表來自多個事務安全型存儲引擎（例如innodb和bdb），並且事務隔離等級不是serializable，則有可能當一個事務提交時，其它正在進行中的、使用同樣的表的事務將只會發生由第一個事務產生的變更。也就是，用混合引擎不能保證事務的原子性，並會造成不一致。（如果混合引擎事務不經常有，則您可以根據需要使用set transaction isolation level把隔離等級設置到serializable。）

·         如果您在事務中使用非事務安全型表，則對這些表的任何變更被立刻存儲，不論autocommit模式的狀態如何。

如果您在更新了事務中一個事務表之後，發布一個rollback語句，則會出現一個er_warning_not_complete_rollback警告。對事務安全型表的變更被 回滾，但是對非事務安全型表沒有變更。

每個事務被存儲在一個組塊中的二進制日志中，在commit之上。被回滾的事務不被計入日志。（例外情況：對非事務表的更改不會被 回滾。如果一個被回滾的事務包括對非事務表的更改，則整個事務使用一個在末端的rollback語句計入日志，以確保對這些表的更改進行復制。）見5.11.3節，“二進制日志”。

您可以使用set transaction isolation level更改事務的隔離等級。請參見13.4.6節，“set transaction語法”。

回滾可以慢速運行。在用戶沒有明確要求時，也可以進行回滾（例如，當錯誤發生時）。因此，在明確地和隱含的（rollback sql命令）回滾時，show processlist會在stage列中顯示rolling back，用於連接。

13.4.2. 不能回滾的語句
有些語句不能被回滾。通常，這些語句包括數據定義語言（ddl）語句，比如創建或取消數據庫教程的語句，和創建、取消或更改表或存儲的子程序的語句。

您在設計事務時，不應包含這類語句。如果您在事務的前部中發布了一個不能被回滾的語句，則後部的其它語句會發生錯誤，在這些情況下，通過發布rollback語句不能 回滾事務的全部效果。

13.4.3. 會造成隱式提交的語句
以下語句（以及同義詞）均隱含地結束一個事務，似乎是在執行本語句前，您已經進行了一個commit。

·         alter function, alter procedure, alter table, begin, create database, create function, create index, create procedure, create table, drop database, drop function, drop index, drop procedure, drop table, load master data, lock tables, rename table, set autocommit=1, start transaction, truncate table, unlock tables.

·         當當前所有的表均被鎖定時，unlock tables可以提交事務。

·         create table, create database drop database, truncate table, alter function, alter procedure, create function, create procedure, drop function和drop procedure等語句會導致一個隱含提交。

·         innodb中的create table語句被作為一個單一事務進行處理。這意味著，來自用戶的rollback不會撤銷用戶在事務處理過程中創建的create table語句。

事務不能被嵌套。這是隱含commit的結果。當您發布一個start transaction語句或其同義詞時，該commit被執行，用於任何當前事務。

13.4.4. savepoint和rollback to savepoint語法
savepoint identifierrollback [work] to savepoint identifierrelease savepoint identifierinnodb支持sql語句savepoint, rollback to savepoint, release savepoint和自選的用於rollback的work關鍵詞。

savepoint語句用於設置一個事務保存點，帶一個標識符名稱。如果當前事務有一個同樣名稱的保存點，則舊的保存點被刪除，新的保存點被設置。

rollback to savepoint語句會向以命名的保存點回滾一個事務。如果在保存點被設置後，當前事務對行進行了更改，則這些更改會在 回滾中被撤銷。但是，innodb不會釋放被存儲在保存點之後的存儲器中的行鎖定。（注意，對於新插入的行，鎖定信息被存儲在行中的事務id承載；鎖定沒有被分開存儲在存儲器中。在這種情況下，行鎖定在撤銷中被釋放。）在被命名的保存點之後設置的保存點被刪除。

如果語句返回以下錯誤，則意味著不存在帶有指定名稱的保存點：

error 1181: got error 153 during rollbackrelease savepoint語句會從當前事務的一組保存點中刪除已命名的保存點。不出現提交或 回滾。如果保存點不存在，會出現錯誤。

如果您執行commit或執行不能命名保存點的rollback，則當前事務的所有保存點被刪除。

13.4.5. lock tables和unlock tables語法
lock tables    tbl_name [as alias] {read [local] | [low_priority] write}    [, tbl_name [as alias] {read [local] | [low_priority] write}] ...unlock tableslock tables可以鎖定用於當前線程的表。如果表被其它線程鎖定，則造成堵塞，直到可以獲取所有鎖定為止。unlock tables可以釋放被當前線程保持的任何鎖定。當線程發布另一個lock tables時，或當與服務器的連接被關閉時，所有由當前線程鎖定的表被隱含地解鎖。

  表鎖定只用於防止其它客戶端進行不正當地讀取和寫入。保持鎖定（即使是讀取鎖定）的客戶端可以進行表層級的操作，比如drop table。

注意，下面是對事務表使用lock tables的說明：

·         在嘗試鎖定表之前，lock tables不是事務安全型的，會隱含地提交所有活性事務。同時，開始一項事務（例如，使用start transaction），會隱含地執行unlock tables。（見13.4.3節，“會造成隱式提交的語句”。

·         對事務表（如innodb）使用lock tables的正確方法是，設置autocommit=0並且不能調用unlock tables，直到您明確地提交事務為止。當您調用lock tables時，innodb會內部地取其自己的表鎖定，mysql取其自己的表鎖定。innodb在下一個提交時釋放其表鎖定，但是，對於mysql，要釋放表鎖定，您必須調用unlock tables。您不應該讓autocommit=1，因為那樣的話，innodb會在調用lock tables之後立刻釋放表鎖定，並且很容易形成死鎖定。注意，如果autocommit=1，我們根本不能獲取innodb表鎖定，這樣就可以幫助舊的應用軟件避免不必要的死鎖定。

·         rollback不會釋放mysql的非事務表鎖定。

要使用lock tables，您必須擁有相關表的lock tables權限和select權限。

使用lock tables的主要原因是仿效事務，或在更新表時加快速度。這將在後面進行更詳細的解釋。

如果一個線程獲得對一個表地read鎖定，該線程（和所有其它線程）只能從該表中讀取。如果一個線程獲得對一個表的write鎖定，只有保持鎖定的線程可以對表進行寫入。其它的線程被阻止，直到鎖定被釋放時為止。

read local和read之間的區別是，read local允許在鎖定被保持時，執行非沖突性insert語句（同時插入）。但是，如果您正打算在mysql外面操作數據庫文件，同時您保持鎖定，則不能使用read local。對於innodb表，read local與read相同。

當您使用lock tables時，您必須鎖定您打算在查詢中使用的所有的表。雖然使用lock tables語句獲得的鎖定仍然有效，但是您不能訪問沒有被此語句鎖定的任何的表。同時，您不能在一次查詢中多次使用一個已鎖定的表——使用別名代替，在此情況下，您必須分別獲得對每個別名的鎖定。

mysql> lock table t write, t as t1 write;mysql> insert into t select * from t;error 1100: table 't' was not locked with lock tablesmysql> insert into t select * from t as t1;如果您的查詢使用一個別名引用一個表，那麼您必須使用同樣的別名鎖定該表。如果沒有指定別名，則不會鎖定該表。

mysql> lock table t read;mysql> select * from t as myalias;error 1100: table 'myalias' was not locked with lock tables相反的，如果您使用一個別名鎖定一個表，您必須使用該別名在您的查詢中引用該表。

mysql> lock table t as myalias read;mysql> select * from t;error 1100: table 't' was not locked with lock tablesmysql> select * from t as myalias;write鎖定通常比read鎖定擁有更高的優先權，以確保更新被盡快地處理。這意味著，如果一個線程獲得了一個read鎖定，則另一個線程會申請一個write鎖定，後續的read鎖定申請會等待，直到write線程獲得鎖定並釋放鎖定。您可以使用low_priority write鎖定來允許其它線程在該線程正在等待write鎖定時獲得read鎖定。只有當您確定最終將有一個時機，此時沒有線程擁有read鎖定時，您才應該使用low_priority write鎖定。

lock tables按照如下方式執行：

1.    按照內部定義的順序，對所有要被鎖定的表進行分類。從用戶的角度，此順序是未經定義的。

2.    如果使用一個讀取和一個寫入鎖定對一個表進行鎖定，則把寫入鎖定放在讀取鎖定之前。

3.    一次鎖定一個表，直到線程得到所有鎖定為止。

該規則確保表鎖定不會出現死鎖定。但是，對於該規則，您需要注意其它的事情：

如果您正在對一個表使用一個low_priority write鎖定，這只意味著，mysql等待特定的鎖定，直到沒有申請read鎖定的線程時為止。當線程已經獲得write鎖定，並正在等待得到鎖定表清單中的用於下一個表的鎖定時，所有其它線程會等待write鎖定被釋放。如果這成為對於應用程序的嚴重的問題，則您應該考慮把部分表轉化為事務安全型表。

您可以安全地使用kill來結束一個正在等待表鎖定的線程。請參見13.5.5.3節，“kill語法”。

注意，您不能使用insert delayed鎖定任何您正在使用的表，因為，在這種情況下，insert由另一個線程執行。

通常，您不需要鎖定表，因為所有的單個update語句都是原子性的；沒有其它的線程可以干擾任何其它當前正在執行的sql語句。但是，在幾種情況下，鎖定表會有好處：

·         如果您正在對一組myisam表運行許多操作，鎖定您正在使用的表，可以快很多。鎖定myisam表可以加快插入、更新或刪除的速度。不利方面是，沒有線程可以更新一個用read鎖定的表（包括保持鎖定的表），也沒有線程可以訪問用write鎖定的表（除了保持鎖定的表以外）。

有些myisam操作在lock tables之下更快的原因是，mysql不會清空用於已鎖定表的關鍵緩存，直到unlock table被調用為止。通常，關鍵緩存在每個sql語句之後被清空。

·         如果您正在使用mysql中的一個不支持事務的存儲引擎，則如果您想要確定在select和update之間沒有其它線程，您必須使用lock tables。本處所示的例子要求lock tables，以便安全地執行：

·                mysql> lock tables trans read, customer write;·                mysql> select sum(value) from trans where customer_id=some_id;·                mysql> update customer·                    ->     set total_value=sum_from_previous_statement·                    ->     where customer_id=some_id;·                mysql> unlock tables;如果沒有lock tables，有可能另一個線程會在執行select和update語句之間在trans表中插入一個新行