ทรานแซกชันของ SQLite

• ในช่วงไม่กี่ปีที่ผ่านมา SQLite ได้รับความสนใจมากขึ้นในฐานะเอนจินฐานข้อมูล SQL แบบ in-process ที่เชื่อถือได้สูงสำหรับใช้เป็นแบ็กเอนด์ของโปรเซสเซิร์ฟเวอร์
• ความนิยมเพิ่มขึ้นอย่างรวดเร็ว แม้ว่านักพัฒนา SQLite จะออกมาเตือนค่อนข้างชัดเจนไม่ให้ใช้งาน SQLite ในลักษณะนี้ ซึ่งต่างจากบทบาทแบบดั้งเดิมของมันในฐานะแอปพลิเคชันฝั่งไคลเอนต์หรือเอดจ์

เหตุผลหลักที่ทำให้ฉันสนใจ SQLite:

• เรียบง่ายในเชิงแนวคิด: ลองนึกถึง B-tree ของแถว/ทูเพิลที่ถูกแบ่งพาร์ทิชันด้วยคีย์หลัก จากนั้นมีการทดสอบอย่างหนักเพื่อให้คงอยู่บนดิสก์ได้อย่างมั่นคง และเพิ่มชั้นการโต้ตอบผ่าน SQL เข้าไป
• สามารถวางกลยุทธ์สำรองข้อมูลที่ใช้งานได้จริงผ่าน Litestream โดยสำรอง WAL ไปยังตำแหน่งระยะไกลและทำ replication อย่างต่อเนื่อง สามารถกู้คืนอัตโนมัติเมื่อเริ่มต้นได้ด้วยคำสั่งง่าย ๆ
• ฉันยังคงชอบสภาพแวดล้อมการพัฒนาที่สมบูรณ์และสามารถรันแบบออฟไลน์ได้
• ด้วย file::memory: จึงทำงานแบบ in-memory ได้ ทำให้โค้ดทดสอบสามารถเริ่มและปิดอินสแตนซ์ได้ง่ายตามต้องการ

ข้อจำกัดแบบ single-writer

• นักพัฒนา SQLite ได้จัดทำเอกสารเรื่อง "ข้อจำกัดของ SQLite บนเซิร์ฟเวอร์" ไว้อย่างดี และวิเคราะห์การตั้งค่าฝั่งเซิร์ฟเวอร์ที่เหมาะสมที่สุดแล้ว แต่ข้อจำกัดที่เด่นชัดคือเว็บไซต์ที่มีทราฟฟิกสูง ซึ่งในที่นี้หมายถึงเว็บไซต์ที่มีการเขียนจำนวนมาก
• ในโหมด WAL นั้น SQLite ถูกออกแบบให้มี Writer ได้เพียงตัวเดียว นั่นคืออนุญาตให้มีทรานแซกชันเขียนพร้อมกันได้สูงสุด 1 รายการ และมีทรานแซกชันแบบอ่านอย่างเดียวได้หลายรายการ
• การออกแบบนี้ทำให้คอขวดของเว็บไซต์ที่มีทราฟฟิกสูงและเน้นการเขียน ไปอยู่ที่การจัดการ throughput ของ Writer เพียงตัวเดียวนั้น ซึ่งพาเรากลับไปสู่หนึ่งในองค์ประกอบแกนหลักของเทคโนโลยีสมัยใหม่

SQLite

• โดยพื้นฐานแล้ว SQLite ให้ทรานแซกชันแบบ isolated ระดับ SERIALIZABLE ที่เข้มงวด ซึ่งเป็นระดับการรับประกัน Isolation ที่แข็งแกร่งที่สุด
• ด้วยการใช้ Writer เพียงตัวเดียว SQLite จึงใช้รูปแบบของ pessimistic concurrency control ที่สามารถรับประกันได้ง่ายว่าข้อมูลพื้นฐานจะไม่ถูกเปลี่ยนระหว่างที่ทรานแซกชันเขียนกำลังดำเนินอยู่

Postgres

