Subject Re: Insert ot update statements locks a table and all other instances that try to run same statement are lcoked to
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.