Long Transactions and table locking

I'm using a combination of triggers and stored procedures to perform an "audit trail" function on some of the tables in my unbuffered logging database (IDS 7.30.UC10 on Solaris). *Sometimes* (by no means all of the time) the application performing an insert/update that has a trigger on it appears to lock up... eventually coming back with a "Long Transaction" error. Looking through the sysmaster/systables info it seems that the stored procedures/triggers have taken out exclusive locks on those tables... locks that I never said it should use. Is this default behaviour, and if so can I override it in my SPs? At the same time, other users attempting to use the tables that the other app is trying to insert/update are getting rebuffed with "table locked" errors. The app itself doesn't perform any table locking. Now, my logical and physical logs are of a reasonable size, so I don't quite see what the problem is. Can anyone point me in roughly the right direction? Many thanks, Paul

The "Long Transaction" error can affect transactions that take very little log space as well (although this rarely happens). Informix marks a transaction as "Long" when its BEGIN WORK entry is in a log more that LTXHWM % away from the current log. This does not mean the specific transaction has used all that log space. For example, imagine a dbaccess session that is sitting idle after executing the BEGIN WORK statement. If there are other sessions that are using up log space, there will come a point in time when the dbaccess's session BEGIN WORK log entry has "crossed LTXHWM" and will encounter the "Long transaction" error even though it has done absolutely nothing of value. In your case, keep in mind that rows modified by triggers and SPs that get triggered due to some action caused by your App. (say, an insert) will also be locked for the duration of the Apps transaction. Maybe competing sessions doing approximately the same task are causing deadlocks. You need to investigate the reasons for the "lock up". Cheers Rudy Paul Harman wrote: > I'm using a combination of triggers and stored procedures to perform an > "audit trail" function on some of the tables in my unbuffered logging > database (IDS 7.30.UC10 on Solaris). > > *Sometimes* (by no means all of the time) the application performing an > insert/update that has a trigger on it appears to lock up... eventually > coming back with a "Long Transaction" error. Looking through the > sysmaster/systables info it seems that the stored procedures/triggers have > taken out exclusive locks on those tables... locks that I never said it > should use. Is this default behaviour, and if so can I override it in my > SPs? > > At the same time, other users attempting to use the tables that the other > app is trying to insert/update are getting rebuffed with "table locked" > errors. The app itself doesn't perform any table locking. > > Now, my logical and physical logs are of a reasonable size, so I don't quite > see what the problem is. Can anyone point me in roughly the right direction? > > Many thanks, > > Paul

Rudy Fernandes <rferdy@americasm01.nt.com> wrote in message news:38C561B0.98C0910B@americasm01.nt.com... > The "Long Transaction" error can affect transactions that take very little log > space as well (although this rarely happens). Informix marks a transaction as > "Long" when its BEGIN WORK entry is in a log more that LTXHWM % away from the > current log. This does not mean the specific transaction has used all that log > space. Thanks for the reply, Rudy. However, I'm not explicitly using transactions anywhere within the application - it's all "one hit wonders" (I'm using JDBC prepared statements). So unless something "behind the scenes" is opening transactions and not closing them, I'm at a bit of a loss. One possibility I'm looking into is that my stored procedure may be entering an "infinite" loop inserting audit trail rows... I can't think of an easy way to prove this though without using TRACE and getting a log file several gigabytes in size.... :*( Oh well. Paul

Paul Harman wrote: > Rudy Fernandes <rferdy@americasm01.nt.com> wrote in message > news:38C561B0.98C0910B@americasm01.nt.com... > ... > > However, I'm not explicitly using transactions anywhere within the > application - it's all "one hit wonders" (I'm using JDBC prepared > statements). So unless something "behind the scenes" is opening transactions > and not closing them, I'm at a bit of a loss. Triggered actions occur in implicit transactions in a logged database. That is to say, if a row is inserted into a table that has an insert trigger on it that executes an SP that inserts rows into a second table (that, possibly, also has an insert trigger), then all the rows created by the trigger including the original row that "fired" the trigger are in an implicit transaction. These implicit transactions are as susceptible to the "Long trx." problem as the explicit ones started by the BEGIN WORK statement. > > One possibility I'm looking into is that my stored procedure may be entering > an "infinite" loop inserting audit trail rows... I can't think of an easy > way to prove this though without using TRACE and getting a log file several > gigabytes in size.... :*( An infinite loop in the SP, per se, would cause the problem, irrespective of whether audit trail records are being inserted or not - a code-walkthrough of the SP could be able to settle that. Consider locking contention between multiple sessions "firing" the same trigger. When you next have this "lock up", examine the session flags of the "onstat -u" command to determine if the session is being locked out. And by what - its possible that your audit trail table is being locked by an entirely different process. Does this table get auto-cleaned of old records? Cheers Rudy

Rudy Fernandes <rferdy@americasm01.nt.com> wrote in message news:38C64B9C.4FE371AE@americasm01.nt.com... > Triggered actions occur in implicit transactions in a logged database. > [...] > These implicit transactions are as susceptible to the "Long trx." problem as > the explicit ones started by the BEGIN WORK statement. I thought that might be the case... > An infinite loop in the SP, per se, would cause the problem, irrespective of > whether audit trail records are being inserted or not - a code-walkthrough of > the SP could be able to settle that. That's what I'm going to look into. > Consider locking contention between multiple sessions "firing" the same > trigger. When you next have this "lock up", examine the session flags of the > "onstat -u" command to determine if the session is being locked out. Thanks > And by what - its possible that your audit trail table is being locked by an > entirely different process. Does this table get auto-cleaned of old records? Not yet };*) Since it's a multi-user DB it's likely that there's lock contention between triggers for the table... I assume that Informix is "grown up" enough to deal with that without me having to do anything. The behaviour I've observed is that the SPs have an implicit "SET LOCK MODE TO WAIT" hanging around them, but if this proves a problem I can simply add one. Paul