當前位置:首頁 » 編程語言 » sql回滾事務

sql回滾事務

發布時間: 2024-02-24 19:34:22

sql中的存儲過程里怎麼寫事務回滾啊

CREATE PROC [dbo].[notice_Delete] --- 同時刪除該通知書和對應的節點
@tbl VARCHAR(30),
@pid INT
AS
BEGIN
DECLARE @tblname VARCHAR(30) ;
DECLARE @sql VARCHAR(1000) ;
SET @tblname = @tbl
SET @sql = 'delete ' + @tblname + ' where id ='
+ CONVERT(VARCHAR(10), @pid)

BEGIN TRAN --開始事務

EXEC ( @sql
)
IF ( @@rowcount = 0 ) --執行結果影響行數為0
BEGIN
ROLLBACK TRAN --回滾
END
ELSE
BEGIN
DELETE FROM tbl_treenotice
WHERE purposeid = @pid
IF ( @@rowcount = 0 ) --執行結果影響行數為0
BEGIN
ROLLBACK TRAN --回滾
END
ELSE
BEGIN
COMMIT TRAN --提交事務
END
END
END

㈡ 對於已經執行成功的sql命令,如何回滾

當啟動Binlog後,事務會產生Binlog Event,這些Event被看做事務數據的一部分。因此要保證事務的Binlog Event和InnoDB引擎中的數據的一致性。所以帶Binlog的CrashSafe要求MySQL宕機重啟後能夠保證:

- 所有已經提交的事務的數據仍然存在。

- 所有沒有提交的事務的數據自動回滾。

- 所有已經提交了的事務的Binlog Event也仍然存在。

- 所有沒有提交事務沒有記錄Binlog Event。

這些要求很好理解,如果重啟後數據還在,但是Binlog Event沒有了,就沒辦法復制到其他節點上了。如果重啟後,數據沒了,但是Binlog Event還在,那麼不存在的數據就會被復制到其他節點上,從而導致主從的不一致。

為了保證帶Binlog的CrashSafe,MySQL內部使用的兩階段提交(Two Phase Commit)。

2 - MySQL的Two Phase Commit(2PC)

在開啟Binlog後,MySQL內部會自動將普通事務當做一個XA事務來處理:
- 自動為每個事務分配一個唯一的ID
- COMMIT會被自動的分成Prepare和Commit兩個階段。
- Binlog會被當做事務協調者(Transaction Coordinator),Binlog Event會被當做協調者日誌。
想了解2PC,可以參考文檔:【https://en.wikipedia.org/wiki/Two-phase_commit_protocol。】

- 分布式事務ID(XID)

使用2PC時,MySQL會自動的為每一個事務分配一個ID,叫XID。XID是唯一的,每個事務的XID都不相同。XID會分別被Binlog和InnoDB記入日誌中,供恢復時使用。MySQ內部的XID由三部分組成:

- 前綴部分

前綴部分是字元串"MySQLXid"

- Server ID部分

當前MySQL的server_id
- query_id部分

為了保證XID的的唯一性,數字部分使用了query_id。MySQL內部會自動的為每一個語句分配一個query_id,全局唯一。

參考代碼:sql/xa。h的struct xid_t結構。

- 事務的協調者Binlog

Binlog在2PC中充當了事務的協調者(Transaction Coordinator)。由Binlog來通知InnoDB引擎來執行prepare,commit或者rollback的步驟。事務提交的整個過程如下:

1. 協調者准備階段(Prepare Phase)

告訴引擎做Prepare,InnoDB更改事務狀態,並將Redo Log刷入磁碟。

2. 協調者提交階段(Commit Phase)

2.1 記錄協調者日誌,即Binlog日誌。

2.2 告訴引擎做commit。
注意:記錄Binlog是在InnoDB引擎Prepare(即Redo Log寫入磁碟)之後,這點至關重要。

在MySQ的代碼中將協調者叫做tc_log。在MySQL啟動時,tc_log將被初始化為mysql_bin_log對象。參考sql/binlog.cc中的init_server_components():
if (opt_bin_log) tc_log= &mysql_bin_log;

而在事務提交時,會依次執行:
tc_log->prepare();
tc_log->commit();
參考代碼:sql/binlog.cc中的ha_commit_trans()。當mysql_bin_log是tc_log時,prepare和commit的代碼在sql/binlog.cc中:

MYSQL_BIN_LOG::prepare();
MYSQL_BIN_LOG::commit();

-協調者日誌Xid_log_event
作為協調者,Binlog需要將事務的XID記入日誌,供恢復時使用。Xid_log_event有以下幾個特點:
- 僅記錄query_id
因為前綴部分不變,server_id已經記錄在Event Header中,Xid_log_event中只記錄query_id部分。
- 標志事務的結束

在Binlog中相當於一個事務的COMMIT語句。

一個事務在Binlog中看起來時這樣的:
Query_log_event("BEGIN");DML產生的events; Xid_log_event;

- DDL沒有BEGIN,也沒有Xid_log_event 。
- 僅InnoDB的DML會產生Xid_log_event
因為MyISAM不支持2PC所以不能用Xid_log_event ,但會有COMMIT Event。
Query_log_event("BEGIN");DML產生的events;Query_log_event("COMMIT");

問題:Query_log_event("COMMIT")和Xid_log_event 有不同的影響嗎?

- Xid_log_event 中的Xid可以幫助master實現CrashSafe。
- Slave的CrashSafe不依賴Xid_log_event
事務在Slave上重做時,會重新產生XID。所以Slave伺服器的CrashSafe並不依賴於Xid_log_event 。Xid_log_event 和Query_log_event("COMMIT"),只是作為事務的結尾,告訴Slave Applier去提交這個事務。因此二者在Slave上的影響是一樣的。

