mikelopster
วงแหวนเว็บ

มารู้จักกับ SQL Transaction กันว่ามันคืออะไร ?

/ 6 min read

มี Video
sql , database , transaction , deadlock , acid , commit , rollback , consistency , durability , isolation

Share on social media

sql-transaction สามารถดู video ของหัวข้อนี้ก่อนได้ ดู video

Transaction คืออะไร ?

ก่อนจะเล่าถึงเรื่องของ Transaction เราต้องมาทำความเข้าใจปัญหาของ SQL กันก่อนว่า “ทำไมเราต้องใช้ Transaction เข้ามา”

เวลาที่เราทำกระบวนการบางอย่างขึ้นมา เช่น ต้องจ่ายเงิน > เสร็จแล้วได้ของ > ต้องลดเงิน​ > ลด stock

  • เราจะเจอว่า ของพวกนี้จะต้องทำอย่าง “เป็นขั้นเป็นตอนและเป็นลำดับ” ไม่ได้ทำแต่ละอย่างแยกออกจากกัน (เช่น เราจะไม่สามารถได้ของ หากไม่จ่ายเงิน)

Transaction คือ การทำงาน logic ซึ่งจะเป็นการ “กระทำบางอย่าง” ใน database ให้เข้าสู่ State หนึ่งก่อน ก่อนที่จะมีการ commit จริงๆ

  • หมายความว่า จากเหตุการณ์ด้านบน สิ่งที่เราจะทำคือ เราจะทำการ snapshot ทุกส่วนเอาไว้ก่อน > ทุกอย่างพร้อม ก็จะเป็นการ update เข้า database เข้าไป “พร้อมกันทีเดียว” (เรียกว่าการ Commit state)

ทีนี้ Transaction นั้นจะถือว่าสมบูรณ์ได้ จะต้องมีคุณสมบัติของ Transaction 4 อย่างตาม ACID คือ

  • Atomicity = All changes must be performed successfully or not at all.
  • Consistency = Data must be in a consistent state before and after the transaction.
  • Isolation = No other process can change the data while the transaction is running.
  • Durability = The changes made by the transaction must persist.

รู้จักกับ Concept ACID

เราจะลองมาทำความเข้าใจ 4 อย่างนี้ไปด้วยกัน

1. Atomicity

All changes must be performed successfully or not at all.
  • ถ้า Transaction success = ทุกคำสั่งต้อง Success
  • ถ้า Transaction fail = ทุกคำสั่งต้อง Fail (และนับเป็น Fail ใน Transaction นั้น)

2. Consistency

Data must be in a consistent state before and after the transaction.
  • ข้อมูลจะต้อง “สอดคล้องกัน” ทั้งก่อนและหลังเกิด Transaction
  • เช่นถ้ามีการโอนเงินจากนาย A ไปนาย B (สมมุติว่า A มีเงิน 500, B มีเงิน 300 และมีการโอนจาก A ไป B 200, นาย A ต้องเหลือเงิน 300 และ นาย B ต้องมีเงินเป็น 500)

3. Isolation

No other process can change the data while the transaction is running.
  • ระหว่างจัดการ Transaction “ห้าม” มี process ใดไปรบกวนข้อมูลใน Transaction ได้ จนกว่า จะ commit ลง Database (การบันทึกจริงๆหลังจาก Transaction เกิดขึ้นมา)

4. Durability

The changes made by the transaction must persist.
  • เมื่อ Transaction ได้ดำเนินการเรียบร้อย จะต้องคงอยูแบบนั้น “ไม่หายไป” (ไม่ว่า database จะดับไป ข้อมูลก็ยังต้องคงอยู่แบบเดิม)

โดย Transaction นั้นจะมี 2 state ใหญ่ๆด้วยกันคือ

  • COMMIT = state ที่จะทำการยืนยัน SQL Change
  • ROLLBACK = state ที่จะทำการลบ Transaction ออก เพื่อไม่ให้เกิด change ใน Database

ด้วยคุณสมบัติเหล่านี้ Transaction เองทั้ง 2 state นี้ทำการรองรับคุณสมบัติ ACID ไว้เพื่อให้การทำ Transaction เป็นสิ่งที่สามารถไว้วางใจในการดำเนินการได้

รวมถึง Transaction นั้นเป็น logic อีกหนึ่งประเภทที่ database เกือบทั้งหมดเตรียมไว้ = ไม่จำเป็นต้องอยู่ใน SQL เท่านั้น ทุกๆ Database technology จะมีคุณสมบัติของ Transaction ให้อยู่แล้ว

มาดูเคสระหว่างใช้กับไม่ใช้ Transaction กัน

สมมุติเราจะต้องทำ 3 อย่างด้วยกัน

  • ต้องตัด stock (ลดจำนวน product ลง 1 ชิ้น)
  • เพิ่ม point ให้ user ตามราคาสินค้าที่ซื้อ
  • สร้าง order ว่า user ซื้อ product นี้ไป

