What are database transactions? with Example

Database transaction is one of the fundamental concepts in database management systems (DBMS). It enables reliability, accuracy and consistency in data operations. In this tutorial, we will explore what database transactions are by understanding its types and properties with a simple example using SQLite and Python code.

What is transaction in DBMS?

When we work with any database, we do perform various database operations like inserts, updates, deletes, or other database manipulations. We perform those operations separately based on our needs and requirements.

When we perform some of those operations together and treat all of the used operations as a complete task or a single task then we call that single task a transaction. This means that either all the actions or operations will happen successfully or none of them happen at all.

It’s like when we buy a pack of chips from a store – we pay for it and get the chips together (inside a packet or box). We don’t get the chips unless we pay for them. Shop keeper will not give you one or two single chips (one or tow operations separately in dbms) for less money.

Transactional database vs Relational database

Transactional databases and relational databases both are part of database management systems (DBMS). But we use them for different purposes.

Relational Database Management System (RDBMS)

We use RDBMS database to store and manage data in a structured way. To do that we use tables with rows and columns. It is mainly used to define relationship between different data elements. SQL (Structured Query Language) is the most commonly used language for RDBMS.

Transactional Database Management System (TDBMS)

This type of database is designed to handle transactions with accuracy. TDBMS ensures that all operations within a transaction are executed as a single unit. If any part of the transaction fails, the entire transaction is rolled back to its initial state.

One thing to note that many modern databases can handle both transactional and relational capabilities. Most popular examples of those databases are Oracle, Microsoft SQL Server, and PostgreSQL.

Database transaction properties

So we understood that in order to execute a transaction successfully, all operations within the set must be performed correctly. Now how can we ensure the accuracy of a transaction? A database must be atomic, consistent, isolated, and durable in order to ensure the correctness of a transaction. These four properties of transaction database are known as ACID in short.

Now let’s understand each of these properties separately.

Atomicity

In most cases, a transaction is made with several SQL statements or operations. The atomic property says that all the operations within a transaction must be treated as a single unit of work which can either succeed or fail as a whole. This is the thing I explained in the above image.

Consistency

Consistency is very important in a database. It helps to make sure that all the rules and constraints are followed properly when we do database operations.

For example, suppose we have a bank database that keeps track of customer accounts. Now if a customer trying to withdraw some money from his bank account. This can be done by a set of database operations or atomic operations.

We must perform this kind of database operation without breaking any rules or constraints. A rule can be like: your account balance should be greater than or equal to the amount you are trying to withdraw or the account balance cannot be negative.

Also Read: Getting Started with Django with Python - Easy Tutorial

If the withdrawal transaction is successful, the database should be in a valid state where the balance in the account is correct and all the rules are followed.

But if something goes wrong and the transaction fails, the database should go back to the way it was before the transaction started (initial state), and undo any changes that were made. This is to make sure that the database is always correct and all the rules are followed correctly.

Isolation

Isolation says that each transaction should be executed in an isolated mode or independently from other transactions. So that the results of one transaction do not affect the results of other transactions.

For example, suppose two users are withdrawing money from their bank accounts at the same time. In this case, the isolation property ensures that the balance of one user’s account is not affected by the other user’s withdrawal transaction.

To achieve isolation, the database management system (DBMS) uses locking mechanisms. Locking mechanisms ensures that each transaction has exclusive access to the data it needs and no other transaction can modify that.

Durability

Durability is an important property of a database transaction. It ensures that once a transaction is committed, its changes are permanent and should survive any type of system failure or crash.

For example, let’s say a person is withdrawing money from his bank account using an ATM. The ATM sends a request to the bank database to deduct the typed amount from the person’s account balance. If the transaction is successful, the bank database system will commit the changes to the account balance. It will update the person’s account balance making the deduction permanent.

Now suppose there is a power outage or system crash at the ATM just after the transaction has been committed. In this case, the durability property ensures that the changes made by the transaction or the deduction from the person’s account balance will survive the system failure or crash.

