SQL ขั้นสูง (Window Functions & Optimization)

ทฤษฎีและกลไกการทำงาน (How it works): เมื่อระบบฐานข้อมูลมีหลายโปรเซสพยายามอ่านและเขียนข้อมูลพร้อมกัน (Concurrency) จะเกิดปัญหาความสับสนในสเตทข้อมูลซึ่งแบ่งออกเป็น 4 รูปแบบหลัก: - **Dirty Read**: ธุรกรรม A อ่านข้อมูลที่แก้ไขโดยธุรกรรม B ที่ยังไม่ได้กดยืนยันบันทึกสำเร็จ (Commit) ต่อมาหากธุรกรรม B กดยกเลิก (Rollback) ข้อมูลที่ธุรกรรม A นำไปใช้วิเคราะห์จะถือเป็นค่าขยะไม่มีอยู่จริง - **Non-repeatable Read**: ธุรกรรม A อ่านข้อมูลแถวหนึ่ง จากนั้นธุรกรรม B ทำการแก้ไขข้อมูลแถวนั้นและ Commit เมื่อธุรกรรม A สแกนแถวเดิมซ้ำในรอบงานเดิม กลับพบว่าฟิลด์ข้อมูลเปลี่ยนไป - **Phantom Read**: ธุรกรรม A สแกนข้อมูลช่วงคีย์ (Range Query) จากนั้นธุรกรรม B แทรกแถวใหม่เข้ามาในช่วงนั้นและ Commit เมื่อธุรกรรม A คิวรีซ้ำพบว่ามีแถวใหม่โผล่ขึ้นมาเสมือนร่างเงา - **Write Skew**: เกิดขึ้นในระบบที่สองธุรกรรมทำงานขนานกันโดยอ่านเงื่อนไขร่วมกัน (เช่น เช็คว่ายอดเงินในบัญชีรวมห้ามต่ำกว่า 0) แต่เลือกอัปเดตคนละแถว ทำให้เมื่อบันทึกผล ยอดรวมรวมกลายเป็นติดลบ ขัดแย้งกับสัจพจน์ของระบบ

-- SQL simulation demonstrating a potential Write Skew anomaly under Repeatable Read
-- Transaction 1: Check balance and withdraw from Account A
BEGIN;
SELECT SUM(balance) FROM user_accounts WHERE user_id = 42; -- Returns 100
UPDATE user_accounts SET balance = balance - 80 WHERE account_id = 'A';
COMMIT;

-- Transaction 2 (Runs concurrently): Check balance and withdraw from Account B
BEGIN;
SELECT SUM(balance) FROM user_accounts WHERE user_id = 42; -- Returns 100
UPDATE user_accounts SET balance = balance - 80 WHERE account_id = 'B';
COMMIT;
-- Result: Total balance becomes -60, violating consistency constraints!
                    

Use Case ในชีวิตจริง (Real-world Scenario): ระบบจองตั๋วภาพยนตร์หรือตั๋วเครื่องบิน หากไม่มีการจัดการ Concurrency ที่ดี ผู้ใช้งานสองคนอาจเห็นที่นั่งว่างตรงกันและทำการกดชำระเงินเข้ามาพร้อมกัน ส่งผลให้เกิดการจองทับซ้อนในที่นั่งเดียวกัน (Double Booking)

ข้อควรระวังและวิธีแก้ (Pitfalls & Mitigations): การจำกัดสิทธิ์ Concurrency ในระดับสูงเกินไปจะทำให้ประสิทธิภาพระบบงานช้าลง แก้ไขโดยสืบค้นและเลือกระดับการแยกธุรกรรม (Isolation Levels) ให้เหมาะสมกับงาน หรือใช้การควบคุมความถูกต้องร่วมกันในระดับแอปพลิเคชัน

ทฤษฎีและกลไกการทำงาน (How it works): มาตรฐาน SQL-92 กำหนดระดับการแยกธุรกรรมออกเป็น 4 ระดับเพื่อแก้ไขปัญหา Anomalies โดยแลกคืนด้วยระดับประสิทธิภาพประมวลผล: 1. **Read Uncommitted**: ยอมรับสิทธิ์ต่ำสุด เกิดข้อผิดพลาดได้ทุกแบบ แต่ความเร็วเขียนสูง 2. **Read Committed**: ป้องกัน Dirty Read โดยระบบจะยอมให้อ่านเฉพาะข้อมูลที่ยืนยันบันทึกสำเร็จแล้วเท่านั้น (ค่าเริ่มต้นของ PostgreSQL/SQL Server) 3. **Repeatable Read**: ป้องกัน Non-repeatable Read โดยล็อกให้ข้อมูลที่อ่านในธุรกรรมมีค่าคงที่เสมอจนกว่าจะจบงาน 4. **Serializable**: ระดับสูงสุด ป้องกันปัญหารวมถึง Write Skew โดยเครื่องจะจำลองประมวลผลทีละธุรกรรมเรียงลำดับกันเสมือนรันบน Single Thread

-- Explicitly setting transaction isolation levels in PostgreSQL
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- All queries inside this block will read from the same snapshot snapshot
SELECT balance FROM user_accounts WHERE account_id = 'A';
COMMIT;
                    

Use Case ในชีวิตจริง (Real-world Scenario): ระบบรายงานการเงินช่วงสิ้นวันของธนาคารต้องการความถูกต้อง 100% จึงต้องกำหนดระดับการแยกธุรกรรมไว้ที่ `SERIALIZABLE` เพื่อป้องกันไม่ให้ข้อมูลสถิติตัวเลขเคลื่อนระหว่างรันคำสั่งรวมยอด

