error -245

Hi, We have a legacy application which does not use logging. We are trying to convert it to a logged database with minimum application change. First we converted the database to a logged (unbuffered) and sporadically we get error -245. Next we added set lock mode to wait 15 in our code, but we still get. I have a tool which queries sysmaster database to find out about locks. So what I did was to run that tool and it printed out the locking contention. Looking at the table/index which shows lock error 245, I reconstructed the application scenario in a test case:- 1. From a session I continuously load large number of rows into a table. 2. From many sessions I query the table where the data is getting loaded. Sure enuf, some of the sessions crap out with this error. Both (1) and (2) are with set lock mode to wait 15. However both (1) and (2) are not within begin work and commit work. That is bcos our application does not use begin work, commit work. Changing the lock mode to wait 30 also spews out error in (2). finally what I did was to issue set isolation level to dirty read. All errors vanished. From what I understand, while inserting, informix does a lock on index key. And if at that time another session tries to read that key (our application queries on the key only), it spews out error 245 or 243. Why does lock mode 15 fail. Sure, a record does not take 30 seconds to insert, or even 15. Second question: What happens when I issue dirty read. How does it behave in a logged database. Am I missing something. I thought we should not have problem like above. TIA. ravi

Ravi How are you loading the data? If you are doing it in one hit (and no explicit commits after every x records) then all inserted records (and index entries) will be locked until the load session completes (use onstat -u to observe locks increasing at a phenomenal rate !! :-) ). Use dbload (or one of Art's scripts) to load and commit every, say 10%, of the load file. An isolation level of dirty read will actually skip any locked records so you may not see all the records returned that are present. Keith -> -----Original Message----- -> From: rkusenet [mailto:rkusenet@sympatico.ca] -> Sent: Saturday, April 26, 2003 12:16 AM -> To: ids@iiug.org -> Subject: error -245 [1004] -> -> -> Hi, -> -> We have a legacy application which does not use logging. We -> are trying to -> convert it to a logged database with minimum application -> change. First -> we converted the database to a logged (unbuffered) and -> sporadically we -> get error -245. Next we added set lock mode to wait 15 in our code, -> but we still get. -> -> I have a tool which queries sysmaster database to find out -> about locks. -> So what I did was to run that tool and it printed out the locking -> contention. -> Looking at the table/index which shows lock error 245, I -> reconstructed -> the application scenario in a test case:- -> -> 1. From a session I continuously load large number of rows -> into a table. -> -> 2. From many sessions I query the table where the data is -> getting loaded. -> Sure enuf, some of the sessions crap out with this error. -> -> Both (1) and (2) are with set lock mode to wait 15. However both (1) -> and (2) are not within begin work and commit work. That is bcos our -> application does not use begin work, commit work. -> -> Changing the lock mode to wait 30 also spews out error in (2). -> finally what I did was to issue set isolation level to dirty read. -> All errors vanished. -> -> From what I understand, while inserting, informix does a -> lock on index key. -> And if at that time another session tries to read that key -> (our application -> queries on the key only), it spews out error 245 or 243. Why -> does lock mode -> 15 -> fail. Sure, a record does not take 30 seconds to insert, or even 15. -> -> Second question: What happens when I issue dirty read. How -> does it behave -> in a logged database. -> -> Am I missing something. I thought we should not have problem -> like above. -> -> TIA. -> -> ravi -> -> -> ******************************************************************************** ** This message is sent in strict confidence for the addressee only. It may contain legally privileged information. The contents are not to be disclosed to anyone other than the addressee. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission. This footnote also confirms that this email message has been swept for the presence of computer viruses, however we cannot guarantee that this message is free from such problems. ******************************************************************************** **