Transactions Introduction
Learn the basics of SQL Server Transactions, why transactions are used, and how COMMIT, ROLLBACK and SAVEPOINT work.
What is a Transaction?
A Transaction is a group of one or more SQL statements that are treated as a single unit of work.
A transaction allows us to make sure that a group of database operations is completed successfully. If something goes wrong, the changes can be rolled back.
Transactions are commonly used when multiple database operations must be performed together.
Why Do We Use Transactions?
Transactions help maintain the consistency and reliability of data in a database.
- To group multiple SQL operations together.
- To save changes only when all required operations are successful.
- To undo changes when an error occurs.
- To maintain database consistency.
- To control when changes become permanent.
Real-World Example
Consider a bank transfer from Account A to Account B.
Two operations are required:
- Deduct money from Account A.
- Add money to Account B.
Both operations should succeed together. If money is deducted from Account A but the second operation fails, the first operation should also be undone.
Either all required operations are completed successfully, or the changes can be rolled back.
Basic Transaction Syntax
BEGIN TRANSACTION;
-- SQL statements
COMMIT;
Here, BEGIN TRANSACTION starts the transaction and COMMIT makes the changes permanent.
BEGIN TRANSACTION
BEGIN TRANSACTION starts a new transaction.
BEGIN TRANSACTION;
After the transaction starts, SQL statements executed as part of that transaction can be committed or rolled back.
COMMIT
COMMIT permanently saves the changes made during the transaction.
COMMIT;
If all operations are successful, use COMMIT to save the changes.
ROLLBACK
ROLLBACK is used to undo changes made during a transaction that have not been committed.
ROLLBACK;
If an error occurs during the transaction, we can use ROLLBACK to undo the transaction's changes.
Simple Transaction Example
Let's create a simple transaction using a Students table.
BEGIN TRANSACTION;
INSERT INTO Students
(
StudentId,
StudentName,
City,
Age
)
VALUES
(
105,
'Amit',
'Pune',
23
);
COMMIT;
The INSERT operation is performed inside the transaction. When COMMIT is executed, the change becomes permanent.
ROLLBACK Example
BEGIN TRANSACTION;
INSERT INTO Students
(
StudentId,
StudentName,
City,
Age
)
VALUES
(
106,
'Sneha',
'Mumbai',
22
);
ROLLBACK;
In this example, the INSERT operation is undone because the transaction is rolled back.
Transaction Flow
BEGIN TRANSACTION
↓
SQL Statements
↓
Check Result
↓
COMMIT → Save Changes
OR
ROLLBACK → Undo Changes
Important Transaction Commands
| Command | Purpose |
|---|---|
BEGIN TRANSACTION
|
Starts a transaction. |
COMMIT
|
Permanently saves the transaction changes. |
ROLLBACK
|
Undoes uncommitted transaction changes. |
SAVE TRANSACTION
|
Creates a savepoint inside a transaction. |
Important Points
- A transaction groups multiple SQL operations into a single unit of work.
- BEGIN TRANSACTION starts the transaction.
- COMMIT saves the changes.
- ROLLBACK undoes uncommitted changes.
- SAVE TRANSACTION creates a savepoint inside a transaction.
- Transactions help maintain data consistency.
Summary
A transaction is a group of SQL statements treated as one unit of work.
SQL Server provides commands such as BEGIN TRANSACTION, COMMIT, ROLLBACK and SAVE TRANSACTION to control database changes.
Transactions are useful when multiple database operations need to be completed reliably and consistently.