--- In firebird-support@yahoogroups.com, "Michael Vilhelmsen"
<Michael.Vilhelmsen@M...> 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 ?
>

No, that is not what he said. For a start you are using a generator to
allocate the ID, which means they will never try to use the same ID. I
don't experience these deadlocks because I use NOWAIT transactions so
I would receive an exception instead at step 3.

Using your example, I could force a deadlock if you use WAIT transactions

TrA : insert into AAA (ID, TEKST) VALUES (1, 'Michael');
TrB : insert into AAA (ID, TEKST) VALUES (2, 'Robert');
TrA : insert into AAA (ID, TEKST) VALUES (2, 'Fred');

If TrA is a WAIT transaction, he has to sit around and wait to see
what TrB does. If he is lucky, TrB will rollback and then TrA will
succeed. Otherwise, if TrB commits, he will then get an exception. If
TrA is a NOWAIT transaction, he gets an exception immediately.

TrB : insert into AAA (ID, TEKST) VALUES (1, 'Wilma');

If TrB is a WAIT transaction, he has to sit around and wait to see
what TrA does. Now this is a bad situation to be in, because left
unchecked, these guys would be waiting forever. Firebird detects that
the transactions are deadlocked, picks a victim (at random) and gives
it an exception.

For us, the waiting game caused some UI issues. It was more convenient
to inform the user that someone else was making changes to the record
and to try again later, than to freeze to UI in the (usually
unrealistic) hope the other transaction failed.

On a side note, two transactions can share the same database
connection (and a transaction can span multiple databases). You may
also want to change your if conditions to be if (NOT t1.Active) etc

Adam