Welcome to deBUG.to Community where you can ask questions and receive answers from Microsoft MVPs and other experts in our community.
0 like 0 dislike
3.7k views
in SQL Queries by 21 20 26

I have noted that someone writes the below SQL query but I don't know why he uses the BeginTransaction statement before the update query
Can anyone tell me when and why we should use BeginTransaction before running the SQL query

Begin Transaction
Update Employee set Name = 'khaled'  where EmpID = 1

 


1 Answer

0 like 0 dislike
by 24 26 40
selected by
 
Best answer

What's a Transaction?

  • A transaction is a unit of work that is performed against a database.
  • A transaction ensures that either all of the commands succeed, or none of them.
  • If one of the commands in the transaction fails all of the commands fail and any data that was modified in the database is rolled back. In this way, transactions maintain the integrity of data in a database.

Begin Transaction in SQL

The Begin Transaction is used with Explicit transactions as a kind of transaction mode.
Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.

  • BEGIN TRANSACTION increments @@TRANCOUNT by 1.
  • BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent.
  • If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency.
  • Each transaction lasts until either it completes without errors and COMMIT TRANSACTION is issued to make the modifications a permanent part of the database, or errors are encountered and all modifications are erased with a ROLLBACK TRANSACTION statement.
  • BEGIN TRANSACTION starts a local transaction for the connection issuing the statement. Depending on the current transaction isolation level settings, many resources acquired to support the Transact-SQL statements issued by the connection are locked by the transaction until it is completed with either a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement. Transactions left outstanding for long periods of time can prevent other users from accessing these locked resources, and also can prevent log truncation.
  • Although BEGIN TRANSACTION starts a local transaction, it is not recorded in the transaction log until the application subsequently performs an action that must be recorded in the log, such as executing an INSERT, UPDATE, or DELETE statement. An application can perform actions such as acquiring locks to protect the transaction isolation level of SELECT statements, but nothing is recorded in the log until the application performs a modification action.

Explicit Transaction Example
This example will commit the query after the begin transaction.

BEGIN TRANSACTION;  
DELETE FROM EMPLOYEE  
    WHERE EPMID= 10;  
COMMIT;  

Rollback Transaction Example
This example will rollback the query after the begin transaction.

BEGIN TRANSACTION;  
       INSERT INTO Table1 VALUES(1);  
       INSERT INTO Table1 VALUES(2);  
ROLLBACK; 

See also

If you don’t ask, the answer is always NO!
...