• แท้จริงแล้ว Postgres แตกต่างจากค่าเริ่มต้น SERIALIZABLE ที่กำหนดไว้ในมาตรฐาน SQL โดยเลือกใช้ READ COMMITTED ซึ่งผ่อนคลายกว่าแทน (แม้จะมี multiversion concurrency control ที่ซับซ้อนกว่ามากก็ตาม)
  • ความเข้มงวดที่ลดลงนี้ทำให้เกิดความเสี่ยงของ non-repeatable reads กล่าวคือ แม้อยู่ในทรานแซกชันเดียวกัน หากค่าถูกเปลี่ยนเบื้องหลังโดยทรานแซกชันอื่นที่ COMMITTED แล้ว การรันคำสั่งอ่านเดิมหลายครั้งก็อาจได้ผลลัพธ์ต่างกัน
  • เมื่อเลือกระดับ Isolation นี้ Postgres จึงเปิดความเป็นไปได้ที่ทรานแซกชันจะทำงานบนข้อมูลที่ล้าสมัย นักพัฒนาต้องตระหนักถึงจุดนี้ไว้เสมอ
• หากตั้งเป็น SERIALIZABLE Postgres จะใช้กลไก optimistic-concurrency control เพื่อติดตามข้อมูลที่เข้าถึงระหว่าง transaction และตรวจสอบก่อน commit ว่าข้อมูลนั้นไม่ถูกเปลี่ยนแปลง
  • Postgres ทำเช่นนี้โดยอิงกับ lock ระดับ row หรือระดับ page ตามทรานแซกชัน เพื่อจัดการการใช้หน่วยความจำ
  • แพตเทิร์นนี้เรียกว่า optimistic เพราะคาดหวังว่าข้อมูลพื้นฐานจะไม่ถูกเปลี่ยน กล่าวคือ เมื่อ transaction ทำการ monitor ข้อมูลละเอียดมากขึ้น โอกาสที่ข้อมูลนั้นจะถูกเปลี่ยนก่อน transaction commit ก็ยิ่งน้อยลง

FoundationDB

• ทรานแซกชันไม่ได้จำกัดอยู่แค่ฐานข้อมูลเชิงสัมพันธ์เท่านั้น โดยในระบบ distributed key-value store ก็มีการใช้ optimistic concurrency control เพื่อให้ได้การรับประกันแบบ SERIALIZABLE
• ตอนที่ NoSQL เริ่มปรากฏขึ้น distributed NoSQL store ที่มีการรับประกันแบบ ACID ยังไม่ใช่เรื่องทั่วไป FoundationDB จึงออก transaction manifesto เพื่อเน้นย้ำว่านักพัฒนาจะได้รับประโยชน์อย่างมากจากการรับประกันแบบ ACID
• FoundationDB ให้คำแนะนำทั้งเรื่องวิธีเขียนโค้ดสำหรับ optimistic concurrency control และเรื่องที่บางครั้งข้อมูลอาจถูกเปลี่ยนจากการชนกันของทรานแซกชันพร้อมกัน จนทำให้ทรานแซกชันต้องถูก retry อัตโนมัติ

Idempotence

• ทรานแซกชันแบบ idempotent คือทรานแซกชันที่ให้ผลเหมือนกัน ไม่ว่าจะ commit หนึ่งครั้งหรือสองครั้ง
• FoundationDB เสนอแพตเทิร์นสำหรับทำให้ทรานแซกชันเป็น idempotent เพื่อป้องกันปัญหาในกรณีที่ต้อง retry ทรานแซกชันหลายครั้งเพราะเกิด conflict

ดังนั้นเมื่อคำนึงถึงทั้งหมดนี้แล้ว SQLite มีตัวเลือกอะไรให้บ้าง?

BEGIN …

SQLite มีหลายวิธีให้นักพัฒนาระบุเอนจินว่าต้องการให้ทรานแซกชันทำงานอย่างไร ผ่านคีย์เวิร์ด IMMEDIATE, EXCLUSIVE, DEFERRED ซึ่งในโหมด WAL สามารถย่อให้เหลือความต่างหลักระหว่าง DEFERRED กับ IMMEDIATE ได้

DEFERRED

• ทรานแซกชันเริ่มต้นในโหมด READ ที่สามารถทำงานพร้อมกับทรานแซกชันอ่านหรือเขียนอื่นได้
• จะถูกอัปเกรดเป็นทรานแซกชัน READ-WRITE แบบที่บล็อกผู้อื่น ก็ต่อเมื่อมีการรันคำสั่งที่แก้ไขสถานะของ DB (INSERT, UPDATE, DELETE) เท่านั้น
• ระหว่างการอัปเกรด หาก DB ถูกล็อกโดยทรานแซกชันอื่นอยู่ จะคืนข้อผิดพลาด SQLITE_BUSY และไคลเอนต์ต้องจัดการเอง

IMMEDIATE

• ทรานแซกชันเริ่มต้นทันทีในโหมด READ-WRITE
• หากมีทรานแซกชันเขียนกำลังทำงานอยู่แล้ว จะคืน SQLITE_BUSY ทันที
• ไคลเอนต์ต้องเป็นผู้ตัดสินใจว่าจะจัดการอย่างไร

