Home » SQL Server » SQL Server – BEGIN TRAN

SQL Server – BEGIN TRAN

SQL Server BEGIN-TRAN

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

Sometimes we wrongly update & Delete records from table. So always use this when you performing with DML commands like INSERT, UPDATE & DELETE.

Refer : ROLLBACK, COMMIT & SAVEPOINT





Note: Explicit transactions are those in which you explicitly control when the transaction begins and when it ends.

Syntax:

BEGIN TRANSACTION
or 
BEGIN TRAN

Let’s understand with an example:

Step 1: Sample data as below:

SQL Sample Data

SQL Sample Data





Step 2: Now, delete all records from Students Table using BEGIN TRAN command.

Query:

BEGIN TRAN T1
DELETE Students
T1 : is Transaction name
Execute both query together
BEGIN TRAN with Delete Statement

BEGIN TRAN with Delete Statement




Step 3: Select Students table, all records has been deleted successfully.

SELECT * FROM Students
Select Statement

Select Statement

Step 4: But we used BEGIN TRAN before SQL Query statement, so it holds the transaction until the transaction is either committed or rolled back.




Step 5:  Now we rolled back the Transaction.

ROLLBACK TRAN T1
ROLLBACK TRAN

ROLLBACK TRAN

Step 6: check again the table all records rolled back or not?

SELECT * FROM Students
SELECT Statement-2

SELECT Statement-2

As you saw, all records rolled back successfully!

Note:  Don’t forget to Commit or Rollback the transaction if you used Begin Tran, otherwise you will faced deadlock issue.

Refer : ROLLBACK, COMMIT & SAVEPOINT




Hope you enjoy the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.

Leave a Reply