week 10
10.1 Transactions
A transaction is a logical unit of work that consists of one or more database operations (read, write) that must be executed as a single, indivisible unit. Either ALL operations in the transaction complete successfully, or NONE of them take effect.
|
Transaction Example — Bank Transfer: Amina transfers ₦10,000 from her Savings to her sister Ngozi's account: Step 1: Debit Amina's Savings: UPDATE ACCOUNT SET Balance = Balance - 10000 WHERE AccountNo='Amina'; Step 2: Credit Ngozi's account: UPDATE ACCOUNT SET Balance = Balance + 10000 WHERE AccountNo='Ngozi'; BOTH steps must succeed or NEITHER should execute. If the system crashes between step 1 and step 2, Amina loses ₦10,000 without Ngozi receiving it — this is catastrophic without proper transaction management. |
SQL TRANSACTION SYNTAX:
START TRANSACTION;
UPDATE ACCOUNT SET Balance = Balance - 10000 WHERE AccountNo = 'Amina_001';
UPDATE ACCOUNT SET Balance = Balance + 10000 WHERE AccountNo = 'Ngozi_002';
COMMIT; -- Make changes permanent
-- OR: ROLLBACK; -- Undo all changes if an error occurs