ข้อพึงระวัง ในการใช้ PDO Transaction กับ auto_increment

ผมมีปัญหาเกี่ยวกับการใช้ transaction ซึ่งเดิมผมสร้าง table ไว้ 3 ตาราง เป็น MyISAM แต่ตอนหลังพบปัญหาข้อมูลไม่ถูกต้อง จึงเปลี่ยนเป็น InnoDB เพื่อทำ foreign key เชื่อมกัน

ปัญหาที่พบคือ บางครั้ง INSERT ข้อมูลไปแล้วทั้ง 3 ตารางพร้อมกัน ข้อมูลมันติดค้างอยู่ใน transaction ซักพักก็ rollback ออกมา ทั้งที่ได้ข้อมูลครบถ้วนทุกอย่าง (พอ insert ผมลอง select ดู ซึ่งเท่าที่คิดน่าจะไป select จากใน trancaction ออกมาแสดง ) ปัญหานี้ทำให้ field ที่เป็น PK {auto increment} เลขมันรันต่อไปแล้ว แต่ record กลับไม่มี

ปัญหาต่อมาคือ try INSERT ไปแล้ว ไม่แจ้ง error แต่ ข้อมูลไม่เข้า {select แล้วไม่มี } แต่พอ INSERT ซ้ำไป เลข auto increment มันกระโดดไป +2 +3 เลยครับ

ปล. ส่วนของการ update ไม่มีปัญหา

ซึ่งก็ได้คำแนะนำจาก

  • Sorrawut Kittikeereechaikun $sql ตัวเดียวนี่ 3 query เลยป่ะครับ หรือ insert อันอื่นอยู่ method อื่น?
  • Thanikul Sriuthis คงต้องแยก query ทีละอัน ภายใน transaction ครับ ลอง prepare แล้วไม่ผ่าน
  • Sorrawut Kittikeereechaikun ตกลงที่ comment ข้างล่างนั่นใช้ไม่ได้รึ จริงๆผมว่าถ้าแบบด้านล่างใช้ Auto Increment ต่อไปก็ได้ครับ ส่วนมากเลขโดดคือ Transaction ล่ม เลย rollback

และ

Natthaphon Chansama แก้ปัญหา auto_increment

1. เลิกใช้ auto_increment แล้วใช้วิธีดึง id ล่าสุดมา +1 แทน เนื่องจาก transaction มันจะ insert ข้อมูลจริงลงไปในตารางก่อน จากนั้นก็สร้าง transaction มาควบคุมอีกที แปลว่า auto_increment มันจะต้องรัน number ถัดไปแน่นอน

2. ถ้าจะใช้ auto_increment ต่อ หลังจาก rollback แล้ว ให้ query คำสั่ง

ALTER TABLE ชื่อตาราง auto_increment = 1

มันจะตั้งค่า auto_increment ให้เริ่มจาก id ล่าสุด +1 โดยอัตโนมัติ

1. เลิกใช้ auto_increment แล้วใช้วิธีดึง id ล่าสุดมา +1 แทน เนื่องจาก transaction มันจะ insert ข้อมูลจริงลงไปในตารางก่อน จากนั้นก็สร้าง transaction มาควบคุมอีกที แปลว่า auto_increment มันจะต้องรัน number ถัดไปแน่นอน

2. ถ้าจะใช้ auto_increment ต่อ หลังจาก rollback แล้ว ให้ query คำสั่ง

ALTER TABLE ชื่อตาราง auto_increment = 1

มันจะตั้งค่า auto_increment ให้เริ่มจาก id ล่าสุด +1 โดยอัตโนมัติ

Sorrawut Kittikeereechaikun ข้อ 1 ถ้าคนบังเอิญคน 2 คนใชพร้อมกัน จะมีคนนึง commit ไม่ได้ เพราะ pk ซ้ำรึเปล่าครับ ผมไม่แน่ใจว่ามันจะรอคนแรกทำให้จบก่อน หรือว่าคนที่ 2 จะพังเพราะ pk ซ้ำ ถ้าคนใช้เยอะๆผมว่าวิธีนี้ไม่ค่อยดีเท่าไหร่ (เท่าที่ผมรู้มาถ้า beginTransaction แล้ว insert คนอื่นจะยังไม่เห็นข้อมูลที่ insert จนกว่าจะ commit ไม่แน่ใจมีตั้งค่าแบบอื่นไหม)

Natthaphon Chansama ครับมีปัญหาแน่นอน แต่ถ้า query โดย get last id โดยใช้ query ไม่น่าจะซ้ำเพราะใช้ตัว sql gen ให้เลย แต่ถ้าทำในระดับ application คือมาดึงจากโค้ดแล้วมาใส่อีกทีนี่โอกาสซ้ำสูงมาก ส่วนเรื่อง transaction ใช่ครับมันไม่เห็นข้อมูล ยังไงซะ transaction จะนับ auto_increment เพิ่มให้โดยอัตโนมัติ เพื่อป้องกัน id ซ้ำนั่นแหละครับ คือจอง id ไว้ก่อน เดี๋ยวคนอื่นมาใช้ มันก็เลยต้องเพิ่มให้

