Join TalkersCode Now !

Get Latest Tutorials And Links On Web Development
And Get Existing Offers Via Email

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

Join Us With Our 21000+ Subscribers And Get Our Latest Tutorials Update Via Email

Are You Starting A New Blog
Do You Want Best Web Hosting?

Try World's Best Web Hosting Provider BlueHost
At Just $3.95/mo

Hurry Up! Limited Time Offer

Yes, I Want To Check Out BlueHost

Check The World's Best SEO And All-In-One Marketing Toolkit
For Digital Marketing Professionals SEMRUSH

Check Out SEMRUSH