Database Transaction Tutorial in SQL with Example for Beginners
Database transaction is an important concept to understand while working in database and SQL. Transaction in database is required to protect data and keep it consistent when multiple users access the database at same time. In this database transaction tutorial we will learn what is transaction in database, why do you need transaction in database, ACID properties of database transaction and an example of database transaction along with commit and rollback. Almost all vendors like Oracle, MySQL, SQL Server or Sybase provide transaction facility though MySQL only provide it for certain storage engine like InnoDB and BDB and not for MyISAM.
What is transaction in database?
Database transaction is collection of SQL queries which forms a logical one task. For transaction to be completed successfully all SQL queries has 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. Transaction is implemented in 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
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 database, all your shares of DMAT account is stored in database and many application constantly work on these data. In order to protect data and keep it consistent any changes in this data needs to be done in transaction so that even in case of failure data remain in previous state before start of 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
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 transaction to perform such task. If SQL statements would have been executed inside 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.
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.
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
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 simulteneous 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 databse isolation levels:
This is lowest level of databse isolation level in this one database transaction can see changes made by other databse transaction which is not yet commited. This can allow you dirty read so quite dangerous.
This is sligltly better where one database transaction only sees commited changes by other database transaction. But this is also not safe and can lead you to non-repeatable reads problem.
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 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.
Database transaction is collection of SQL queries which forms a logical one task. For transaction to be completed successfully all SQL queries has 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. Transaction is implemented in 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.
No comments:
Post a Comment