Transaction inMySQL

A transaction is a set of database operations that

are executed as a single unit of work. Transactions

ensure that all the operations are completed successfully

or that none of them are executed at all.

By default, MySQL automatically commits the changes permanently to the

database.

To force MySQL not to commit changes automatically, you use the following

statement:

SET autocommit = 0;

You use the following statement to enable the autocommit mode explicitly:

SET autocommit = 1;

MySQL provides us with the following important statement to control

transactions:

1) To start a transaction, you use the START TRANSACTION statement.

2) To commit the current transaction and make its changes permanent,

you use the COMMIT statement.

3) To roll back the current transaction and cancel its changes,

you use the ROLLBACK statement.

Suppose we are deleting some records but not sure about wheather query

will delete only required rows then we can use transaction like below

START TRANSACTION;

DELETE FROM orders where id=45;

after this verify if things got right

or not then decide to commit or rollback ROLLBACK;

Between our Delete operation and ROLLBACK or commit we can check if our

delete has done deletion of desired rows or not

Our Delete was mistake so we reverted back to original values.

Untill we commit changes other will not see effect of delete operation above

Instead of ROLLBACK as above if we done COMMIT changes of deletion will be

saved permanetly after COMMIT others will be able to see effect of delete

operation.