RE: ODS concurrency question

Wanda The SERIAL column should be unique in itself. I am not sure whether the = mechanism by which the SERIAL value is generated will cause some = internal index lookup, in which case your problem may not be solved. = Maybe somebody in the newsgroup could throw some light on how SERIAL = values are generated within Informix? Replacing the unique index with a non-unique one could cause a = performance impact in some cases. Again YMMV. Since you already have the infrastructure in place to carry out these = experiments, I would be really interested in knowing if the concurrency = problem is solved by changing the index to non-unique. Also if there are = any performance impact. TIA Sujit Pal ---------- From: Wanda Beck[SMTP:wbeck@sybase.com] Sent: Wednesday, August 27, 1997 1:37 PM To: spal@scotch.den.csci.csc.com Cc: informix-list@rmy.emory.edu Subject: RE: ODS concurrency question Thank you, Sujit. Indeed, I do have a unique index on that table. I have a serial column that must be unique. Would I be better off constraining the column and making the index non-unique? Thanks again. -- wanda spal@scotch.den.csci.csc.com (Sujit Pal) on 08/27/97 01:01:39 PM To: wbeck@sybase.com ("'Wanda Beck'") @ smtp cc: informix-list@rmy.emory.edu ("'informix-list@rmy.emory.edu'") @ = smtp (bcc: Wanda Beck/SYBASE) Subject: RE: ODS concurrency question Wanda Maybe your example illustrates the "pessimistic locking" implemented by Informix as against the "optimistic locking" implemented by Oracle? Here Informix does not actually write the rows till the COMMIT WORK is encountered, so it will write the records for userA together = (consecutive rowids) and records for userB together. Informix assumes that the = INSERTs may be rolled back so it does not grab rowids when cacheing the inserted rows in buffer, whereas Oracle assumes that the rows may be committed = and if it is rolled back it will have holes in the rowids. However this will only explain the sequence of rows in your table. The strange locking behavior may be because of the current ISOLATION LEVEL. If it is anything other than DIRTY READ, then during the INSERT by userB, any unique indexes on the table need to be read to ensure that uniqueness is not violated. This will hang up userB until userA commits the transaction he is in. I would suggest keeping transactions more atomic to ensure concurrency. However I am just guessing about the possible causes of the problem, so I may be wrong. HTH Sujit Pal ---------- From: Wanda Beck[SMTP:wbeck@sybase.com] Sent: Wednesday, August 27, 1997 7:16 AM To: informix-list@rmy.emory.edu Subject: ODS concurrency question Hi, All! I'm running ODS 7.21 on HP, and I'm trying to understand what my = concurrent users can expect. I have a table on which I've set LOCK MODE to ROW. Users will be doing inserts. The behavior I'm seeing is that if one = user is inserting into the table, **no** other user can insert until the = first user is finished: USER1 USER2 ----------- ----------- BEGIN WORK insert A insert B insert C BEGIN WORK insert X insert Y COMMIT WORK insert D insert E insert F COMMIT WORK Depending on whether user2 does a SET LOCK MODE TO WAIT or not, s/he <<File: ATT01>>