LATEST 🚀 Welcome to Upgradix by Shrash Studio – Your destination for free technology learning and career growth.
🕘 Updated:
MS SQL Server

SQL Server Transactions – COMMIT, ROLLBACK and ACID Properties

A SQL Server transaction groups database operations into one logical unit. COMMIT saves the changes, while ROLLBACK reverses uncommitted changes. ACID properties help transactions maintain reliable and consistent data.

SQL Server Transactions – COMMIT, ROLLBACK and ACID Properties

Quick Answer: A SQL Server transaction groups database operations into one logical unit. COMMIT saves the changes, while ROLLBACK reverses uncommitted changes. ACID properties help transactions maintain reliable and consistent data.

1. Why Do We Need SQL Server Transactions?

Imagine Meena works as a bank officer. She processes 500 money transfers every day between customer accounts.

One customer transfers ₹5,000 from Ravi's account to Priya's account. Ravi must lose ₹5,000, and Priya must receive the same amount.

What happens if the first update succeeds but the second update fails?

The problem without transactions

We could update both accounts using separate SQL statements. Each statement can run independently in SQL Server's default autocommit mode.

Example 1: Transfer money without a transaction

Assume both accounts start with the balances shown in this example.

CREATE TABLE #BankAccounts
(
    AccountId INT PRIMARY KEY,
    AccountName VARCHAR(30),
    Balance DECIMAL(10, 2)
);

INSERT INTO #BankAccounts
VALUES
(101, 'Ravi', 10000),
(102, 'Priya', 4000);

UPDATE #BankAccounts
SET Balance = Balance - 5000
WHERE AccountId = 101;

UPDATE #BankAccounts
SET Balance = Balance + 5000
WHERE AccountId = 999;

SELECT AccountName, Balance
FROM #BankAccounts;

DROP TABLE #BankAccounts;

The first update succeeds. The second update finds no destination account.

Expected output

AccountName  Balance
-----------  ---------
Ravi         5000.00
Priya        4000.00

Ravi loses ₹5,000, but Priya receives nothing. The two operations are incomplete.

  1. The first update is saved independently.
  2. The second update affects zero records.
  3. The money transfer is incomplete.
  4. The application must handle this inconsistent business result.

Now think about thousands of bank transfers happening simultaneously. We need both account updates to succeed together.

We need SQL Server transactions to protect related database operations. We also need a way to undo changes when something fails.

The solution: SQL Server Transactions

Transaction = a group of database operations treated as one logical unit.

BEGIN TRANSACTION = starts an explicit transaction.

COMMIT = completes the transaction and saves its changes.

ROLLBACK = reverses the transaction's uncommitted changes.

Meena starts a transaction before updating Ravi's account. She then updates Priya's account and checks both operations.

Animation 1: How a bank transaction works

Watch Meena process Ravi's ₹5,000 transfer to Priya.

Meena Bank officer · processes transfer
starts transaction
BEGIN TRANSACTION Transfer ₹5,000
deducts from Ravi
Debit Account Ravi: 10,000 → 5,000
credits Priya
Credit Account Priya: 4,000 → 9,000
both updates succeed
COMMIT Both account changes saved
Transaction starts Database operations Committed result Current animation stage

Both updates belong to one transaction. If either update fails, Meena can roll back the changes instead.

2. Implement SQL Server Transactions in T-SQL

Let's implement Meena's bank transfer using SQL Server.

@@ROWCOUNT = returns the number of rows affected by the previous statement.

TRY...CATCH = handles errors raised while SQL statements execute.

XACT_STATE() = shows whether the current transaction is active and committable.

Transfer money using COMMIT and ROLLBACK

Example 2: Perform a safe bank transfer

Run this complete example in SQL Server Management Studio.

CREATE TABLE #BankAccounts
(
    AccountId INT PRIMARY KEY,
    AccountName VARCHAR(30),
    Balance DECIMAL(10, 2)
);
INSERT INTO #BankAccounts VALUES
(101, 'Ravi', 10000),
(102, 'Priya', 4000);

BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE #BankAccounts SET Balance = Balance - 5000 WHERE AccountId = 101;
    IF @@ROWCOUNT <> 1 THROW 50001, 'Source account not found.', 1;
    UPDATE #BankAccounts SET Balance = Balance + 5000 WHERE AccountId = 102;
    IF @@ROWCOUNT <> 1 THROW 50002, 'Destination account not found.', 1;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
