✖

All TalkersCode Topics

Follow TalkersCode On Social Media

✖

devloprr.com - A Social Media Network for developers Join Now ➔

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