Subject Re: [firebird-support] Insert ot update statements locks a table and all other instances that try to run same statement are lcoked to
Author Thomas Steinmaurer

> Hi guys,
> I'm looking for some ideas how to find what is causing this lock.
> I have 2 production databases that process multiple insert/update
> statements. There is one background process that does all these
> modifications 24h/day.
> The problem is that with one of these databases almost every morning
> when client tries to connect it runs series of inserts in one table. And
> during that process the insert is blocked by something.
> The other database experiences similar problem but there the lock is on
> another table that as I said earlier modified trough the whole day.
> If such a lock happens it locks all similar requests to these tables.
> I need to shutdown the main process and run Sweep in order to remove the
> leftover from all transactions executed after initially locked statement
> transaction.
> So I'm looking for ideas how to diagnose this problem? what I can do to
> pinpoint the source of the problem?
> I'm using Firebird 2.1.6 Classic on Linux.
> I have same databases on other production setups that does not have this
> problem. The difference there is that they don't have that background
> process running on these setups.

I would check your transaction settings for both, the non-background
process requests and the background task. E.g. WAIT vs. NO WAIT, read
requests using READ COMMITTED isolation level with NO REC VERSION etc.

With regards,
Thomas Steinmaurer

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.