SQL Server Performance

@@TRANCOUNT difference between SQL 7 and SQL 2000

Discussion in 'General Developer Questions' started by neil_rutherford, Aug 7, 2004.

  1. neil_rutherford New Member

    During testing of an application, i noticed a difference between
    SQL 2000 and SQL 7, both with identical config.

    In a nutshell:
    A table has a trigger for UPDATE and DELETE.
    When a column in the table is UPDATED the following happens:

    In autocommit mode, when entering a trigger the trancount equals
    1 for both SQL 7 and 2000.

    When the same update is performed in an explicit transaction
    in SQL 7 @@TRANCOUNT equal 2, and in SQL 2000 @@TRANCOUNT equals 1.

    Configuration is the same and there are no implicit transactions.

    I don't need a work around as this will invalidate the migration
    process as both products should behave identically.
    What would influence the difference or why is there a difference???
    Is there something which has been overlooked?

    =========================================================

    The following code replicates the problem

    Ensure implicit transactions are off in both versions at the server
    level, thus defaulting to autocommitted mode.
    Ensure sp_configure settings are identical.

    Step 1: Create a DB called test:

    Step 2: Execute the following under the context of test DB.

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trigtest]') and OBJECTPROPERTY(id, Outrigger') = 1)

    drop trigger [dbo].[trigtest]
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[test]
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trancount]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[trancount]
    GO

    CREATE TABLE [dbo].[test] (
    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [text] [char] (10) NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[trancount] (
    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [trancount] [int] NOT NULL
    ) ON [PRIMARY]
    GO

    CREATE TRIGGER trigtest ON [dbo].[test]
    FOR UPDATE, DELETE
    AS
    declare @trancount int

    select @trancount = @@TRANCOUNT

    insert into trancount ( trancount ) values ( @trancount )


    Step 3: Run the following against the DB, then check trancount table.

    -- Add a record to the test table (trigger will not fire)
    insert into test (text) values ( 'xxxx' );
    go

    -- Update the value (autocommit mode) to fire trigger
    -- Under SQL 7 and 2000, trancount table will only indicate 1 tranaction open.
    -- This is being performed in autocommit mode.
    update test set text = 'test1'
    go

    -- Update value using an explicit transaction
    -- Under SQL 7, trancount will equal 2 in trigger, in SQL 2000 trancount equals 1
    begin transaction
    update test set text = 'test2'
    commit work
    go

Share This Page