SQL SERVER事務處理

事務定義：

事務是單個的工作單元。如果某一事務成功，則在該事務中進行的所有資料更改均會

送出，成為資料庫中的永久組成部分。如果事務遇到錯誤且必須取消或復原，則所有

資料更改均被清除。

事務三種運作模式：

自動送出事務

每條單獨的語句都是一個事務。

顯式事務

每個事務均以 BEGIN TRANSACTION 語句顯式開始，

以 COMMIT 或 ROLLBACK 語句顯式結束。

隐性事務

在前一個事務完成時新事務隐式啟動，但每個事務仍以 COMMIT 或 ROLLBACK 語句

顯式完成。

事務操作的文法：

BEGIN TRANSACTION

BEGIN DISTRIBUTED TRANSACTION

COMMIT TRANSACTION

COMMIT WORK

ROLLBACK WORK

SAVE TRANSACTION

标記一個顯式本地事務的起始點。

BEGIN TRANSACTION将 @@TRANCOUNT 加 1。

BEGIN TRANSACTION 代表一點，由連接配接引用的資料在該點是邏輯和實體上都一緻的。如果遇上錯誤，在 BEGIN TRANSACTION 之後的所有資料改動都能進行復原，以将資料傳回到已知的一緻狀态 。每個事務繼續執行直到它無誤地完成并且用 COMMIT TRANSACTION 對資料庫作永久的改動，或者遇上錯誤并且用 ROLLBACK TRANSACTION 語句擦除所有改動

文法

BEGIN TRAN [ SACTION ] [ transaction_name | @tran_name_variable [ WITH MARK [ 'description' ] ] ]

例子：

BEGIN TRAN T1

UPDATE table1 ...

--nest transaction M2

BEGIN TRAN M2 WITH MARK

UPDATE table2 ...

SELECT * from table1

COMMIT TRAN M2

UPDATE table3 ...

COMMIT TRAN T1

指定一個由 Microsoft 分布式事務處理協調器 (MS DTC) 管理的 Transact-SQL 分布式事務的起始。

BEGIN DISTRIBUTED TRAN [ SACTION ]

[ transaction_name | @tran_name_variable ]

參數

transaction_name

是使用者定義的事務名，用于跟蹤 MS DTC 實用工具中的分布式事務。 transaction_name 必須符合辨別符規則，但是僅使用頭 32 個字元

@tran_name_variable

是使用者定義的一個變量名，它含有一個事務名，該事務名用于跟蹤 MS DTC 實用工具中的分布式事務。必須用 char、varchar、nchar 或 nvarchar 資料類型聲明該變量。

注釋

執行BEGIN DISTRIBUTED TRANSACTION 語句的伺服器是事務建立人，并且控制事務的完成

當連接配接發出後續 COMMIT TRANSACTION 或 ROLLBACK TRANSACTION 語句時，

主要伺服器請求 MS DTC 在所涉及的伺服器間管理分布式事務的完成。

有兩個方法可将遠端 SQL 伺服器登記在一個分布式事務中：

分布式事務中已登記的連接配接執行一個遠端存儲過程調用，該調用引用一個遠端伺服器。 

分布式事務中已登記的連接配接執行一個分布式查詢，該查詢引用一個遠端伺服器。

示例

本例在本地和遠端資料庫上更新作者的姓。本地和遠端資料庫将同時送出或同時復原本事務。

說明 

目前的SQL Server 上必須安裝 MS DTC.

USE pubs

GO

UPDATE authors

SET au_lname = 'McDonald' WHERE au_id = '409-56-7008'

EXECUTE link_Server_T.pubs.dbo.changeauth_lname '409-56-7008','McDonald'

COMMIT TRAN

GONote:

如果需要連接配接遠端DB,如果是linkServer 方式連接配接的話，一定要修該linkServer的 RPC 選項置為 True。

SET XACT_ABORT

指定當 Transact-SQL 語句産生運作時錯誤時，Microsoft? SQL Server? 是否自動復原目前事務。

