> For the complete documentation index, see [llms.txt](https://notes.nomanaziz.me/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://notes.nomanaziz.me/development/database/sql/basics/10.-transactions.md).

# 10. Transactions

### ACID

* DB transaction is a single unit of work that consists of one or more operations
* Example of a transaction is a bank transfer from one account to another. A complete transaction must ensure a balance between the sender and receiver accounts. If sender account transfers X amount, then receiver receives X amount, no more or less
* ACID properties mean
  * **Atomicity**
    * it guarentees that the transaction completes in all or nothing manner
  * **Consistency**
    * It ensures that change to data written to db must be valid and follow predefined rules
  * **Isolation**
    * It determines how transaction integrity is visible to other transactions
  * **Durability**
    * It makes sure that transactions that have been committed will be stored in db permenantly

***

### Begin a transaction

* When we insert a row in table using normal syntax, we dont know when transaction begins and cannot intercept the modiciation such as rolling it back
* So start a transaction we can use

```sql
BEGIN TRANSACTION;

-- or

BEGIN WORK;

-- or

BEGIN;
```

* Using this syntax, if we apply a query, it will only be visible for us during our session since it is not commited and it will be lost once our session in ended

### Commit a transaction

* To make the change become visible to other sessions (or users) you need to commit the transaction by using

```sql
COMMIT WORK;

-- or

COMMIT TRANSACTION;

-- or

COMMIT;
```

* So now if we start a transaction and commit it, it will be saved and viewable in other sessions

***

### Example (Bank account transfer)

```sql
-- start a transaction
BEGIN;

-- deduct 1000 from account 1
UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;

-- add 1000 to account 2
UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;

-- select the data from accounts
SELECT id, name, balance
FROM accounts;

-- commit the transaction
COMMIT;
```

***

### Rolling back a transaction

* To roll back or undo the change of the current transaction, you use

```sql
ROLLBACK WORK;

-- or

ROLLBACK TRANSACTION;

-- or

ROLLBACK;
```

***

### Example (Invalid bank account transfer)

```sql
-- begin the transaction
BEGIN;

-- deduct the amount from the account 1
UPDATE accounts
SET balance = balance - 1500
WHERE id = 1;

-- add the amount from the account 3 (instead of 2)
UPDATE accounts
SET balance = balance + 1500
WHERE id = 3;

-- roll back the transaction
ROLLBACK;
```