SQLServer數(shù)據(jù)庫事務(wù)處理詳解TRANSACTION

1、 SQLServer數(shù)據(jù)庫事務(wù)處理詳解TRANSACTION務(wù)定義：事務(wù)是單個(gè)的作單元。如果某事務(wù)成功，則在該事務(wù)中進(jìn)的所有數(shù)據(jù)更改均會提交，成為數(shù)據(jù)庫中的永久組成部分。如果事務(wù)遇到錯(cuò)誤且必須取消或回滾，則所有數(shù)據(jù)更改均被清除。事務(wù)三種運(yùn)模式：動提交事務(wù)每條單獨(dú)的語句都是個(gè)事務(wù)。顯式事務(wù)每個(gè)事務(wù)均以 BEGIN TRANSACTION 語句顯式開始，以 COMMIT 或ROLLBACK 語句顯式結(jié)束。隱性事務(wù)在前個(gè)事務(wù)完成時(shí)新事務(wù)隱式啟動，但每個(gè)事務(wù)仍以 COMMIT 或 ROLLBACK 語句顯式完成。事務(wù)操作的語法：BEGIN TRANSACTIONBEGIN DISTRIBUTED TR

2、ANSACTIONCOMMIT TRANSACTIONCOMMIT WORKROLLBACK WORKSAVE TRANSACTIONBEGIN TRANSACTIONBEGIN TRANSACTION標(biāo)記個(gè)顯式本地事務(wù)的起始點(diǎn)。BEGIN TRANSACTION將 TRANCOUNT 加 1。BEGIN TRANSACTION 代表點(diǎn)，由連接引的數(shù)據(jù)在該點(diǎn)是邏輯和物理上都致的。如果遇上錯(cuò)誤，在 BEGIN TRANSACTION 之后的所有數(shù)據(jù)改動都能進(jìn)回滾，以將數(shù)據(jù)返回到已知的致狀態(tài) 。每個(gè)事務(wù)繼續(xù)執(zhí)直到它誤地完成并且 COMMIT TRANSACTION對數(shù)據(jù)庫作永久的改動，或者遇上錯(cuò)誤

3、并且 ROLLBACK TRANSACTION 語句擦除所有改動語法BEGIN TRAN SACTION transaction_name | tran_name_variable WITH MARK description 例：BEGIN TRAN T1UPDATE table1 .-nest transaction M2BEGIN TRAN M2 WITH MARKUPDATE table2 .SELECT * from table1COMMIT TRAN M2UPDATE table3 .COMMIT TRAN T1BEGIN DISTRIBUTED TRANSACTION指定個(gè)由 Mi

4、crosoft 分布式事務(wù)處理協(xié)調(diào)器 (MS DTC) 管理的 Transact-SQL 分布式事務(wù)的起始。語法BEGIN DISTRIBUTED TRAN SACTION transaction_name | tran_name_variable 參數(shù)transaction_name是戶定義的事務(wù)名，于跟蹤 MS DTC 實(shí)具中的分布式事務(wù)。 transaction_name 必須符合標(biāo)識符規(guī)則，但是僅使頭 32 個(gè)字符tran_name_variable是戶定義的個(gè)變量名，它含有個(gè)事務(wù)名，該事務(wù)名于跟蹤 MS DTC 實(shí)具中的分布式事務(wù)。必須 char、varchar、nchar 或nva

5、rchar 數(shù)據(jù)類型聲明該變量。 注釋執(zhí)BEGIN DISTRIBUTED TRANSACTION 語句的服務(wù)器是事務(wù)創(chuàng)建，并且控制事務(wù)的完成當(dāng)連接發(fā)出后續(xù) COMMIT TRANSACTION 或 ROLLBACK TRANSACTION 語句時(shí)，主控服務(wù)器請求 MS DTC 在所涉及的服務(wù)器間管理分布式事務(wù)的完成。有兩個(gè)法可將遠(yuǎn)程 SQL 服務(wù)器登記在個(gè)分布式事務(wù)中：分布式事務(wù)中已登記的連接執(zhí)個(gè)遠(yuǎn)程存儲過程調(diào)，該調(diào)引個(gè)遠(yuǎn)程服務(wù)器。分布式事務(wù)中已登記的連接執(zhí)個(gè)分布式查詢，該查詢引個(gè)遠(yuǎn)程服務(wù)器。例本例在本地和遠(yuǎn)程數(shù)據(jù)庫上更新作者的姓。本地和遠(yuǎn)程數(shù)據(jù)庫將同時(shí)提交或同時(shí)回滾本事務(wù)。說明當(dāng)前的SQL