END CATCH;

SELECT AccountName, Balance FROM #BankAccounts;
DROP TABLE #BankAccounts;

Notice that BEGIN TRANSACTION starts the transfer. Both account updates happen inside that transaction.

If both updates succeed, COMMIT TRANSACTION saves the changes. If an update fails, the CATCH block rolls back the active transaction.

Expected output

AccountName  Balance
-----------  ---------
Ravi         5000.00
Priya        9000.00

Try the rollback scenario: Change the destination AccountId from 102 to 999. The second update affects zero rows and raises an error. SQL Server rolls back the first update too. Both original balances remain unchanged.

3. Understand ACID Properties

ACID = four properties that help database transactions remain reliable.

These properties are important for banking, payments and other business systems.

1. Atomicity

Atomicity = all transaction operations succeed together, or the transaction is rolled back.

Ravi must not lose money unless Priya receives the transfer. SQL Server reverses uncommitted changes when the transaction is rolled back.

2. Consistency

Consistency = a transaction moves the database from one valid state to another.

Ravi's debit and Priya's credit must follow the bank's business rules. Database constraints also help protect valid data.

3. Isolation

Isolation = concurrent transactions should not interfere with each other's results.

Two bank transfers may access the same account. SQL Server uses isolation levels and locking to manage concurrent access.

4. Durability

Durability = committed changes survive supported system failures.

After Meena commits the transfer, SQL Server uses its transaction log. This helps recover committed changes after a system restart.

ACID properties in Meena's bank transfer
Property Meaning Bank example
Atomicity All or nothing Both account updates succeed together.
Consistency Valid database state Account balances follow business rules.
Isolation Safe concurrent access Simultaneous transfers are managed safely.
Durability Committed data survives recovery Committed transfer changes can be recovered.

4. Common Mistakes and Best Practices

1. Forgetting to commit or roll back

An open transaction can hold locks and affect other database sessions.

Better approach: Always finish transactions using COMMIT or ROLLBACK.

2. Not checking affected rows

An UPDATE statement can execute successfully without changing any records.

Better approach: Check @@ROWCOUNT when the operation expects specific records.

3. Using transactions for too much work

Long transactions can hold locks and increase blocking between sessions.

Better approach: Keep transactions short and perform only related database operations inside them.

5. How SQL Server Transactions Come Up in Interviews

Question 1: What is a transaction in SQL Server?

What the interviewer expects: Explain how related database operations work together.

Strong answer: "A transaction groups database operations into one logical unit. In Meena's bank transfer, both the debit and credit must succeed together. We use COMMIT to save changes and ROLLBACK to undo uncommitted changes."

Question 2: What are ACID properties?

What the interviewer expects: Explain all four properties with practical examples.

Strong answer: "ACID stands for Atomicity, Consistency, Isolation and Durability. These properties help ensure reliable transactions, especially when multiple users access business data."

Question 3: What is the difference between COMMIT and ROLLBACK?

What the interviewer expects: Explain how transaction completion differs from undoing changes.

Strong answer: "COMMIT saves the transaction changes. ROLLBACK reverses uncommitted changes. We use ROLLBACK when an operation fails or business validation does not pass."

6. Hands-On Practice

Beginner: Grocery payment

Create a transaction for a grocery order. Deduct the payment from the customer's wallet and update the order payment status.

Expected result: Both changes should succeed together.

Intermediate: Hospital billing

Create a transaction that records a hospital payment and updates the patient's outstanding balance.

Hint: Use BEGIN TRANSACTION, COMMIT and ROLLBACK.

Advanced: Bank transfers

Extend Meena's transfer to include account balance checks and concurrent transactions.

  • Prevent transfers when the source balance is insufficient.
  • Check both account records before committing.
  • Handle errors using TRY...CATCH.
  • Test two sessions updating the same account.

Challenge: Protect the transfer from incomplete updates and concurrent balance changes.

7. Next Steps and Community

Previous article: SQL Server Indexes – Clustered, Nonclustered and Composite Indexes

Next article: Query Execution Plans and SQL Server Performance Tuning

For more details, read the official Microsoft documentation:

Try Meena's bank transfer example and test both success and failure cases. Share your implementation and discuss transaction handling with other learners.

Comments & Ratings (0)

Log in or create a student account to comment and rate this article.

Be the first to comment on this article.