Transanction count??!!?!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Transanction count??!!?!!

Author

Topic

cct
Starting Member

5 Posts

Posted - 2006-02-09 : 22:43:03

hi, looking at the help file example given in SQL 2000, i don understand why is the transaction count return value is 2. can anyone explain to me further pls. TQ

subject: set implicit_transaction
example:


USE pubs
GO

CREATE table t1 (a int)
GO
INSERT INTO t1 VALUES (1)
GO

...
PRINT 'Setting IMPLICIT_TRANSACTIONS ON'
GO
SET IMPLICIT_TRANSACTIONS ON
GO

PRINT 'Use explicit transactions with IMPLICIT_TRANSACTIONS ON'
GO
BEGIN TRAN
INSERT INTO t1 VALUES (5)
SELECT 'Tran count in transaction'= @@TRANCOUNT
COMMIT TRAN
SELECT 'Tran count outside transaction'= @@TRANCOUNT
GO
...

Result:
Tran count in transaction
2

Tran count outside transaction
1

why is trancount return 2 here? I only can see one active transaction here, but why it show 2

thanks in advance

Kristen
Test

22859 Posts

Posted - 2006-02-10 : 03:42:34

I think that

SET IMPLICIT_TRANSACTIONS ON

implicitly starts the transaction, so that

BEGIN TRAN

causes a new nested transaction to be started. So you should just need some COMMITs with Implicit Transactions.

Kristen

cct
Starting Member

5 Posts

Posted - 2006-02-10 : 05:03:13

thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-10 : 08:02:23

I could have sworn that implicit transactions didn't work like this in the past.
Now it seems if you execute a begin tran it will start 2 transactions whether or not there is an update.
I thought it used to only start a transaction if trancount was 0 when an update took place.
so you need

begin tran -- trancount 2
commit tran -- trancount 1
commit tran -- trancount 0

even if there is no update.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-12 : 22:50:06

quote:
in the conclusion, can i say that explicit transaction always have the @@TRANCOUNT value start with 1, whereas implicit transaction OFF always have the @@TRANCOUNT value start with 2 ?!?!?!!

No.

Try this scripts

set implicit_transactions off
SELECT  'count '= @@TRANCOUNT	-- 0
begin tran
SELECT 'count '= @@TRANCOUNT	-- 1
rollback
SELECT 'count '= @@TRANCOUNT	-- 0

BEGIN TRAN will increment the transaction so @@trancount will be +1. (if there isn't any transaction then it will be 1) And ROLLBACK will decrease the trancount

----------------------------------
'KH'
everything that has a beginning has an end

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-12 : 22:53:32

You may also refer to following for more info on transaction
http://www.sqlteam.com/item.asp?ItemID=15583
http://www.informit.com/articles/article.asp?p=26657&rl=1

----------------------------------
'KH'
everything that has a beginning has an end

cct
Starting Member

5 Posts

Posted - 2006-02-13 : 01:05:28

gosh...i've accidentally remove my previous post

i think i have make a typo error in my statement, it should be implicit transaction ON not OFF let me rephrase again...

Explicit transaction always have the @@TRANCOUNT value start with 1, whereas implicit transaction ON always have the @@TRANCOUNT value start with 2. Can i say that with the code proven below??!?


set implicit_transaction OFF
BEGIN TRAN
SELECT 'count '= @@TRANCOUNT -- 1
BEGIN TRAN
SELECT 'count '= @@TRANCOUNT -- 2
...
ROLLBACK TRAN --reset

--------------------------------------

set implicit_transaction ON
BEGIN TRAN
SELECT 'count '= @@TRANCOUNT -- it start with 2!!!
BEGIN TRAN
SELECT 'count '= @@TRANCOUNT -- 3
...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources