Why are transactions used in databases?

Transactions ensure atomicity, consistency, isolation, and durability (ACID) of database operations.

• Atomicity: All operations in a transaction are completed fully or not at all. If one operation fails, all are rolled back.
• Consistency: The transaction transitions the database from one consistent state to another. Constraints (like unique keys) are enforced.
• Isolation: Each transaction is executed as if it were the only one in the system. Changes made by one transaction are not visible to others until committed.
• Durability: Once a transaction is committed, its changes are permanent, even in case of system failure.

Transactions are crucial for maintaining data integrity, especially when executing multiple related operations that must be completed together or not at all. Example: transferring money from savings to checking account.

-- Start transaction
START TRANSACTION;

-- Debit from first account
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- If previous operation succeeded, credit to second account
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- Check that both operations succeeded and no errors
-- IF error THEN
--   ROLLBACK; -- Rollback all changes
-- ELSE
--   COMMIT; -- Commit all changes
-- END IF;

-- Assuming success, commit
COMMIT; -- Confirm transaction
-- ROLLBACK; -- Rollback in case of error