Database Transaction Tutorial in SQL with Example for Beginners

A database transaction is an important concept to understand while working in databases and SQL. Transaction in the database is required to protect data and keep it consistent when multiple users access the database at the same time.  In this database transaction tutorial we will learn what is a transaction in a database, why do you need transactions in the database, ACID properties of database transactions,s and an example of database transactions along with commit and rollback.   

Almost all vendors like Oracle, MySQL, SQL Server, or Sybase provide transaction facility but MySQL only provides it for certain storage engines like InnoDB and BDB and not for MyISAM.

What is a transaction in the database?

Database transaction is a collection of SQL queries which forms a logical one task. For a transaction to be completed successfully all SQL queries have to run successfully. Database transaction executes either all or none, so for example if your database transaction contains 4 SQL queries and one of them fails then change made by other 3 queries will be rolled back. 

This way your database always remain consistent whether transaction succeeded or failed. The transaction is implemented in the database using SQL keyword transaction, commit, and rollback. 

Commit writes the changes made by transaction into database and rollback removes temporary changes logged in transaction log by database transaction. 

Database Transaction tutorial

Why transaction is required in database

The database is used to store data required by real life application e.g. Banking, Healthcare, Finance etc. All your money stored in banks is stored in the database, all your shares of DMAT account is stored in the database and many application constantly works on these data. 

In order to protect data and keep it consistent, any changes in this data need to be done in a transaction so that even in the case of failure data remain in the previous state before the start of a transaction. 

Consider a Classical example of ATM (Automated Tailor Machine); we all use to withdraw and transfer money by using ATM. If you break withdrawal operation into individual steps you will find:

1) Verify account details.

2) Accept withdrawal request

3) Check balance

4) Update balance

4) Dispense money

Suppose your account balance is 1000$ and you make a withdrawal request of 900$. At fourth step, your balance is updated to 900$ and ATM machine stops working due to power outage.

Once power comes back and you again tried to withdraw money you surprised by seeing your balance just 100$ instead of 1000$. This is not acceptable by any person in the world :) so we need a transaction to perform such task. 

If SQL statements would have been executed inside a transaction in database balance would be either 100$ until money has been dispensed or 1000$ if money has not been dispensed.

ACID Properties of database transaction

There are four important properties of database transactions these are represented by acronym ACID and also called ACID properties or database transaction where:

A stands for Atomicity, Atom is considered to be smallest particle which can not be broken into further pieces.database transaction has to be atomic means either all steps of transaction completes or none of them.

C stands for Consistency, transaction must leave database in consistent state even if it succeed or rollback.

I is for Isolation

Two database transactions happening at same time should not affect each other and has consistent view of database. This is achieved by using isolation levels in database.

D stands for Durability

Data has to be persisted successfully in database once transaction completed successfully and it has to be saved from power outage or other threats. This is achieved by saving data related to transaction in more than one places along with database.

When to use database transaction

Whenever any operation falls under ACID criteria you should use transactions. Many real world scenarios require transaction mostly in banking, finance and trading domain.

How to implement transaction in SQL

Database transaction is implemented in SQL using three keywords start transaction, commit and rollback.once you type start transaction, database starts a transaction and execute all subsequent SQL statements in transaction and keep track of all of them to either commit or rollback changes. Commit keywords saves then changes made by transaction into database and after commit change is normally visible to other transaction though is subject to isolation level. In case you encountered any error while executing individual sql statements inside database transaction, you can rollback all your changes by executing "rollback" command.

Database Transaction Example

To understand database transaction better let's see a real life example of transaction in database. For this example we will assume we have an Account table which represent a Bank Account and we will transfer money from one account to another account

Request: transfer 900$ from Account 9001 to 9002

start transaction

select balance from Account where Account_Number='9001';

select balance from Account where Account_Number='9002';

update Account set balance=balance-900 here Account_Number='9001' ;

update Account set balance=balance+900 here Account_Number='9002' ;

commit; //if all sql queries succed

rollback; //if any of Sql queries failed or error

Database transaction in MySQL

In my previous mysql command tutorials I have talked aobut different databse storage engines available in mysql e.g. myISAM or InnoDB. Not all mysql engines supports transaction in order to make transaction works in mysql you either need to use InnoDB or BDB Engine. You can specify engige while creating table in mysql or you can also change your engine in mysql by using ALTER keyword. For example "ALTER TABLE tablename TYPE=InnoDB;

Important point about database transaction

1. Database transaction is nothing but a set of SQL statement.

2. Transaction in database is either all or none means either all SQL statement success or none.

3. Its good practice to execute sql query inside transaction and commit or rollback based on result but you need to be little careful with transaction log. To faciliate rollback and commit every sql query which executed inside database transaction is written into transaction log and size of transaction log can grow significantly if don't commit or rollback for longtime.

4. Effect of two simultaneous database transaction into data is controlled by using Isolation level. Isolation level is used to separate one database transaction with other and currently there are four database isolation levels:

1) Read Uncommitted

This is the lowest level of database isolation level in this one database transaction can see changes made by other database transaction which is not yet committed. This can allow you dirty read so quite dangerous.

2) Read Committed

This is slightly better where one database transaction only sees committed changes by other database transaction. But this is also not safe and can lead you to non-repeatable reads problem.

3) Repeatable Reads

4) Serializable

The highest level of database isolation level. In this, all database transactions are totally isolated with other database transaction.though this is safe but this safety can cause a significant performance hit.

5. MyISAM storage engine in MySQL doesn't support transaction. In order to make transaction works in MySQL use InnoDB.

6. Databse transaction should follow ACID properties.

That’s all for now on database transaction tutorial, I will add more useful points about transaction in databse as I come across or recall, you can also provide your input and issues face during transaction in database on different RDBMS e.g. Oracle, MySQL, MSSQL Server or Sybase etc.

Other Database Tutorials

Difference between truncate and delete in SQL

Basic and Advanced SELECT command example

How to use MySQLdump utility for backing up data

MySQL command tutorials part 2

MySQL command tutorials part 1