Wednesday 3 February 2016

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:
What does BEGIN TRAN, ROLLBACK TRAN, and COMMIT TRAN mean?
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)
----------------------------------------------------------------------

I accidentally made every record have a JobTitle of DBA
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:
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:
Since I specified a BEGIN TRAN, the transaction is now waiting on 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
As you can see, SPID 52 is getting blocked by 54.
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
It would execute the statement and my changes would be committed to the database.