How do you use COMMIT and ROLLBACK in SQL?

COMMIT and ROLLBACK are SQL commands used to manage transactions, ensuring data integrity by grouping operations into atomic units. A transaction is a sequence of one or more SQL statements that must execute completely or not at all. COMMIT finalizes changes made during a transaction, making them permanent in the database. ROLLBACK undoes all changes made in the current transaction, reverting the database to its state before the transaction began. These commands are critical when handling operations that require multiple steps to succeed together, such as financial transfers or batch updates.

To use these commands, you typically start a transaction explicitly (e.g., with BEGIN TRANSACTION in SQL Server or START TRANSACTION in MySQL). For example, consider a banking application transferring funds between accounts:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

Here, both updates must succeed for the transfer to be valid. If either statement fails, executing ROLLBACK instead of COMMIT cancels both changes. Without explicit transactions, databases often default to “autocommit” mode, where each statement is treated as its own transaction. Explicit transactions let you group statements, ensuring atomicity. Error handling in application code (e.g., using try-catch blocks) is essential to decide whether to COMMIT or ROLLBACK based on success or failure.

A key practical consideration is autocommit settings. For example, PostgreSQL enables autocommit by default, but you can disable it to bundle multiple operations. In tools like Python’s psycopg2 or Java’s JDBC, you might set autocommit=False to manage transactions programmatically. Additionally, long-running transactions should be avoided, as they can lock resources and impact performance. Always structure transactions to encompass only necessary operations and include error handling to invoke ROLLBACK during exceptions. Proper use of COMMIT and ROLLBACK ensures data consistency, especially in systems where partial updates could leave the database in an invalid state.