What is transaction in 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
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
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.
Posts
0 comments:
Post a Comment