Re: "LOCK MODE TO WAIT" seemingly ignored

Mark Stevans wrote in message <34EA08B8.4CBEF655@dnai.com>... >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". > "Set lock mode to wait " has no effect with respect to exclusive locks. If your process encounters an exclusive lock it will take the error exit provided and slam the door on the way out. If you operate in a multi user environment you must adopt a locking strategy appropriate to the applications using the database. "set lock mode to wait" will only work if you set shared locks initially, which is difficult to do whilst inserting, and not a lot of use at other times :-) One approach would be to make sure that anything that generates an exclusive lock commits as soon as possible, and that any process encountering an exclusive lock goes round and tries again. You need "whenever error continue", and a loop to keep trying until the insert succeeds.