y****9
发帖数: 144 | 1 Hi, SQL Server friends,
I run the following script, my question is why when I set implict_
transaction on, and I issued" begin tran", then execute an insert, I will
see TRANCOUNT=2?
note: I just start to learn sql server, I may ask some silly questions now
and then, thanks for your help!
------- script start------
USE testdata
GO
IF OBJECT_ID('t1','U') IS NOT NULL
DROP TABLE t1;
GO
CREATE table t1 (a int)
GO
-- IMPLICIT_TRANSACTION OFF - autocommit mode
INSERT INTO t1 VALUES (1)
SELECT 'Tran count in transaction'= @@TRANCOUNT -- see 0
GO
PRINT ' '
PRINT '#### Setting IMPLICIT_TRANSACTIONS ON'
GO
SET IMPLICIT_TRANSACTIONS ON
GO
PRINT ' '
PRINT '#### Use implicit transactions'
GO
-- No BEGIN TRAN needed here.
INSERT INTO t1 VALUES (4)
SELECT 'Tran count in transaction'= @@TRANCOUNT -- see 1
COMMIT TRAN
SELECT 'Tran count outside transaction'= @@TRANCOUNT -- see 0
GO
PRINT ' '
PRINT '#### Use explicit transactions with IMPLICIT_TRANSACTIONS ON'
GO
BEGIN TRAN
INSERT INTO t1 VALUES (5)
SELECT 'Tran count in transaction'= @@TRANCOUNT -- see 2 --- why???!!!
COMMIT TRAN
SELECT 'Tran count outside transaction'= @@TRANCOUNT -- see 1
GO
----- script end ---- | i****a
发帖数: 36252 | 2 it becomes 2 transactions. one is from the server automated transaction from
implicit_transaction, other is from your own begin tran
【在 y****9 的大作中提到】
: Hi, SQL Server friends,
: I run the following script, my question is why when I set implict_
: transaction on, and I issued" begin tran", then execute an insert, I will
: see TRANCOUNT=2?
: note: I just start to learn sql server, I may ask some silly questions now
: and then, thanks for your help!
: ------- script start------
: USE testdata
: GO
: IF OBJECT_ID('t1','U') IS NOT NULL
| y****9
发帖数: 144 | 3 Got it thanks. I learned that every "BEGIN TRANS" increment @@TRANSCOUNT by
1. In my case, there are explicit one and implicit one from the insert, so
the count =2.
from
【在 i****a 的大作中提到】
: it becomes 2 transactions. one is from the server automated transaction from
: implicit_transaction, other is from your own begin tran
|
|
