What he said would give you a deadlock, was:

q1.sql.clear;
q1.sql.add('Insert into aaa (id, tekst) values (1, "Michael");');
q1.ExecSQL;
q2.sql.clear;
q2.sql.add('Insert into aaa (id, tekst) values (2, "Robert");');
q2.ExecSQL;

q1.sql.clear;
q1.sql.add('Insert into aaa (id, tekst) values (2, "Helen");');
q1.ExecSQL;
q2.sql.clear;
q2.sql.add('Insert into aaa (id, tekst) values (1, "Martijn");');
q2.ExecSQL;

t1.commit;
t2.commit;

Here, q1 (tr1) creates a record with ID=1, whereas q2 (tr2) creates a
record with ID=2. The deadlock arises when they then try to create an
additional record with the PK value the other transaction already has
inserted.

If you use a generator to generate new PKs for every new record, then
this can only cause deadlocks if the number for some reason is used
already (like someone using GEN_ID(<generator>, 0) or assigning a
fixed value to the field). New PKs doesn't cause deadlocks.

Set

--- In firebird-support@yahoogroups.com, "Michael Vilhelmsen" wrote:
> > > An insert - Can this give me a deadlock ?
> >
> > Transaction A inserts row with PK=1.
> >
> > Transaction B inserts row with PK=2.
> >
> > Transaction A tries to insert row with PK=2 - because PK=2
> > is already inserted but not committed by transaction B, tr. A
> > waits ...
> >
> > Transaction B tries to insert row with PK=1 - because PK=1
> > is already inserted but not committed by transaction A, tr. B
> > waits ...
> >
> > ... deadlock
>
> I have just create two table in a DB.
> One called AAA another called BBB.
> Both have two fields (ID Integer, and Tekst Varchar(30)).
> Both are defined as:
>
> CREATE TABLE BBB (
> ID INTEGER NOT NULL,
> TEKST VARCHAR(30)
> );
>
> CREATE TABLE AAA (
> ID INTEGER NOT NULL,
> TEKST VARCHAR(30)
> );
>
>
> Both have a trigger BEFORE INSERT which does this:
>
> CREATE TRIGGER BBB_BI FOR BBB
> ACTIVE BEFORE INSERT POSITION 0
> AS
> BEGIN
> IF (NEW.ID IS NULL) THEN
> NEW.ID = GEN_ID(GEN_BBB_ID,1);
> END
> CREATE TRIGGER AAA_BI FOR AAA
> ACTIVE BEFORE INSERT POSITION 0
> AS
> BEGIN
> IF (NEW.ID IS NULL) THEN
> NEW.ID = GEN_ID(GEN_AAA_ID,1);
> END
>
> Table AAA has this trigger AFTER INSERT as well.
> CREATE TRIGGER AAA_AI0 FOR AAA
> ACTIVE AFTER INSERT POSITION 0
> AS
> begin
> Insert into bbb (tekst) values (new.tekst);
> end
>
>
> I made a small program, with two DB connections.
> In short I did this:
>
> db.Open;
> db2.Open;
>
> if (t1.active) then
> t1.StartTransaction;
> if (t2.active) then
> t2.StartTransaction;
>
> q1.sql.clear;
> q1.sql.add('Insert into aaa (tekst) values ("Michael");');
> q1.ExecSQL;
>
> q2.sql.clear;
> q2.sql.add('Insert into aaa (tekst) values ("Robert");');
> q2.ExecSQL;
>
> t1.commit;
> t2.commit;
>
> db.close;
> db2.close;
>
>
> This did not give me an deadlock.
>
> Wasn't this what you told me would resolve in a deadlock ?
>
> Regards
>
> Michael