>CASHBOXID is already know. It is a reference to a table that we don't touch in
>the transaction.
>This table contains the current total amount of a cash box at the begging of
>the day.
>So when I try to load data for a cash box a java code checks is there record
>for this cash box for current day.
>If there is no such record it calculates the total current amount and calls
>the insert that adds a record to this table.
>
>Usually inserting record with same value for primary key will create error and
>probably will not block the execution of the statement.
>User is sysdba. There is no security restrictions for access to database.
>End users have no access to database server at all. They all use code that
>runs in application server to access or modify data.
>That is why I'm not restricting the access to the database for the moment.
>
>I'm not sure it is a lock conflict. But I suspect that something is locked
>somewhere and that forces the execution of the
>statement to stop and to lock all other inserts that other connections try to
>execute.
>
>As I said I need ideas or hints what to do in order to find the real problem
>when this situation happens again.
>One more thing. When I try to stop application server when this happens all
>connections that are blocked including the initial one stop the shutdown of
>the server.
>When I try to kill firebird process that initially blocked the execution all
>other processes are unlocked and server is shutdown after that.
>But in this case I try guess which process is that. When I look in the
>monitoring tables I can't see the pid of the process of each statement.
Have you considered trying something like (written using a text editor, so
there could well be errors):
CREATE GENERATOR PK_GEN;
CREATE TABLE CASH_CASHBOX_DAY_AMMOUNT
( PK INTEGER NOT NULL,
CASHBOXID INTEGER NOT NULL,
DATE_TIME NUMERIC( 18, 0) NOT NULL,
AMMOUNT NUMERIC( 18, 0),
CONSTRAINT PK_CASH_CASHBOX_DAY_AMMOUNT PRIMARY KEY (PK)
);
ALTER TABLE CASH_CASHBOX_DAY_AMMOUNT ADD CONSTRAINT FK_CASH_CASHBOX_DAY_AMMOUNT
FOREIGN KEY (CASHBOXID) REFERENCES CASH_CASHBOX
(ID);
CREATE INDEX IDX_CCDA_CASHBOXID ON CASH_CASHBOX_DAY_AMMOUNT(CASHBOXID);
CREATE INDEX IDX_CCDA_DATE_TIME ON CASH_CASHBOX_DAY_AMMOUNT(DATE_TIME);
SET TERM ^^ ;
CREATE TRIGGER CASH_CASHBOX_DAY_AMMOUNT_PK ACTIVE BEFORE INSERT AS
BEGIN
if (new.PK IS NULL) then
new.PK = GEN_ID(PK_GEN, 1);
END ^^
SET TERM ; ^^
If this makes the problem disappear for INSERTs, then that problem is you
inserting identical CASHBOXID and DATE_TIME combinations.
If this is the problem, you'd probably want to change a few selects in your
programs from (e.g.)
select cashboxid, date_time, ammount
from cash_cashbox_day_ammount
to
select cashboxid, date_time, sum(ammount) ammount
from cash_cashbox_day_ammount
group by 1, 2
since there now can be duplicates.
Another thing is that you now may want to do some occational housekeeping,
allowing for duplicates to occur is not the same as wanting there to remain
duplicates of CASHBOXID and DATE_TIME over time in your database. I'd recommend
you to run this statement regularly, e.g. once each night or week (change it to
a stored procedure if you want to - and if this table is huge, you may want to
add another trigger-populated field with the insert and update time - so that
you can exclude rows not changed/inserted recently from the calculation):
execute block as
declare variable cbid integer;
declare variable dt numeric(18,0);
declare variable total_amount numberic(18,0);
begin
for select cashboxid, date_time, sum(ammount)
from cash_cashbox_day_ammount
group by 1, 2
having count(*) > 1
into :cbid, :dt, :total_amount do
begin
delete from cash_cashbox_day_ammount
where cashbox_id = :cbid
and date_time = :dt;
insert into cash_cashbox_day_ammount(cashboxid, date_time, ammount)
values(:cbid, :dt, :total_amount);
end
end
HTH,
Set