โดยเรามี Database schema หน้าตาประมาณนี้

erDiagram
    USERS ||--o{ ORDERS : "has"
    PRODUCTS ||--o{ ORDERS : "contains"
    USERS {
        int id PK
        string name
        int point
    }
    PRODUCTS {
        int id PK
        string name
        int quantity
        float price
    }
    ORDERS {
        int id PK
        int user_id FK
        string product_name
        int quantity
        float total_price
        datetime created_at
    }

คำถามคือ

  1. เราควรทำอะไรก่อนกัน ? = อะไรสำคัญกว่าต้องทำก่อนและเช็คก่อน
  2. ถ้าเกิด 1 ใน 3 นี้เกิดมีปัญหาขึ้นมา ควรจัดการอย่างไร ? = ต้องจัดการพร้อมกัน ด้วยคุณสมบัติของการ Commit และ Rollback ของ Transaction

เราจะมาลองดูผ่านตัวอย่างนี้กัน

Transaction และ Rollback

setup project

docker-compose.yml

version: "3.7"


services:
  db:
    image: mysql:latest
    container_name: mysql_db
    command: --default-authentication-plugin=mysql_native_password
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: tutorial
    ports:
      - "3306:3306"
    volumes:
      - mysql_data:/var/lib/mysql
    networks:
      - my_network


  phpmyadmin:
    image: phpmyadmin/phpmyadmin:latest
    container_name: phpmyadmin
    environment:
      PMA_HOST: db
      PMA_PORT: 3306
      PMA_USER: root
      PMA_PASSWORD: root
    ports:
      - "8080:80"
    depends_on:
      - db
    networks:
      - my_network


networks:
  my_network:
    driver: bridge


volumes:
  mysql_data:
    driver: local

เราจะลองเปรียบเทียบ 2 กรณีกันคือ ใช้ Trasaction และ ไม่ใช้ Transaction เพื่อให้เห็นภาพมากขึ้น

ก่อนที่จะไป เราจะมารู้จักกับ SQL Store procedure ก่อนว่าคืออะไร ?

Store procedure คืออะไร ?

SQL Stored Procedure คือการสร้างกลุ่มคำสั่งของ SQL เอาไว้เป็นคำสั่งเดียว (ปกติมักจะใช้รวมคำสั่งที่มีการใช้บ่อยๆใน SQL) โดย feature นี้สามารถเรียกใช้งานผ่าน SQL ทั่วไปได้เลย

โดยข้อดีของการใช้ SQL Stored Procedure คือ

  • สามารถ reuse complex SQL ได้จากการใช้จาก Procedure แทนที่จะต้องพิมพ์คำสั่งยาวๆของ SQL
  • สามารถรับเป็น parameter คล้ายๆกับการสร้าง function เพื่อให้สามารถใช้งานกับตัวแปรที่หลากหลายผ่าน SQL เดียวกันได้

สาเหตุที่เราต้องใช้ procedures ในการ handle IF นั้น เนื่องจาก SQL ไม่ได้มีการ handle case ของการ throw error ออกมา

  • ปกติ SQL จะมีการเกิด error ทันที ขณะรัน ทำให้ไม่สามารถ break error ได้
  • แต่ procedures เป็นการ handle คล้ายๆ function ทำให้สามารถที่จะ break และ return error message ออกไปได้

ปกติ เคส Transaction สามารถทำงานได้โดยไม่ต้องใช้ procedures แต่เนื่องจากเราจะทำใน SQL เท่านั้น = เราเลยเลือกใช้ท่านี้แทน

กรณีไม่ใช้ Transaction

  • ใช้ Store procedure สร้าง function createOrder ขึ้นมา
  • run 3 query ตรงไปตรงมา
DELIMITER //
CREATE PROCEDURE createOrder(IN ordered_quantity INT, IN product_id INT, IN user_id INT)
BEGIN
  -- Step 1: Decrease product quantity
  UPDATE products
  SET quantity = quantity - ordered_quantity
  WHERE id = product_id;


  -- Check if product quantity is less than 0
  SELECT quantity INTO @quantity
  FROM products
  WHERE id = product_id;


  -- If quantity is less than 0, return an error message
  IF @quantity < 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Product quantity cannot be less than 0';
  END IF;
    -- Calculate total_price
    SELECT price INTO @price
    FROM products
    WHERE id = product_id;


  SET @total_price = ordered_quantity * @price;


  -- Step 2: Create order
    INSERT INTO orders (product_id, user_id, quantity, total_price)
    VALUES (product_id, user_id, ordered_quantity, @total_price);


  -- Step 3: Update users points
  UPDATE users
  SET point = point + @total_price
  WHERE id = user_id;
END //
DELIMITER ;

คำสั่งเพื่อ run Procedure

CALL createOrder(1, 1, 1);
DROP PROCEDURE IF EXISTS createOrder;

สิ่งที่จะเกิดขึ้นคือ มันก็จะ run ตาม step เลย

  • แต่! เมื่อไหร่ก็ตามที่ มี step อันใดอันหนึ่ง Error ออกมา = ต้องคืนค่าเดิมจาก query ออกมา (อารมณ์ประมาณ ตัด stock ไปแล้ว แต่ดันเกิด process บางอย่างมีปัญหา = ต้องคืน stock ทั้งหมดกลับไปใหม่)

กรณีที่ทำ Transaction

  • ใช้ Store procedure สร้าง function createOrderTransaction ขึ้นมา
  • นำ 3 query ไปใส่ใน Transaction เดียวกัน
DELIMITER //


CREATE PROCEDURE createOrderTransaction(IN ordered_quantity INT, IN product_id INT, IN user_id INT)
BEGIN
    START TRANSACTION;


    -- Step 1: Decrease product quantity
    UPDATE products
    SET quantity = quantity - ordered_quantity
    WHERE id = product_id;


    -- Check if product quantity is less than 0
    SELECT quantity INTO @quantity
    FROM products
    WHERE id = product_id;


    -- Calculate total_price
    SELECT name, price INTO @product_name, @price
    FROM products
    WHERE id = product_id;


    SET @total_price = ordered_quantity * @price;


    -- Step 2: Create order
    INSERT INTO orders (product_name, user_id, quantity, total_price)
    VALUES (@product_name, user_id, ordered_quantity, @total_price);


    -- Step 3: Update users points
    UPDATE users
    SET point = point + @total_price
    WHERE id = user_id;


    -- If everything is fine, commit the transaction




    -- If quantity is less than 0, return an error message and rollback the transaction
    IF @quantity - ordered_quantity < 0 THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Product quantity cannot be less than 0';
    END IF;


    COMMIT;


END //


DELIMITER ;
CALL createOrderTransaction(2, 1, 1);
  • ถ้าเราลองย้ายตำแหน่ง SQL ใดๆก็ตาม ก็จะเจอว่า ถ้าเกิด if ไม่่ผ่าน = ROLLBACK ทั้ง set กลับไปได้ (จะไม่มีคำสั่งใดทำงานไปจริงๆ)

Deadlock = ปัญหาการรอ Transaction ให้เสร็จ

เราจะลองให้ run SQL command นี้ใน mysql ก่อน (ทำผ่าน DBMS บางเจ้าไม่ได้ เนื่องจากมัน release lock ให้ auto เพื่อกัน process ค้าง) เป็นการ run Transaction โดยยังไม่ commit = ยังไม่จบ Transaction

  • process จะทำการจอง Resource เอาไว้ก่อน (เท่ากับ ไม่อนุญาตให้แก้ product ที่มี id = 1, 2 เอาไว้)
-- Transaction 1 (run ใน command mysql)
START TRANSACTION;
UPDATE products SET quantity = quantity - 1 WHERE id = 1;
UPDATE products SET quantity = quantity - 1 WHERE id = 2;

แล้วทีนี้เราลองมา run

UPDATE products SET quantity = quantity - 1 WHERE id = 1;

จะเจอ process lock wait ออกมา deadlock

Deadlock คือ ปัญหาของการ lock resource เอาไว้ เพื่อกันไม่ได้เกิดการ write resource แบบ concurrence เกิดขึ้น (เพื่อคงความ consistency ของระบบไว้)

วิธีแก้ปัญหา / คำแนะนำ Deadlock

จริงๆ ขึ้นอยู่กับเคสว่ากำลังเผชิญกับอะไรอยู่ ก็จะต้องแก้ตามนั้น แต่ส่วนใหญ่มักจะเจอปัญหาเหล่านี้

  1. Long Transaction = Transaction ยาวเกินทำให้ต้อง lock process ไว้นาน แก้โดยการทำให้สั้นลง และ ยกของที่ไม่จำเป็นออกมาเป็น query ปกติแทน (เพื่อไม่ให้เกิดการ lock process)

  2. เรียงลำดับ transaction ใหม่ วิเคราะห์ว่าอะไรที่สามารถทำงานพร้อมกันได้ และ อะไรที่ทำงานพร้อมกันไม่ได้

  • ทำพร้อมกันได้ หมายถึง ไม่มีปัญหา deadlock เนื่องจากใช้ Resource ไม่ชนกัน
  • ถ้าทำพร้อมกันไม่ได้ ให้ใช้ technology อื่นอย่างเช่น Queue ในการจัดการลำดับของการ Query แทน
  1. ให้เพิ่ม log และ load test ไว้ให้ดี
  • ปัญหานี้มีความยากอยู่ตรงที่จะไม่มีทางเจอได้ง่ายใน dev environment (เนื่องจาก concurrence อาจจะยังไม่มากพอที่จะเผชิญกับปัญหานี้)
  • ต้องลอง design การทำ load test ให้ดี เพื่อตรวจสอบปัญหานี้

Rerference

Related Post

Share on social media