CONCURRENT

• SQLite มีจุดทดลองที่ขยับทรานแซกชันจากแบบ pessimistic ไปสู่ optimistic แบบจำกัด
• ที่บอกว่าจำกัด เพราะ optimistic locking นี้ทำงานในระดับ page ของ DB (ค่าเริ่มต้น 4096 bytes) ไม่ใช่ระดับแถว/ทูเพิล
• ในโหมด CONCURRENT นั้น SQLite สามารถเปิดใช้งานทรานแซกชันเขียนหลายรายการพร้อมกันได้ แต่ก่อน commit จะตรวจสอบว่า page ที่ทรานแซกชันเข้าถึงระหว่างการทำงานนั้น ไม่ได้ถูกเปลี่ยนแปลงไปตั้งแต่ทรานแซกชันเริ่มต้น
• หากไม่เกิด conflict การเปลี่ยนแปลงจะถูก commit แบบต่อเนื่องตามลำดับและยังคงได้การรับประกัน SERIALIZABLE ที่เข้มงวด หากเกิด conflict จะคืน SQLITE_BUSY

HC-Tree

• อีกหนึ่ง experimental branch ของ SQLite คือ [HC-Tree] ซึ่งเป็นงานที่ยังดำเนินอยู่และมีเป้าหมายเพื่อมอบ optimistic locking ระดับแถว/ทูเพิล หนึ่งในผลลัพธ์ที่น่าสนใจคือมันมีชุด benchmark ที่ยอดเยี่ยม ซึ่งแสดงข้อได้เปรียบด้านประสิทธิภาพของการออกแบบนี้เมื่อเทียบกับ branch BEGIN CONCURRENT

ถ้าลองนำแนวทางการทำ benchmark ของพวกเขามาใช้ แล้วรันกับตัวเลือกมาตรฐานดูล่ะ?

การทำ Benchmark

nUpdate=1, nScan=0

• ทรานแซกชันแบบ write-only นี้แสดงให้เห็นชัดถึงข้อดีของ IMMEDIATE เมื่อเทียบกับ DEFERRED การล็อกเกิดขึ้นทันที และทรานแซกชันไม่ได้รับผลจากต้นทุนของการอัปเกรด
• CONCURRENT แสดงให้เห็นถึง throughput ที่เพิ่มขึ้นเมื่อจำนวนเธรดมากขึ้นและ conflict เพิ่มขึ้น

nUpdate=10, nScan=0

• ตามคาด การทำ write แบบเป็นชุดช่วยอย่างมากต่อจำนวนแถวที่อัปเดตที่ 16 เธรด โดย CONCURRENT เพิ่มจากประมาณ ~12k/sec เป็น ~19k/sec
• ความต่างระหว่าง IMMEDIATE กับ DEFERRED มีความสำคัญน้อยลง เพราะต้นทุนของการอัปเดตเองสำคัญกว่าต้นทุนการอัปเกรดทรานแซกชัน

nUpdate=1, nScan=10

• ทรานแซกชันนี้ควรเผยให้เห็นจุดอ่อนของการล็อกแบบ CONCURRENT ระดับ page เพราะมี random read
• และยังแสดงให้เห็นทันทีว่าทำไมการใช้ IMMEDIATE กับทรานแซกชันที่จะอัปเดตข้อมูลจึงสำคัญกว่าการแบกรับต้นทุนการอัปเกรดของ DEFERRED
• สำหรับ CONCURRENT นั้น ผลลัพธ์นี้ค่อนข้างแข็งแกร่งมาก เพราะ conflict พื้นฐานแทบไม่ได้เพิ่มขึ้นมากนัก

nUpdate=0, nScan=10

• ทรานแซกชันแบบเป็นชุดที่อ่านอย่างเดียวนี้แสดงผลกระทบของ pessimistic concurrency control
• แสดงให้เห็นว่าทำไมเราไม่ควรตั้งค่า IMMEDIATE เป็นค่าเริ่มต้นสำหรับทุกทรานแซกชัน
• CONCURRENT เทียบกับ IMMEDIATE บ่งชี้ว่าการใช้โหมด CONCURRENT มีข้อเสียเล็กน้อย โดย "ประสิทธิภาพแย่ลงเล็กน้อยในทุกกรณี"
  • แต่ CONCURRENT ก็น่าจะเป็นตัวเลือกค่าเริ่มต้นที่ดี