Oct 25, 2023

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

มารู้จักเรื่องราวของการทำ Transaction และ Deadlock ผ่าน SQL กันว่ามันคืออะไร

smart_displayมี Video

#sql #database #transaction #deadlock #acid #commit #rollback #consistency #durability #isolation

สารบัญ

สามารถดู 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 หน้าตาประมาณนี้

Loading diagram…

คำถามคือ

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

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

Transaction และ Rollback

setup project

docker-compose.yml

plain

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 ตรงไปตรงมา

sql

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

sql

CALL createOrder(1, 1, 1);

sql

DROP PROCEDURE IF EXISTS createOrder;

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

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

กรณีที่ทำ Transaction

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

sql

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 ;

sql

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 เอาไว้)

sql

-- 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

sql

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

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

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

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

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

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

ทำพร้อมกันได้ หมายถึง ไม่มีปัญหา deadlock เนื่องจากใช้ Resource ไม่ชนกัน
ถ้าทำพร้อมกันไม่ได้ ให้ใช้ technology อื่นอย่างเช่น Queue ในการจัดการลำดับของการ Query แทน

ให้เพิ่ม log และ load test ไว้ให้ดี

ปัญหานี้มีความยากอยู่ตรงที่จะไม่มีทางเจอได้ง่ายใน dev environment (เนื่องจาก concurrence อาจจะยังไม่มากพอที่จะเผชิญกับปัญหานี้)
ต้องลอง design การทำ load test ให้ดี เพื่อตรวจสอบปัญหานี้

Rerference

forumComments (0)

lockLog in to join the conversation.

Loading comments…