Re: "LOCK MODE TO WAIT" seemingly ignored

Mark Stevans wrote: > Hi. I'm programming in ESQL/C on Solaris for Informix 7.12. After my > "CONNECT" statement, I issue the statement "SET LOCK MODE TO WAIT", and > then my app does lots of "INSERT" operations. No problem when I run the > app alone. > > But when I try to run multiple copies of my app concurrently, once in a > while the "INSERT" fails, returning SQLSTATE "IX000", SQLCODE -271 > within a second of real time of when the "INSERT" was initiated. The > exception messages fetched are "Could not insert new row into the table" > and "ISAM error: key value locked". > > I thought that if I set the lock mode to "WAIT" in my app, I would never > have a statement fail due to concurrent accesses/locks. Things might > hang a long time, but would eventually gain access to the table and > execute OK. If I try supplying an explicit time ("SET LOCK MODE TO WAIT > 10", where the configuration file specify DEADLOCK_TIMEOUT as 60) the > "INSERT" statements that fail still fail with a second of their > initiation. Where am I going wrong? Thanks. > > MLS The most probable cause for your error is a Deadlock! When a dead lock occurs, in online engines, the engine terminates the lock wait and returns an SQL error as if a lock as occurred and the lock mode was set no no wait. If you have the Informix guide to SQL (Tutorial, Version 4.1) you can read on page 7-29, about "Concurrency and Locking", Handling Deadlock": "A deadlock is a situation in witch a pair of programs block each other's progress. Each program has a lock on some object that the other program wants to access. A dead lock can only arise when programs concerned have set their lock modes to wait for locks. Informix-online can detect deadlocks immediately when they involve only data at a single network site. It prevents the deadlock from occurring by returning an error code to the second program to request a lock. The error code is the one the program would have received if it had set it's lock mode to not wait for locks. Thus, if your program receives an error code related to locks even after it has set lock mode to wait, you know the cause was an impending deadlock." For some clues on code an data access strategy to handle and avoid this problems please read lock and deadlock related topics on this same manual. Joao Fonseca http://www.terravista.pt/aguaalto/1006