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.
- The first update is saved independently.
- The second update affects zero records.
- The money transfer is incomplete.
- 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.
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.
| 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.