SQLite Transaction

SQLite is a database engine. It is a software that allows users to interact with relational databases. Basically, it is a serverless database which means it does not require any server to process queries. With the help of SQLite, we can develop embedded software without any configurations. SQLite is preferable for small datasets. SQLite is a portable database resource. We have an extension of SQLite in any language to access that database.

Transaction in SQLite

• The transaction is the set of operations that is used to perform some logical set of work.
• Transactions are used to ensure that data is always in a consistent state.
• We use transactions in SQLite to maintain data integrity.
• It provides a way to group a set of related database operations into a single unit.
• Transaction is very important because it follows the ACID properties. We will discuss this in detail with the help of examples.

ACID Properties

ACID Properties in the database play a vital role in maintaining reliability and integrity within transactions.

1. Atomicity

Atomicity is defined as either all operations will execute or none of them will execute. This means either the whole transaction will take place or none of them will execute. Because atomicity prevents our database from storing partial or incomplete data. In this way, it ensures that data is always stored in valid form.

2. Consistency

It is also an ACID property that ensures consistency in our database. It ensures consistency by ensuring data isin a property state before and after the transaction. Let's understand with the help of an example.

Example:

Suppose we have two account A and B. A have 2500 in his account and B have 2500 in his account, we want to credit 500 from account A and debit to account B. Now if before transaction and after transaction the amount of both account is same then we can say that our transaction is consistent state otherwise it is not.

 Account A have before transaction => 2500 rupees
Account B have before transaction> = 2500 rupees
Before transaction sum of both amount is: A + B => 2500 + 2500 => 5000 rupees
After transaction sum of both amount is: A + B => 2000 + 3000 =>5000 rupees

Both Account A and B have same amount of money so our transaction is in the consistent state.

3. Isolation

It is a type of property which allow multiple transaction running at the same time without being into incnsistent state. It allow us to perform multiple operations at the same time without interfare of each others process.

4. Durability

Durability is a type of acid property which ensures to make changes to the database or transaction for those have successfully committed to our database. It is helpful in case of system failure because when the system got crashed due to some reasons it does not affect our database or transactions.

Commands in Transactions

We can handle or manage our transactions to ensure integrity and consistency using some commands to make our transaction more scalable, accurate and efficient.

1. Begin command

Basically this command is used to initiate or start the transaction.

Syntax:

BEGIN Transaction or BEGIN

2. Commit Command

This command is used to saved permanently all changes happen into our database. It Simply reflect the changes occur in the database.

Syntax:

For commit we can also use END command which work same as COMMIT did.

COMMIT Transaction or END Transaction

3. ROLLBACK Command

This command is used to rollback or move back all the changes from our transactions. It erases all the modifictions and changes made from the start of transactions.

Syntax:

ROLLBACK Transaction or ROLLBACK

SQLite Transaction - Examples

We have employees table with employee id, employee name and employee city name.

👁 Emp-transaction

We have a employees table and we want to begin transaction as remove those employee whose city is Meerut. Then we ROLLBACK.

BEGIN TRANSACTION
DELETE FROM Employees WHERE CITY='Meerut'
ROLLBACK

Explanation:

👁 AFTER-ROLLBACK

In this query we firstly remove those employee who are belong to Meerut then we do ROLLBACK. Since We have not commit/save the database or transaction, which means after ROLLBACK it revoke all the changes that we have performed and make database into original manner and change nothing.

👁 AFTER-ROLLBACK

After ROLLBACK, as you can clearly saw that their is no change in database because it ROLLBACK or revoke all the changes.

Let's understand the importance of COMMIT Command

In above example, if we commit the transaction immidiate after the deletion operation. Then we can't perform the ROLLBACK operation because it reflect all the channges and store permanently in our disk.

BEGIN TRANSACTION
DELETE FROM Employees WHERE CITY='Meerut'
COMMIT

👁 COMMIT-Command

Explanation: By using commit command, all the changes are reflect and permanently stored in our disk. We can not ROLLBACK or revoke changes after Commit Operation.

Conclusion

The purpose of transaction is to maintain data integrity and consisteny into database. I have also explained about BEGIN, ROLLBACK and COMMIT commands which are used in to handle and managed transaction. just read, understand and try to apply these command in transactions operations.