What does BEGIN TRAN, ROLLBACK TRAN, and COMMIT TRAN mean?
By: Brady Upton
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 affected 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