What is a transaction in SQL?

A transaction in SQL is a sequence of one or more database operations grouped into a single logical unit. These operations, such as inserting, updating, or deleting data, are treated as a whole: either all succeed, or none do. Transactions ensure data integrity by adhering to the ACID principles—Atomicity, Consistency, Isolation, and Durability. Atomicity guarantees that all operations within a transaction are completed or rolled back entirely if an error occurs. Consistency ensures the database transitions from one valid state to another, maintaining predefined rules like constraints. Isolation keeps transactions independent from each other during execution, preventing interference. Durability ensures that once a transaction is committed, its changes persist even in system failures.

For example, consider a banking application transferring funds between accounts. A transaction would involve deducting money from one account and adding it to another. If either step fails (e.g., insufficient funds or a network error), the entire transaction rolls back, leaving both accounts unchanged. Without transactions, partial updates could corrupt data—like deducting funds without crediting the recipient. Transactions prevent such inconsistencies by bundling operations. Another example is an e-commerce order: updating inventory, charging a payment method, and creating an order record must all succeed together. If any step fails, the transaction undoes prior changes, avoiding oversold items or unprocessed payments.

In SQL, transactions are managed using commands like BEGIN TRANSACTION, COMMIT, and ROLLBACK. Developers explicitly start a transaction with BEGIN, execute operations, and finalize it with COMMIT. If an error occurs, ROLLBACK undoes all changes since the transaction began. For instance, in PostgreSQL, a transaction might look like:

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

If the second update fails, rolling back ensures the first update isn’t applied. Transactions are critical in multi-user systems where concurrent operations could lead to conflicts. By isolating transactions (e.g., using isolation levels like READ COMMITTED), databases prevent issues like dirty reads or phantom data. This structured approach ensures reliability in applications where data accuracy is non-negotiable, such as finance or inventory management.