BEGIN TRAN, ROLLBACK TRAN, and COMMIT TRAN

When creating a SQL Statement by default, for example, SELECT * FROM HumanResources.Employee, SQL Server will run this statement and immediately return the results:

If you were to add BEGIN TRANSACTION (or BEGIN TRAN) before the statement it automatically makes the transaction explicit and holds a lock on the table until the transaction is either committed or rolled back.

BEGIN TRANSACTION marks the starting point of an explicit, local transaction. - MS

For example, when I issue a DELETE or UPDATE statement I always explicitly use BEGIN TRAN to make sure my statement is correct and I get the correct number of results returned.

Let’s say I want to UPDATE the Employee table and set JobTitle equal to 'DBA' where LoginID is like '%barbara%'. I accidentally create my statement wrong and issue the statement below which actually would make every JobTitle equal to 'DBA':
----------------------------------------------------------------------

 UPDATE HumanResources.Employee
        SET JobTitle = ‘DBA’
        WHERE LoginID IN (
        SELECT LoginID FROM HumanResources.Employee)

----------------------------------------------------------------------

Oops! I didn’t mean to do that!! I accidentally made every record have a JobTitle of DBA. If I would have placed a BEGIN TRAN before my statement I would have noticed that 290 results would be effected and something is wrong with my statement:

Since I specified a BEGIN TRAN, the transaction is now waiting on a ROLLBACK or COMMIT. While the transaction is waiting it has created a lock on the table and any other processes that are trying to access HumanResources.Employee are now being blocked. Be careful using BEGIN TRAN and make sure you immediately issue a ROLLBACK or COMMIT:

As you can see, SPID 52 is getting blocked by 54.

Since I noticed something was terribly wrong with my UPDATE statement, I can issue a ROLLBACK TRAN statement to rollback the transaction meaning that none of the data actually changed:

ROLLBACK TRANSACTION rolls back an explicit or implicit transaction to the beginning of the transaction, or to a savepoint inside the transaction. It also frees resources held by the transaction. - MS

If I had written my statement correct the first time and noticed the right amount of results displayed then I could issue a COMMIT TRAN and it would execute the statement and my changes would be committed to the database:

COMMIT TRANSACTION marks the end of a successful implicit or explicit transaction. If @@TRANCOUNT is 1, COMMIT TRANSACTION makes all data modifications performed since the start of the transaction a permanent part of the database, frees the resources held by the transaction, and decrements @@TRANCOUNT to 0. If @@TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @@TRANCOUNT only by 1 and the transaction stays active. - MS