MySQL Tutorial
Introduction Installation of MySQL Getting MySQL server information RDBMS Concepts Data Definition Language Data types Managing Databases Managing Tables Managing Views Inserting records in a table Updating records in a table Deleting records from a table Retrieving Records Data Control Language Variables in MySQL Procedures in MySQL Built-In functions in MySQL User Defined functions in MySQL Triggers in MySQL Cursors in MySQL Transaction control Event scheduler Tuning MySQL Server Performance Managing users Importing and Exporting data Database Testing Workbench PHPMyAdminTransaction 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!!