使用PDO检查数据库插入和回滚的正确方法

I have here a code to insert the order of the customer in the orders table and insert the purchased products in that order in the purchased_products table. I want to check if the insertions were made, otherwise undo the changes with PDO rollback(). My code is:

$options = [
PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false
];    

try
{
    $connection = new PDO("mysql:host={$HOST};dbname={$DB_NAME}", $USERNAME, $PASS, $options);
}

$connection->beginTransaction();

try
{
    $sql = "INSERT INTO orders (customer_id, customer_name, order_value, order_date)
            VALUES (?, ?, ?, ?)";

    $query = $connection->prepare($sql);
    $query->execute(array
    (
        $user_id,
        $user['user_name'],
        $order_value,
        $date
    ));

    $id_of_respective_order = $connection->lastInsertId();
}
catch(PDOException $exception) 
{
    $connection->rollback();
    echo "<script>alert('An error occurred while completing your purchase. Please try again later.');</script>";
}

try
{
    $sql = "INSERT INTO purchased_products (order_id, product_name, product_price, quantity)
            VALUES (?, ?, ?, ?)";

    $query = $connection->prepare($sql);

    foreach($_SESSION['cart'] as $product)
    {
        $query->execute(array
        (
            $id_of_respective_order,
            $product['product_name'],
            $product['product_price'],
            $product['quantity']
        ));
    }
}
catch(PDOException $exception) 
{
    $connection->rollback();
    echo "<script>alert('An error occurred while completing your purchase. Please try again later.');</script>";
}

$connection->commit();

Is this way safe? I use a transaction to lock the tables and lastInsertId () to assign the ID of the order to the products that belongs to it. I check the insertions and if something went wrong undo the changes with rollback(). Is my checkout system well prepared and totally safe?

It makes more sence to do all your inserts inside the same Try/Catch and then if the order insert or the order_item insert fails a single catch block will deal with the rollback and any cleanup/reporting that may be required.

The way you had it the order insert could fail and then the order_item insert would still try and run, possibly creating items without a owning order.

try {
    $connection = new PDO("mysql:host={$HOST};dbname={$DB_NAME}", 
                          $USERNAME, $PASS);
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e ) {
    echo 'Connection failed: ' . $e->getMessage();
    exit;
}

$connection->beginTransaction();

try {
    $sql = "INSERT INTO orders 
           (customer_id, customer_name, order_value, order_date)
           VALUES (?, ?, ?, ?)";

    $query = $connection->prepare($sql);
    $query->execute(array( $user_id,
                         $user['user_name'],
                         $order_value,
                         $date
                        )
                   );

    $id_of_respective_order = $connection->lastInsertId();

    $sql = "INSERT INTO purchased_products 
                   (order_id, product_name, product_price, quantity)
            VALUES (?, ?, ?, ?)";

    $query = $connection->prepare($sql);

    foreach($_SESSION['cart'] as $product) {
        $query->execute(array( $id_of_respective_order,
                               $product['product_name'],
                               $product['product_price'],
                               $product['quantity']
                              )
                       );
    }

    $connection->commit();

}
catch(PDOException $e) {
    $connection->rollBack();

    echo 'Order creation failed: ' . $e->getMessage();
    echo "<script>alert('An error occurred while completing your purchase. Please try again later.');</script>";
    exit;
}