Thursday, August 5, 2021

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 tutorial example in sqlDatabase 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

12 comments :

Vikram said...

database transaction management is a tricky task and it can create subtle issue including locking of database if not commited for long time.Can you also explain how to manage transaction in Oracle ?

Sandeep Kumar said...

Very well explained. I've compiled list of some popular JDBC questions for Java Developers. Read these interview questions.

JDBC Interview Questions & Answers for Java Developers – Part 1
JDBC Interview Questions & Answers for Java Developers – Part 2

crazy4db said...

while I appreciate this post there may be some more details required by the curious related to locking. The concurrency is implemented through locking and is an equally important as transactions. You may get some exposure here : http://crazy4db.blogspot.in/2012/03/transaction-locks.html

Jessica said...

This is a beginner question:
Why is there no IF clause on the Commit and Rollback?
The example implies that it is built in and will not execute rollback unless there are errors.
I just want to double check that assumption is correct! Thanks

Unknown said...

great information .... but there are drawbacks by using transaction is just we make our queries slower.
we should know where and how to use transactions.

Erenor said...

@Jad Mattia: He said that:

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

Of course, every situation is different, you will have to analyse your case, your users and your needs, but in general, if transactions "should" be used, a proper hardware "should" be bought to support them :)

Anonymous said...

your account balance is 1000$ and you make a withdrawal request of 900$. At fourth step your balance is updated to 900$ .The balance would be 100$ at this step isn't?

Anonymous said...

i have one small doubt in sql transaction..

im trying to execute insert query in looping to the database table..at the same time am also trying to feching some data from same table....

in the first looping iteration it is executing.. but second iteration it is not executing.....


any suggestion ......?

Anonymous said...

One example of transaction is transffering 100$ from account X to account Y. A transaction must has ACID properties :

Atomic : entire transaction either succeeds or fail. It's not allowed that part of transaction succeeds and other part fails, imaging that money is deducted from account X but never reached account Y, will that be acceptable?

Consistent : System e.g. database must be consistent afterwards regardless of whether transaction succeeds of failed. Which means account ballance of account X and Y must be like it was before if transaction failed otherwise money must be deduced from X and must be credited on Y.

Isolation : Any query outside the transaction must not see system on intermediate state. For example, until transaction commit, account X must not show money deducted and Y must not show money credited.

Durable : transaction must be persisted on permanent storage, or checkpoints must be created to ensure that account should have correct balance if power goes off and comes back.

Anonymous said...

how can I execute a mysql procedure with an embeded transaction using php:
CREATE PROCEDURE sp_addnewdetails(IN in_haeding varchar(255))
MODIFIES SQL DATA
BEGIN
INSERT INTO heading(head,title) VALUES(in_PLANrids,in_heading);

SELECT 'success ';

COMMIT;
END

javin paul said...

@Anonymous, Sorry but don't know much about you can do it in PHP. May be posting this question in PHP and MySQL forum will help.

Unknown said...

I need about normalization

Post a Comment