ACID principles: Atomicity, Consistency, Isolation, and Durability - in SQLite

1. Atomicity

<?Php
try { 
$my_conn->beginTransaction(); // Update a student's mark 
$my_conn->exec("UPDATE student SET mark = 80 WHERE id = 1");
// Intentional error to trigger rollback 
$my_conn->exec("UPDATE students SET mark = 90 WHERE id = 2"); 
$my_conn->commit();
} catch (Exception $e) { 
  $my_conn->rollBack();
  echo "Failed: " . $e->getMessage(); 
}
?> 

2. Consistency

<?Php
try { 
$my_conn->beginTransaction(); // Check if a student's mark update maintains grade consistency 
$newMark = 95; 
if ($newMark <= 100) { $my_conn->exec("UPDATE student SET mark = $newMark WHERE id = 2"); }
else { throw new Exception("Invalid mark"); }
$my_conn->commit(); 
} catch (Exception $e) { 
 $my_conn->rollBack();
 echo "Failed: " . $e->getMessage();
} 
?> 

try { 
$my_conn->beginTransaction(); // Insert a new student record
// id 3 is alredy there , Integrity constraint violation 
$my_conn->exec("INSERT INTO student (id,name, class, mark, gender) 
	VALUES (3,'Jane Doe', 'Six', 90, 'female')"); 
$my_conn->commit(); 
} catch (Exception $e) { 
 $my_conn->rollBack(); 
 echo "Failed: " . $e->getMessage(); 
}

3. Isolation

<?Php 
try { 
$my_conn1 = new PDO('sqlite:my_student.db'); // Use correct path here, check config.php 
$my_conn2 = new PDO('sqlite:my_student.db'); 
// Transaction 1: Update student's mark 
$my_conn1->beginTransaction(); 
$my_conn1->exec("UPDATE student SET mark = 50 WHERE id = 3"); 
sleep(5); // Simulate delay before committing the transaction 
// Transaction 2: Read student's mark during Transaction 1 
$my_conn2->beginTransaction(); 
$mark = $my_conn2->query("SELECT mark FROM student WHERE id = 3")->fetchColumn(); 
echo "Mark in Transaction 2: $mark\n"; // Should return the original mark, not the updated one 
$my_conn2->commit(); 
$my_conn1->commit(); // Transaction 1 commits after Transaction 2 
} catch (Exception $e) { 
$my_conn1->rollBack(); 
$my_conn2->rollBack(); 
echo "Failed: " . $e->getMessage();
}
?> 

4. Durability

<?Php 
try { 
$my_conn->beginTransaction(); // Insert a new student record 
$my_conn->exec("INSERT INTO student (name, class, mark, gender) VALUES ('Jane Doe', 'Six', 90, 'female')"); 
$my_conn->commit(); // Simulate a crash (uncomment the next line to simulate) 
// exit(); 
} catch (Exception $e) { 
 $my_conn->rollBack(); 
 echo "Failed: " . $e->getMessage(); 
}
?> 

Conclusion