Solution to the problem that PHP transactions cannot be rolled back: 1. Open the corresponding PHP file; 2. Check the "function update_user_set_date_of_birth($date_of_birth){...}" code; 3. Pass "$this-> Mysqli->autocommit(false);" can be used to turn off automatic submission.
The operating environment of this tutorial: Windows 7 system, PHP version 8.1, Dell G3 computer.
What should I do if the php transaction cannot be rolled back?
Problem description:
About php transaction not performing rollback
mysqliphptransactions Transactions not doing the rollback
I have the function of updating users:
function update_user_set_common($date_of_birth, $mobile, $street, $housenumber, $addition, $postal_code, $location, $country) { try { $this->mysqli->begin_transaction(); $this->update_user_set_date_of_birth($date_of_birth); $this->update_user_set_mobile($mobile); $this->update_user_set_address($street, $housenumber, $addition, $postal_code, $location, $country); $this->mysqli->commit(); return true; } catch (Exception $e) { $this->mysqli->rollback(); echo"Error:" . $e; return false; } }
As you can see, I made a transaction. If everything is OK, it should be committed, if an error occurs, it should be rolled back.
Here's an example of how my update query works:
function update_user_set_date_of_birth($date_of_birth) { return $this->update_user_set_singlefield(COL_USER_DATE_OF_BIRTH, $date_of_birth); } function update_user_set_singlefield($field, $value) { if ($update_stmt = $this->mysqli->prepare("UPDATE" . TABLE_USER ." SET" . $field ." = ? WHERE" . COL_USER_ID ." = ?")) : if (!$update_stmt->bind_param("ss", $value, $this->USER_ID)) : $update_stmt->close(); return false; endif; if (!$update_stmt->execute()) : $update_stmt->close(); return false; else : $update_stmt->close(); return true; endif; else : return false; endif; }
So now for example update_user_set_mobile fails, but there is no rollback. The statement before update_user_set_date_of_birth will still be executed and will not change back to the original one.
Why is there no rollback?
Example creating table to show me using innodb:
CREATE TABLE `USER` ( `USER_ID` bigint(20) NOT NULL PRIMARY KEY AUTO_INCREMENT, `USER_E_MAIL` varchar(255) COLLATE utf8_bin NOT NULL, `USER_PASSWORT` varchar(255) COLLATE utf8_bin NOT NULL, `USER_PASSWORT_SALT` varchar(255) COLLATE utf8_bin NOT NULL, `USER_FIRSTNAME` varchar(255) COLLATE utf8_bin NOT NULL, `USER_LASTNAME` varchar(255) COLLATE utf8_bin NOT NULL, `USER_DATE_OF_BIRTH` varchar(200) COLLATE utf8_bin DEFAULT NULL, `USER_MOBILE` varchar(255) COLLATE utf8_bin DEFAULT NULL, `ADDRESS_ID` bigint(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Problem solved:
Turn off autocommit instead of mysqli-> begin_transaction();
$this->mysqli->autocommit(false);
http://php.net/manual/zh/mysqli.autocommit.php
Recommended learning: "PHP Video Tutorial"
The above is the detailed content of What should I do if the php transaction cannot be rolled back?. For more information, please follow other related articles on the PHP Chinese website!