Lock problems - soliciting suggestions

I've been asked to look at an application running on 7.23, Dec Unix. The database is logged unbuffered (but not ANSI). The application is written in PowerBuilder, making HEAVY use of stored procedures. The Stored Procedures contain "BEGIN WORK", "COMMIT" and "ROLLBACK" statements. The SP's can call other SP's and are recursive. What we witness is that a user session will begin acquiring locks as it begins it's work. However, often the session does not show that a "BEGIN WORK" has taken place. Note the output of "onstat -u" there is no "B" in the third position. 2149d7868 Y--P--- 1099 lsmith PC331 214ef1c70 0 5539 0 0 Here's the problem, the session holds the locks. According to the Informix Guide to SQL, statements issued without a "BEGIN WORK" are treated as their own transaction. I can see where locks might be obtained in this fashion but those locks should also be released at the conclusion of the individual statement. Most of the locks held are on tables that are viewed but not modified. Consequently, there are no "tracks" left to look at in the logical logs. Suggests (or condolences) would be appreciated.

Fred Prose wrote: > I've been asked to look at an application running on 7.23, Dec Unix. The > database is logged unbuffered (but not ANSI). The application is written in > PowerBuilder, making HEAVY use of stored procedures. The Stored Procedures > contain "BEGIN WORK", "COMMIT" and "ROLLBACK" statements. The SP's can call > other SP's and are recursive. > > What we witness is that a user session will begin acquiring locks as it > begins it's work. However, often the session does not show that a "BEGIN > WORK" has taken place. Note the output of "onstat -u" there is no "B" in > the third position. > > 2149d7868 Y--P--- 1099 lsmith PC331 214ef1c70 0 > 5539 0 0 > > Here's the problem, the session holds the locks. According to the Informix > Guide to SQL, statements issued without a "BEGIN WORK" are treated as their > own transaction. I can see where locks might be obtained in this fashion > but those locks should also be released at the conclusion of the individual > statement. > > Most of the locks held are on tables that are viewed but not modified. > Consequently, there are no "tracks" left to look at in the logical logs. > > Suggests (or condolences) would be appreciated. Show us some onstat -k output showing the offending locks and an onstat -g ses from one of the offending sessions. Art S. Kagel