原子性

1DELIMITER $$
2
3CREATE PROCEDURE PlaceSuccessfulOrder()
4BEGIN
5    -- Start the transaction
6    START TRANSACTION;
7
8    -- Variables for the order
9    SET @customer_id = 1;
10    SET @order_date = CURDATE();
11    SET @product1_id = 1; -- Laptop
12    SET @product1_quantity = 1;
13    SET @product1_price = 1200.00;
14    SET @product2_id = 3; -- Wireless Earbuds
15    SET @product2_quantity = 2;
16    SET @product2_price = 99.99;
17    SET @total_order_amount = (@product1_quantity * @product1_price) + (@product2_quantity * @product2_price);
18
19    -- Placeholder for the new OrderID
20    SET @new_order_id = NULL;
21
22    -- Attempt to insert the order
23    INSERT INTO Orders (CustomerID, OrderDate, TotalAmount, ExpectedDeliveryDate, PaymentMethod)
24    VALUES (@customer_id, @order_date, @total_order_amount, DATE_ADD(@order_date, INTERVAL 5 DAY), 'Credit Card');
25
26    -- Get the ID of the newly inserted order
27    SET @new_order_id = LAST_INSERT_ID();
28
29    -- Check if order insertion was successful (basic check)
30    IF @new_order_id IS NOT NULL THEN
31        -- Attempt to insert order details for Laptop
32        INSERT INTO Order_Details (OrderID, ProductID, Quantity, TotalItemPrice)
33        VALUES (@new_order_id, @product1_id, @product1_quantity, @product1_quantity * @product1_price);
34
35        -- Attempt to update stock for Laptop
36        UPDATE Products
37        SET Stock = Stock - @product1_quantity
38        WHERE ProductID = @product1_id AND Stock >= @product1_quantity;
39
40        -- Attempt to insert order details for Wireless Earbuds
41        INSERT INTO Order_Details (OrderID, ProductID, Quantity, TotalItemPrice)
42        VALUES (@new_order_id, @product2_id, @product2_quantity, @product2_quantity * @product2_price);
43
44        -- Attempt to update stock for Wireless Earbuds
45        UPDATE Products
46        SET Stock = Stock - @product2_quantity
47        WHERE ProductID = @product2_id AND Stock >= @product2_quantity;
48
49        -- Check if stock updates were successful (by checking affected rows, for simplicity)
50        -- A more robust check would verify actual stock levels or handle errors more gracefully.
51        IF ROW_COUNT() > 0 THEN
52            -- All operations successful, commit the transaction
53            COMMIT;
54            SELECT 'Order placed successfully. Order ID:', @new_order_id AS Message;
55        ELSE
56            -- Something went wrong (e.g., insufficient stock for the second item)
57            ROLLBACK;
58            SELECT 'Order failed due to insufficient stock or other issue. Transaction rolled back.' AS Message;
59        END IF;
60    ELSE
61        -- Order insertion failed
62        ROLLBACK;
63        SELECT 'Order failed during order creation. Transaction rolled back.' AS Message;
64    END IF;
65
66    -- Let's verify the stock and orders (after running the successful transaction)
67    SELECT ProductID, ProductName, Stock FROM Products WHERE ProductID IN (@product1_id, @product2_id);
68    SELECT * FROM Orders WHERE OrderID = @new_order_id;
69    SELECT * FROM Order_Details WHERE OrderID = @new_order_id;
70END$$
71
72DELIMITER ;
73
74-- Call it like this:
75CALL PlaceSuccessfulOrder();

1DELIMITER $$
2
3CREATE PROCEDURE PlaceFailingOrder()
4BEGIN
5    -- Manually adjust stock for testing if needed, e.g., set Laptop stock to 3
6    -- UPDATE Products SET Stock = 3 WHERE ProductID = 1;
7
8    -- Start the transaction
9    START TRANSACTION;
10
11    -- Variables for the order
12    SET @customer_id_fail = 2; -- Assuming CustomerID 2
13    SET @order_date_fail = CURDATE();
14    SET @product_id_fail = 1; -- Laptop
15    SET @product_quantity_fail = 10; -- Trying to buy 10
16    SET @product_price_fail = 1200.00;
17    SET @total_order_amount_fail = @product_quantity_fail * @product_price_fail;
18
19    -- Placeholder for the new OrderID
20    SET @new_order_id_fail = NULL;
21
22    -- Attempt to insert the order
23    INSERT INTO Orders (CustomerID, OrderDate, TotalAmount, ExpectedDeliveryDate, PaymentMethod)
24    VALUES (@customer_id_fail, @order_date_fail, @total_order_amount_fail, DATE_ADD(@order_date_fail, INTERVAL 5 DAY), 'PayPal');
25
26    SET @new_order_id_fail = LAST_INSERT_ID();
27
28    IF @new_order_id_fail IS NOT NULL THEN
29        -- Attempt to insert order details
30        INSERT INTO Order_Details (OrderID, ProductID, Quantity, TotalItemPrice)
31        VALUES (@new_order_id_fail, @product_id_fail, @product_quantity_fail, @total_order_amount_fail);
32
33        -- Attempt to update stock - THIS WILL LIKELY FAIL OR UPDATE 0 ROWS if stock is insufficient
34        -- For this example, let's assume a check constraint or a trigger would cause an error,
35        -- or simply the WHERE clause `Stock >= @product_quantity_fail` prevents the update.
36        UPDATE Products
37        SET Stock = Stock - @product_quantity_fail
38        WHERE ProductID = @product_id_fail AND Stock >= @product_quantity_fail;
39
40        -- Check if the stock update actually happened. ROW_COUNT() will be 0 if stock was insufficient.
41        IF ROW_COUNT() > 0 THEN
42            COMMIT;
43            SELECT 'Order (Fail Scenario) processed and committed. This should not happen if stock is insufficient.' AS Message;
44        ELSE
45            -- Stock update failed (e.g., insufficient stock)
46            ROLLBACK;
47            SELECT 'Order (Fail Scenario) failed due to insufficient stock. Transaction rolled back.' AS Message;
48            SET @new_order_id_fail = NULL; -- Ensure we don't try to select a non-existent order
49        END IF;
50    ELSE
51        ROLLBACK;
52        SELECT 'Order (Fail Scenario) failed during order creation. Transaction rolled back.' AS Message;
53    END IF;
54
55    -- Verify that no partial changes were made if the transaction was rolled back
56    SELECT ProductName, Stock FROM Products WHERE ProductID = @product_id_fail;
57    SELECT * FROM Orders WHERE OrderID = @new_order_id_fail; -- Should be empty if rolled back
58    SELECT * FROM Order_Details WHERE OrderID = @new_order_id_fail; -- Should be empty if rolled back
59END$$
60
61DELIMITER ;
62
63-- Call the procedure:
64CALL PlaceFailingOrder();