SQL Transactions (BEGIN, COMMIT, ROLLBACK)

A transaction is a sequence of SQL operations treated as a single unit. Transactions ensure data integrity — either all operations succeed, or none do.

🔹 Key Concepts

• Transactions maintain ACID properties: Atomicity, Consistency, Isolation, Durability.
• Use transactions to group related changes so your database stays consistent.

🔹 Basic Commands

BEGIN;             -- Start a transaction
-- Your SQL operations here
COMMIT;            -- Save all changes permanently
ROLLBACK;          -- Undo all changes since BEGIN

🔹 Example

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

If any step fails, use ROLLBACK; to undo changes and keep data consistent.

🔹 Use Cases

• Transferring money between bank accounts
• Batch inserts/updates that must succeed together
• Complex business logic requiring multiple updates

🔹 Important Notes

• If a transaction isn’t explicitly committed, it may auto-rollback depending on DBMS.
• Long transactions can block resources — keep them short.
• Use SAVEPOINT to create intermediate rollback points within a transaction (supported in many DBMS).

🧠 Quick Recap

💡 Transactions protect your data from partial updates and keep your database reliable.