lesson1Title

lesson2Title

lesson3Title

lesson4Title

lesson5Title

lesson6Title

lesson7Title

lesson8Title

lesson9Title

lesson10Title

lesson11Title

lesson12Title

sqlIntroAdvancedChapter3Title

sqlIntroAdvancedChapter1Title

sqlIntroAdvancedChapter2Title

-- Transaction simulation

-- Begin transaction
BEGIN;

-- Subtract 100 coins from Alice
UPDATE accounts
SET balance = balance - 100
WHERE name = 'Alice';

-- Add 100 coins to Bob
UPDATE accounts
SET balance = balance + 100
WHERE name = 'Bob';

-- Commit the transaction
COMMIT;

-- If one of the operations fails, the entire transaction will be rolled back
-- and the database will remain in the same state as before the transaction
-- This is the power of ACID transactions

# ACID Transactions

In SQL databases, a `transaction` is a sequence of operations that should be treated as a single unit.

To maintain data reliability and integrity, every transaction follows four core principles, collectively known as `ACID`.

<br/>

## What does ACID stand for?

`ACID` stands for the four core principles:

### 1. Atomicity
- All operations in a transaction *succeed or fail together*.
- If one part fails, the entire transaction is *rolled back*.

### 2. Consistency
- The database transitions from one *valid state to another*.
- It enforces all data rules and constraints, even if multiple changes happen at once.

### 3. Isolation
- Transactions don't interfere with each other.
- Until a transaction is complete, its intermediate steps are *invisible to others*.

### 4. Durability
- Once a transaction is committed, the changes are *permanent*.
- They won't be lost even if the system crashes immediately afterward.

<br/>

## Example: Transfer money

Imagine users can transfer money to each other.

*accounts*

| user_id | name   | balance |
| ------- | ------ | ------- |
| 1       | Alice  | 500     |
| 2       | Bob    | 300     |

To move 100 coins from Alice to Bob safely, you can use a transaction as follows:

```sql title="ACID-compliant transfer"
BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE name = 'Alice';

UPDATE accounts
SET balance = balance + 100
WHERE name = 'Bob';

COMMIT;
```

* If both updates succeed → changes are saved.
* If either fails → you can roll back with `ROLLBACK`.

<br/>

## Why it matters

Without ACID guarantees, you risk:

* Users losing money during a failed transfer
* Inconsistent or corrupted data
* Race conditions when multiple users interact with the same data

ACID ensures your application behaves safely and predictably.

The 'Durability' property of ACID transactions ensures that once a transaction is committed, the changes are permanent and will not be lost, even if the system crashes immediately afterward. This guarantees that committed data remains safe and reliable, providing a crucial aspect of data integrity in SQL databases.

### In SQL databases, the 'Durability' property of ACID transactions ensures that once a transaction is committed, it can be lost if the system crashes immediately afterward.