Re: Locking Question

do a onstat -k to identify the owner and then onstat -u to match the owner from the previous command to the address column. by now you should able to identify the session_id and then do an onstat -g ses <ses_id> and look an the last few rows from the output and cross check the SQL statement printed. I am sure u can do it thru the SMI tables does anybody has any idea on this ? for repeatable read, the engine will lock every it examined. Regards --- Steven Mastandrea <stevem@cstech.com> wrote: > I have recently run into a problem with running out > of locks on Informix > 7.23.UC1 on hpux OS. > We have an application that receives an item into > our inventory and > fulfills any backorders that it can from this > receipt. > > When we have the isolation level set to DIRTY READ, > the stored procedure > works fine. However, when we change the isolation > level to REPEATABLE > READ (shared locks) we get the out of locks error > message (Error #243, > ISAM ERROR #-134). The current lock level in the > config file is set to > 60000. This is in a test environment and nobody > else is using the > system at the time of this test, so I know nobody > else is using locks > (except for shared lock on sysdatabases for login). > I know that the SP > is not locking anywhere near 60000 rows directly, as > the test item I am > using has only 1 backorder for it. > > My question is: does Informix use shared locks on > rows before they are > returned in the query. In other words, say I do a > two table join, and > Informix is doing a hash join between the two > tables, does it put a > shared lock on all the rows before doing the hash, > and then remove them > if they don't join? > > Also, is there a way that I can identify which locks > are coming from > which SQL Statements? I turned TRACE ON, but that > just identifies which > SQL Statement it runs out of locks with, but that > may not necessarily be > the one that used all the locks. > > Thanks in advance for your help, > --Steven > > > ===== __________________________________________________ Do You Yahoo!? Bid and sell for free at http://auctions.yahoo.com