SQL - SQL Transactions: COMMIT, ROLLBACK, and SAVEPOINT

An SQL transaction is a group of one or more SQL statements that are treated as a single unit of work. Transactions are mainly used when multiple database operations must either be completed successfully together or cancelled together. For example, when transferring money from one bank account to another, the amount must be deducted from one account and added to another account. If one operation succeeds but the other fails, the database could become inconsistent. A transaction helps prevent this problem by allowing all related changes to be committed together or rolled back when something goes wrong.

1. COMMIT

COMMIT is used to permanently save the changes made during a transaction. Once a transaction is committed, the changes generally become permanent and are visible to other database users according to the database system's transaction rules.

For example:

START TRANSACTION;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 101;

UPDATE accounts
SET balance = balance + 500
WHERE account_id = 102;

COMMIT;

In this example, ₹500 is deducted from account 101 and added to account 102. When COMMIT is executed, both changes are saved permanently.

The important point is that COMMIT should normally be executed only after all required operations have completed successfully.

2. ROLLBACK

ROLLBACK is used to cancel changes made during the current transaction that have not yet been committed. It returns the affected data to its previous state, subject to the database system and transaction configuration.

For example:

START TRANSACTION;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 101;

UPDATE accounts
SET balance = balance + 500
WHERE account_id = 102;

ROLLBACK;

Here, the updates are cancelled because ROLLBACK is executed instead of COMMIT.

This is particularly useful when an error occurs during a series of related operations. Rather than leaving the database with only some of the intended changes, the transaction can be cancelled.

3. SAVEPOINT

A SAVEPOINT creates a temporary checkpoint inside a transaction. It allows you to roll back part of a transaction without cancelling the entire transaction.

For example:

START TRANSACTION;

UPDATE employees
SET salary = salary + 2000
WHERE department = 'IT';

SAVEPOINT salary_update;

UPDATE employees
SET salary = salary + 1000
WHERE department = 'HR';

ROLLBACK TO salary_update;

COMMIT;

In this example, the first salary update is retained, while the second update is cancelled by ROLLBACK TO salary_update. The transaction can then continue and eventually be committed.

A savepoint is useful when a transaction contains several stages and you want the ability to undo only a particular stage.

4. Difference Between COMMIT, ROLLBACK, and SAVEPOINT

For example, consider a transaction containing three operations:

Operation 1
Operation 2
SAVEPOINT A
Operation 3
Operation 4

If ROLLBACK TO A is executed, operations 3 and 4 can be undone while the earlier work remains available within the transaction.

If ROLLBACK is executed, the entire uncommitted transaction is cancelled.

If COMMIT is executed, the transaction's remaining changes are saved.

5. Why Transactions Are Important

Transactions are important because database applications frequently perform several related operations. Consider an online shopping application. When a customer places an order, the system may need to:

1. Create an order record.

2. Add products to the order.

3. Reduce product inventory.

4. Record the payment.

5. Update the order status.

If the payment is recorded but the inventory update fails, the database could contain inconsistent information. Using a transaction allows these related operations to be managed as one unit.

A simplified example is:

START TRANSACTION;

INSERT INTO orders (order_id, customer_id)
VALUES (1001, 25);

UPDATE products
SET stock = stock - 1
WHERE product_id = 501;

COMMIT;

If an important operation fails, the application can use ROLLBACK instead.

6. Transactions and Data Consistency

Transactions help maintain database consistency by controlling when changes become permanent. They are especially useful for operations involving financial records, inventory, reservations, order processing, employee records, and other systems where partial updates can cause problems.

For example, in a banking transaction:

START TRANSACTION;

UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 101;

UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 102;

COMMIT;

Both account updates belong to the same transaction. If the application detects a problem before the transaction is committed, it can use:

ROLLBACK;

This prevents the intended transfer from being partially completed.

7. Practical Example Using SAVEPOINT

Suppose a company wants to update salaries in several departments:

START TRANSACTION;

UPDATE employees
SET salary = salary + 5000
WHERE department = 'IT';

SAVEPOINT it_update;

UPDATE employees
SET salary = salary + 3000
WHERE department = 'HR';

SAVEPOINT hr_update;

UPDATE employees
SET salary = salary + 2000
WHERE department = 'Sales';

ROLLBACK TO hr_update;

COMMIT;

Here, the IT update remains part of the transaction. The HR update is retained because the rollback is to the hr_update savepoint, while the Sales update is undone because it occurred after that savepoint. The transaction can then be committed.

8. Important Considerations

The exact transaction syntax and behavior can differ between database management systems such as MySQL, PostgreSQL, SQL Server, and Oracle. Some systems use START TRANSACTION, while others provide alternatives such as BEGIN or BEGIN TRANSACTION.

It is also important to understand that transaction behavior can depend on settings such as autocommit. In some database systems or client tools, individual SQL statements may be committed automatically unless an explicit transaction is started.

Another important consideration is that COMMIT is generally the point at which the transaction's changes are made permanent. Therefore, applications should perform appropriate validation and error handling before committing important operations.

Conclusion

COMMIT, ROLLBACK, and SAVEPOINT are fundamental tools for controlling SQL transactions. COMMIT saves successful changes, ROLLBACK cancels uncommitted changes, and SAVEPOINT provides checkpoints that allow selected portions of a transaction to be undone. Together, these commands help database applications handle multiple related operations safely and maintain reliable and consistent data.