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;
- 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