In SQLite, a transaction is a group of T-SQL commands that execute as a unique T-SQL command. If an error occurs while performing these SQLite commands, the transaction will be rolled back in its entirety. In general, SQLite is in auto-commit mode, which means it automatically creates a transaction with each command, processes it, and commits the changes to the database.
In this article, we will demonstrate how to implement the SQLite transaction to assure the data’s integrity and reliability.”
Properties of SQLite Transaction
SQLite is a transactional database, which means that all updates and searches are atomic, consistent, isolated, and long-lasting (ACID). The four standard features stated below, which are commonly abbreviated as ACID, are present in transactions.
Atomicity: A single step of the process is required to be completed by an atomic transaction. It denotes that a modification cannot be split down into smaller components. Either the complete transaction is executed or not when a transaction is in COMMIT mode.
Consistency: Transactions must guarantee that the database is changed from one legitimate state to the next. The database gets inconsistent when a transaction initiates and executes a command to update data. However, the database must remain consistent when COMMIT and ROLLBACK are performed on the transactions.
Isolation: The session’s pending transaction must be segregated from other sessions. When a session initiates a transaction and uses the INSERT or UPDATE command to modify data, the modifications are only accessible to the current operation and not to others. The changes made by other users after the transaction started, on the other hand, should never be apparent to the current user.
Durability: If a transaction is properly committed, the changes in the database must be permanent in the event of a power outage or a program crash. The update should not persist if the program terminates first before the transaction is committed.
How to Perform Transactions in SQLite?
Suppose we wish to regulate such transactions to retain data consistency and handle database failures. We can stop auto-commit mode and initiate the transactions explicitly based on our needs by using the following instructions.
- BEGIN: This is the point where the transaction is started.
- COMMIT: With this terminology in SQLite, we will commit the transaction, which means it will save all the modifications to the database.
- ROLLBACK: The transaction as a whole will be reversed.
Note that only the DML operations INSERT, UPDATE, and DELETE use transactional control instructions. They can’t be used for extracting tables since the database promptly commits these operations.
Creating SQLite Tables for Performing Transactions
To perform transactions, first, we have to create a table. In the figure, you can notice that we have created a table with the name “person_accounts.” The table is specified with the four columns as person_id, person_name, account_number, and the account_balance with their data types.
person_id INT PRIMARY KEY ,
person_name CHAR(10) ,
account_number INT ,
account_balance FLOAT
);
Here, we have a record inserted with the SQLite insert command.
INSERT INTO person_accounts VALUES (2, 'Muneeb', 673201984, 8000 );
INSERT INTO person_accounts VALUES (3, 'Wahaj',112603502, 4000);
INSERT INTO person_accounts VALUES (4, 'Maya', 501738449, 7500);
The table is viewable in the table format as follows:
Example 1: Perform Transaction With BEGIN Command in SQLite
BEGIN TRANSACTION, or the BEGIN command, can be used to start transactions. However, if the database is terminated or an error occurs, a transaction will ROLLBACK.
Initially, we have included a BEGIN terminology with the TRANSACTION term. Then, we have the UPDATE command, which is operating on the account_balance column where person_id is equal to “2” along with the person_id equal to “4”. As we have given a COMMIT instruction, the transaction terminates here.
UPDATE person_accounts
SET account_balance = account_balance - 1000
WHERE person_id = 2;
UPDATE person_accounts
SET account_balance = account_balance + 500
WHERE person_id = 4;
COMMIT;
The translation is performed successfully on the table “person_accounts,” and the “account_balance” field is updated on the specified person_id.
Example 2: Perform Transaction on COMMIT Command in SQLite
The COMMIT command is a transactional command that saves changes to the database triggered by a transaction. The COMMIT command preserves all database transactions since the previous COMMIT or ROLLBACK instruction.
In the following example, we have the first start of the transaction with the term BEGIN TRANSACTION. After this, we have the insert command, which inserts a new record in the table “person_accounts.” We have given a COMMIT command in the end, which terminates the transaction here and saves the change in the given table.
INSERT INTO person_accounts(person_id,person_name,account_number,account_balance)
VALUES(3, 'Wahaj',112603502, 4000);
COMMIT;
The view of the table shows the new record in the following table.
Example 3: Perform Transaction on ROLLBACK Command in SQLite
ROLLBACK is a transactional command that is used to unwind transactions that haven’t been committed to the database yet.
Here, we have performed the DELETE operation on the table “persons_accounts,” where it matched the condition on the given account_number field. After this, we have given ROLLBACK instructions that will also terminate the transaction here but not save the changes we have performed on the table.
DELETE FROM person_accounts WHERE account_number= 112603502;
DELETE FROM person_accounts WHERE account_number= 171636460;
ROLLBACK;
After performing the ROLLBACK operation, the table has the same values and fields existing in the table as before.
Conclusion
After reading this article, we hope you have a clear grasp of SQLite Transactions. We talked about SQLite’s transaction properties and transaction control. Also implemented several SQLite examples describing transaction behavior with the COMMIT and ROLLBACK functionalities. That’s everything there is to know about SQLite transactions, including COMMIT and ROLLBACK transaction information.
