MySQL Transactions

MySQL Transactions

Transaction is a group of SQL statements which you combine into a single logical unit. A transaction will never be complete unless each individual operation within the group is successful. If a any single query fails the complete transaction will fail.


Some MySQL storage systems like MyISAM cannot support transactions but InnoDB support transactions.Transactions are often coded in a stored procedures



Example of transaction

CREATE PROCEDURE demo()
BEGIN
     DECLARE sql_error TINYINT DEFAULT FALSE;

     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
         SET sql_error = TRUE;

     START TRANSACTION;

     INSERT INTO student_info
     values(2, Raman, MCA);  

	 INSERT INTO student_marks
     values(2, 20, 40);  

	 IF sql_error = FALSE THEN
         COMMIT;
         SELECT 'The transaction was successful';
     ELSE
	     ROLLBACK;
         SELECT 'The transaction fails';
     END IF;

Code Explanation

  • In this example we declare a variable sql_error to FALSE.

  • Then the second declare statement creates a condition handler that sets the sql_error variable to TRUE.

  • The START TRANSACTION statements is the start of transaction.

  • Then we insert values into student_info and student_marks table.

  • IF statement uses the sql_error variable to check whether an error occured or not in our queries.

  • If no error occured then a COMMIT statement used to commit the changes to the database.

  • If an error occured then a ROLLBACK statement rollback all the changes done by the our queries

Prev

Subscribe Our Newsletter And Get Tutorials
And Offers Via Email

Subscribe Our Newsletter And Get Tutorials And Offers Via Email