Deadlock problem...

I have some serious deadlock problems (so it seems). We use Informix Online 5.0. The programming language is Hyperscript Tools 1.0. The machine is a Sparc Ultra2, with 7 users. There is a lot of transactions. Often, we (the user) got this error: SQL error (-346): could not update a row in the table ISAM error (-143): deadlock detected ---------------------------------------------------------------------------------- Here is part of the code that generate this error: Declare immediate cursor "CR_Ferm_Appel" FOR "SELECT rowid", "From bloc_radio_carte", "Where no_carte = ?" Open cursor "CR_Ferm_Appel" Using No_Cart Fetch "CR_Ferm_Appel" Into VL_Rowid Close cursor "CR_Ferm_Appel" Free "CR_Ferm_Appel" Execute immediate "Update bloc_radio_carte ", "Set etat_carte = ""I"" Where rowid = ?" Usin VL_Rowid ----------------------------------------------------------------------- On the opening code for the database, we use these commands: set isolation level to cursor stability set lock mode to wait 4 ----------------------------------------------------------------------- I have tried to set lock mode to 30 (seconds). When the user see the error, it is instantanous (there is no 30 seconds wait). Some users say that they received a SQl error -535 Already in transaction before the deadlock problem. Is this a locking problem (if so, why did the message appears before the 30 sesonds)? Can somebody help me? Thank you for your help!

St'fane Ruel wrote in message <3A6642A5.7ED442B5@riq.qc.ca>... >Is this a locking problem (if so, why did the message appears before the >30 sesonds)? A dead lock happens when: process 1 has a lock on resource A process 2 has a lock on resource B process 1 wants to lock resource B (at this stage, it can wait the 30 seconds you mention) process 2 wants to lock resource A At this point, definitely process 2 will never get what it wants so it's called a deadlock (an ugly anology I've heard is: it's like you are holding a scorpion by the tail and the scorpion is holding you by the finger. Neither one of you want's to let go...) Since this circular locking problem can be detected immediately and has no solution, the engine returns a deadlock error immediately. >----------------------------------------------------------------------- >On the opening code for the database, we use these commands: >set isolation level to cursor stability >set lock mode to wait 4 This is almost but not quite the harshest isolation mode you can impose upon yourself. I recommend that you downgrade to "committed read". In fact, I ALWAYS work with "dirty read" because of various detailed arguments, which I won't bore you with unless you wish that upon yourself.

I love Andrew Hamm's "scorpion" analogy. We can extend it a bit further to say that God, in His (or Her, Wynne!) little-discussed form as Informix OnLIne, chooses ether the human or the scorpion to let go; the choosee then dies immediately. You've asked the question of the Informix newsgroup, but really this is an application and database design issue. As Andrew's pointed out, the engine is just reacting to a situation that the code has created. So, your most fruitful course of action is to review the code. St'fane Ruel <sruel@riq.qc.ca> wrote in message news:3A6642A5.7ED442B5@riq.qc.ca... > I have some serious deadlock problems (so it seems). > We use Informix Online 5.0. The programming language is Hyperscript > Tools 1.0. > The machine is a Sparc Ultra2, with 7 users. > There is a lot of transactions. Often, we (the user) got this error: > > SQL error (-346): could not update a row in the table > ISAM error (-143): deadlock detected > -------------------------------------------------------------------------- --------> > Here is part of the code that generate this error: > > Declare immediate cursor "CR_Ferm_Appel" > FOR "SELECT rowid", > "From bloc_radio_carte", > "Where no_carte = ?" > > Open cursor "CR_Ferm_Appel" Using No_Cart > Fetch "CR_Ferm_Appel" Into VL_Rowid > Close cursor "CR_Ferm_Appel" > Free "CR_Ferm_Appel" > > Execute immediate "Update bloc_radio_carte ", > "Set etat_carte = ""I"" Where rowid = ?" > Usin VL_Rowid > ----------------------------------------------------------------------- > > On the opening code for the database, we use these commands: > set isolation level to cursor stability > set lock mode to wait 4 > -----------------------------------------------------------------------> > I have tried to set lock mode to 30 (seconds). > When the user see the error, it is instantanous (there is no 30 seconds > wait). > Some users say that they received a SQl error -535 Already in > transaction before the deadlock problem. > > Is this a locking problem (if so, why did the message appears before the > 30 sesonds)? > > Can somebody help me? > > Thank you for your help! > >

> I recommend that you downgrade to "committed read". If connection isolation level is "committed read" then why "java.sql.SQLException: Could not do a physical-order read to fetch next row." is raised? Sent via Deja.com http://www.deja.com/

> I recommend that you downgrade to "committed read". If isolation level is "committed read" then what about "java.sql.SQLException: Could not do a physical-order read to fetch next row." Sent via Deja.com http://www.deja.com/

> Open cursor "CR_Ferm_Appel" Using No_Cart > Fetch "CR_Ferm_Appel" Into VL_Rowid > Close cursor "CR_Ferm_Appel" > Free "CR_Ferm_Appel" > > Execute immediate "Update bloc_radio_carte ", > "Set etat_carte = ""I"" Where rowid = ?" > Usin VL_Rowid It is really a question why you use the cursor stability isolation level. At least in the code above it makes no sense, because you perform no modification of the currently fetched row. As soon as you close the cursor (or even when you read the next row) the current row is freed to other users. So if you update the table after closing the read cursor (as the code above does) you cannot be sure the it was not changed by other user meanwhile. Using the code above I would propose to use comitted read. On the other hand if you want to be sure that the rows haven't been changed before updating them, you should review your code. Petr Sobotka Sent via Deja.com http://www.deja.com/