Performing Transactions with BEGIN and COMMIT

     MYSQL supports the use of transaction in its server. Transaction  is a sequential group of database manipulation operations, which is performed as if it were one single work unit. In other words, a transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail.
     There are many statement you can use to provide control over the use of transactions. I will discuss four of them, the START TRANSACTION or BEGIN statement, the COMMIT statement, the ROLLBACK statement, and the SET AUTOCOMMIT statment. To start a new transaction, you need to use the START TRANSACTION or BEGIN statement. The COMMIT statement coommits the current transaction which makes its changes permanent. The ROLLBACK statement rolls back the current transaction cancelling the changes made to it. The SET AUTOCOMMIT statement disables or enables the default autocommit mode for the current session.
     Here is a sample code that uses transaction with ROLLBACK:
     CREATE TABLE T(Year INT) TYPE=InnoDB;
     BEGIN;
     INSERT INTO T(Year) VALUES(2012);
     SELECT * FROM T;
     ROLLBACK;
     SELECT  * FROM T;
     Here is the output:

     Here is another code that uses transaction with COMMIT and ROLLBACK:
     BEGIN;
     INSERT INTO T(Year) VALUES(2012);
     SELECT * FROM T;
     COMMIT;
     ROLLBACK;
     SELECT  * FROM T;
     Here is the output:

   
      A good practice of using transactions is by using the Table Lock. The Table Lock locks the table being used and prevents other user from accessing that table. This way one user can access the table at a time preventing the inconsistency in changes made to the database. To use Table Locks you will need to use the LOCK TABLES statement like this 'LOCK TABLES T READ'. The T is the table and the READ is the access your giving to that table. To unlock the table use the UNLOCK TABLES statement.
     For more information about Table Locks you can visit the mysql site here.