• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1016
  • Last Modified:

chained transaction mode

I am testing a query in chained transaction mode and got some confusuin over value of @@trancount and @@transtate:

I have got follwing query:
update CAA_CA set CAA_NAME ='G1' where CAA_CA_ID ='2006000846'
begin tran
update CAA_CA set CAA_NAME ='G2' where CAA_CA_ID ='2006000847'
rollback tran


Now i have run them in chained mode
After running them result is as expected, no update got executed. Till this point it is ok.
But now @@trancount is showing as 1 and @@transtate = 0
Why @@trancount is still showing 1, it should be 0
and @@transtate should be 3.

If i run in unchained mode then, @@trancount and @@transtate shows values as expected by me.

Is there something that @@trancount and @@transtate also depends upon transaction mode, if yes, please let me know, how?
Thanks
Gyan
0
gyanibaba
Asked:
gyanibaba
  • 4
1 Solution
 
ChrisKingCommented:
in chained mode the first query (even a select) begins a transaction automatically
so the first update is execute at transaction level 1

the "begin tran" moves this up to transaction level 2
and the second update does not amend this level
"rollback tran" reduces the level from 2 to 1 (undoing the 2nd update only)
this leaves you at transaction level 1 therefore @@trancount should show 1 (and does)

maybe you were thinking of "rollback" instead of "rollback tran"
"rollback" would rolled back everything and left you at level 0
0
 
gyanibabaAuthor Commented:
Hi ChrisKing
I have even tried following query as er your suggestion:
1> update CAA_CA set CAA_NAME='G1' where CAA_CA_ID = '2006000846'
2> begin tran
3> update CAA_CA set CAA_NAME='G2' where CAA_CA_ID = '2006000847'
4> rollback

but still getting
1> select @@trancount,@@transtate
2>

 ----------- -----------
           1           0

(1 row affected)

Thanks
Gyan
0
 
ChrisKingCommented:
as I said before
> in chained mode the first query (even a select) begins a transaction automatically

so, which you are still in chained mode, the "select @@trancount" will begin ANOTHER
transaction before it shouw you the value of @@trancount

to prove this, run these 2 batches

select @@trancount
begin tran
select @@trancount
begin tran
select @@trancount
rollback
select @@trancount
go

and then

rollback            -- to undo the last batch
go
set chained on
go
select @@trancount
begin tran
select @@trancount
begin tran
select @@trancount
rollback
go
set chained off
select @@trancount
go
0
 
ChrisKingCommented:
in summary, using CHAINED MODE is very confusing

most people (including myself) avoid it and go for explicit transaction control
0
 
ChrisKingCommented:
oh, and that first batch should have begum with

set chained on
go
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now