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


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

<< Previous

Next >>



Comments For This Article




Tuesday, May 16, 2023 - 11:32:53 AM - Don Draper Back To Top (91197)
I was shown this technique many years ago by a SQL guru friend. I have used it since and has saved me many times.

Wednesday, May 26, 2021 - 9:22:03 PM - Rui Back To Top (88749)
How did you get to see the results in the picture above the line, "As you can see, SPID 52 is getting blocked by 54."

Thursday, January 17, 2019 - 8:23:51 AM - Hadi Back To Top (78796)

 

 Thank you very match.. helpfull

Wednesday, June 20, 2018 - 10:58:08 AM - Arslan Back To Top (76257)

 

Thank you for awfully helpful article

Wednesday, June 13, 2018 - 2:35:35 AM - Ree Wang Back To Top (76203)

Very easy to understanding, very helpful! Thanks

Thursday, February 8, 2018 - 8:40:03 AM - Paul Back To Top (75136)

 I would like to comment the sentence:

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

The bolded part of sentence is not always true.

For example for INSERT/UPDATE/DELETE statement, the bolded part of sentence  is true.

But for SELECT statement is wrong.

I assume default isolation level - read commited.

If you have code like this:

BEGIN TRANSACTION

SELECT * FROM HumanResources.Employee

 

Means that lock is hold only when SELECT statement is executing.

But when SELECT has been finished  and the transaction is still active.

There is no lock on table.

 

But if you have code like this:

BEGIN TRANSACTION

SELECT * FROM HumanResources.Employee (holdlock)

Lock is hold untill transaction is active (untill commit or rollback).

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

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 (51511)

Very good information.  Detailed and super helpful!

Wednesday, September 7, 2016 - 9:45:04 AM - Mohseen Back To Top (43274)

 

 good one, very helpfull, thanks.

Friday, July 8, 2016 - 12:32:44 PM - Eric Back To Top (41843)

hello good infarmation!!

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

 

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 (41738)

 so helpful

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

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

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

"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 9, 2016 - 7:32:58 AM - ANKUR Back To Top (40631)

 

 

 

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.

 

 














get free sql tips
agree to terms