Hi !
Em 16/1/2014 04:13, brucedickin...@wp.pl escreveu:
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 !
