SQL Server Performance

How does COMMIT Works

Discussion in 'Getting Started' started by ishu_pd, Aug 4, 2007.

  1. ishu_pd New Member

    I am a new SQL Server user. I would like to know how the Commit tran works. I would like to know what happens when there is a begin tran and I don't run the COMMIT TRAN. Does it lock the database.
  2. gaurav_bindlish New Member

    Short answer - Depending on the isolation level and plan used to execute the query, database objects will be locked.
    I would suggest looking at BEGIN TRANSACTION in SQL Server 2005 Books Online -
    http://msdn2.microsoft.com/en-us/library/ms188929.aspx
  3. satya Moderator

    Hey Gaurav
    Good to see your post after a long time...
    coming to the topic as you haven't mentioned about version of SQL Server, by default the responsibility of the TSQL statement to issue COMMIT TRANSACTION only at a point when all data referenced by the transaction is logically correct.
    If the transaction committed was a Transact-SQL distributed transaction, COMMIT TRANSACTION triggers MS DTC to use a two-phase commit protocol to commit all the servers involved in the transaction. If a local transaction spans two or more databases on the same server, SQL Server uses an internal two-phase commit to commit all the databases involved in the transaction.
    Further if a COMMIT TRANSACTION or COMMIT WORK statement is executed in a trigger, and there is no corresponding explicit or implicit BEGIN TRANSACTION statement at the start of the trigger.
  4. FrankKalis Moderator

    Indeed, nice to see you back. [:)]

Share This Page