Re: The twin problem (was Re: HELP - Index Locks are creating DEADLOCKS)

>>>>> On 11 Nov 92 20:35:38 GMT, cortesi@informix.com (David Cortesi) said: > In article <OLIVER.92Nov10221419@snoopy.infix.de> oliver@infix.de (Oliver Okrongli) writes: >> [...] >>We are also facing similar locking problems which may or may not be >>related to yours (error numbers tend to vary between different >>releases). > Purely a side issue, but this is in general not true. Error > numbers are very stable. > [...] > But as long as the error cause is the same, the number is the same. While we all are subject to human error please let me prove that I was not totally wrong in this case. In I-SE 4.0 the error number for "statement not available" was -515. This changed to -554 in I-SE 4.1 whithout any obvious reason. Since our code is designed to work with I-SE and I-OnLine whithout recompilation one of the first things in database initialization is: $set isolation to dirty read; if (!db_stmt_not_available()) db_error("set isolation to dirty read"); To work with both releases the definition of db_stmt_not_available had to be changed: #define db_stmt_not_available() (db_code() == -515 || db_code() == -554) >>Our number 1 problem is: when a row gets locked sometimes >>rows next to it are locked also. > >[... excellent description of locking methods deleted ...] > >>We do consider these effects extremely undesirable because they lead >>to unpredictable application behaviour. We are currently not aware of >>any workaround. > There is no basic difference between a lock conflict based on the twin > problem and one caused by two processes trying to update the same row. > Both result from two processes attempting to seize the same resource at > the same time. > It seems to me in my ignorance that this lock conflict could be > handled exactly like a lock conflict from any other source. > You could either > * run with SET LOCK MODE TO WAIT > or > * when any operation discovers a lock conflict, roll > the transaction back and retry it. > Is this not so? Thank you for asking this question. In our applications we lock rows just in those tables necessary for some kind of transaction. Any tables containing dependent objects are then updated without further checking for locks because application logic assumes everyone else conforms to this protocol. An example: We have a customer table. Each customer may have a variable number of contact persons. Contact persons of a customer may be modified only if access to this customer is granted. Any lock detected while updating a contact person is considered an application error. So there are lock conflicts we do expect (such is the case with the customer table) and those we don't. SET LOCK MODE TO WAIT is not an option because we lock objects for an extended period of time while they are being updated on a terminal. This is achieved by declaring a for update cursor every time a modification form is being processed. This way we prevent conflicting modifications on concurrently operating terminals (the famous lost update problem). Rolling back transactions is also not possible in our case because we use some form of nested transations (nesting counter implemented in C code) to allow nested windowed operations on screen. This way it is possible to enter an order for a new customer and add that new customer on the fly without leaving or interrupting the order entry. Consequently any application module has no guarantee that a transaction has not been begun BEFORE it was called. Transactions are rolled back ONLY in case of emergency to ensure data integrity. Of course application logic is designed not to open very long transactions so users do not loose too much work. I hope I managed to point out the severity of the problem. There IS substantial difference between expected an unexpected locks even if you consider the problem 'basically equivalent'. We are pleased to hear these things will be corrected in 6.0 release. But when will it start shipping - for example on i486 multiprocessors such as NCR3550 using SVR4.x? -- Oliver Okrongli infix Software-Systeme GmbH Phone +49 531 238090 Rebenring 33 Fax +49 531 3801152 oliver@infix.de D-W-3300 Braunschweig F.R. Germany