It means when the system is back online, the bank database should still show the reduced balance in that person’s account (it will not deduct any money from his account).

Types of database transaction

There are several types of database transactions for different requirements. Below are some popular types:

Read-only transactions: This type of transaction only read data from the database and does not make any changes to it.
Write transactions: These transactions can modify data in the database
Distributed transactions: These transactions use multiple databases or systems and require coordination and communication between them to ensure consistency

Transaction database Example with Code

Bank transfers are a common and simple example to understand transactions. I will write the entire code in python by connecting SQLite database, If you want, you can take this example to test this concept of database transaction directly in any database like MySQL, Oracle, etc.

Let’s say we have four customers in our bank database: Sophia, Henry, Maya, and Ishan. We will understand what are database transactions and how it is happening with below two simple scenarios:

Sophia wants to transfer an amount of 20 dollars to Henry
Ishan wants to transfer an amount of 35 dollars to Maya

Now to do this we need to follow below processes:

Check whether current balance is higher than the amount he is trying to send
Decrease the amount from the account who is sending money
Increase the amount to the account who is receiving money

Also Read: Flask vs Django: Which One is Easier for Machine Learning?

I will break the entire code into some steps:

Step1: Create example Database

First step is to create an example database. We are going to use SQLite database and connect and manage it using Python. Let’s assume that current balances of Sophia, Henry, Maya, and Ishan are 100, 50, 270, and 12 dollars respectively. Let’s insert those values to a demo table using below code.

import sqlite3

# Establish a database connection
conn = sqlite3.connect('bank_database.db')

# Create the accounts table if it doesn't exist
conn.execute('''CREATE TABLE IF NOT EXISTS accounts (
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                balance REAL NOT NULL
            )''')

# Insert some sample data into the accounts table
conn.execute("INSERT INTO accounts (name, balance) VALUES ('Sophia', 100.0)")
conn.execute("INSERT INTO accounts (name, balance) VALUES ('Henry', 50.0)")

conn.execute("INSERT INTO accounts (name, balance) VALUES ('Maya', 270.0)")
conn.execute("INSERT INTO accounts (name, balance) VALUES ('Ishan', 12.0)")

# Commit the changes and close the database connection
conn.commit()
conn.close()

In the above code, we are creating a SQLite database named bank_database.db. Inside that bank_database.db database, we are creating a table named accounts. After that, we are inserting demo customer data into that table.

Step2: Check the data

In this step, we will print our demo table (accounts) from the example database bank_database.db. This is just to validate our data. Following code is to do that:

# Establish a database connection
conn = sqlite3.connect('bank_database.db')

# Retrieve all rows from the accounts table
rows = conn.execute('SELECT * FROM accounts').fetchall()

# Print the rows
for row in rows:
    print(row)

# Close the database connection
conn.close()

(1, 'Sophia', 100.0)
(2, 'Henry', 50.0)
(3, 'Maya', 270.0)
(4, 'Ishan', 12.0)

Step3: Perform the first scenario

Now let’s perform the first scenario which is Sophia wants to transfer an amount of 20 dollars to Henry. Let’s do that in the following code:

# Establish a database connection
conn = sqlite3.connect('bank_database.db')

# Start a transaction
conn.execute('BEGIN TRANSACTION')

# Step 1: Retrieve the current balance of Sophia's account
sophia_balance = conn.execute("SELECT balance FROM accounts WHERE name = 'Sophia'").fetchone()[0]

# Step 2: Check if Sophia has sufficient balance to transfer 20 dollars
if sophia_balance >= 20:
    # Step 3: Deduct 20 dollars from Sophia's account
    conn.execute("UPDATE accounts SET balance = balance - 20 WHERE name = 'Sophia'")

    # Step 4: Add 20 dollars to Henry's account
    conn.execute("UPDATE accounts SET balance = balance + 20 WHERE name = 'Henry'")

    # Commit the transaction if all operations are successful
    conn.commit()
    print('Transaction succeeded: 20 dollars transferred from Sophia to Henry')
