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

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

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)

-- 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

ROLLBACK WORK;

-- or

ROLLBACK TRANSACTION;

-- or

ROLLBACK;

---

Example (Invalid bank account transfer)

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