Re: "LOCK MODE TO WAIT" seemingly ignored

In article <887913385.14013.0.nnrp-03.9e982eb2@news.demon.co.uk>, Tony Tyrwhitt-Drake <tonytd@ttyrwhit.demon.co.uk> writes > >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 WRONG. I have today seen a progrma with set lock mode to wait end up waiting >3 minutes for a row which had an exclusive lock on it. This insert error is the only one which does not seem to get trapped. Instead do WHENEVER ERROR CONTINUE and keep retrying the insert it you get key valu locked... >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. > > > -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care