DbSchema | How to Perform Transactions in SQLite?

Sqlite • 10-Jun-2023

by Malik Umair Sajjad

SQLite alt >

Introduction
Prerequisites
Understanding Transactions
Properties of Transaction
Transaction Control
Performing Transactions in sqlite3
Performing Transactions in DbSchema
Conclusion
References

Introduction

SQLite is a widely used relational database management system that provides robust data storage solutions in a serverless, zero-configuration environment. This article focuses on one crucial aspect of SQLite - transactions, and will explain how to handle transactions in both sqlite3 and DbSchema.

Prerequisites

Basic knowledge of SQL.
SQLite installed on your machine.
Familiarity with sqlite3 command line utility and DbSchema.

For installation and establishing connection you can read our article SQLite-How to create a database?

Understanding Transactions

A transaction is a single logical unit of work that accesses and possibly modifies the contents of a database. Transactions encapsulate a set of related changes such that either all occur, or none occur. Transactional integrity is crucial in a multi-access database environment.

Advantages of Using a Transaction

Atomicity: Transactions ensure that all the tasks within it are executed or none are.
Consistency: Transactions make sure the database moves from one consistent state to another.
Isolation: Multiple transactions can occur concurrently without leading to inconsistencies.
Durability: Once a transaction has been committed, its effects are permanently in place in the database.

Limitations of Using a Transaction

Transactions can lead to system resources being held unnecessarily long, leading to reduced system throughput.
Poorly designed transactional logic can lead to deadlocks.
Transactions require additional system resources to manage them, which can impact performance.

Properties of Transaction

*Property*	*Explanation*
Atomicity	Ensures the transaction is treated as a single, indivisible logical unit of work.
Consistency	Ensures a transaction brings the database from one valid state to another.
Isolation	Ensures the concurrent execution of transactions results in a system state equivalent to a sequential execution.
Durability	Ensures the effect of committed transactions are permanent and persist even after a system failure.

Transaction Control

*Command*	_Explanation_
BEGIN TRANSACTION	Marks the start of a transaction.
COMMIT	Marks the end of a successful transaction, making all changes permanent.
ROLLBACK	Reverts the database state back to the last COMMIT or BEGIN TRANSACTION, undoing all changes.

Performing Transactions in sqlite3

Let’s consider an example where we’re making a simple bank transfer between two accounts.

Open the SQLite command line interface:

__shell sqlite3 Bank.db __

To know more about creating a database you can read our article SQLite-How to create a database?

Begin the transaction:

__sql BEGIN TRANSACTION; __

Sample Database:

ID	*Name*	*Balance*
1	Alice	500
2	Bob	300

Execute the transfer:

__sql UPDATE Accounts SET balance = balance - 100 WHERE name = 'Alice'; UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob'; __

Results From Query:

Now, if you query the Accounts table, you should see the updated balances:

ID	*Name*	_Balance_
1	Alice	400
2	Bob	400

As you can see, the transaction has been executed successfully and atomically, transferring funds from Alice’s account to Bob’s account while maintaining the consistency and integrity of the data.

Commit the transaction:

__sql COMMIT; __

The above queries execute atomically and in isolation. If an error occurs during the transaction (e.g., Bob’s account does not exist), you can roll back the transaction using the ROLLBACK command.

Performing Transactions in DbSchema

DbSchema is a visual database design & management tool that can also be used to handle transactions.

Start DbSchema: Open DbSchema on your computer. Make sure that SQLite is installed and correctly configured on your machine.
Connect to your SQLite database: DbSchema connects to databases using JDBC drivers. So, to connect to your SQLite database, click on Connect -> Connect to Database. Select SQLite from the list of databases, browse to the location of your SQLite database file, and click Connect.
Open SQL Editor: Once connected, go to the SQL Editor tab at the top of the DbSchema interface. This will open a new SQL Editor window where you can write and execute SQL queries.
Begin the transaction: Now we can begin our transaction. In the SQL Editor, write and execute the following command:
BEGIN TRANSACTION;
You can execute this command by clicking on the Run button or by pressing F5.
Perform operations: Let’s say you want to transfer 100 units from Alice's account to Bob's account, like in the previous sqlite3 example. The accounts are in a table named Accounts. Run the following commands:
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Alice'; UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob';
You can execute these commands by clicking on the Run button or by pressing F5.
Commit the transaction: If everything has been executed without errors, you can commit the transaction. This makes all changes to the database permanent. Execute the following command:
COMMIT;
Again, use the Run button or F5 to execute the command.
Verify the result: To verify that the transaction was successful, you can retrieve the data from the Accounts table and check the balances. Execute the following command:
SELECT * FROM Accounts;
The balances of Alice and Bob should now reflect the transfer.
Rollback if necessary: If an error occurred during the transaction, and you did not commit the changes, you can roll back the transaction, which will return the database to its state at the beginning of the transaction. Execute the following command:
ROLLBACK;
Close the connection: Once you’re done, remember to close the connection to the database. You can do this by clicking on Connect -> Disconnect.

Visually Manage SQLite using DbSchema

DbSchema is a SQLite client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.

Key Features of DbSchema:

Following are the key features of DbSchema which distinguish it from other database GUI tools.

Conclusion

Transactions are an essential feature of SQLite and relational databases in general, ensuring data integrity and consistency. While transactions do require careful handling to avoid potential deadlocks and system resource issues, their benefits are indispensable in a concurrent environment. Both sqlite3 and DbSchema provide straightforward ways to perform transactions.