Hi guys.

Yesterday I was trying to figure out what is the cause of my problem. And I've noticed that two months ago my colleague added one trigger on this table. The trigger is:

SET TERM ^ ;
ALTER TRIGGER CLIENT_LM INACTIVE
AFTER INSERT OR UPDATE POSITION 1
AS
BEGIN
   UPDATE OR INSERT INTO LAST_MODIFICATION (TABLE_NAME, RECORD_TIME)
   VALUES ('CLIENT', NEW.RECORD_TIME)
   MATCHING (TABLE_NAME);
END^
SET TERM ; ^

So, after each insert or update this trigger was saving last modification time to a different table. On each table in the system such trigger exists. After dissabling the trigger, performance get back to normal.

Results for gstat are strange:

Database header page information:
        Flags                   0
        Checksum                12345
        Generation              228629442
        Page size               16384
        ODS version             11.2
        Oldest transaction      219409373
        Oldest active           219409374
        Oldest snapshot         219409374
        Next transaction        227560937
        Bumped transaction      1
        Sequence number         0
        Next attachment ID      1068492
        Implementation ID       24
        Shadow count            0
        Page buffers            0
        Next header page        0
        Database dialect        3
        Creation date           Aug 9, 2013 23:34:51
        Attributes              force write

    Variable header data:
        Sweep interval:         20000
        *END*

I see the big difference between oldest transaction and the current transaction. Is it possible that this trigger is caused this? BTW my colleague has reported to me that he had a number of deadlocks becuase of this mentioned trigger.

        Oldest transaction      219409373
        Oldest active           219409374
        Oldest snapshot         219409374
        Next transaction        227560937

Your oldest transaction is around 8 million transctions old, you have around 1.5 milion transactions a day (22 million transactions in 150 days), so you have an open transaction for around 6 days.

The trigger is not causing the transaction lock, it's caused by an open transaction that never gets closed.

The trigger could cause a lot of record versions (check with gstat you_databse.fdb -r -t LAST_MODIFICATION -user sysdba -password masterkey) because it updates the LAST_MODIFICATION table and since there is a transaction that needs to see the old version, new versions are created and never get garbage collected, because it's still interesting for some transaction.

You should check wich application holds the oldest transaction open, and fix it.

see you !