Understanding ACID Properties in SQLite with Practical Examples

Using our sample `student` table, we'll demonstrate each of these properties with clear, practical examples.

1. Atomicity

Atomicity ensures that all operations within a transaction are completed successfully; otherwise, the transaction is rolled back.

<?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(); 
}
?>

Explanation: If the second update fails (due to a typo in the table name), the entire transaction is rolled back, preserving the initial state.

2. Consistency

Consistency ensures that a transaction only brings the database from one valid state to another, maintaining data integrity.

<?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();
} 
?>

Explanation: This ensures that updates adhere to the logical consistency rules defined (e.g., marks should not exceed 100).

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(); 
}

Explanation: This will say Integrity constraint violation: 19 UNIQUE constraint failed: student.id, student id 3 is already available.

3. Isolation

Isolation ensures that transactions are executed independently, with intermediate states not visible to other transactions.

<?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();
}
?>

Explanation: While one transaction is updating the student's mark, another transaction checks the mark. Due to isolation, the second transaction sees the original mark until the first transaction is committed.

4. Durability

Durability ensures that once a transaction is committed, the changes are permanent, even in the event of a system failure.

<?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(); 
}
?>

Explanation: Even if the system crashes right after the commit, the new student record remains in the database, ensuring durability.

Conclusion

Understanding and implementing the ACID properties in your SQLite transactions ensures the reliability and integrity of your database operations. These examples with the `student` table illustrate how Atomicity, Consistency, Isolation, and Durability work in practice, helping you write more robust and secure database code.
Download sample script for SQLite with instructions on how to use.

SQLite support Display records from SQLite database table

PHP SQLite PDO Functions

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

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