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;

You may also like...