# 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;
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://notes.nomanaziz.me/development/database/sql/basics/10.-transactions.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.