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
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
Step 3: Select Students table, all records has been deleted successfully.
SELECT * FROM Students
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
Step 6: check again the table all records rolled back or not?
SELECT * FROM Students
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.