ข้อควรระวังและวิธีแก้ (Pitfalls & Mitigations): การตั้งค่าระดับ `SERIALIZABLE` อาจนำไปสู่ข้อผิดพลาด **Serialization Failure** (Error code 40001) เมื่อเอนจิ้นตรวจจับพบทรานแซกชันชนกันและเลือกยกเลิกตัวหลัง แก้ไขโดยต้องเขียนตรรกะในระดับโค้ดแอปพลิเคชันให้ทำการส่งรันซ้ำอัตโนมัติ (Retry Loop) เมื่อได้รับโค้ดข้อผิดพลาดดังกล่าว

ทฤษฎีและกลไกการทำงาน (How it works): เพื่อหลีกเลี่ยงการล็อกตารางส่งผลให้ระบบงานหยุดชะงัก ฐานข้อมูลสมัยใหม่นำระบบ **MVCC** มาใช้ ภายใต้กลไกนี้การอ่านจะไม่บล็อกการเขียนและการเขียนจะไม่บล็อกการอ่าน ในระดับฟิสิคัลของ PostgreSQL ทุกแถวข้อมูลจะมีแฮดเดอร์ลับระบุหมายเลขธุรกรรมควบคุม: - `xmin`: หมายเลข ID ของทรานแซกชันที่ทำการบันทึกแถวข้อมูลนี้ขึ้นมา - `xmax`: หมายเลข ID ของทรานแซกชันที่ทำการลบหรืออัปเดตแถวข้อมูลนี้ (หากยังไม่ลบค่าจะเป็น 0) เมื่อมีการเขียนแก้ไข ฐานข้อมูลจะไม่เขียนทับที่เดิม แต่จะสร้างเวอร์ชันใหม่ของแถวนั้นขึ้นมาและระบุ `xmin` ตัวแปรตัวใหม่ พร้อมระบุ `xmax` ลงในเวอร์ชันเก่า ระบบจะตัดสินใจแสดงผลเวอร์ชันที่ตรงกับสแนปช็อตของทรานแซกชันที่สั่งคิวรี

-- Querying hidden MVCC metadata columns in PostgreSQL
SELECT xmin, xmax, account_id, balance 
FROM user_accounts 
WHERE account_id = 'A';
                    

Use Case ในชีวิตจริง (Real-world Scenario): ระบบอีคอมเมิร์ซที่มีลูกค้าเข้ามาเปิดดูรายชื่อสินค้าล้านคิวรี่ต่อวินาที (Read heavy) ในขณะที่ฝ่ายคลังสินค้าทำการอัปเดตจำนวนสต็อกพร้อมกัน (Write) ระบบ MVCC ช่วยให้หน้าเว็บแสดงผลปกติโดยไม่ต้องรอเครื่องรันบันทึกสต็อกเสร็จสิ้น

ข้อควรระวังและวิธีแก้ (Pitfalls & Mitigations): เนื่องจากเวอร์ชันเก่าของแถวข้อมูลไม่ถูกลบทิ้งทันที จะเกิดสภาวะ **Table Bloat** (ขยะค้างในตารางดิสก์) ส่งผลให้คิวรีอ่านช้าลงมหาศาล แก้ไขโดยการตั้งระบบทำความสะอาดอัตโนมัติ **Autovacuum** ในเบื้องหลังของฐานข้อมูลเพื่อเคลียร์เนื้อที่ดิสก์

ทฤษฎีและกลไกการทำงาน (How it works): นอกจากการพึ่งพา Isolation Level ของเอนจิ้น เราสามารถระบุการล็อกแถวข้อมูลได้เองแบบชัดเจน (Pessimistic Locking) เพื่อป้องกันไม่ให้ธุรกรรมอื่นเข้ามาแก้ไขระหว่างกลางงานหลัก: - `SELECT ... FOR UPDATE`: ทำการล็อกแถวที่คิวรีเจอด้านเขียน ป้องกันไม่ให้ใครมาสั่งปรับแก้หรือล็อกทับจนจบธุรกรรม - `SELECT ... FOR SHARE`: ล็อกเพื่อขอสิทธิ์อ่านร่วมกัน ป้องกันการแก้ไขแต่ยังอนุญาตให้อ่านพร้อมกัน หากสองธุรกรรมถือสิทธิ์ล็อกคนละฝั่งและพยายามแย่งเข้าล็อกของอีกฝ่ายพร้อมกันจะเกิดสภาวะติดหล่มเดดล็อก (**Deadlock**)

-- Acquiring row lock and handling locked tables gracefully
BEGIN;
-- Select and lock target row, abort immediately if someone else holds the lock
SELECT balance 
FROM user_accounts 
WHERE account_id = 'A' 
FOR UPDATE NOWAIT;

UPDATE user_accounts SET balance = balance - 50 WHERE account_id = 'A';
COMMIT;
                    

Use Case ในชีวิตจริง (Real-world Scenario): ระบบตัดแต้มสะสมของลูกค้า โดยแอปพลิเคชันต้องรันคำสั่งล็อกข้อมูลยอดแต้มด้วย `FOR UPDATE` ก่อนคำนวณแต้มสะสมใหม่ เพื่อหลีกเลี่ยงยอดสะสมคลาดเคลื่อนหากผู้ใช้กดสิทธิ์ใช้งานพร้อมกันในเครื่องหลายเครื่อง

ข้อควรระวังและวิธีแก้ (Pitfalls & Mitigations): การล็อกด้วย `FOR UPDATE` อาจทำให้โปรเซสอื่นค้างรอคิวแบบไม่มีวันสิ้นสุดหากธุรกรรมหลักใช้เวลานาน แก้ไขโดยระบุแฟล็กจำกัดคิว `FOR UPDATE SKIP LOCKED` (ข้ามแถวที่ล็อกอยู่) หรือ `FOR UPDATE NOWAIT` (คืนค่าเออเร่อทันทีแทนการรอคิว)