gyanibaba
asked on
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
in summary, using CHAINED MODE is very confusing
most people (including myself) avoid it and go for explicit transaction control
most people (including myself) avoid it and go for explicit transaction control
oh, and that first batch should have begum with
set chained on
go
set chained on
go
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