SQL Server Performance

error handling with @@TRANCOUNT

Discussion in 'SQL Server 2005 General Developer Questions' started by tran008, Dec 7, 2007.

  1. tran008 New Member

    Hi all,
    With the statement below, what will happen if more then 1 persons run at the same time?
    thanks
    DECLARE @TranStarted BIT
    IF( @@TRANCOUNT = 0 )
    BEGIN
    BEGIN TRANSACTION
    SET @TranStarted = 1
    END
    IF( @TranStarted = 1 )
    BEGIN
    UPDATE TABLE
    IF( @@ERROR <> 0 )
    SET @TranStarted = 0
    ROLLBACK TRANSACTION
    ELSE
    SET @TranStarted = 0
    COMMIT TRANSACTION
    END
    END
  2. Adriaan New Member

    As you can see in Books Online, @@TRANCOUNT relates to the current connection. There can only be one user on any current connection.
    And so the number of transactions is determined by the code which is being executed for this one user.
  3. tran008 New Member

    Hi Adriaan,
    thank you for replying. I just want to make sure, when you says 'current connection' , when 10 users log into the web site, if all off them select submit which call the sql statement, only 1 can get connect?. The others will be delay until the current connection finished the transaction?
  4. ranjitjain New Member

    Your website must be maintaining a connection pool which is set of connections made to SQL server else it is kept to SQL server to maintain all the requests. All 10 connections will maintain their own trancount and based on update stement lock type, modified table resource will be allocated to each 10 connection command handled internally by sql server.
  5. satya Moderator

Share This Page