SQL Topics
COMMIT in SQL
title: COMMIT in SQL
In the previous lesson, you learned about Transactions and how multiple SQL statements can be grouped into a single logical unit.
A transaction allows a database to ensure that:
All Operations Succeed
OR
All Operations FailHowever, after making changes inside a transaction, those changes are not automatically permanent.
The database needs confirmation that the transaction has completed successfully.
This confirmation is provided using the COMMIT statement.
The COMMIT statement permanently saves all changes made during a transaction.
Once committed, the changes become visible to other users and cannot be undone using ROLLBACK.
What is COMMIT?
COMMIT is a transaction control command that permanently saves all changes made during the current transaction.
Think of COMMIT as:
Save ChangesWhen COMMIT executes:
Transaction Successful
↓
Save Changes Permanently
↓
Release ResourcesWhy is COMMIT Important?
Suppose a banking application transfers money:
Withdraw ₹1000
From Account Aand
Deposit ₹1000
Into Account BBoth operations succeed.
Now the database must permanently save these updates.
Without COMMIT:
Changes Are TemporaryWith COMMIT:
Changes Become PermanentHow COMMIT Works
Transaction Flow:
BEGIN TRANSACTION
↓
Execute SQL Statements
↓
COMMIT
↓
Changes Saved PermanentlyTransaction Lifecycle
BEGIN
↓
Changes Made
↓
COMMIT
↓
Permanent Database UpdateBasic COMMIT Syntax
BEGIN TRANSACTION;
SQL Statements;
COMMIT;Example:
BEGIN TRANSACTION;
UPDATE Employees
SET Salary = 60000
WHERE EmployeeID = 1;
COMMIT;The salary update becomes permanent.
Creating Sample Table
CREATE TABLE Employees (
EmployeeID INT,
EmployeeName VARCHAR(100),
Salary DECIMAL(10,2)
);Insert data:
INSERT INTO Employees VALUES
(1,'Rahul',50000),
(2,'Priya',60000);First COMMIT Example
BEGIN TRANSACTION;
UPDATE Employees
SET Salary = 55000
WHERE EmployeeID = 1;
COMMIT;Result:
| EmployeeID | EmployeeName | Salary |
|---|---|---|
| 1 | Rahul | 55000 |
Change is permanently stored.
COMMIT with Multiple Statements
BEGIN TRANSACTION;
UPDATE Employees
SET Salary = 70000
WHERE EmployeeID = 1;
UPDATE Employees
SET Salary = 80000
WHERE EmployeeID = 2;
COMMIT;Both updates become permanent together.
Visual Representation
Before COMMIT:
Database
↓
Temporary ChangesAfter COMMIT:
Database
↓
Permanent ChangesCOMMIT and INSERT
Example:
BEGIN TRANSACTION;
INSERT INTO Employees
VALUES
(
3,
'Amit',
70000
);
COMMIT;Result:
New Employee Saved
PermanentlyCOMMIT and UPDATE
Example:
BEGIN TRANSACTION;
UPDATE Employees
SET Salary = 75000
WHERE EmployeeID = 3;
COMMIT;Update becomes permanent.
COMMIT and DELETE
Example:
BEGIN TRANSACTION;
DELETE FROM Employees
WHERE EmployeeID = 3;
COMMIT;Deletion becomes permanent.
What Happens Without COMMIT?
Example:
BEGIN TRANSACTION;
UPDATE Employees
SET Salary = 100000
WHERE EmployeeID = 1;No COMMIT.
If session closes:
Changes May Be Lostdepending on the database system.
COMMIT vs Auto Commit
Many databases use:
Auto Commit Modeby default.
Example:
UPDATE Employees
SET Salary = 65000
WHERE EmployeeID = 1;Immediately committed.
Manual COMMIT
Example:
BEGIN TRANSACTION;
UPDATE Employees
SET Salary = 70000
WHERE EmployeeID = 1;
COMMIT;Developer controls when changes are saved.
COMMIT with Banking Example
Initial Data:
| Account | Balance |
|---|---|
| A | 10000 |
| B | 5000 |
Transaction:
BEGIN TRANSACTION;
UPDATE Accounts
SET Balance = Balance - 1000
WHERE AccountID = 1;
UPDATE Accounts
SET Balance = Balance + 1000
WHERE AccountID = 2;
COMMIT;Final Data:
| Account | Balance |
|---|---|
| A | 9000 |
| B | 6000 |
COMMIT with E-Commerce Example
Order Process:
Create Order
Update Inventory
Generate InvoiceAfter successful completion:
COMMIT;All changes saved together.
COMMIT with Payroll System
Transaction:
Calculate Salary
Deduct Tax
Update Employee AccountSuccessful execution:
COMMIT;Ensures payroll consistency.
COMMIT vs ROLLBACK
COMMIT:
Save ChangesROLLBACK:
Undo ChangesComparison:
| Feature | COMMIT | ROLLBACK |
|---|---|---|
| Save Changes | Yes | No |
| Undo Changes | No | Yes |
| Permanent Update | Yes | No |
| End Transaction | Yes | Yes |
COMMIT vs SAVEPOINT
COMMIT:
Ends TransactionSAVEPOINT:
Creates Checkpointwithin a transaction.
Advantages of COMMIT
Permanent Data Storage
Changes are saved.
Maintains Consistency
Ensures successful transactions remain valid.
Releases Locks
Database resources become available.
Supports Business Operations
Essential for banking, e-commerce, and ERP systems.
Improves Reliability
Successful work is preserved.
Disadvantages of COMMIT
Cannot Be Undone with ROLLBACK
After commit:
Changes PermanentIncorrect Data May Be Saved
Errors committed accidentally remain stored.
Requires Careful Validation
Always verify before committing.
Performance Considerations
Frequent commits:
More Disk WritesVery large transactions:
Consume More ResourcesBalance is important.
Common Errors
Forgetting COMMIT
Changes may remain temporary.
Committing Incorrect Data
Always validate before commit.
Excessive Commit Frequency
Can reduce performance.
Assuming COMMIT Can Be Reversed
After commit:
ROLLBACK No Longer Worksfor that transaction.
Best Practices
Validate Data Before COMMIT
Avoid permanent mistakes.
Keep Transactions Short
Reduce lock duration.
Commit Only Successful Transactions
Ensure all operations completed correctly.
Use Error Handling
Prevent accidental commits.
Test Transaction Logic
Verify expected behavior.
Common Interview Questions
What is COMMIT?
A transaction control command that permanently saves transaction changes.
What happens after COMMIT?
Changes become permanent and transaction resources are released.
Can COMMIT be undone?
Not with ROLLBACK.
A new transaction would be required.
What is the difference between COMMIT and ROLLBACK?
COMMIT saves changes; ROLLBACK undoes them.
Why is COMMIT important?
It ensures successful transactions are permanently stored.
Summary
COMMIT is one of the most important transaction control commands in SQL. It permanently saves all successful changes made during a transaction, ensures data consistency, and finalizes business operations safely.
In this lesson, you learned:
- What COMMIT is
- Why COMMIT is important
- How COMMIT works
- COMMIT syntax
- COMMIT with INSERT
- COMMIT with UPDATE
- COMMIT with DELETE
- Auto Commit vs Manual Commit
- COMMIT vs ROLLBACK
- COMMIT vs SAVEPOINT
- Real-world examples
- Best practices
Mastering COMMIT is essential because every reliable database application depends on proper transaction management.
Next Step
Continue to the next lesson:
ROLLBACK in SQL →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for COMMIT in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, transactions, commit
Related SQL Topics