E-Commerce Transaction Processing: ACID Compliance & PL/SQL Solution

E-Commerce Transaction Processing: ACID Compliance and PL/SQL Implementation

An e-commerce platform processes customer orders in the following two sequential steps:

1. Inventory Update: Decrease the available quantity of purchased items in the Inventory table.
2. Payment Processing: Charge the customer’s credit card and record the payment in the Payments table.

Suppose the inventory update succeeds, but the system crashes before the payment is recorded.

Tasks

1. ACID Property Analysis:
   • Identify which ACID property is violated in this scenario.
   • Explain why this violation could lead to inconsistent data.
2. Transactional Implementation:
   • Write a PL/SQL block or SQL transaction to handle order processing so that both steps execute atomically.
   • Use COMMIT and ROLLBACK to ensure database consistency.
3. Solution Justification:
   • Explain how your solution guarantees that either both steps succeed or none are applied.
4. Practical Implications:
   • Discuss the potential consequences if such transaction control is not implemented in a real e-commerce system, particularly regarding data integrity, customer trust, and operational risk.

E-commerce ACID violation, SQL transaction atomicity, PL/SQL e-commerce example, COMMIT and ROLLBACK usage, inventory and payment transaction, database consistency, transaction control tutorial, Oracle PL/SQL e-commerce, data integrity in transactions

PL/SQL Solution: Atomic Order Processing in E-Commerce

To ensure consistent order processing in an e-commerce system, both the inventory update and payment recording must be executed atomically. Below is a PL/SQL block demonstrating this:

DECLARE
  v_order_id     NUMBER := 101; -- Example OrderID
  v_customer_id  NUMBER := 1001; -- Example CustomerID
  v_payment_amt  NUMBER := 2500; -- Example payment amount
BEGIN
  -- Start transaction
  SAVEPOINT start_order;

  -- Step 1: Update Inventory
  UPDATE Inventory
  SET QuantityAvailable = QuantityAvailable - 1
  WHERE ProductID = 501
    AND QuantityAvailable > 0;

  IF SQL%ROWCOUNT = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Insufficient inventory.');
  END IF;

  -- Step 2: Record Payment
  INSERT INTO Payments(OrderID, CustomerID, PaymentAmount, PaymentDate)
  VALUES (v_order_id, v_customer_id, v_payment_amt, SYSDATE);

  -- Commit both steps if successful
  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    -- Rollback to savepoint to undo both steps if any error occurs
    ROLLBACK TO start_order;
    DBMS_OUTPUT.PUT_LINE('Transaction failed: ' || SQLERRM);
END;
/
  

Explanation

• The PL/SQL block ensures atomic execution of inventory update and payment recording.
• If any step fails (e.g., insufficient stock or payment error), the transaction is rolled back to the savepoint, leaving the database consistent.
• COMMIT ensures that both operations are permanently applied only when both succeed.

Business Implications

• Without transaction control, inventory could be reduced without successful payment, causing revenue loss and data inconsistency.
• Atomic transactions protect customer trust by ensuring they are not charged incorrectly.
• Maintains operational integrity and prevents potential disputes, errors in shipping, and accounting discrepancies.

PL/SQL transaction e-commerce, atomic order processing, COMMIT ROLLBACK example, inventory payment consistency, Oracle PL/SQL tutorial, ACID compliance in SQL, transaction control e-commerce, database integrity Oracle