[SQL SERVER]謹慎使用巢狀交易

我個人相當排斥寫巢狀交易,甚至禁止member使用

正確控制交易commit和rollback相當重要,因為孤兒交易影響資料庫效能極大,

下面我簡單demo一下巢狀交易帶來的影響

 

1.下面那個交易成功?

set nocount on

begin tran
update AWBuildVersion set VersionDate='2014-03-14 00:00:00.000'
begin tran
update Address set AddressLine1='1970 Napa Ct.B'
where AddressID=1
begin tran
update DatabaseLog set DatabaseUser='ricoisme'
where DatabaseLogID=1
commit tran

 

答案:沒有任何交易成功,同時三個資料表均被Lock,

如有其他session要存取這三個資料表時,就會產生blocking,

因為巢狀交易要最外圍交易commit整個交易才算完成,

但rollback是可以正常取消所有巢狀交易。

 

2.對交易紀錄檔的影響

建立新資料庫和資料表

use master
go
drop database testtranslog

create database testtranslog

use testtranslog

create table testnestedtrans
(
c1 int identity 
, c2 char (8000) default 'rico' )

create clustered index cidx on testnestedtrans(c1)

--查看目前交易紀錄檔使用空間大小
dbcc sqlperf ('LOGSPACE'); --38.7688
go

使用巢狀交易

begin tran a
go
begin tran aa
go
insert into testnestedtrans default values;
go 1000
select  @@TRANCOUNT,count(*) from testnestedtrans

dbcc sqlperf ('LOGSPACE'); --96.58334
go

新增1000筆資料後,交易紀錄檔成長為96.58334

commit tran aa
go


checkpoint
go

認可交易aa並手動執行checkpoint後,發現交易紀錄檔卻無縮小

dbcc sqlperf('LOGSPACE');--97.13889
go

前面提過,巢狀交易需最外圍交易認可後才算真正完成,所以只要最外圍交易並無commit的話,

那麼交易紀錄檔大小將永遠不會縮小,你要知道無法重用交易紀錄檔空間是一件大災難,

下面我將認可最外圍交易並執行checkpoint後,看看交易紀錄檔大小是否有如預期縮小。

commit tran a
go

checkpoint
go

dbcc sqlperf('LOGSPACE');
go

看完後這兩個demo後,你還想寫巢狀交易嗎?