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=20 the INSERT by userB, any unique indexes on the table need to be read=20 to ensure that uniqueness is not violated. This will hang up userB=20 until userA commits the transaction he is in. I would suggest keeping transactions more atomic to ensure=20 concurrency. However I am just guessing about the possible causes of=20 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 =