MySQL การใช้ Transaction ใน Stored Procedure

Transaction คือชุดคำสั่ง SQL ที่ต้องสำเร็จทั้งหมด หรือยกเลิกทั้งหมด เปรียบเสมือนการห่อกลุ่มคำสั่งสำคัญเข้าด้วยกัน เพื่อป้องกันข้อมูลผิดเพี้ยน (inconsistent) หากคำสั่งใดคำสั่งหนึ่งล้มเหลว

Transaction จึงเป็นหัวใจของระบบที่ต้องการความถูกต้อง 100% เช่น

• ระบบโอนเงิน
• ระบบขายสินค้า
• การอัปเดตหลายตารางพร้อมกัน

ประโยชน์ของการใช้ Transaction ใน Stored Procedure

• ป้องกันข้อมูลไม่สมบูรณ์เมื่อคำสั่งบางรายการล้มเหลว
• ควบคุมการเขียนหลายตารางให้เป็นหนึ่งเดียว
• เพิ่มความปลอดภัยในการอัปเดตข้อมูลสำคัญ
• ย้อนกลับ (Rollback) ได้เมื่อเกิดข้อผิดพลาด

รูปแบบคำสั่ง Transaction

START TRANSACTION;     -- เริ่มธุรกรรม
-- คำสั่ง SQL หลายรายการที่ต้องทำพร้อมกัน
COMMIT;                -- ยืนยันการเปลี่ยนแปลง (ถ้าทุกอย่างสำเร็จ)
ROLLBACK;              -- ยกเลิกธุรกรรม (ถ้ามีข้อผิดพลาด)

• ใช้ใน Stored Procedure ร่วมกับ DECLARE HANDLER เพื่อดักจับ error

ตัวอย่างการใช้งาน

อัปเดตออเดอร์ และตัดสต๊อกสินค้าแบบปลอดภัย

เมื่อมีการยืนยันคำสั่งซื้อ เราต้อง

1. เปลี่ยนสถานะออเดอร์เป็น “confirmed”
2. หักจำนวนสินค้าออกจากคลัง
3. หากรายการใดล้มเหลว ให้ยกเลิกทั้งหมด

สมมติว่ามีตาราง orders และตาราง products ซึ่งเก็บข้อมูลดังนี้

Stored Procedure

DELIMITER //

CREATE PROCEDURE confirm_order(
  IN p_order_id INT,
  IN p_product_id INT,
  IN p_quantity INT
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- ถ้ามี error ให้ยกเลิกธุรกรรม
    ROLLBACK;
  END;

  -- เริ่มธุรกรรม
  START TRANSACTION;

  -- อัปเดตสถานะคำสั่งซื้อ
  UPDATE orders
  SET status = 'confirmed'
  WHERE order_id = p_order_id;

  -- หักจำนวนสินค้า
  UPDATE products
  SET quantity = quantity - p_quantity
  WHERE product_id = p_product_id;

  -- ทุกอย่างสำเร็จ → ยืนยันการเปลี่ยนแปลง
  COMMIT;
END //

DELIMITER ;

การเรียกใช้งาน

CALL confirm_order(101, 1, 2);

ผลลัพธ์ในตาราง orders

ผลลัพธ์ในตาราง products

หมายเหตุ

• COMMIT ต้องอยู่ท้ายสุด เมื่อแน่ใจว่าทุกคำสั่งสำเร็จ
• ควรมี ROLLBACK อยู่ใน DECLARE HANDLER เสมอ
• หากไม่ใช้ START TRANSACTION การเปลี่ยนแปลงจะเกิดทันที (Auto-commit)
• Transaction ทำงานได้เฉพาะกับ Engine แบบ InnoDB เท่านั้น (ไม่รองรับใน MyISAM)