First MySQL version to support transaction is 3.23.0. The transaction support to MySQL is done using new storage engines. The storage engines which support transactions were named as InnoDB and Berkeley DB. New storage engines were added in the MySQL to improve the compatibility of transaction. Improving the transaction compatibility will result in smooth transactions in the newer versions of MySQL.

Transactions in a database are defined as an atomic unit of database operations which act against the data present in the database. All SQL statements which are indulged in database operations are either committed or rolled back.

• Read: Tuning SQL queries

Operations within database transactions are atomic. Atomic operations will result in either success or failure. The binary property of atomic operations is known as “all or nothing” rule of database transactions. The consistency of database transactions is ensured by the consistency property of the transactions in SQL. Half finished transactions are not allowed in database transactions.

Isolation can be defined as the process of isolating the data which undergoes transactions from other operations. The transaction data will end up as inconsistent without isolation process. The durability property of database transaction will ensure that the database recover the updates of transaction in the event system failure.

 

• How to Prevent SQL Injection?

 

Isolation Levels of Database Transaction

Isolated transactions may lead to a deadlock in highly concurrent environments. Deadlock situations can be defined as the process of transactions competing over resources and preventing a transaction from accessing other resources. Deadlock is a tradeoff between isolation and performance of the database.

There are four levels of transaction isolation present in MySQL

• Serializable
• Repeatable read
• Read committed
• Read uncommitted

Serializable

Isolated transactions present in serializable level occur in isolated fashion. All serialized transactions are executed one after another.

Repeatable Read

Isolated transaction present in repeatable read level cannot read the modified data but other transactions are allowed to read the modified data. Modification of data by other transactions is not allowed until the end of current transaction.

Read Committed

Isolated transaction present in read commit level is not allowed to read the data modified by other transactions. This process prevents the transaction from reading the dirty data.

Read Uncommitted

Isolated transaction present in read uncommitted level can read the rows of modified data. The rows of modified data transaction which is yet to be completed can also read using read uncommitted isolated transaction.

 

• Difference Between Primary, Foreign and Unique Keys

 

COMMIT and ROLLBACK

Every MySQL transaction use two important keywords namely COMMIT and ROLLBACK

COMMIT

COMMIT keyword is used after completing a successful database transaction. This keyword is used to save all the changes made in a database table.

ROLLBACK

This command keyword is used in the event failure in the database transaction. By utilizing the ROLLBACK statement all database tables are referenced to the state before transaction.

All behaviours of database transaction are controlled by using the command keyword AUTOCOMMIT. In default, AUTOCOMMIT for a SQL statement is set to 1 by default; transactions which are not completed are set to 0 by default. SET AUTOCOMMIT = 0 command can be used to declare the AUTOCOMMIT of the transaction as 0. The SQL commands can be executed by using the mysql_query() function.

MySQL Transaction Steps

• Transaction is started using the statement START TRANSACTION.
• Acquire the data used for the transaction.
• Insert the transacted data inside the table
• Save the changes using the COMMIT statement
• Acquire the transacted data from the table to cross check the transaction.

Example for Database Transaction

Let us consider that we have a table named students and we need to transact a new student details into our students table. The SQL statement given below can be used to transact the student details into our students table.

start transaction;

select @student_id ;= max(student_id)

from students;

set @student_id = @student_id + 1;

insert into students ( student_id

student_name

joining_date

roll_no

class_details

)

Values ( @student_id,

now()

date_add( now()),

‘In Process’,

101);

Commit;

In the MySQL code used above, statements like start transaction and commit are used to manage transactions and also provide data integrity.