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.
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
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.
