I have written code to insert multiple rows in a table. If there is an error on any of them, I want them to rollback.

The code is written in PHP:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$conn->autocommit(false);
$conn->begin_transaction();
try {
    $projectsLoaded = "";
    foreach ($data as $key => $value){
        if ($key === "palette__for__"){
            // palette data to save to userpalettes
            $stmt = $conn->prepare("UPDATE userpalettes SET userId = ?, paletteData = ?, dataSize = ?");
            $paletteData = $value;
            $dataSize = strlen($value)/1024;
            $stmt->bind_param("ssd", $userId, $paletteData, $dataSize);
            $stmt->execute();
        }
        else if ($key === "searches__for__library__patterns" || $key === "__symbols__color__map__" ||
                 $key === "default_project_in_local_storage" || $key === "hints__on__off__local__storage"){
            continue;
        }
        else {
            // patterns should have bg, fg and grid keys in them
            try {
                $pattern = json_decode($value);
                error_log(print_r($pattern, true));
            }
            catch(Exception $e){
                continue; // skip
            }
            if (property_exists($pattern, 'bg') && property_exists($pattern,'fg') && property_exists($pattern,'grid')){
                 $projectName = $key;
                 // write to userprojects table
                $stmt = $conn->prepare("INSERT INTO userprojects (userId, projectName, projectData, dataSize) VALUES (?, ?, ?, ?)");
                $projectData = $value;
                $dataSize = strlen($value)/1024;
                $stmt->bind_param("sssd", $userId, $projectName, $projectData, $dataSize);
                $stmt->execute();
                $projectsLoaded = $projectsLoaded.",".$projectName;
            }
            else {
                continue;
            }
        }
    }
    echo "Success:".$projectsLoaded;
}
catch(Exception $e){
    $conn->rollback();
    $conn->close();
    echo("Error: Exception while inserting data: ".$e->getMessage()." Line:".$e->getLine()." Rolled back");
}

I am still in the process of testing this code and it’s the first time I have tried to use a transaction.

I have noticed that when an Exception occurs, I am getting back the error message at the bottom, so rollback() has executed, but the tables then contain inserted data up to the point of the error.

This is also the first time I have used prepared statements, so am wondering if that causes some problem also.

Is there something wrong with the statements I am using? Or is it a problem with my database not supporting transactions?

I am using a mysql db that was installed as part of a WAMP server installation. Don’t know too much about it other than that, but I have been using it as my test system for quite some time and have had no problems. But, maybe the transaction stuff does not work on it?

I looked up possible problems and I found that others were using the autocommit(false) statement. One place said to do this before begin_transaction, but I have also seen it without a begin_transaction following it.

Should I be using autocommit(false) in place of begin_transaction? Does it matter?