MySQL 5.1 รองรับการล็อคระดับตารางสำหรับตาราง MyISAM และ MEMORY การล็อคระดับหน้าสำหรับตาราง BDB และ InnoDB ตาราง การล็อคระดับแถว ในหลายกรณี เป็นไปได้ที่จะคาดเดาตามการฝึกอบรมว่าประเภทล็อคใดดีที่สุดสำหรับการใช้งาน แต่โดยทั่วไปแล้วเป็นการยากที่จะบอกได้ว่าประเภทล็อคที่กำหนดนั้นดีกว่าประเภทอื่นหรือไม่ ทุกอย่างขึ้นอยู่กับการใช้งาน ส่วนต่าง ๆ ของการใช้งานอาจต้องใช้ประเภทการล็อคที่แตกต่างกัน เพื่อตรวจสอบว่าคุณต้องการใช้เครื่องมือจัดเก็บข้อมูลการล็อกระดับแถวหรือไม่ คุณควรดูว่าแอปพลิเคชันของคุณทำอะไร และคำสั่ง select และ update ที่ใช้ผสมกัน ตัวอย่างเช่น เว็บแอปพลิเคชันส่วนใหญ่ทำการเลือกหลายครั้งและลบไม่กี่ครั้ง อัปเดตเฉพาะค่าคีย์ และแทรกตารางเฉพาะเพียงไม่กี่รายการเท่านั้น การตั้งค่า MySQL MyISAM ขั้นพื้นฐานได้รับการปรับแต่งอย่างดี
ใน MySQL สำหรับกลไกการจัดเก็บข้อมูลที่ใช้การล็อคระดับตาราง จะไม่มีการหยุดชะงักเมื่อตารางถูกล็อค ซึ่งได้รับการจัดการโดยการร้องขอการล็อกที่จำเป็นทั้งหมดทันทีที่จุดเริ่มต้นของแบบสอบถาม และล็อกตารางในลำดับเดียวกันเสมอ
สำหรับการเขียน วิธีการล็อคตารางที่ใช้โดย MySQL จะทำงานดังนี้:
◆ ถ้าไม่มีการล็อคบนโต๊ะ ให้ใส่ล็อคการเขียนไว้
◆ มิฉะนั้น ใส่คำขอล็อคในคิวล็อคการเขียน
สำหรับการอ่าน วิธีการล็อคที่ใช้โดย MySQL จะทำงานดังนี้:
◆หากไม่มีการล็อคการเขียนบนโต๊ะ ให้ใส่ล็อคการอ่านไว้
◆ มิฉะนั้น ใส่คำขอล็อคในคิวล็อคการอ่าน
เมื่อปลดล็อค สามารถรับการล็อคได้โดยเธรดในคิวล็อคการเขียน และจากนั้นโดยเธรดในคิวล็อคการอ่าน
ซึ่งหมายความว่าหากคุณมีการอัปเดตจำนวนมากในตาราง คำสั่ง SELECT จะรอจนกว่าจะไม่มีการอัพเดตอีกต่อไป
หากคำสั่ง INSERT ไม่ขัดแย้งกัน คุณสามารถผสมคำสั่ง INSERT และ SELECT แบบขนานสำหรับตาราง MyISAM ได้อย่างอิสระโดยไม่ต้องล็อก
InnoDB ใช้การล็อคแถวและ BDB ใช้การล็อคหน้า ด้วยกลไกการจัดเก็บทั้งสองแบบ การหยุดชะงักจึงเกิดขึ้นได้ นี่เป็นเพราะว่า InnoDB จะได้รับการล็อคแถวโดยอัตโนมัติ และ BDB จะได้รับการล็อคหน้าในระหว่างการประมวลผลคำสั่ง SQL แทนที่จะเป็นเมื่อธุรกรรมเริ่มต้นขึ้น
ข้อดีของการล็อกระดับแถว:
· มีข้อขัดแย้งในการล็อกเพียงเล็กน้อยเท่านั้นเมื่อมีการเข้าถึงแถวที่แตกต่างกันในหลายเธรด
· ย้อนกลับโดยมีการเปลี่ยนแปลงเพียงเล็กน้อยเท่านั้น
· แถวเดียวสามารถล็อคได้เป็นเวลานาน
ข้อเสียของการล็อกระดับแถว:
· ใช้หน่วยความจำมากกว่าการล็อกระดับเพจหรือระดับตาราง
· เมื่อใช้กับส่วนใหญ่ของตาราง ซึ่งช้ากว่าการล็อกระดับหน้าหรือระดับตาราง เนื่องจากคุณต้องได้รับการล็อกเพิ่มเติม
· หากคุณดำเนินการ GROUP BY กับข้อมูลส่วนใหญ่ของคุณบ่อยครั้งหรือต้องสแกนทั้งตารางบ่อยครั้ง การล็อคจะช้ากว่าการล็อคอื่นๆ อย่างมาก
· ด้วยการล็อคระดับสูง คุณสามารถปรับขนาดแอปพลิเคชันของคุณได้อย่างง่ายดายโดยรองรับการล็อคประเภทต่างๆ เนื่องจากต้นทุนการล็อคน้อยกว่าการล็อคระดับแถว
การล็อกตารางจะมีความสำคัญเหนือกว่าการล็อกระดับเพจหรือระดับแถว เมื่อ:
· คำสั่งส่วนใหญ่ในตารางใช้ในการอ่าน
· อ่านและอัปเดตด้วยคีย์ที่เข้มงวด คุณสามารถอัปเดตหรือลบแถวที่สามารถแยกออกมาได้ด้วยคีย์การอ่านอันเดียว:
• อัปเดต tbl_name SET column = value WHERE Unique_key_col = key_value;
• DELETE FROM tbl_name WHERE Unique_key_col = key_value;
· SELECT รวมกับ คำสั่ง INSERT แบบขนานและคำสั่ง UPDATE หรือ DELETE น้อยมาก
· มีการสแกนหรือการดำเนินการ GROUP BY จำนวนมากบนทั้งตารางโดยไม่มีการดำเนินการเขียนใดๆ
ตัวเลือกที่แตกต่างจากการล็อกระดับแถวหรือระดับเพจ:
· การกำหนดเวอร์ชัน (เช่น เทคนิคที่ใช้ใน MySQL สำหรับการแทรกแบบขนาน) ซึ่งสามารถมีการดำเนินการเขียนหนึ่งครั้งและการดำเนินการอ่านจำนวนมากในเวลาเดียวกัน ซึ่งหมายความว่าฐานข้อมูลหรือตารางรองรับมุมมองข้อมูลที่แตกต่างกัน ขึ้นอยู่กับว่าการเข้าถึงเริ่มต้นเมื่อใด คำทั่วไปอื่นๆ ได้แก่ "การติดตามเวลา" "คัดลอกเมื่อเขียน" หรือ "คัดลอกตามความต้องการ"
· การจำลองแบบตามความต้องการมีความสำคัญเหนือกว่าการล็อกระดับเพจหรือระดับแถวในหลายกรณี อย่างไรก็ตาม ในกรณีที่เลวร้ายที่สุด อาจใช้หน่วยความจำมากกว่าการใช้การล็อคแบบปกติ
· นอกเหนือจากการล็อกระดับแถวแล้ว คุณยังสามารถใช้การล็อกระดับแอปพลิเคชัน เช่น GET_LOCK() และ RELEASE_LOCK() ใน MySQL ได้ สิ่งเหล่านี้คือคำแนะนำในการล็อก และจะใช้ได้กับแอปพลิเคชันที่ทำงานได้ดีเท่านั้น
เพื่อให้บรรลุความเร็วการล็อคสูงสุด MySQL ใช้การล็อคตาราง (แทนการล็อคหน้า แถว หรือคอลัมน์) สำหรับกลไกการจัดเก็บข้อมูลทั้งหมด ยกเว้น InnoDB และ BDB สำหรับตาราง InnoDB และ BDB นั้น MySQL จะใช้การล็อกตารางเฉพาะเมื่อคุณล็อกตารางอย่างชัดเจนด้วย LOCK TABLES เท่านั้น หากคุณไม่ได้ใช้ LOCK TABLES เนื่องจาก InnoDB ใช้การล็อกระดับแถวอัตโนมัติและ BDB ใช้การล็อกระดับเพจเพื่อให้แน่ใจว่ามีการแยกธุรกรรม
แต่สำหรับตารางขนาดใหญ่ การล็อกตารางจะดีกว่าการล็อกแถวสำหรับแอปพลิเคชันส่วนใหญ่ แต่ก็มีข้อเสียบางประการ การล็อกตารางช่วยให้เธรดจำนวนมากสามารถอ่านจากตารางได้พร้อม ๆ กัน แต่หากเธรดต้องการเขียนลงในตาราง เธรดนั้นจะต้องได้รับสิทธิ์การเข้าถึงแบบเอกสิทธิ์เฉพาะบุคคลก่อน ในระหว่างการอัปเดต เธรดอื่นๆ ทั้งหมดที่ต้องการเข้าถึงตารางต้องรอจนกว่าการอัปเดตจะเสร็จสมบูรณ์
โดยทั่วไปการอัปเดตตารางถือว่ามีความสำคัญมากกว่าการดึงข้อมูลตาราง ดังนั้นจึงได้รับลำดับความสำคัญที่สูงกว่า สิ่งนี้ควรให้แน่ใจว่ากิจกรรมที่อัปเดตตารางจะไม่อดอาหาร แม้ว่าจะมีกิจกรรม SELECT จำนวนมากบนโต๊ะก็ตาม
การล็อกตารางอาจทำให้เกิดปัญหาในสถานการณ์ เช่น เมื่อเธรดกำลังรอเนื่องจากฮาร์ดดิสก์เต็ม และต้องมีเนื้อที่ว่างก่อนที่เธรดจะสามารถประมวลผลได้ ในกรณีนี้ เธรดทั้งหมดที่ต้องการเข้าถึงตารางที่เป็นปัญหาจะยังอยู่ในสถานะรอจนกว่าจะมีเนื้อที่ว่างบนฮาร์ดดิสก์เพิ่มขึ้น
การล็อกตารางยังมีปัญหาในสถานการณ์ต่อไปนี้:
· ลูกค้าส่งแบบสอบถามที่ใช้เวลานาน
· จากนั้น ไคลเอ็นต์อื่นจะอัปเดตตารางเดียวกัน ลูกค้าต้องรอจนกว่า SELECT จะเสร็จสิ้น
· ไคลเอนต์อื่นออกคำสั่ง SELECT อื่นในตารางเดียวกัน เนื่องจาก UPDATE มีลำดับความสำคัญสูงกว่า SELECT คำสั่ง SELECT จึงรอให้ UPDATE ดำเนินการให้เสร็จสิ้น และรอให้ SELECT คำสั่งแรกดำเนินการให้เสร็จสิ้น
วิธีการบางอย่างจะอธิบายไว้ด้านล่างเพื่อหลีกเลี่ยงหรือลดการโต้แย้งที่เกิดจากการล็อกตาราง:
· พยายามทำให้คำสั่ง SELECT ทำงานเร็วขึ้น คุณอาจต้องสร้างตารางสรุปเพื่อทำสิ่งนี้
· เริ่ม mysqld ด้วย --low-priority-updates ซึ่งจะทำให้คำสั่งทั้งหมดที่อัปเดต (แก้ไข) ตารางมีลำดับความสำคัญต่ำกว่าคำสั่ง SELECT ในกรณีนี้ คำสั่ง SELECT ที่สองในสถานการณ์ก่อนหน้านี้จะถูกดำเนินการก่อนคำสั่ง UPDATE โดยไม่ต้องรอให้ SELECT แรกเสร็จสิ้น
· คุณสามารถใช้คำสั่ง SET LOW_PRIORITY_UPDATES=1 เพื่อระบุว่าการอัพเดตทั้งหมดในการเชื่อมต่อเฉพาะควรใช้ลำดับความสำคัญต่ำ
· คุณสามารถใช้แอ็ตทริบิวต์ LOW_PRIORITY เพื่อกำหนดลำดับความสำคัญที่ต่ำกว่าให้กับคำสั่ง INSERT, UPDATE หรือ DELETE
· คุณสามารถใช้แอตทริบิวต์ HIGH_PRIORITY เพื่อให้คำสั่ง SELECT เฉพาะมีลำดับความสำคัญที่สูงกว่าได้
· เริ่มต้น mysqld โดยการระบุค่าต่ำสำหรับตัวแปรระบบ max_write_lock_count เพื่อบังคับให้ MySQL เพิ่มลำดับความสำคัญของคำสั่ง SELECT ทั้งหมดที่รอตารางเป็นการชั่วคราว หลังจากที่แทรกตามจำนวนที่ระบุเสร็จสิ้นแล้ว ซึ่งช่วยให้สามารถล็อคการอ่านได้หลังจากการล็อค WRITE ตามจำนวนที่กำหนด
· หากคุณมีปัญหากับ INSERT ร่วมกับ SELECT ให้เปลี่ยนไปใช้ตาราง MyISAM ใหม่ เนื่องจากรองรับ SELECT และ INSERT พร้อมกัน
· หากคุณผสมส่วนแทรกและการลบในตารางเดียวกัน INSERT DELAYED จะช่วยได้มาก
· หากคุณมีปัญหาในการผสมคำสั่ง SELECT และ DELETE บนตารางเดียวกัน ตัวเลือก LIMIT ของ DELETE สามารถช่วยได้
· การใช้ SQL_BUFFER_RESULT สำหรับคำสั่ง SELECT สามารถช่วยลดเวลาการล็อคตารางได้
· รหัสล็อคใน mysys/thr_lock.c สามารถเปลี่ยนเพื่อใช้คิวเดียวได้ ในกรณีนี้ การล็อกการเขียนและการล็อกการอ่านจะมีลำดับความสำคัญเท่ากัน ซึ่งอาจมีประโยชน์สำหรับบางแอปพลิเคชัน
ต่อไปนี้เป็นเคล็ดลับบางส่วนที่เกี่ยวข้องกับการล็อกตารางใน MySQL:
· หากคุณไม่ผสมการอัปเดตเข้ากับตัวเลือกที่จำเป็นต้องตรวจสอบหลายแถวในตารางเดียวกัน คุณสามารถดำเนินการแบบขนานได้
· คุณสามารถใช้ LOCK TABLES เพื่อปรับปรุงความเร็วได้ เนื่องจากการอัพเดตจำนวนมากในการล็อคเดียวจะเร็วกว่าการอัปเดตที่ไม่มีการล็อคมาก การแยกเนื้อหาของตารางออกเป็นหลายๆ ตารางก็สามารถช่วยได้เช่นกัน
· หากคุณประสบปัญหาด้านความเร็วเมื่อล็อกตารางใน MySQL คุณสามารถแปลงตารางเป็นตาราง InnoDB หรือ BDB เพื่อปรับปรุงประสิทธิภาพได้