Re: [Patch] DB_DataObject and Postgres INSERT race condition obtaining last key

Justin Patrin wrote: > On 10/13/06, Matt Craig <mcraig@leadehealth.com> wrote: > >> DB_DataObject has a race condition in the PostgreSQL insert() + last >> inserted key code. >> >> Currently, the actual INSERT INTO statement is completed and then >> later in the code the >> sequence itself is checked to see what the last used value of the >> sequence was. In between >> those two steps it is very easy for another INSERT INTO statement for >> the same table in a >> different object to increment the sequence a second time before the >> first object even checks >> what the value of the sequence is. The end result is that both >> objects return the same last >> insert key. One of them incorrectly, of course. >> >> This code fixes the problem by recognizing autoincrement fields for >> PostgreSQL and selecting a >> next value from the sequence to insert, rather than letting the >> automatic nextval() function >> run. This ensures that the same key inserted into the table gets >> returned as the last inserted >> key. >> >> The final section of the patch is in the joinAdd function to allow >> auto joins where the tables >> in the join share a column name given as the parameter $joinCol. This >> is a feature, rather >> than a bug fix and could be dropped, but the pgsql INSERT code is, in >> my estimation, essential. >> >> This code has been tested and in production for about 3 months on both >> a Postgres 7.4.13 and >> 8.0 installation. >> > > Good catch, but I don't think DB_DataObject is the place to fix this. > If there is indeed a race condition (have you seen it actually > happen?) then this should probably be fixed in DB's sequence handling, > not in DB_DO. > Whoops, sorry, forgot to answer the other question. Yes. I have recreated this problem consistently and I have been the unforunate victim of this race condition on about 4 occasions, where I did not create the occurrence, causing personal data for some customers to overlap (fortunately in a non-identifiable way). matt