Jinsi ya Kufahamu Transactions na Data Integrity

Wakati unafanya kazi na database (kama MySQL), usalama wa data ni muhimu sana. Unataka kuhakikisha kwamba data yako inabaki sahihi, haipotei, na haivurugwi hata kama kuna makosa wakati wa mchakato wa kuandika (INSERT, UPDATE, DELETE).

Hapo ndipo tunapotumia dhana mbili kuu:

Transactions — kudhibiti mabadiliko kwenye database.

Data Integrity — kuhakikisha data inabaki sahihi na ya kuaminika.

⚙️ 2. Transaction ni Nini?

Transaction ni kundi la queries zinazotekelezwa kama kitengo kimoja (atomic unit).

➡️ Hii inamaanisha:
Ikiwa query moja itashindwa — zote zinarudishwa nyuma (rollback).
Ikiwa zote zikifanikiwa — zinawekwa rasmi (commit).

🔹 Mfano Rahisi wa Transaction

Tuchukulie tunahamisha pesa kati ya akaunti mbili:

Akaunti Kiasi
John 100,000
Peter 50,000

Tunahitaji kupunguza 10,000 kutoka kwa John na kuongeza 10,000 kwa Peter.

START TRANSACTION;

UPDATE accounts SET balance = balance - 10000 WHERE name = 'John';
UPDATE accounts SET balance = balance + 10000 WHERE name = 'Peter';

COMMIT;


💡 Maelezo:

START TRANSACTION inaanza mchakato.

COMMIT inahakikisha mabadiliko yote yanahifadhiwa.

Ikiwa kuna kosa, tunatumia ROLLBACK kurudisha data kama ilivyokuwa mwanzo.

💥 3. Rollback — Kurudisha Mabadiliko

Ikiwa query moja itashindwa, unaweza kurudisha mabadiliko yote:

START TRANSACTION;

UPDATE accounts SET balance = balance - 10000 WHERE name = 'John';
UPDATE accounts SET balance = balance + 10000 WHERE name = 'Peter';

-- Oh! Kuna kosa hapa
ROLLBACK;


Sasa data zote zinabaki kama zilivyokuwa kabla ya transaction.

🧩 4. PHP Example – Transaction katika PDO

Kwenye mfumo wa PHP unaotumia PDO (object-oriented), unaweza kutumia transactions kama hivi:

<?php
try {
$conn = new PDO("mysql:host=localhost;dbname=bank", "root", "");
$conn->beginTransaction();

$conn->exec("UPDATE accounts SET balance = balance - 10000 WHERE name = 'John'");
$conn->exec("UPDATE accounts SET balance = balance + 10000 WHERE name = 'Peter'");

$conn->commit();
echo "Transaction successful!";
} catch (Exception $e) {
$conn->rollback();
echo "Transaction failed: " . $e->getMessage();
}
?>


✅ Faida:

Data inabaki salama.

Ikiwa query moja itashindwa, hakuna data itakayoharibika.

🧠 5. ACID Properties – Msingi wa Data Integrity

ACID ni kanuni nne muhimu za kuhakikisha data integrity ndani ya transaction.

Kanuni Maelezo
A – Atomicity Mabadiliko yote lazima yafanyike yote au yote yashindikane (no half-updates).
C – Consistency Data lazima ibaki sahihi kulingana na sheria za database.
I – Isolation Transactions mbili haziingiliani (zinafanyika kwa kujitegemea).
D – Durability Data iliyohifadhiwa inabaki salama hata kama server itazimwa.

💡 Mfano:
Ikiwa unafanya transaction ya kulipa bili, Atomicity inahakikisha pesa hazipotei katikati ya mchakato.

🧩 6. Data Integrity ni Nini?

Data Integrity ni uwezo wa database kuhifadhi data sahihi, thabiti, na inayotegemewa.

🔹 Aina za Data Integrity
Aina Maelezo
Entity Integrity Kila rekodi lazima iwe na kitambulisho cha kipekee (PRIMARY KEY).
Referential Integrity Uhusiano kati ya tables lazima uwe sahihi (FOREIGN KEY).
Domain Integrity Thamani lazima ziwe sahihi kwa aina ya data (mfano: umri hauwezi kuwa “maneno”).
User-Defined Integrity Sheria maalum zilizowekwa na mtumiaji (mfano: balance haiwezi kuwa hasi).
🔹 Mfano wa Referential Integrity
CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(100)
);

CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(id)
);


💡 Maelezo:

department_id kwenye employees lazima ipatikane kwenye departments.

Hii inazuia kuingiza mfanyakazi kwenye idara ambayo haipo.

🧩 7. Kutumia Constraints kwa Data Integrity
✅ Primary Key
ALTER TABLE students ADD PRIMARY KEY (id);

✅ Unique Key
ALTER TABLE users ADD UNIQUE (email);

✅ Check Constraint
ALTER TABLE employees ADD CHECK (salary > 0);

✅ Foreign Key
ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(id);

🧩 8. Mchanganyiko wa Transactions na Integrity

Ukiunganisha hizi dhana mbili — Transactions + Data Integrity,
unapata database imara yenye usalama wa hali ya juu.

Mfano: Mfumo wa mauzo au shule:

START TRANSACTION;

INSERT INTO students (name, class) VALUES ('Mary', 'Form Two');
INSERT INTO payments (student_id, amount) VALUES (LAST_INSERT_ID(), 50000);

COMMIT;


Ikiwa INSERT ya pili itashindwa, ROLLBACK itarudisha zote — hakuna mwanafunzi bila malipo sahihi.

🧩 9. Mambo Muhimu ya Kukumbuka

✅ Tumia InnoDB kama engine — ndiyo inayo-support transactions na foreign keys.
✅ Daima tumia transactions unapohusisha zaidi ya query moja.
✅ Tumia constraints kudhibiti usahihi wa data.
✅ Tumia error handling kwenye PHP/MariaDB ili kudhibiti makosa.

✅ Hitimisho

🔸 Transactions = kudhibiti mabadiliko ya data kwa usalama.
🔸 Data Integrity = kuhakikisha data inabaki sahihi na thabiti.
🔸 Zikitumika pamoja, zinajenga mifumo ya database iliyo salama, thabiti, na ya kuaminika.