skip to content

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

/ 6 min read

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

USERSintidPKstringnameintpointORDERSintidPKintuser_idFKstringproduct_nameintquantityfloattotal_pricedatetimecreated_atPRODUCTSintidPKstringnameintquantityfloatpricehascontains

คำถามคือ

  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