Subject Re: [firebird-support] What can be the possible cause for this lock
Author Ann W. Harrison
Johannes Pretorius wrote:
> In this scenario lets say we have 3 types of clients
> A) Client A that INSERTS and UPDATES data into database and
> refreshes data from stored procedure.
> B) Client B that does updates on 1 table common with A's
> inserts and 1 not affected by A at all (second one is
> inserted into by B).
> C) Client C that polls table only inserted to by A and reacts
> by inserting into it's own table not affected by others.
> ....
> When A Inserts data into database and B and C is running, then it
> sometimes happens that A gets a deadlock when creating (INSERTING)
> the data into the database. Please note that B cannot work on data
> UNTIL A has inserted it, thus B does not have affect on this.

Unless you're doing something complicated, INSERTs don't deadlock.
Complications include constraints (e.g. unique, primary key) with
the NO_REC_VERSIONS transaction option, triggers that update other
tables. UPDATEs deadlock, DELETEs deadlock, but INSERTs don't.

> Thus the only client that is suppose to affect this process is C.
> But C only reacts on newly INSERTED data. After data is process
> by it is DELETED from table (All tables has UNIQUE identifiers and
> primary keys).
> Can this be related to the transaction type (isolation) or just
> bad coding beeing missed somewhere ?
> Information about database and machines
> --------------------------------------------------------------
> Version : Interbase 5.6
> OS : Windows NT & 2000
> DB Size : From 400 mb till under 4GB.
> Force writes is ON

Don't use InterBase 5.6 on Windows. It's got several bad bugs that
will corrupt your databases, irrecoverably.