sql事務的使用及其技巧整理
- 2019 年 10 月 23 日
- 筆記
sql事務的使用及其技巧整理
概述：
在實際項目開發中，為了確保數據操作結果的一致性等要求，事務是一個必不可少的解決利器。
根據SQLSERVER實現原理，其實，SQLSERVER的每一條執行語句都是一個事務操作，也就是說每一個SQL語句要麼操作都成功，要麼操作都失敗：比如，更新語句，同時更新多個欄位，不會出現有的欄位更新成功，有的欄位更新失敗。
但是，我們平時在開發過程過程中，說的事務：其實是指的一組有序的SQL集合，通過事務確保這一組SQL集合執行結果的一致性。
事務特性：
事務的主要特性包括：原則性、一致性、隔離性、持久性
- 原子性：事務必須是一個自動工作的單元，要麼全部執行，要麼全部不執行。
- 一致性：事務把資料庫從一個一致狀態帶入到另一個一致狀態，事務結束的時候，所有的內部數據都是正確的。
- 隔離性：並發多個事務時，一個事務的執行不受其他事務的影響。
- 持久性：事務提交之後，數據是永久性的，不可再回滾，不受關機等事件的影響
事務分類：
根據事務的執行維度力度，事務又分為：自動提交事務、顯式事務、隱式事務
自動提交事務：sqlserver的一種默認機制，也叫自身事務，每一個sql語句執行都是採用的這種模式
顯式事務：這也是我們平時常說的事務， 通過 Begin Transaction開啟事務開始，執行一組SQL語句，由Commit Transaction 提交事務、Rollback Transaction 回滾事務結束。
隱式事務：使用Set IMPLICIT_TRANSACTIONS ON 將隱式事務模式打開，sql執行完畢自動提交，當一個事務結束，這個模式會自動啟用下一個事務，只用Commit Transaction 提交事務、Rollback Transaction 回滾事務即可
顯示事務和隱式的主要區別自在於，隱式事務在執行完畢後自動提交。
顯式事務使用簡介：
顯示事務通過begin Transaction 開啟事務，通過Rollback Transaction 回滾事務
數據準備，首先創建一個表：
---- 創建一個表TEST_Name ，每一個欄位都是非空 CREATE TABLE [dbo].[TEST_Name]( [Id] [int] NULL, [Name] [nvarchar](50) NULL ) ON [PRIMARY]
實例：
---- 正常完整的執行一個事務，且事務內SQL無異常
---- 正常完整的執行一個事務，且事務內SQL無異常 begin tran insert into TEST_name values(1,1) insert into TEST_name values(3,3) commit tran
---- 執行一個事務，且事務內SQL有異常
begin tran insert into TEST_name values(10,10) insert into TEST_name values(11,null)----次語句執行失敗，原因是該name不允許為空 insert into TEST_name values(12,12) commit tran
----- 語句最終執行結果是
成功插入id為：10、12的兩條數據
通過上面的語句，這樣的執行結果和我們事務中的一致性相違背，這不是我們使用事務想要看到的效果，其實我們希望的是這3個語句要麼都插入成功，要麼都插入失敗
為了達到數據，可以通過以下三種方式來實現：try catch;執行結果判斷，一步一步執行，錯誤回滾；開啟 xact_abort（精準終止）
try catch 實現事務回滾
begin tran begin try insert into TEST_name values(1,1) insert into TEST_name values(2,null) insert into TEST_name values(3,2) commit tran end try begin catch select '執行異常，事務回滾' rollback tran end catch
---- 執行結果是：不會插入一條數據
執行結果判斷，一步一步執行，錯誤回滾
begin tran declare @error int set @error=0 insert into TEST_name values(1,1) set @error=@error+@@error insert into TEST_name values(2,null) set @error=@error+@@error insert into TEST_name values(3,2) set @error=@error+@@error if(@error<>0) begin select '執行異常，事務回滾' rollback tran end else begin commit tran end
---- 每一步執行結果都正確才繼續往下執行 begin tran ---- 影響行數 declare @ROWCOUNT int set @ROWCOUNT=0 insert into TEST_name values(1,1) set @ROWCOUNT=@@ROWCOUNT if(@ROWCOUNT>0) begin insert into TEST_name values(2,null) set @ROWCOUNT=@@ROWCOUNT end if(@ROWCOUNT>0) begin insert into TEST_name values(3,2) set @ROWCOUNT=@@ROWCOUNT end if(@ROWCOUNT<=0) begin select '執行異常，事務回滾' rollback tran end else begin commit tran end
開啟 xact_abort（精準終止）
---- XACT_ABORT 設置 on :代表某一個語句執行錯誤，都不在繼續往下執行，並自動回滾事務
---- XACT_ABORT 設置 off :代表某一個語句執行錯誤，子回滾該條語句執行，並繼續執行後續語句，同時提交執行成功的語句
---- off 此種情況應該很少在事務中使用，畢竟使用事務的目的就是實現執行結果的一致性
set XACT_ABORT on begin tran insert into TEST_name values(10,10) insert into TEST_name values(11,null)----次語句執行失敗，原因是該name不允許為空 insert into TEST_name values(12,12) commit tran
設置事務保存點：
在平時的事務使用過程中，還有可能需要實現，事務回滾時，只回滾到指定位置，指定位置之前的執行結果不在回滾
在sqlserver中可以通過事務保存點，來實現對事務的精確回滾，關鍵詞是：save transaction 和rollback transaction ，具體使用規則如下：
---- 每一步執行結果都正確才繼續往下執行 begin tran ---- 影響行數 declare @ROWCOUNT int set @ROWCOUNT=0 insert into TEST_name values(1,1) set @ROWCOUNT=@@ROWCOUNT save tran stanstation1 --- save tran transtation1 if(@ROWCOUNT>0) begin insert into TEST_name values(2,null) set @ROWCOUNT=@@ROWCOUNT end if(@ROWCOUNT>0) begin insert into TEST_name values(3,2) set @ROWCOUNT=@@ROWCOUNT end if(@ROWCOUNT<=0) begin select '執行異常，事務回滾' ---- 執行結果是：1,1 成功插入到資料庫表中 rollback tran stanstation1 end else begin select '事務提交' commit tran end
總結：
通過上面的溫習，結合練習，對sql的事務有了進一步的了解。簡單的總結：實際上執行的每一個sql都是採用事務來實現的，在實際使用中，我們一般採用顯示事務來處理業務，但是在事務的使用過程中一定要結合對應的策略來確保事務執行結果的一致性。
今天就寫到這，明天再簡單那梳理總結一下分散式事務的實現方式，這個也是很重要的模組，尤其是在現在的大型系統中，分庫分表時，分散式事務很管用
