---
title: "Explain the transaction in SQL Server."  
description: "A SQL transaction ensures data integrity by grouping SQL operations into a single unit that either succeeds completely or fails entirely."  
author: "Ashutosh Patel"  
published: 2024-07-15  
updated: 2024-07-15  
canonical: https://www.mindstick.com/blog/304493/explain-the-transaction-in-sql-server  
category: "mssql server"  
tags: ["mssql server", "sql server", "sql"]  
reading_time: 4 minutes  

---

# Explain the transaction in SQL Server.

#### SQL Server Transaction

A transaction is a transaction against a database. This is a series of SQL operations that are executed as a single unit, meaning that all of the operations in the transaction have been successfully completed and loaded into the database, or that none of them are executed if an error occurs or if the transaction clearly occurs ( **rolled back**) rolled back .

Here are the basic concepts of [transactions](https://www.mindstick.com/interview/864/explain-acid-rule-of-thumb-for-transactions) in [SQL Server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server).

ACID Components\
Connections to SQL Server follow ACID capabilities, which ensure reliability and [data integrity](https://www.mindstick.com/forum/160197/what-is-the-normalization-in-sql-server-explain-its-impact-on-data-integrity-and-performance),

**Atomicity-** [Communication](https://www.mindstick.com/articles/126321/5-fails-and-fixes-of-the-office-communication) is atomic, that is, it has one function at a time. All operations in the transaction succeed and are guaranteed, or if an operation fails, the entire transaction is rolled back to maintain [data consistency](https://www.mindstick.com/forum/159960/how-can-you-ensure-data-consistency-in-a-microservices-architecture)\
**Consistency-** Consistency ensures that the database remains in a consistent state before and after operations.\
**Isolation-** Interactions operate in isolation from each other until completion (involvement). This prevents connections from interfering with each other during execution.\
**Durability-** Once a job is executed, its changes are permanently stored in the database even if the system fails.

#### \
Transaction Control Statements

SQL Server provides more information to process transactions:

`BEGIN TRANSACTION` Begins a new transaction.\
`COMMIT TRANSACTION` Commits the current transaction, making its changes permanent.\
`ROLLBACK TRANSACTION` Rolls back the current transaction, undoing its changes.\
`SAVE TRANSACTION` Set a savepoint in the transaction that you can go back to later.

#### Implicit vs Explicit Transactions

## Implicit Transactions

By default, SQL Server operates in self-commit mode, where each individual SQL statement commits after success. However, you can assemble multiple SQL statements into an explicit transaction using `BEGIN TRANSACTION` and `COMMIT or ROLLBACK`.

\
**Explicit Transactions**

These are transactions that are explicitly defined by BEGIN `TRANSACTION`, and require a manual commit (`COMMIT`) or rollback (`ROLLBACK`) to complete the transaction

#### \
Concurrency Control

SQL Server uses a [concurrency control](https://www.mindstick.com/forum/160902/how-does-sql-server-manage-concurrency-control) mechanism to [manage multiple](https://answers.mindstick.com/qa/105236/how-to-manage-multiple-email-accounts-on-a-mobile-phone) transactions to access the same data at the same time. This ensures that connections are isolated from each other to prevent incompatible data. SQL Server supports various [isolation levels](https://www.mindstick.com/forum/160202/explain-acid-properties-isolation-levels-and-locking-mechanisms-in-sql-server) (such as Read Uncommitted, Read Committed, Repeatable Read, Serializable) that control the isolation level and locking behavior for transactions

#### Transaction Logs

SQL Server maintains transaction logs to write all transaction-induced changes to the database. These logs are necessary to ensure longevity and recovery in the event of a system failure.

```plaintext
BEGIN TRANSACTION;
UPDATE Employees
SET Salary = Salary * 1.05
WHERE Department = 'Engineering';
INSERT INTO AuditLog (Action, TableName, Timestamp)
VALUES ('Salary Update', 'Employees', GETDATE());
COMMIT TRANSACTION;
```

## In the example above

- `BEGIN TRANSACTION` Begins a new transaction.
- In the `UPDATE` case, the compensation for employees in the technical department changes.
- The `INSERT` statement writes the update action to the AuditLog table.
- `COMMIT TRANSACTION` Commits the changes made by the transaction to the database.\

If any part of the transaction goes into error (e.g., due to a [constraint violation](https://www.mindstick.com/forum/159600/what-is-a-constraint-violation-error-and-can-handle-it)), it can use a `ROLLBACK TRANSACTION` to undo all changes made by the transaction and restore the database to its previous state

## Example-

Here is a simple example to transfer money from source account to destination account using SQL Transaction,

Suppose we have an SQL table `BankAccounts`

```plaintext
CREATE TABLE BankAccounts (
   AccountID INT PRIMARY KEY,
   AccountNumber VARCHAR(20) NOT NULL,
   Balance DECIMAL(18, 2) NOT NULL
);
```

Suppose we want to transfer a specified amount from one account to another

```plaintext
USE MyTestDB
GO
CREATE PROCEDURE Proc_EmpCursor
(
@FromAccountID INT = 1,   -- Assuming AccountID 1 is the source account
@ToAccountID INT = 2,     -- Assuming AccountID 2 is the destination account
@TransferAmount DECIMAL(18, 2) = 100.00  -- Amount to transfer
)AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION;
-- Check if the source account has enough balance
DECLARE @FromBalance DECIMAL(18, 2);
SELECT @FromBalance = Balance FROM BankAccounts WHERE AccountID = @FromAccountID;
IF @FromBalance >= @TransferAmount
BEGIN
 -- Deduct the transfer amount from the source account
 UPDATE BankAccounts
 SET Balance = Balance - @TransferAmount
 WHERE AccountID = @FromAccountID;
 -- Add the transfer amount to the destination account
 UPDATE BankAccounts
 SET Balance = Balance + @TransferAmount
 WHERE AccountID = @ToAccountID;
 -- Log the transaction in an AuditLog table
 INSERT INTO AuditLog (Action, Description, Timestamp)
 VALUES ('Transfer', 'Transfer from AccountID ' + CAST(@FromAccountID AS VARCHAR)
        + ' to AccountID ' + CAST(@ToAccountID AS VARCHAR)
        + ' Amount: ' + CAST(@TransferAmount AS VARCHAR), GETDATE());
 -- Commit the transaction
 COMMIT TRANSACTION;

 PRINT 'Transfer successful.';
END
ELSE
BEGIN
 -- Rollback the transaction if there's insufficient balance
 ROLLBACK TRANSACTION;
 PRINT 'Insufficient balance in the source account. Transfer aborted.';
END

SET NOCOUNT OFF;
END
```

**Also, Read:** [Explain the SQL CURSOR with example.](https://www.mindstick.com/blog/304490/explain-the-sql-cursor-with-example)

---

Original Source: https://www.mindstick.com/blog/304493/explain-the-transaction-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.