sql server 学习笔记 (nested transaction 嵌套事务)
修订版请移驾到 SQL Server – Transaction & Isolation 事务与隔离
什么时候会用到嵌套事务 ?
为了代码复用,我们会写许多的储蓄过程,而中间如果需要使用到 transaction 难免就会发生嵌套了.
sql server 并不直接支持嵌套事务. 但它可以用一些招式来实现嵌套效果. 虽然这些招式并不优雅,也容易让人陷入迷雾.
这篇收集了一些资料来说说 sql server 中的嵌套事务.
这篇写了基本的 sql server 对 transaction 的处理方式 https://www.cnblogs.com/kymo/archive/2008/05/14/1194161.html
begin trans @@count +1
commit trans @@count -1,如果 -1 后为 0 那么永久写入磁盘, 不然继续等待下一个 commit.
rollback trans @@count = 0 (rollback + save point name 则不会 update count, count 保持)
所以一个常遇到的 bug 就是, 当里面的 trans rollabck 后, 外面的 trans 就废掉了, 也不能 rollback 也不能 commit, 因为 count 直接被设置为 0 了。
这就是为什么说 sql server 不直接支持嵌套事务了。
那有什么方法可以实现呢 ?
这篇给出了很好的示范
在写储蓄过程时,我们需要判断当前的 trans count 来决定如果处理 trans
开始的时候,如果当前没有 count = 0 表示没有 trans
那么储蓄过程需要负责创建 trans
然后也负责 rollback or commit.
如果当前已经有了 trans
那么储蓄过程就不要创建新的 trans 而是做一个 save point
如果没有任何 error 也不需要 commit ,把职责交给外面的人就行了.
那么遇到 error 时就要处理了,首先 rollback to save point. (不会减少 count)
然后 throw 一个 error 到外面.
大致上就是这样,核心概念就是始终只开启一个事务, 然后利用 if else begin trans + save point + rollback save point + throw error 来达到效果
储蓄过程大概长这样
GO IF EXISTS (SELECT name FROM sys.objects WHERE name = N'SaveTranExample') DROP PROCEDURE SaveTranExample; GO CREATE PROCEDURE SaveTranExample AS DECLARE @TranCounter INT; SET @TranCounter = @@TRANCOUNT; IF @TranCounter > 0 SAVE TRANSACTION ProcedureSave; ELSE BEGIN TRANSACTION; print(@@TRANCOUNT) update Products set name = 'mk100' where Id = 1; -- Modify database. BEGIN TRY IF @TranCounter = 0 COMMIT TRANSACTION; END TRY BEGIN CATCH IF @TranCounter = 0 ROLLBACK TRANSACTION; ELSE IF XACT_STATE() <> -1 ROLLBACK TRANSACTION ProcedureSave; DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(); SELECT @ErrorSeverity = ERROR_SEVERITY(); SELECT @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); END CATCH GO
调用的时候是这样的
select * from Products order by Id; update Products set name = 'asdds' where Id = 1; update Products set name = 'asdwf' where Id = 2; begin tran update Products set name = 'mk100' where Id = 2; begin try exec SaveTranExample; commit; end try begin catch print('error') rollback; end catch
解释一下 XACT_STATE
在储蓄过程中,error 时其实只需要 throw 出去就好了,因为外面理应负责 rollback, 但是这里把职责交给了外面. 如果当前决定不可以 commit ,那么它会是 -1 那么储蓄过程可以什么也不做,因为外面肯定会 rollback
但如果它是可以被 commit 的情况下 1 or 0 那么只 rollback 到 save point 而已, 其余交给外面负责. 意思是只是 rollback 了这个储蓄过程, 外面如果还是想 commit 也行. 一种灵活的方式而已啦.
此外还有一个点就是隔离级别. 我们搞这些不就是为了复用 store procedure 吗.
2 个点
1. 切换级别后,之前锁定的东西依然会保持旧的级别
2. 储蓄过程, trigger 内修改级别的话,只有在内部有效,离开储蓄过程就会自动切换回去之前的.
另外提一下, ef core 没有支持 nested transactions, 或者说应该用 transaction scope 来到达效果.
https://github.com/aspnet/EntityFrameworkCore/issues/3470
https://github.com/aspnet/EntityFrameworkCore/issues/6233
其它 refer :
https://docs.microsoft.com/en-us/sql/t-sql/functions/xact-state-transact-sql?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/t-sql/functions/xact-state-transact-sql?view=sql-server-ver15
https://www.cnblogs.com/xugang/archive/2011/04/09/2010216.html
https://stackoverflow.com/questions/16043513/sql-server-try-catch-with-xact-state
https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/
https://stackoverflow.com/questions/527855/nested-transactions-in-sql-server
http://rusanu.com/2009/06/11/exception-handling-and-nested-transactions/
https://github.com/aspnet/EntityFrameworkCore/issues/3470
https://github.com/aspnet/EntityFrameworkCore/issues/6233
https://www.cnblogs.com/kymo/archive/2008/05/14/1194161.html
https://blog.csdn.net/kufeiyun/article/details/27533853
https://stackoverflow.com/questions/31277829/multiple-begin-transactions