COMMIT
Learn how the COMMIT statement permanently saves changes made during a SQL Server transaction.
What is COMMIT?
COMMIT is a SQL Server transaction command used to permanently save all changes made during the current transaction.
Once a transaction is committed, the changes become permanent in the database and normally cannot be undone using ROLLBACK.
Why Do We Use COMMIT?
- To permanently save transaction changes.
- To confirm that all required operations completed successfully.
- To make the transaction changes permanent.
- To complete a transaction successfully.
COMMIT Syntax
BEGIN TRANSACTION;
-- SQL statements
COMMIT;
Simple COMMIT Example
Suppose we want to insert a new student into the Students table.
BEGIN TRANSACTION;
INSERT INTO Students
(
StudentId,
StudentName,
City,
Age
)
VALUES
(
201,
'Rahul',
'Pune',
22
);
COMMIT;
Here, the INSERT operation is performed inside the transaction. When COMMIT executes, the new student record is permanently saved.
UPDATE with COMMIT
COMMIT can also be used after an UPDATE operation.
BEGIN TRANSACTION;
UPDATE Students
SET City = 'Mumbai'
WHERE StudentId = 201;
COMMIT;
The student's city is updated and the change is permanently saved after COMMIT.
DELETE with COMMIT
BEGIN TRANSACTION;
DELETE FROM Students
WHERE StudentId = 201;
COMMIT;
The DELETE operation is performed inside the transaction and becomes permanent after COMMIT.
COMMIT with Multiple Operations
A transaction can contain multiple SQL statements. COMMIT saves all of them together.
BEGIN TRANSACTION;
INSERT INTO Students
(
StudentId,
StudentName,
City,
Age
)
VALUES
(
202,
'Priya',
'Pune',
21
);
UPDATE Students
SET Age = 22
WHERE StudentId = 202;
COMMIT;
Both operations are part of the same transaction. When COMMIT executes, the changes are saved.
COMMIT vs ROLLBACK
| COMMIT | ROLLBACK |
|---|---|
| Permanently saves changes. | Undoes uncommitted changes. |
| Completes the transaction successfully. | Cancels/undoes the transaction changes. |
| Used when the operation is successful. | Used when an error or unwanted result occurs. |
Real-World Example
Consider an online shopping application.
When a customer places an order, several operations may need to happen:
- Create the order.
- Add order items.
- Reduce product stock.
- Save payment information.
If all operations are successful, the application can COMMIT the transaction.
BEGIN TRANSACTION;
-- Create Order
-- Add Order Items
-- Update Stock
-- Save Payment
COMMIT;
Important Points
- COMMIT permanently saves transaction changes.
- COMMIT is normally used after successful transaction operations.
- COMMIT completes the current transaction.
- After COMMIT, the transaction changes are no longer available for normal ROLLBACK.
- Multiple SQL statements can be committed together as one transaction.
Summary
COMMIT is used to permanently save changes made during a SQL Server transaction.
A transaction should generally be committed when all required operations have completed successfully.