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.
update customer set FirstName=’Shaun’ where CustomerId=1;
update customer set FirstName=’Fredrick’ where CustomerId=2;
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.
update customer set FirstName=’Shaun1′ where CustomerId=1;
update customer set FirstName=’Fredrick1′ where CustomerId=2;