PostgreSQL Antipatterns: ต่อสู้กับฝูง "คนตาย"

ลักษณะเฉพาะของกลไกภายในของ PostgreSQL ช่วยให้ทำงานได้เร็วมากในบางสถานการณ์ และ "ไม่เร็วมาก" ในบางสถานการณ์ วันนี้เราจะเน้นไปที่ตัวอย่างคลาสสิกของความขัดแย้งระหว่างวิธีการทำงานของ DBMS และสิ่งที่นักพัฒนาทำกับมัน - UPDATE เทียบกับหลักการ MVCC.

เรื่องสั้นจาก บทความที่ดี:

เมื่อแถวถูกแก้ไขโดยคำสั่ง UPDATE จะมีการดำเนินการสองอย่าง: DELETE และ INSERT ใน เวอร์ชันปัจจุบันของสตริง xmax ถูกตั้งค่าเท่ากับจำนวนธุรกรรมที่ดำเนินการอัปเดต จากนั้นมันก็ถูกสร้างขึ้น новаяверсия เส้นเดียวกัน ค่า xmin ตรงกับค่า xmax ของเวอร์ชันก่อนหน้า

หลังจากธุรกรรมนี้เสร็จสิ้นไประยะหนึ่ง เวอร์ชันเก่าหรือเวอร์ชันใหม่จะขึ้นอยู่กับ COMMIT/ROOLBACKจะได้รับการยอมรับ "ตาย" (สิ่งอันดับที่ตายแล้ว) เมื่อผ่านไป VACUUM ตามตารางแล้วเคลียร์

แต่สิ่งนี้จะไม่เกิดขึ้นทันที แต่ปัญหาเกี่ยวกับ "คนตาย" สามารถรับได้อย่างรวดเร็ว - ด้วยการทำซ้ำหรือ การอัปเดตบันทึกจำนวนมาก ในโต๊ะขนาดใหญ่และอีกไม่นานคุณจะพบกับสถานการณ์เดียวกัน VACUUM จะไม่สามารถช่วยได้.

#1: ฉันชอบที่จะย้ายมัน

สมมติว่าวิธีการของคุณใช้ตรรกะทางธุรกิจ และทันใดนั้นก็พบว่าจำเป็นต้องอัปเดตฟิลด์ X ในบางบันทึก:

UPDATE tbl SET X = <newX> WHERE pk = $1;

จากนั้น เมื่อดำเนินการดำเนินไป ปรากฎว่าควรอัปเดตฟิลด์ Y ด้วย:

UPDATE tbl SET Y = <newY> WHERE pk = $1;

... แล้วก็ Z ด้วย - ทำไมต้องเสียเวลากับเรื่องมโนสาเร่?

UPDATE tbl SET Z = <newZ> WHERE pk = $1;

ขณะนี้เรามีบันทึกนี้กี่เวอร์ชันในฐานข้อมูล? ใช่แล้ว 4 ชิ้น! ในจำนวนนี้ มีหนึ่งรายการที่เกี่ยวข้อง และอีก 3 รายการจะต้องถูกล้างหลังจากคุณด้วย [อัตโนมัติ]VACUUM

อย่าทำแบบนี้! ใช้ อัปเดตทุกช่องในคำขอเดียว — เกือบทุกครั้งตรรกะของวิธีการสามารถเปลี่ยนแปลงได้ดังนี้:

UPDATE tbl SET X = <newX>, Y = <newY>, Z = <newZ> WHERE pk = $1;

#2: ใช้ความแตกต่างจากลุค!

ดังนั้นคุณยังต้องการ อัปเดตระเบียนจำนวนมากในตาราง (ระหว่างการใช้งานสคริปต์หรือตัวแปลง เป็นต้น) และสิ่งนี้ก็บินเข้าไปในสคริปต์:

UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2;

คำขอในรูปแบบโดยประมาณนี้เกิดขึ้นค่อนข้างบ่อยและเกือบทุกครั้งโดยไม่ต้องกรอกข้อมูลในฟิลด์ใหม่ที่ว่างเปล่า แต่เพื่อแก้ไขข้อผิดพลาดบางอย่างในข้อมูล ในขณะเดียวกันเธอก็เอง ความถูกต้องของข้อมูลที่มีอยู่จะไม่นำมาพิจารณาเลย - แต่เปล่าประโยชน์! นั่นคือบันทึกนั้นถูกเขียนใหม่แม้ว่าจะมีสิ่งที่ต้องการอยู่ก็ตาม - แต่ทำไม? มาแก้ไขกัน:

UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM <newX>;

หลายๆ คนไม่ทราบว่ามีโอเปอเรเตอร์ที่ยอดเยี่ยมเช่นนี้อยู่ ดังนั้นนี่คือข้อมูลสรุป IS DISTINCT FROM และตัวดำเนินการเชิงตรรกะอื่นๆ เพื่อช่วย:

... และอีกเล็กน้อยเกี่ยวกับการดำเนินงานบนคอมเพล็กซ์ ROW()-สำนวน:

#3: ฉันรู้จักคนรักของฉันด้วยการ... บล็อก

กำลังเปิดตัว กระบวนการคู่ขนานที่เหมือนกันสองกระบวนการซึ่งแต่ละรายการพยายามทำเครื่องหมายรายการว่า "อยู่ระหว่างดำเนินการ":

UPDATE tbl SET processing = TRUE WHERE pk = $1;

แม้ว่ากระบวนการเหล่านี้จะทำสิ่งต่าง ๆ โดยไม่แยกจากกันจริง ๆ แต่ภายใน ID เดียวกัน ลูกค้าคนที่สองจะถูก "ล็อค" ในคำขอนี้จนกว่าธุรกรรมแรกจะเสร็จสมบูรณ์

โซลูชัน #1: งานจะลดลงไปเป็นงานก่อนหน้า

ลองเพิ่มอีกครั้ง IS DISTINCT FROM:

UPDATE tbl SET processing = TRUE WHERE pk = $1 AND processing IS DISTINCT FROM TRUE;

ในแบบฟอร์มนี้ คำขอที่สองจะไม่เปลี่ยนแปลงสิ่งใดในฐานข้อมูล ทุกอย่างเป็นไปตามที่ควรจะเป็น ดังนั้นการบล็อกจะไม่เกิดขึ้น ต่อไป เราจะประมวลผลข้อเท็จจริงของการ "ไม่พบ" บันทึกในอัลกอริทึมที่ใช้

โซลูชัน #2: ล็อคที่ปรึกษา

หัวข้อใหญ่สำหรับบทความแยกต่างหากซึ่งคุณสามารถอ่านได้ วิธีการสมัครและ “คราด” ของการปิดกั้นการแนะนำ.

โซลูชัน #3: โทรโง่ๆ

แต่นี่คือสิ่งที่จะเกิดขึ้นกับคุณอย่างแน่นอน ทำงานพร้อมกันด้วยบันทึกเดียวกัน? หรือคุณทำผิดพลาดกับอัลกอริธึมในการเรียกตรรกะทางธุรกิจในฝั่งไคลเอ็นต์เป็นต้น? แล้วถ้าลองคิดดูล่ะ?..

ที่มา: will.com