3 - 恢復(Recovery)
這個機制是如何保證MySQL的CrashSafe的呢,我們來分析一下。這里我們假設用戶設置了以下參數來保證可靠性:

- 恢復前事務的狀態
在恢復開始前事務有以下幾種狀態:
- InnoDB中已經提交
根據前面2PC的過程,可知Binlog中也一定記錄了該事務的的Events。所以這種事務是一致的不需要處理。
- InnoDB中是prepared狀態,Binlog中有該事務的Events。
需要通知InnoDB提交這些事務。
- InnoDB中是prepared狀態,Binlog中沒有該事務的Events。
因為Binlog還沒記錄,需要通知InnoDB回滾這些事務。
- Before InnoDB Prepare
事務可能還沒執行完,因此InnoDB中的狀態還沒有prepare。根據2PC的過程,Binlog中也沒有該事務的events。 需要通知InnoDB回滾這些事務。

- 恢復過程
從上面的事務狀態可以看出:恢復時事務要提交還是回滾,是由Binlog來決定的。
- 事務的Xid_log_event 存在,就要提交。
- 事務的Xid_log_event 不存在,就要回滾。

恢復的過程非常簡單:
- 從Binlog中讀出所有的Xid_log_event
- 告訴InnoDB提交這些XID的事務
- InnoDB回滾其它的事務

㈢ SQL存儲過程,如何回滾啊

當 SET XACT_ABORT 為 ON 時,如果執行 Transact-SQL 語句產生運行時錯誤,則整個事務將終止並回滾。
當 SET XACT_ABORT 為 OFF 時,有時只回滾產生錯誤的 Transact-SQL 語句,而事務將繼續進行處理。如果錯誤很嚴重,那麼即使 SET XACT_ABORT 為 OFF,也可能回滾整個事務。OFF 是默認設置。

編譯錯誤(如語法錯誤)不受 SET XACT_ABORT 的影響。

對於大多數 OLE DB 提供程序(包括 SQL Server),必須將隱式或顯示事務中的數據修改語句中的 XACT_ABORT 設置為 ON。唯一不需要該選項的情況是在提供程序支持嵌套事務時。有關詳細信息,請參閱 分布式查詢和分布式事務。

SET XACT_ABORT 的設置是在執行或運行時設置,而不是在分析時設置。

示例
下列代碼示例導致在含有其他 Transact-SQL 語句的事務中發生外鍵沖突錯誤。在第一個語句集中產生錯誤,但其他語句均成功執行且事務成功提交。在第二個語句集中,將 SET XACT_ABORT 設置為 ON。這導致語句錯誤使批處理終止,並使事務回滾。

復制代碼
USE AdventureWorks;
GO
IF OBJECT_ID(N't2', N'U') IS NOT NULL
DROP TABLE t2;
GO
IF OBJECT_ID(N't1', N'U') IS NOT NULL
DROP TABLE t1;
GO
CREATE TABLE t1
(a INT NOT NULL PRIMARY KEY);
CREATE TABLE t2
(a INT NOT NULL REFERENCES t1(a));
GO
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (6);
GO
SET XACT_ABORT OFF;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); -- Foreign key error.
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;
GO
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (4);
INSERT INTO t2 VALUES (5); -- Foreign key error.
INSERT INTO t2 VALUES (6);
COMMIT TRANSACTION;
GO
-- SELECT shows only keys 1 and 3 added.
-- Key 2 insert failed and was rolled back, but
-- XACT_ABORT was OFF and rest of transaction
-- succeeded.
-- Key 5 insert error with XACT_ABORT ON caused
-- all of the second transaction to roll back.
SELECT *
FROM t2;
GO

㈣ SQL語句如何rollback

回滾要放在事務裡面進行,才能進行回滾;sql裡面的事務使用關鍵字TransAction
1:可以用try catch捕獲
begin try
begin tran
update table set a=1;
commit tran
end Try
begin catch
rollback tran
end catch

2:可以使用error 全局變數
begin tran
update tablename set ad=1111
if @@error<>0 begin rollback end
commit tran

注意:如果一個事務寫了 begin trans ,後面一定要跟上 commit tran或 rollback transaction ,否則可能導致被鎖

㈤ sql存儲過程中事務出現錯誤回滾,那麼在回滾之後的語句會執行嗎

會的。

一般回滾操作都是寫在異常處理,或是sql的最後。如果你的sql中出現錯誤 ,代碼會立即跳轉到錯誤處理代碼上執行,比如回滾,但緊接在錯誤行之後的代碼不會執行的。


1.update .....;

2.select ......;
3.when Exception
....rollback;
4.insert into .....

以上偽代碼,如果行1出錯,行2將不會執行,直接跳轉到行3,然後行4 也會執行。

熱點內容
cf窗口化源碼 發布:2024-04-24 07:04:33 瀏覽:737
linuxi2c設備 發布:2024-04-24 06:53:50 瀏覽:345
寶馬x5買什麼配置性價比高 發布:2024-04-24 06:45:22 瀏覽:849
最小的編程語言 發布:2024-04-24 06:44:16 瀏覽:816
自動發朋友圈腳本 發布:2024-04-24 06:40:32 瀏覽:153
最早存儲盤 發布:2024-04-24 06:39:54 瀏覽:943
編程題優惠券 發布:2024-04-24 06:29:46 瀏覽:998
在線編程招聘 發布:2024-04-24 06:23:11 瀏覽:68
svn緩存文件 發布:2024-04-24 06:09:53 瀏覽:139
現在的我的世界伺服器推薦 發布:2024-04-24 05:46:39 瀏覽:151