Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

What does BEGIN TRAN, ROLLBACK TRAN, and COMMIT TRAN mean?



By:

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





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, May 30, 2017 - 9:50:00 PM - Lie Back To Top

What if we do begin tran twice in the same process? Can we commit it or not?

Simply like this

BEGIN TRAN

UPDATE syntax

GO

BEGIN TRAN

UPDATE syntax


Tuesday, March 21, 2017 - 1:49:58 PM - John Back To Top

Very good information.  Detailed and super helpful!


Wednesday, September 07, 2016 - 9:45:04 AM - Mohseen Back To Top

 

 good one, very helpfull, thanks.


Friday, July 08, 2016 - 12:32:44 PM - Eric Back To Top

hello good infarmation!!


Saturday, June 25, 2016 - 8:45:52 AM - SriDotnet Back To Top

 

Hello Sir,

Good Inforamation, I'm a dotnet developer, so my problem is i'm using a stored procedure to insert multiple data 

for example : 

create procedure spInsert(@var1,@var2,@var3,@var4,@var5...)

as

 begin

--Insert Query 1

 insert into Table(var1,var2,var3,var4,var5) values(@var1,@var2,@var3,@var4,@var5)

--Insert Query 2

--Insert table 2 through StoredProcedure

  Eexec spTable2 @var1,@var5

--Insert Query 3

--Insert Anotherr Table 3 through Stored Procedure

   Exec spTable3 @var1, @var3,@var5 

end

how to use Transaction in above StoredProcedure i tried but its not working for me

 

[Thanks]

Regards

SriDotnet

https://allittechnologies.blogspot.in

 


Wednesday, June 22, 2016 - 9:25:02 AM - ram Back To Top

 so helpful


Thursday, March 31, 2016 - 10:53:59 PM - Jonathan Back To Top

Thank you for this nice and easy to understand explanation! :)


Thursday, February 25, 2016 - 4:53:08 PM - Dave Back To Top

"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,"

 

Does this mean that the changes are written to the transaction log AND in memory at the same time? I mean, are the committed changes entered in the transaction log and then the pages in memory are changed? After this permanent change, the actual changes to the datafile happen when a checkpoint is issued or the lazywriter flushes the dirty pages to the datafile?

 

Is my thinking correct?

 

I would like an official definition of what a "committed transation" means.  What are the explicit steps in committing a transaction?

 

Thanks,

 


Tuesday, February 09, 2016 - 7:32:58 AM - ANKUR Back To Top

 

 

 

rollback transaction doesnt have any effect on table

there is no change once you begin transaction and end it by ROLLBACK tran

while in case of COMMIT the changes have been done on database table.

 

 


Learn more about SQL Server tools