Categories

Articles in MySQL Category

MySQL Transactions

MySLQL transactions can be used when you want to make sure all the statements you specify are executed. Only the tables of InnoDB storage engine support transactions.

In a transaction, if at least one statement fails, all the changes will be rolled back and database will be in its initial state (There are some statements that can not be rolled back: Will be discussed at the end).

In web applications, it’s common that you want to either run set of statements or do no change since success of only few statements can cause data corruption.

For explanations in this article, we are going to use `employee` and `telephone` tables mentioned below. SQL commands for creating the tables and inserting data are available here.

id first_name last_name job_title salary notes
1 Robin Jackman Software Engineer 5500  
2 Taylor Edward Software Architect 7200  
3 Vivian Dickens Database Administrator 6000  
4 Harry Clifford Database Administrator 6800  
5 Eliza Clifford Software Engineer 4750  
6 Nancy Newman Software Engineer 5100  
7 Melinda Clifford Project Manager 8500  
8 Harley Gilbert Software Architect 8000  
id employee_id type no
1 1 mobile 245-249697
2 2 mobile 270-235969
3 2 land 325-888885
4 3 mobile 270-684972
5 4 mobile 245-782365
6 4 land 325-888886
7 5 mobile 245-537891
8 6 mobile 270-359457
9 7 mobile 245-436589
10 7 land 325-888887
11 8 mobile 245-279164
12 8 land 325-888888

Think you need to add Grace Williams as a new employee with her telephone details. You would run following two INSERT statements.

INSERT INTO `employee` (`id`, `first_name`, `last_name`, `job_title`, `salary`) VALUES (9, 'Grace', 'Williams', 'Softwaree Engineer', 5000);
INSERT INTO `telephone` (`id`, `employee_id`, `type`, `no`) VALUES (13, 9, 'mobile', '270-598712');

You can see that in the second statement, 9 is given for `employee_id` which is the value of `id` in first query. When running these two statements, think that first one fails and second one succeeds.

Then `telephone` table will have a row that refers to an employee with ID 9 that doesn’t exist. If we ran these two statements in a MySQL transaction, if the first statement fails then the second statement will be rolled back making no changes.

In PHP, we can execute a transaction using MySQLi functions like below.

<?php

//$salary = 5000;
$salary = '$5000';

/* Change database details according to your database */
$dbConnection = mysqli_connect('localhost', 'robin', 'robin123', 'company_db');

mysqli_autocommit($dbConnection, false);

$flag = true;

$query1 = "INSERT INTO `employee` (`id`, `first_name`, `last_name`, `job_title`, `salary`) VALUES (9, 'Grace', 'Williams', 'Softwaree Engineer', $salary)";
$query2 = "INSERT INTO `telephone` (`id`, `employee_id`, `type`, `no`) VALUES (13, 9, 'mobile', '270-598712')";

$result = mysqli_query($dbConnection, $query1);

if (!$result) {
	$flag = false;
    echo "Error details: " . mysqli_error($dbConnection) . ". ";
}

$result = mysqli_query($dbConnection, $query2);

if (!$result) {
	$flag = false;
    echo "Error details: " . mysqli_error($dbConnection) . ". ";
}

if ($flag) {

    mysqli_commit($dbConnection);
    echo "All queries were executed successfully";

} else {

	mysqli_rollback($dbConnection);
    echo "All queries were rolled back";  

} 

mysqli_close($dbConnection);

?>
  • When you execute mysqli_query() function, result is immediately committed to the database. Using mysqli_autocommit() function, you can turn off this behavior so that result won’t be committed to the database permanently till you command.
  • After that we simply execute the necessary statements and set the $flag to false if any statement fails. If there are many statements to run, consider storing the statements in an array and using a for or foreach loop.
  • At the end, if flag is true (no error has occurred), we commit the results to the database permanently using mysqli_commit(). Else we roll back the results using mysqli_rollback() function.

If you ran above PHP script, you would notice that no statement is executed since the salary we pass to the first statement causes an error. Assign the integer value to the salary and you would notice that both statements are executed successfully.

While transactions will help you to eliminate data corruptions in programming level, it’s advisable to define foreign key constraints among related tables like `employee` and `telephone` so that data integrity will be kept in database level as well.

Statements That Don’t Support Transactions

Not all statements support transactions. For an example if you have CREATE TABLE or ALTER TABLE statements, they make implicit commits (permanently change the database) without waiting till an explicit commit (via mysqli_commit() in PHP) is made. Check MySQL manual for statements that can not be rolled back and statements that make implicit commits.

Where to Head from Here...
Share with Your Peers...

We Value Your Feedback...

We love to hear what you think about this article. Please provide your opinion, suggestions and improvements using following form. Note that submitted feedback is not displayed but we will get back to you if it needs a reply.