DbSchema Database Designer

DbSchema | How to Perform Transactions in SQLite?

Publish on DbSchema Blog >>>

SQLite alt >

Table of Contents

  1. Introduction
  2. Prerequisites
  3. Understanding Transactions
  4. Properties of Transaction
  5. Transaction Control
  6. Performing Transactions in sqlite3
  7. Performing Transactions in DbSchema
  8. Conclusion
  9. References


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`.


  • 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

  1. Atomicity: Transactions ensure that all the __tasks` within it are executed or none are.
  2. Consistency: Transactions make sure the database moves from one __consistent state` to another.
  3. Isolation: Multiple transactions can occur concurrently without leading to __inconsistencies`.
  4. Durability: Once a transaction has been __committed, its __effects are permanently in place in the database.

Limitations of Using a Transaction

  1. Transactions can lead to __system resources being held unnecessarily long, leading to __reduced system throughput.
  2. Poorly designed transactional logic can lead to __deadlocks`.
  3. __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.

  1. 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?

  1. Begin the transaction:


Sample Database:

ID Name Balance
__1` Alice 500
__2` Bob 300
  1. 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.

  1. 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.

  1. Start DbSchema: Open __DbSchema on your computer. Make sure that SQLite is installed and correctly __configured on your machine.

  2. 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.

  3. 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.

  4. Begin the transaction: Now we can __beginour transaction. In theSQL Editor`, write and execute the following command:


    You can execute this command by clicking on the Run button or by pressing F5.

  5. 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.

  6. 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:


    Again, use the Run button or F5 to execute the command.

  7. 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.

  8. 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:

  9. 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.


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.


  1. SQLite Transaction
  2. DbSchema
  3. ACID (Atomicity, Consistency, Isolation, Durability)
  4. SQLite Official Documentation

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams, by double-clicking them. Changes will be saved to the design model and, if DbSchema is connected to the database also into the database. More.

Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected. Choose 'Edit Manually' into the JDBC URL combo to enter a custom URL. More.

Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys. Double-click cells to edit the data. More.

Query Builder alt >

Query Builder

Create SQL Queries featuring JOINS, GROUP BY, ORDER BY just using the mouse. More.

SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries. The editor is autocompletion-enabled. More.

Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model. More.

Dark Theme alt >

Dark Theme

Configurable styles & dark theme. More.

Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.

DbSchema can be downloaded for free. No registration is required.