Probajte izvesiti sledecu skriptu:
Code:
create table #T1(ID int not null)
insert into #T1 values(1)
go
SELECT 'no tran', @@TRANCOUNT, ID from #T1
begin transaction tran1
UPDATE #T1 SET ID = ID + 1
SELECT 'tran1 ', @@TRANCOUNT, ID from #T1
begin transaction tran2
UPDATE #T1 SET ID = ID + 1
SELECT 'tran2', @@TRANCOUNT, ID from #T1
rollback transaction tran2
SELECT 'tran2', @@TRANCOUNT, ID from #T1
commit transaction tran2
SELECT 'tran1', @@TRANCOUNT, ID from #T1
commit transaction tran1
SELECT 'no tran', @@TRANCOUNT, ID from #T1
while @@TRANCOUNT > 0
commit transaction
go
drop table #T1
go
create table #T1(ID int not null)
insert into #T1 values(1)
go
SELECT 'no tran', @@TRANCOUNT, ID from #T1
begin transaction tran1
UPDATE #T1 SET ID = ID + 1
SELECT 'tran1 ', @@TRANCOUNT, ID from #T1
begin transaction tran2
UPDATE #T1 SET ID = ID + 1
SELECT 'tran2', @@TRANCOUNT, ID from #T1
rollback transaction tran2
SELECT 'tran2', @@TRANCOUNT, ID from #T1
commit transaction tran2
SELECT 'tran1', @@TRANCOUNT, ID from #T1
commit transaction tran1
SELECT 'no tran', @@TRANCOUNT, ID from #T1
while @@TRANCOUNT > 0
commit transaction
go
drop table #T1
go
Kod je napisan ispravno i po pravilima T-SQL-a. Medjutim u liniji "rollback transaction tran2" javlja se greska "Cannot roll back tran2. No transaction or savepoint of that name was found."
Malo sam trazio po netu i jedini savet koji sam nasao je da se izbegavaju ugnjezdene transakcije ili da se koristi @@trancount za pracenje broja transakcija sto mi bas i ne pomaze.
Problem u ugnjezedenim transakcijama je to sto commit transaction bez nevedenog imena transakcije commit-uje zadje otvorenu transakciju dok rollback transaction bez navedenog imena transakcije rollback-uje sve otvorene transakcije. Ako navedemo ime koje hocemo da rollback-ujem, SQL server javlja gore navedenu gresku.
Ako morate koristiti ugnjezdene petlje probajte sledeci kod:
Code:
create table #T1(ID int not null)
insert into #T1 values(1)
go
SELECT 'no tran', @@TRANCOUNT, ID from #T1
begin transaction tran1
UPDATE #T1 SET ID = ID + 1
SELECT 'tran1 ', @@TRANCOUNT, ID from #T1
begin transaction tran2
save transaction tran2s
UPDATE #T1 SET ID = ID + 1
SELECT 'tran2', @@TRANCOUNT, ID from #T1
rollback transaction tran2s
SELECT 'tran2', @@TRANCOUNT, ID from #T1
commit transaction tran2
SELECT 'tran1', @@TRANCOUNT, ID from #T1
commit transaction tran1
SELECT 'no tran', @@TRANCOUNT, ID from #T1
while @@TRANCOUNT > 0
commit transaction
go
drop table #T1
go
create table #T1(ID int not null)
insert into #T1 values(1)
go
SELECT 'no tran', @@TRANCOUNT, ID from #T1
begin transaction tran1
UPDATE #T1 SET ID = ID + 1
SELECT 'tran1 ', @@TRANCOUNT, ID from #T1
begin transaction tran2
save transaction tran2s
UPDATE #T1 SET ID = ID + 1
SELECT 'tran2', @@TRANCOUNT, ID from #T1
rollback transaction tran2s
SELECT 'tran2', @@TRANCOUNT, ID from #T1
commit transaction tran2
SELECT 'tran1', @@TRANCOUNT, ID from #T1
commit transaction tran1
SELECT 'no tran', @@TRANCOUNT, ID from #T1
while @@TRANCOUNT > 0
commit transaction
go
drop table #T1
go
Sada sve radi kako treba. Dakle kada imate ugnjezdene transakcije radite sledece:
- posle svakog begin transaction <ime transakcije> dodajte red save transaction <ime snimljene transakcije>
Code:
begin transaction <ime transakcije>
save transaction <ime snimljene transakcije>
begin transaction <ime transakcije>
save transaction <ime snimljene transakcije>
- ako dodje do greske i morate raditi rollback radite ga na sledeci nacin
Code:
rollback transaction <ime snimljene transakcije>
commit transaction <ime transakcije>
rollback transaction <ime snimljene transakcije>
commit transaction <ime transakcije>
- ako je ceo kod izvrsen kako treba i nema gresaka uradite normalan commit a snimljena transakcija ce se sama zatvoriti
Code:
commit transaction <ime transakcije>
commit transaction <ime transakcije>