( 可以比較簡單的了解，如果中間有任何一句SQL 出錯，所有SQL全部復原.特别适用于 Procedure 中間調用Procedure ，如果第一個Procedure Ok，被調用的Procedure 中間有錯誤，如果SET XACT_ABORT=false，則出錯的部分復原，其他部分送出，當然外部Procedure 也送出。）.

---在分布式Trans中一定要注意設定下面參數(XACT_ABORT)

文法SET XACT_ABORT { ON | OFF }

注釋 當 SET XACT_ABORT 為 ON 時，如果 Transact-SQL 語句産生運作時錯誤，整個事務将終止并復原。為 OFF 時，隻復原産生錯誤的Transact-SQL語句，而事務将繼續進行處理。編譯錯誤（如文法錯誤）不受 SET XACT_ABORT 的影響。

對于大多數 OLE DB 提供程式（包括 SQL Server），隐性或顯式事務中的資料修改語句必須将 XACT_ABORT 設定為 ON。

SET XACT_ABORT 的設定是在執行或運作時設定，而不是在分析時設定。

示例 下例導緻在含有其它 Transact-SQL 語句的事務中發生違反外鍵錯誤。在第一個語句集中産生錯誤，但其它語句均成功執行且事務成功

送出。在第二個語句集中，SET XACT_ABORT 設定為 ON。這導緻語句錯誤使批處理終止，并使事務復原。

CREATE TABLE t1 (a int PRIMARY KEY)

CREATE TABLE t2 (a int REFERENCES t1(a))

INSERT INTO t1 VALUES (1)

INSERT INTO t1 VALUES (3)

INSERT INTO t1 VALUES (4)

INSERT INTO t1 VALUES (6)

SET XACT_ABORT OFF

BEGIN TRAN

INSERT INTO t2 VALUES (1)

INSERT INTO t2 VALUES (2) /* Foreign key error */

INSERT INTO t2 VALUES (3)

SET XACT_ABORT ON

INSERT INTO t2 VALUES (4)

INSERT INTO t2 VALUES (5) /* Foreign key error */

INSERT INTO t2 VALUES (6)

在事務内設定儲存點。

文法 SAVE TRAN [ SACTION ] { savepoint_name | @savepoint_variable }

參數 savepoint_name

是指派給儲存點的名稱。儲存點名稱必須符合辨別符規則，但隻使用前 32 個字元。

@savepoint_variable

是使用者定義的、含有有效儲存點名稱的變量的名稱。

必須用 char、varchar、nchar 或 nvarchar 資料類型聲明該變量。 注釋

使用者可以在事務内設定儲存點或标記。儲存點定義如果有條件地取消事務的一部分，事 務可以傳回的位置。如果将事務復原到儲存點，則必須（如果需要，使用更多的 Transact-SQL 語句和 COMMIT TRANSACTION 語句）繼續完成事務，或者必須（通過将事務復原到其起始點）完全取消事務。若要取消整個事務，請使用 ROLLBACK TRANSACTION transaction_name 格式。這将撤消事務的所 有語句和過程。

Note：1： 在由 BEGIN DISTRIBUTED TRANSACTION 顯式啟動或從本地事務更新而來的分布式事務中，不支援 SAVE TRANSACTION。

2：當事務開始時，将一直控制事務中所使用的資源直到事務完成（也就是鎖定）。當将事務的一部分復原到儲存點時，将繼續控制資源直到事務完成（或者復原全部事務）。

例子：begin transaction

save transaction A

insert into demo values('BB','B term')

rollback TRANSACTION A

create table demo2(name varchar(10),age int)

insert into demo2(name,age) values('lis',1)

commit transaction

ROLLBACK TRANSACTION

将顯式事務或隐性事務復原到事務的起點或事務内的某個儲存點。

ROLLBACK [ TRAN [ SACTION ]

[ transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable ] ]

是給 BEGIN TRANSACTION 上的事務指派的名稱。transaction_name 必須符合辨別符規則，但隻使用事務名稱的前 32 個字元。嵌套

事務時，transaction_name 必須是來自最遠的 BEGIN TRANSACTION 語句的名稱。

是使用者定義的、含有有效事務名稱的變量的名稱。必須用 char、varchar、nchar 或 nvarchar 資料類型聲明該變量。

savepoint_name

是來自 SAVE TRANSACTION 語句的 savepoint_name。savepoint_name 必須符合辨別符規則。當條件復原隻影響事務的一部分時使 用savepoint_name。

是使用者定義的、含有有效儲存點名稱的變量的名稱。必須用 char、varchar、nchar 或 nvarchar 資料類型聲明該變量。

注釋 ROLLBACK TRANSACTION 清除自事務的起點或到某個儲存點所做的所有資料修改。ROLLBACK 還釋放由事務控制的資源。

不帶 savepoint_name 和 transaction_name 的 ROLLBACK TRANSACTION 復原到事務的起點。嵌套事務時，該語句将所有内層事務復原到 最遠的BEGIN TRANSACTION 語句。在這兩種情況下，ROLLBACK TRANSACTION 均将 @@TRANCOUNT 系統函數減為 0。ROLLBACK 

TRANSACTION savepoint_name 不減少 @@TRANCOUNT。

Note: 

ROLLBACK TRANSACTION 語句若指定 savepoint_name 則不釋放任何鎖。

在由 BEGIN DISTRIBUTED TRANSACTION 顯式啟動或從本地事務更新而來的分布式事務中，ROLLBACK TRANSACTION 不能

引用savepoint_name。在執行 COMMIT TRANSACTION 語句後不能復原事務。

在事務内允許有重複的儲存點名稱，但 ROLLBACK TRANSACTION 若使用重複的儲存點名稱，則隻復原到最近的使用該儲存點名稱的SAVE TRANSACTION。

在存儲過程中，不帶 savepoint_name 和 transaction_name 的 ROLLBACK TRANSACTION 語句将所有語句復原到最遠的 BEGINTRANSACTION。在存儲過程中，ROLLBACK TRANSACTION 語句使 @@TRANCOUNT 在觸發器完成時的值不同于調用該存儲過程時的@@TRANCOUNT 值，并且生成一個資訊。該資訊不影響後面的處理。

如果在觸發器中發出 ROLLBACK TRANSACTION：将復原對目前事務中的那一點所做的所有資料修改，包括觸發器所做的修改。 

觸發器繼續執行 ROLLBACK 語句之後的所有其餘語句。如果這些語句中的任意語句修改資料，則不復原這些修改。執行其餘的語句不會激發嵌套觸發器。在批進行中，不執行所有位于激發觸發器的語句之後的語句。每次進入觸發器，@@TRANCOUNT 就增加 1，即使在自動送出模式下也是如此。（系統将觸發器視作隐性嵌套事務。）

在存儲過程中，ROLLBACK TRANSACTION 語句不影響調用該過程的批進行中的後續語句；

将執行批進行中的後續語句。在觸發器中，ROLLBACK TRANSACTION 語句終止含有激發觸發器的語句的批處理；

不執行批進行中的後續語句。

ROLLBACK TRANSACTION 語句不生成顯示給使用者的資訊。如果在存儲過程或觸發器中需要警告，請使用 RAISERROR 或 PRINT 語句。RAISERROR 是用于指出錯誤的首選語句。

ROLLBACK 對遊标的影響由下面三個規則定義： 

當 CURSOR_CLOSE_ON_COMMIT 設定為 ON 時，ROLLBACK 關閉但不釋放所有打開的遊标。 

當 CURSOR_CLOSE_ON_COMMIT 設定為 OFF 時，ROLLBACK 不影響任何打開的同步 STATIC 或 INSENSITIVE 遊标不影響已完全填充的異步 STATIC 遊标。将關閉但不釋放任何其它類型的打開的遊标。 

對于導緻終止批處理并生成内部復原的錯誤，将釋放在含有該錯誤語句的批處理内聲明的所有遊标。

不論遊标的類型或 CURSOR_CLOSE_ON_COMMIT 的設定，所有遊标均将被釋放，其中包括在該錯誤批處理所調用的存儲過程内聲明的遊标。在該錯誤批處理之前的批處理内聲明的遊标以規則 1 和 2 為準。死鎖錯誤就屬于這類錯誤。在觸發器中發出的 ROLLBACK 語句也 自動生成這類錯誤。

權限

ROLLBACK TRANSACTION 權限預設授予任何有效使用者。

begin transaction

-- select * into demo2 from demo1

rollback transaction

标志一個成功的隐性事務或使用者定義事務的結束。如果 @@TRANCOUNT 為 1，COMMIT

TRANSACTION 使得自從事務開始以來所執行的 所有資料修改成為資料庫的永久部分，釋放連接配接

占用的資源，并将 @@TRANCOUNT 減少到 0。如果@@TRANCOUNT 大于 1，則COMMIT

TRANSACTION 使 @@TRANCOUNT 按 1 遞減。

隻有當事務所引用的所有資料的邏輯都正确時，發出 COMMIT TRANSACTION 指令。

标志事務的結束。

COMMIT [ WORK ]

此語句的功能與 COMMIT TRANSACTION 相同，但 COMMIT TRANSACTION 接受使用者定義的事務

名稱。這個指定或沒有指定可選關鍵字WORK 的 COMMIT 文法與 SQL-92 相容

begin transaction a

commit TRANSACTION A

當連接配接以隐性事務模式進行操作時，SQL Server将在送出或復原目前事務後自動啟動新事務。無須描述事務的開始，隻需送出或

復原每個事務。隐性事務模式生成連續的事務鍊。

在為連接配接将隐性事務模式設定為打開之後，當 SQL Server 首次執行下列任何語句時，都會自動啟動一個事務：

ALTER TABLE

INSERT

CREATE

OPEN

DELETE

REVOKE

DROP

SELECT

FETCH

TRUNCATE TABLE

GRANT

UPDATE

在發出 COMMIT 或 ROLLBACK 語句之前，該事務将一直保持有效。在第一個事務被送出或復原之後，下次當連接配接執行這些語句

中的任何語句時，SQL Server 都将自動啟動一個新事務。SQL Server 将不斷地生成一個隐性事務鍊，

直到隐性事務模式關閉為止

begin transaction 

-- 在 Create table demo2 時 SQL Server 已經隐式建立一個Trans,知道送出或復原

嵌套事務處理:

1： Trans 嵌套，将内部的trans 合并到外部并形成一個Trans.

begin tran t1

----In the first trans .

Insert into demo2(name,age) values('lis',1)

---Second Trans begin transaction t2 

commit transaction t2

Insert into demo2(name,age) values('lis',2)

rollback transaction t1

Note:

在一系列嵌套的事務中用一個事務名給多個事務命名對該事務沒有什麼影響。系統僅登記第一個（最外部的）事務名。復原

到其它任何名字（有效的儲存點名除外）都會産生錯誤。

事實上，任何在復原之前執行的語句都沒有在錯誤發生時復原。這語句僅當外層的事務復原時才會進行復原。

例：内部事務復原SQL server 報錯。

begin tran t1 

---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 t2 

rollback transaction t2

commit transaction t1

例： 内部事務送出SQL server 不會報錯。

---Second Trans no error

commit transaction t2

SQL Server 的隔離級别：

1: 設定TimeOut 參數

Set Lock_TimeOut 5000

被鎖逾時5秒将自動解鎖

Set Lock_TimeOut 0

産立即解鎖，傳回Error 預設為-1，無限等待

2：

(SET TRANSACTION ISOLATION LEVEL

{ READ COMMITTED

| READ UNCOMMITTED

| REPEATABLE READ | SERIALIZABLE}）

READ COMMITTED

指定在讀取資料時控制共享鎖以避免髒讀，但資料可在事務結束前更改，進而産生不可重複讀取或

幻像資料。該選項是SQL Server 的預設值。

避免髒讀，并在其他session 在事務中不能對已有資料進行修改。共享鎖。

READ UNCOMMITTED

執行髒讀或 0 級隔離鎖定，這表示不發出共享鎖，也不接受排它鎖。當設定該選項時，可以對數

據執行未送出讀或髒讀；在事務結束前可以更改資料内的數值，行也可以出現在資料集中或從資料

集消失。該選項的作用與在事務内所有語句中的所有表上設定 NOLOCK 相同。這是四個隔離級别中

限制最小的級别。

REPEATABLE READ

鎖定查詢中使用的所有資料以防止其他使用者更新資料，但是其他使用者可以将新的幻像行插入資料

集，且幻像行包括在目前事務的後續讀取中。因為并發低于預設隔離級别，是以應隻在必要時才使

用該選項。

SERIALIZABLE

在資料集上放置一個範圍鎖，以防止其他使用者在事務完成之前更新資料集或将行插入資料集内。這

是四個隔離級别中限制最大的級别。因為并發級别較低，是以應隻在必要時才使用該選項。該選項

的作用與在事務内所有 SELECT 語句中的所有表上設定 HOLDLOCK 相同。