6、 Server 上必須安裝 MS DTC.USE pubsGOBEGIN DISTRIBUTED TRANSACTIONUPDATE authorsSET au_lname = McDonald WHERE au_id = 409-56-7008EXECUTE link_Server_T.pubs.dbo.changeauth_lname 409-56-7008,McDonaldCOMMIT TRANGONote:如果需要連接遠(yuǎn)程DB,如果是linkServer 式連接的話，定要修該linkServer的 RPC 選項(xiàng)置為 True。SET XACT_ABORT指定當(dāng) Transact-SQL

7、 語句產(chǎn)運(yùn)時(shí)錯(cuò)誤時(shí)，Microsoft? SQL Server? 是否動回滾當(dāng)前事務(wù)。( 可以較簡單的理解，如果中間有任何句SQL 出錯(cuò)，所有SQL全部回滾.特別適于 Procedure 中間調(diào)Procedure ，如果第個(gè)Procedure Ok，被調(diào)的Procedure 中間有錯(cuò)誤，如果SET XACT_ABORT=false，則出錯(cuò)的部分回滾，其他部分提交，當(dāng)然外部Procedure 也提交。）.-在分布式Trans中定要注意設(shè)置下參數(shù)(XACT_ABORT)語法SET XACT_ABORT ON | OFF 注釋 當(dāng) SET XACT_ABORT 為 ON 時(shí)，如果 Transact-

8、SQL 語句產(chǎn)運(yùn)時(shí)錯(cuò)誤，整個(gè)事務(wù)將終并回滾。為 OFF 時(shí)，只回滾產(chǎn)錯(cuò)誤的Transact-SQL 語句，事務(wù)將繼續(xù)進(jìn)處理。編譯錯(cuò)誤（如語法錯(cuò)誤）不受 SET XACT_ABORT 的影響。對于多數(shù) OLE DB 提供程序（包括 SQL Server），隱性或顯式事務(wù)中的數(shù)據(jù)修改語句必須將 XACT_ABORT 設(shè)置為 ON。SET XACT_ABORT 的設(shè)置是在執(zhí)或運(yùn)時(shí)設(shè)置，不是在分析時(shí)設(shè)置。例 下例導(dǎo)致在含有其它 Transact-SQL 語句的事務(wù)中發(fā)違反外鍵錯(cuò)誤。在第個(gè)語句集中產(chǎn)錯(cuò)誤，但其它語句均成功執(zhí)且事務(wù)成功提交。在第個(gè)語句集中，SET XACT_ABORT 設(shè)置為 ON。這導(dǎo)致

9、語句錯(cuò)誤使批處理終，并使事務(wù)回滾。 CREATE TABLE t1 (a int PRIMARY KEY)CREATE TABLE t2 (a int REFERENCES t1(a)GOINSERT INTO t1 VALUES (1)INSERT INTO t1 VALUES (3)INSERT INTO t1 VALUES (4)INSERT INTO t1 VALUES (6)GOSET XACT_ABORT OFFGOBEGIN TRANINSERT INTO t2 VALUES (1)INSERT INTO t2 VALUES (2) /* Foreign key error */

10、INSERT INTO t2 VALUES (3)COMMIT TRANGOSET XACT_ABORT ONGOBEGIN TRANINSERT INTO t2 VALUES (4)INSERT INTO t2 VALUES (5) /* Foreign key error */INSERT INTO t2 VALUES (6)COMMIT TRANGOSAVE TRANSACTION在事務(wù)內(nèi)設(shè)置保存點(diǎn)。語法 SAVE TRAN SACTION savepoint_name | savepoint_variable 參數(shù) savepoint_name是指派給保存點(diǎn)的名稱。保存點(diǎn)名稱必須符合標(biāo)

11、識符規(guī)則，但只使前 32 個(gè)字符。savepoint_variable是戶定義的、含有有效保存點(diǎn)名稱的變量的名稱。必須 char、varchar、nchar 或 nvarchar 數(shù)據(jù)類型聲明該變量。 注釋戶可以在事務(wù)內(nèi)設(shè)置保存點(diǎn)或標(biāo)記。保存點(diǎn)定義如果有條件地取消事務(wù)的部分，事 務(wù)可以返回的位置。如果將事務(wù)回滾到保存點(diǎn)，則必須（如果需要，使更多的 Transact-SQL 語句和 COMMIT TRANSACTION 語句）繼續(xù)完成事務(wù)，或者必須（通過將事務(wù)回滾到其起始點(diǎn)）完全取消事務(wù)。若要取消整個(gè)事務(wù)，請使 ROLLBACK TRANSACTION transaction_name 格式。這

12、將撤消事務(wù)的所 有語句和過程。Note：1： 在由 BEGIN DISTRIBUTED TRANSACTION 顯式啟動或從本地事務(wù)升級來的分布式事務(wù)中，不持 SAVETRANSACTION。2：當(dāng)事務(wù)開始時(shí)，將直控制事務(wù)中所使的資源直到事務(wù)完成（也就是鎖定）。當(dāng)將事務(wù)的部分回滾到保存點(diǎn)時(shí)，將繼續(xù)控制資源直到事務(wù)完成（或者回滾全部事務(wù)）。例：begin transactionsave transaction Ainsert into demo values(BB,B term)rollback TRANSACTION Acreate table demo2(name varchar(10),a

13、ge int)insert into demo2(name,age) values(lis,1)commit transactionROLLBACK TRANSACTION 將顯式事務(wù)或隱性事務(wù)回滾到事務(wù)的起點(diǎn)或事務(wù)內(nèi)的某個(gè)保存點(diǎn)。語法ROLLBACK TRAN SACTION transaction_name | tran_name_variable | savepoint_name | savepoint_variable 參數(shù)transaction_name是給 BEGIN TRANSACTION 上的事務(wù)指派的名稱。transaction_name 必須符合標(biāo)識符規(guī)則，但只使事務(wù)名稱的

14、前 32 個(gè)字符。嵌套事務(wù)時(shí)，transaction_name 必須是來最遠(yuǎn)的 BEGIN TRANSACTION 語句的名稱。tran_name_variable是戶定義的、含有有效事務(wù)名稱的變量的名稱。必須 char、varchar、nchar 或 nvarchar 數(shù)據(jù)類型聲明該變量。savepoint_name是來 SAVE TRANSACTION 語句的 savepoint_name。savepoint_name 必須符合標(biāo)識符規(guī)則。當(dāng)條件回滾只影響事務(wù)的部分時(shí)使 savepoint_name。savepoint_variable是戶定義的、含有有效保存點(diǎn)名稱的變量的名稱。必須 ch

15、ar、varchar、nchar 或 nvarchar 數(shù)據(jù)類型聲明該變量。注釋 ROLLBACK TRANSACTION 清除事務(wù)的起點(diǎn)或到某個(gè)保存點(diǎn)所做的所有數(shù)據(jù)修改。ROLLBACK 還釋放由事務(wù)控制的資源。不帶 savepoint_name 和 transaction_name 的 ROLLBACK TRANSACTION 回滾到事務(wù)的起點(diǎn)。嵌套事務(wù)時(shí)，該語句將所有內(nèi)層事務(wù)回滾到 最遠(yuǎn)的 BEGIN TRANSACTION 語句。在這兩種情況下，ROLLBACK TRANSACTION 均將 TRANCOUNT 系統(tǒng)函數(shù)減為0。ROLLBACKTRANSACTION savepoint

16、_name 不減少 TRANCOUNT。Note:ROLLBACK TRANSACTION 語句若指定 savepoint_name 則不釋放任何鎖。在由 BEGIN DISTRIBUTED TRANSACTION 顯式啟動或從本地事務(wù)升級來的分布式事務(wù)中，ROLLBACK TRANSACTION 不能引savepoint_name。在執(zhí) COMMIT TRANSACTION 語句后不能回滾事務(wù)。在事務(wù)內(nèi)允許有重復(fù)的保存點(diǎn)名稱，但 ROLLBACK TRANSACTION 若使重復(fù)的保存點(diǎn)名稱，則只回滾到最近的使該保存點(diǎn)名稱的SAVE TRANSACTION。在存儲過程中，不帶 savepoi

17、nt_name 和 transaction_name 的 ROLLBACK TRANSACTION 語句將所有語句回滾到最遠(yuǎn)的BEGINTRANSACTION。在存儲過程中，ROLLBACK TRANSACTION 語句使 TRANCOUNT 在觸發(fā)器完成時(shí)的值不同于調(diào)該存儲過程時(shí)的TRANCOUNT 值，并且成個(gè)信息。該信息不影響后的處理。如果在觸發(fā)器中發(fā)出 ROLLBACK TRANSACTION：將回滾對當(dāng)前事務(wù)中的那點(diǎn)所做的所有數(shù)據(jù)修改，包括觸發(fā)器所做的修改。觸發(fā)器繼續(xù)執(zhí) ROLLBACK 語句之后的所有其余語句。如果這些語句中的任意語句修改數(shù)據(jù)，則不回滾這些修改。執(zhí)其余的語句不會激發(fā)

18、嵌套觸發(fā)器。在批處理中，不執(zhí)所有位于激發(fā)觸發(fā)器的語句之后的語句。每次進(jìn)觸發(fā)器，TRANCOUNT 就增加 1，即使在動提交模式下也是如此。（系統(tǒng)將觸發(fā)器視作隱性嵌套事務(wù)。）在存儲過程中，ROLLBACK TRANSACTION 語句不影響調(diào)該過程的批處理中的后續(xù)語句；將執(zhí)批處理中的后續(xù)語句。在觸發(fā)器中，ROLLBACK TRANSACTION 語句終含有激發(fā)觸發(fā)器的語句的批處理；不執(zhí)批處理中的后續(xù)語句。ROLLBACK TRANSACTION 語句不成顯給戶的信息。如果在存儲過程或觸發(fā)器中需要警告，請使 RAISERROR 或 PRINT 語句。RAISERROR 是于指出錯(cuò)誤的選語句。ROL

19、LBACK 對游標(biāo)的影響由下三個(gè)規(guī)則定義：當(dāng) CURSOR_CLOSE_ON_COMMIT 設(shè)置為 ON 時(shí)，ROLLBACK 關(guān)閉但不釋放所有打開的游標(biāo)。當(dāng) CURSOR_CLOSE_ON_COMMIT 設(shè)置為 OFF 時(shí)，ROLLBACK 不影響任何打開的同步 STATIC 或 INSENSITIVE 游標(biāo)不影響已完全填充的異步 STATIC 游標(biāo)。將關(guān)閉但不釋放任何其它類型的打開的游標(biāo)。對于導(dǎo)致終批處理并成內(nèi)部回滾的錯(cuò)誤，將釋放在含有該錯(cuò)誤語句的批處理內(nèi)聲明的所有游標(biāo)。不論游標(biāo)的類型或 CURSOR_CLOSE_ON_COMMIT 的設(shè)置，所有游標(biāo)均將被釋放，其中包括在該錯(cuò)誤批處理所調(diào)的

20、存儲過程內(nèi)聲明的游標(biāo)。在該錯(cuò)誤批處理之前的批處理內(nèi)聲明的游標(biāo)以規(guī)則 1 和 2 為準(zhǔn)。死鎖錯(cuò)誤就屬于這類錯(cuò)誤。在觸發(fā)器中發(fā)出的 ROLLBACK 語句也動成這類錯(cuò)誤。 權(quán)限ROLLBACK TRANSACTION 權(quán)限默認(rèn)授予任何有效戶。例：begin transactionsave transaction Ainsert into demo values(BB,B term)rollback TRANSACTION A- select * into demo2 from demo1create table demo2(name varchar(10),age int)insert into

21、demo2(name,age) values(lis,1)rollback transactionCOMMIT TRANSACTION標(biāo)志個(gè)成功的隱性事務(wù)或戶定義事務(wù)的結(jié)束。如果 TRANCOUNT 為 1，COMMITTRANSACTION 使得從事務(wù)開始以來所執(zhí)的 所有數(shù)據(jù)修改成為數(shù)據(jù)庫的永久部分，釋放連接占的資源，并將 TRANCOUNT 減少到 0。如果TRANCOUNT 于 1，則COMMITTRANSACTION 使 TRANCOUNT 按 1 遞減。只有當(dāng)事務(wù)所引的所有數(shù)據(jù)的邏輯都正確時(shí)，發(fā)出 COMMIT TRANSACTION 命令。COMMIT WORK標(biāo)志事務(wù)的結(jié)束。語法

22、COMMIT WORK 注釋此語句的功能與 COMMIT TRANSACTION 相同，但 COMMIT TRANSACTION 接受戶定義的事務(wù)名稱。這個(gè)指定或沒有指定可選關(guān)鍵字WORK 的 COMMIT 語法與 SQL-92 兼容例：begin transaction ainsert into demo values(BB,B term)commit TRANSACTION A隱性事務(wù)當(dāng)連接以隱性事務(wù)模式進(jìn)操作時(shí)，SQL Server將在提交或回滾當(dāng)前事務(wù)后動啟動新事務(wù)。須描述事務(wù)的開始，只需提交或回滾每個(gè)事務(wù)。隱性事務(wù)模式成連續(xù)的事務(wù)鏈。在為連接將隱性事務(wù)模式設(shè)置為打開之后，當(dāng) SQL

23、Server 次執(zhí)下列任何語句時(shí)，都會動啟動個(gè)事務(wù)：INSERTOPENREVOKESELECTFETCHTRUNCATE TABLEUPDATEGRANT在發(fā)出 COMMIT 或 ROLLBACK 語句之前，該事務(wù)將直保持有效。在第個(gè)事務(wù)被提交或回滾之后，下次當(dāng)連接執(zhí)這些語句中的任何語句時(shí)，SQL Server 都將動啟動個(gè)新事務(wù)。SQL Server 將不斷地成個(gè)隱性事務(wù)鏈， 直到隱性事務(wù)模式關(guān)閉為例：begin transactionsave transaction Ainsert into demo values(BB,B term)rollback TRANSACTION Acrea

24、te table demo2(name varchar(10),age int)insert into demo2(name,age) values(lis,1)rollback transaction- 在 Create table demo2 時(shí) SQL Server 已經(jīng)隱式創(chuàng)建個(gè)Trans,知道提交或回滾嵌套事務(wù)處理:1： Trans 嵌套，將內(nèi)部的trans 合并到外部并形成個(gè)Trans.begin tran t1-In the first trans .Insert into demo2(name,age) values(lis,1)-Second Trans begin tran

25、saction t2insert into demo values(BB,B term)commit transaction t2-In the first trans .Insert into demo2(name,age) values(lis,2)rollback transaction t1Note:在系列嵌套的事務(wù)中個(gè)事務(wù)名給多個(gè)事務(wù)命名對該事務(wù)沒有什么影響。系統(tǒng)僅登記第個(gè)（最外部的）事務(wù)名?；貪L到其它任何名字（有效的保存點(diǎn)名除外）都會產(chǎn)錯(cuò)誤。事實(shí)上，任何在回滾之前執(zhí)的語句都沒有在錯(cuò)誤發(fā)時(shí)回滾。這語句僅當(dāng)外層的事務(wù)回滾時(shí)才會進(jìn)回滾。例：內(nèi)部事務(wù)回滾SQL server 報(bào)錯(cuò)。begi

26、n tran t1Insert into demo2(name,age) values(lis,1)-Second Trans-Server: Msg 6401, Level 16, State 1, Line 6-Cannot roll back t2. No transaction or savepoint of that name was found.begin transaction t2insert into demo values(BB,B term)rollback transaction t2-In the first trans .Insert into demo2(name,age) values(lis,2)commit transaction t1例： 內(nèi)部事務(wù)提交SQL server 不會報(bào)錯(cuò)。 begin tran t1Insert into demo2(name,age) values(lis,1