Re: ODS concurrency question

Sujit Pal (spal@scotch.den.csci.csc.com) wrote: : 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. Informix does NOT implement "pessimistic locking" as described above. Informix DOES write the rows when each INSERT statement is executed (assuming you are not talking about an INSERT CURSOR). All rows for a single transaction for userA will NOT always be together. : 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. userB does not have to wait until userA has committed in order to verify index uniqueness. And DIRTY READ has no effect on this. : 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 I performed this exact test on 7.23 on Sun and 7.14 on HP (sorry, that's what I had easily available to me) and on neither version did user2 have to wait for user1 to commit. I don't know why you are seeing user2 wait. I'd suggest that you run a test where user1 inserts rows A-C, user2 sets lock mode to wait, then tries to insert X, and then from another window run an onstat -k and post that so we can see what user2 is waiting for. (Please don't email the onstat to me.) June ---- June Tong Informix Software ---- ---- Senior Consultant (415) 926-6140 ---- ---- International Support junet@informix.com ---- ---- Location-du-jour: Menlo Park ---- * * Standard disclaimers apply * - Please do not send me requests/questions by mail. When I have the knowledge - and time permits, I try to answer questions on comp.databases.informix, but - travel schedule, time, and volume make responding to personal requests - difficult and often slow. Please call your local Informix Technical Support - organization for assistance with technical issues.