Here we will learn ACID (atomicity, consistency, isolation, and durability) properties in SQLite to maintain database integrity with examples.
Generally in SQLite transaction means it’s a set of T-SQL statements that will execute together as a unit like a single T-SQL statement. If all these T-SQL statements executed successfully without having any errors then the transaction will be committed and all the changes made by the transaction will be saved to the database permanently. In case, if any error occurred while executing these SQLite statements then the complete transaction will be rollbacked.
If we are performing any operation on the table like INSERT, UPDATE or DELETE a record from the table then we will call it as the transaction is happening on the table and these transactions will follow ACID properties to ensure data integrity.
In any DBMS the database transactions must follow ACID (atomicity, consistency, isolation and durability) properties to produce consistent results and must be isolated from other database operations.
The SQLite database transactions also follow ACID properties of DBMS to make sure data transactions consistent throught the database.
ACID stands for Atomic, Consistent, Isolated, and Durable and any transaction happening in DBMS must possess all these qualities.
Atomicity means all the operations of transaction must be finished or rollback the complete transaction in case of any failures.
During the transaction in case of any power failures, crash or some other error the complete transaction will be rollbacked.
An Atomic system must guarantee that atomicity will application in each and every situation including power failures, errors, and crashes.
Consistent means it always keep the data consistent and no database rules will be violated during any transaction on the database.
The consistency ensures that any changes to the values in an instance are consistent with the changes to other values in the same instance.
Isolation means one transaction cannot read the data from another transaction until that is completed. In case if two transactions executing concurrently only respective transaction results will appear to the client, not other clients transaction results.
The main goal of the isolation process is to ensure the correct results for concurrent operations.
Durability means once the transaction committed successfully then it is guaranteed that all the changes committed permanently to the database and recorded to durable medium like hard disk regardless of power failure or other errors.
In case, if a power fails or a program crash occur before the transaction committed then that changes will be rollbacked.
All these ACID (atomicity, consistency, isolation and durability) properties need to be satisfied by all the transactions which will happen in database.