Natthaphon Chansama โดยปกติแล้วข้อมูลที่เป็น transaction ไม่ควรใช้เป็น auto_increment อยู่แล้วครับ เนื่องจากมีโอกาสที่ข้อมูลจะเต็มได้ เค้าจะใช้วิธี gen id เอาครับ โดยการ gen แต่ละครั้งจะเป็นตัวเลขที่ไม่มีทางซ้ำแน่นอน แต่ไม่ใช่การรัน number เพิ่มไปเรื่อยๆ อันนี้ก็ขึ้นอยู่กับว่าใช้อัลกอริทึ่มแบบไหน

สรุป

Thanikul Sriuthis สรุปนี้ผมใช้ 3 ตารางเหมือนเดิม

– ทำ FK เชื่อมเหมือนเดิม a–>b, a–>c {c–>c1,c2,c3}

– ตัด auto_increment ทิ้งทั้ง 3 ตาราง

– ใช้เลขจาก MAX() แล้วใส่เองตามคำแนะนำครับ

– INSERT แบบมัดรวมเหมือนเดิม – ผ่าน!!

* เดิมผมใช้ MyISAM ไม่ได้เชื่อมตาราง ทำ PK เป็น auto_inc ทั้ง 3 ตาราง ใช้ INSERT มัดรวมเหมือนกัน มันเข้าได้ เพราะ MySIAM มันไม่สนใจอยู่แล้วว่าข้อมูลจะถูกหรือผิด เข้าแล้วก็แล้วกัน (ปัญหาเกิดตอนที่เทสระบบแล้วเผลอลบ record บางตารางออกไป)

** บน innoDB ถ้าใช้ auto_increment กับตารางเดียวปัญหานี้ผมไม่เคยพบเลย

– ทำ FK เชื่อมเหมือนเดิม a–>b, a–>c {c–>c1,c2,c3}

– ตัด auto_increment ทิ้งทั้ง 3 ตาราง

– ใช้เลขจาก MAX() แล้วใส่เองตามคำแนะนำครับ

– INSERT แบบมัดรวมเหมือนเดิม

– ผ่าน!!

* เดิมผมใช้ MyISAM ไม่ได้เชื่อมตาราง ทำ PK เป็น auto_inc ทั้ง 3 ตาราง ใช้ INSERT มัดรวมเหมือนกัน มันเข้าได้ เพราะ MySIAM มันไม่สนใจอยู่แล้วว่าข้อมูลจะถูกหรือผิด เข้าแล้วก็แล้วกัน (ปัญหาเกิดตอนที่เทสระบบแล้วเผลอลบ record บางตารางออกไป)

** บน innoDB ถ้าใช้ auto_increment กับตารางเดียวปัญหานี้ผมไม่เคยพบเลย

มาสรุปอีกรอบว่าผมทำอะไรบ้าง

หลังจากที่ผมลองปรับตารางใหม่ ทั้งแบบใช้ auto_increment และไม่มีนั้น พบว่าปัญหาจริงๆ ไม่ได้เกิดจาก transaction เพราะผมลองเอา SQL ไป query ดู

BEGIN;
SELECT (MAX(ath_member.mem_id)+1) INTO @maxid FROM ath_member;
 INSERT INTO ath_member_contact(mem_id) VALUES(@maxid);
 INSERT INTO ath_member_work(mem_id) VALUES(@maxid);
 INSERT INTO ath_member VALUES(@maxid, 'u', 'p', 'n', '', '', '0', '', 'e', 0,  NOW(), 0, 1, NULL );
COMMIT;

และ

BEGIN;
 INSERT INTO ath_member VALUES('', 'u', 'p', 'n', '', '', '0', '', 'e', 0,  NOW(), 0, 1, NULL );
 INSERT INTO ath_member_contact(mem_id) VALUES(LAST_INSERT_ID());
 INSERT INTO ath_member_work(mem_id) VALUES(LAST_INSERT_ID());
COMMIT;

ข้อมูลที่เข้าถูกต้อง ครบถ้วน เหมือนกันทั้งสองแบบ แต่เมื่อเอามาใส่ใน PHP PDO function ที่เขียนไว้นั้น พบว่ามันเกิด transaction lose คือเหมือนมันสร้าง transaction ขึ้นแต่ไม่มีการ commit;

ที่ผมทราบเพราะว่าผมลองไม่ใช่ transaction และ query แบบเดียวกัน พบว่าข้อมูลเข้ามาครบถ้วนถูกต้อง ไม่มีปัญหาช้าหรือค้างเลยแม้แต่น้อย

ดังนั้น… คงต้องมาปรับรูปแบบการทำงานของ function ของผมใหม่ ให้มันรองรับการ INSERT ข้อมูลหลายๆ record ซึ่งเดิมผมทำแบบ ATTR_EMULATE_PREPARES ถ้าไม่ใช้งาน transaction มันก็ autocommit ไปตามปกติ