Transaction control in MySQL

A transaction is nothing but sequence of SQL statements executed as a one unit. A transaction should meet ACID properties. By default, each sql statement is considered as a transaction. So as soon as you execute the SQL statement, changes are committed to the database. There is a variable with name autocommit that controls this behaviour. You can use below query to view if autocommit is on or off.
 
show variables where Variable_name = ‘autocommit’

You can turn on or off autocommit by using below syntax.
 
set autocommit = 0

Example of MySQL transaction

At the end of below transaction, all changes will be committed and customer table will be updated.
 

start transaction;

update customer set FirstName=’Shaun’ where CustomerId=1;

update customer set FirstName=’Fredrick’ where CustomerId=2;

commit;

At the end of below transaction, all changes will be rolled back and customer table will remain as it was before the transaction was started.
 

start transaction;

update customer set FirstName=’Shaun1′ where CustomerId=1;

update customer set FirstName=’Fredrick1′ where CustomerId=2;

rollback;

Web development and Automation testing

solutions delivered!!