SQL transactions are a fundamental concept in database management systems, designed to ensure data integrity and consistency when executing multiple operations that are interdependent. A transaction in SQL is a sequence of one or more SQL operations that are treated as a single unit of work. These transactions follow the ACID properties to maintain reliability in database systems.

### ACID Properties

1. **Atomicity**: This ensures that all operations within a transaction are completed successfully; otherwise, the transaction is aborted. In other words, a transaction is “all or nothing”. For example, in a financial transfer, moving money from one account to another should either fully complete or not happen at all.

2. **Consistency**: A transaction must transition the database from one valid state to another, maintaining the database invariants. For instance, in a bank transfer, after transferring $100 from Account A to Account B, the total amount across both accounts must remain unchanged.

3. **Isolation**: This property ensures that transactions are executed in isolation from each other. Intermediate states are invisible to other transactions to avoid inconsistencies. For instance, if two transactions are simultaneously transferring amounts involving the same accounts, isolation ensures they do not interfere with each other.

4. **Durability**: Once a transaction has been committed, it is permanently recorded in the database. This means that even if the system crashes immediately after the transaction is completed, the data remains intact.

### Transaction Commands

– **BEGIN TRANSACTION**: This command signals the start of a new transaction.
– **COMMIT**: This command saves all of the operations completed in a transaction, making them permanent in the database. For example, after transferring funds in a financial system, a `COMMIT` is issued to finalize the transaction.

– **ROLLBACK**: This command undoes all operations within the transaction, reverting the database to its previous state. In the case of a failure (like insufficient funds), you would use `ROLLBACK` to cancel the transaction.

– **SAVEPOINT**: This allows setting a specific point within a transaction to which you can later `ROLLBACK`. This is useful for complex transactions that may require partial undo operations. For a financial application, you might set a `SAVEPOINT` after debiting from an account but before crediting to another, in case the second operation fails.

### Isolation Levels

To control the visibility of changes made in concurrent transactions, SQL defines several isolation levels:

1. **Read Uncommitted**: Allows transactions to see uncommitted changes made by other transactions. This level is fast but prone to issues like dirty reads.

2. **Read Committed**: Ensures that any data read is committed at the moment it is read. This prevents dirty reads but not non-repeatable reads or phantom reads.

3. **Repeatable Read**: Guarantees that if a transaction reads a row, it will see the same data in subsequent reads within the transaction, preventing non-repeatable reads but not phantom reads.

4. **Serializable**: The highest isolation level, ensuring complete isolation by making transactions appear as if they were executed sequentially. This prevents dirty reads, non-repeatable reads, and phantom reads.

### Real-World Example: Financial Transfer

Consider a scenario where User A is transferring $500 to User B. The steps for this transaction might be:

1. **BEGIN TRANSACTION**

2. **Check Balance**: Ensure User A’s account has at least $500.
– If not, `ROLLBACK`.

3. **Debit Account**: Subtract $500 from User A.
– Set a `SAVEPOINT` here to rollback to, if crediting fails.

4. **Credit Account**: Add $500 to User B.

5. **COMMIT**: Finalize the transaction, confirming both debit and credit.

This ensures that the $500 is safely moved from User A to User B, adhering to the ACID properties, and the system remains consistent and reliable.