Menu
How can I start a transaction when there is any error in the SQL statements the system will rollback the changes automatically?
Transaction MySQL
PHP + MySQL transactions examples
in PHP
try {
// First of all, let's begin a transaction
$db->beginTransaction();
// A set of queries; if one fails, an exception should be thrown
$db->query('first query');
$db->query('second query');
$db->query('third query');
// If we arrive here, it means that no exception was thrown
// i.e. no query has failed, and we can commit the transaction
$db->commit();
} catch (Exception $e) {
// An exception has been thrown
// We must rollback the transaction
$db->rollback();
}
How to repeat the logic without PHP,only MYSQL
276
Let's start off by saying there aren't traditional TRY CATCH statements in MySQL, but there are Conditions which are almost synonymous with Exceptions. Conditions contain both a code and description. Instead of wrapping all of your code in a try catch block you basically add what's called a handler to your procedure.
For SQLEXCEPTION conditions, the stored program terminates at the statement that raised the condition, as if there were an EXIT handler. If the program was called by another stored program, the calling program handles the condition using the handler selection rules applied to its own handlers.
A TRY... CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection. A TRY block must be immediately followed by an associated CATCH block. Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error.
We can write the multiple queries into the MySQL procedure/function and can maintain the transaction. Please refer the sample given below. Basically, you should declare an error handler which will call rollback.
PROCEDURE `myprocedure`()
BEGIN
.. Declare statements ..
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
.. set any flags etc eg. SET @flag = 0; ..
ROLLBACK;
END;
START TRANSACTION;
.. Query 1 ..
.. Query 2 ..
.. Query 3 ..
COMMIT;
.. eg. SET @flag = 1; ..
END
Please see the links below for more details
MySQL : transaction within a stored procedure
How can I use transactions in my MySQL stored procedure?
192
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
