> > 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