Locking Question

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

> > 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. > Two little shell scripts to identify locks: #find all locks : exec dbaccess sysmaster - <<eof 2>/dev/null select dbsname,tabname,type,owner,waiter,username,pid from syslocks a, syssessions b where a.owner = b.sid;eof #find all but NO shared locks : exec dbaccess sysmaster - <<eof 2>/dev/null select dbsname,tabname,type,owner,waiter,username,pid from syslocks a, syssessions b where a.owner = b.sid and type <> "S";eof HTH Reinhard