else:
    # Rollback the transaction if Sophia doesn't have sufficient balance
    conn.rollback()
    print('Transaction failed: Insufficient balance')

# Close the database connection
conn.close()

Transaction succeeded: 20 dollars transferred from Sophia to Henry

In the above code, we are first checking whether Sophia has sufficient balance to transfer 20 dollars before sending it to Henry’s account.

Then if Sophia has sufficient balance, deduct 20 dollars from her account using an UPDATE SQL statement and add 20 dollars to Henry’s account using another UPDATE SQL statement.

Finally, if all operations are successful, commit the transaction using the commit() method of the connection object. This means that the changes made to the database in this transaction are permanently saved to the database.

Also Read: Roadmap to Learn Django for Beginners

Step4: Check database update

Now let’s check or print the database to see whether our database updated successfully or not. Following python code is to do that.

# Establish a database connection
conn = sqlite3.connect('bank_database.db')

# Retrieve all rows from the accounts table
rows = conn.execute('SELECT * FROM accounts').fetchall()

# Print the rows
for row in rows:
    print(row)

# Close the database connection
conn.close()

(1, 'Sophia', 80.0)
(2, 'Henry', 70.0)
(3, 'Maya', 270.0)
(4, 'Ishan', 12.0)

You can see our code worked fine to update or perform the operation correctly. Sophia’s balance reduce and Henry’s balance increased by 20 dollars.

Second scenario

Now let’s perform the second condition where Ishan wants to transfer an amount of 35 dollars to Maya. The following code is to do that:

# Establish a database connection
conn = sqlite3.connect('bank_database.db')

# Start a transaction
conn.execute('BEGIN TRANSACTION')

# Step 1: Retrieve the current balance of Ishan's account
ishan_balance = conn.execute("SELECT balance FROM accounts WHERE name = 'Ishan'").fetchone()[0]

# Step 2: Check if Ishan has sufficient balance to transfer 35 dollars
if ishan_balance >= 35:
    # Step 3: Deduct 35 dollars from Ishan's account
    conn.execute("UPDATE accounts SET balance = balance - 35 WHERE name = 'Ishan'")

    # Step 4: Add 35 dollars to Maya's account
    conn.execute("UPDATE accounts SET balance = balance + 35 WHERE name = 'Maya'")

    # Commit the transaction if all operations are successful
    conn.commit()
    print('Transaction succeeded: 35 dollars transferred from Ishan to Maya')
else:
    # Rollback the transaction if Ishan doesn't have sufficient balance
    conn.rollback()
    print('Transaction failed: Insufficient balance')

# Close the database connection
conn.close()

Transaction failed: Insufficient balance

The transaction is unsuccessful because Ishan does not have sufficient amount (12 dollars) in his account to transfer 35 dollars. So our database operation works fine. Now let’s see whether this failed transaction impacted our database or not.

# Establish a database connection
conn = sqlite3.connect('bank_database.db')

# Retrieve all rows from the accounts table
rows = conn.execute('SELECT * FROM accounts').fetchall()

# Print the rows
for row in rows:
    print(row)

# Close the database connection
conn.close()

(1, 'Sophia', 80.0)
(2, 'Henry', 70.0)
(3, 'Maya', 270.0)
(4, 'Ishan', 12.0)

No, this failed transaction did not impact our main database table. The account balance is same (as before the transaction) for both of them. So our database transaction logic and code is working fine for this example.

Final Thought

In this post we understand what are database transactions, where we can use it, its types and properties with a simple example. I used python code to connect SQLite databse, you can implement this to any other database directly like MySQL, Oracle, etc.

This is it for this tutorial. If you have any questions or suggestions regarding this post, please shoot those in the comment section below.

Anindya

Hi there, I’m Anindya Naskar, Data Science Engineer. I created this website to show you what I believe is the best possible way to get your start in the field of Data Science.