Hi all,

As suggested by Helen I've tracked down what I meant in a prior thread
by DDL statements being not "entirely" under transactional control. I'm
not sure whether this is a bug or feature of Firebird; given that I
mostly work with DB2 which uses a traditional locking mechanism for
transactional control I could be misinterpreting an aspect of the
multi-generational engine used by Firebird. Nevertheless, I'd be
interested to find out the reason for the following behavior.

Consider the following (admittedly contrived) situation:

Given two separate, simultaneous transactions, the first transaction
creates a table, inserts some data into it and then commits. After the
table has been created, but before the first transaction commits, a
second transaction attempts to read from the table.

In other words, the sequence of events looks something like this:

Step TX 1 TX 2
==== ================ ================
1 <start>
2 CREATE TABLE X
3 INSERT INTO X
4 <start>
5 SELECT * FROM X
6 COMMIT

Under DB2, which as mentioned uses a locking model, the following occurs
when the above sequence is carried out in two simultaneous sessions (for
anyone wishing to try this, remember to use -c- or +c to turn off
auto-COMMIT in the command line processor):

Step TX 1 TX 2
==== ================ ================
1 <start>
2 CREATE TABLE X
3 INSERT INTO X
4 <start>
5 SELECT * FROM X
<blocks>
6 COMMIT
<retrieves data>

In other words, after transaction 1 has created the table, but before it
has committed, any other transactions attempting to access the table
will block until transaction 1 commits or rolls back (at which point the
other transactions would either succeed in accessing the table or fail
with "table does not exist" respectively).

Under Firebird, using a multi-generational model, the following occurs
when the sequence is carried out in two simultaneous sessions (anyone
wishing to try this, remember to use SET AUTODDL to disable auto-COMMIT
of DDL statements in the isql tool):

Step TX 1 TX 2
==== ================ ================
1 <start>
2 CREATE TABLE X
3 INSERT INTO X
<fails>

Okay, so it looks like a table can't be used until the CREATE TABLE
statement that created it is committed. This was the first thing that
led me to wonder whether DDL statements were (or weren't) "entirely"
under transactional control.

That said, my gut instinct is that I'm not understanding some aspect of
multi-generational architectures which mandates such behavior?

Anyway, here's a second attempt, this time adding a COMMIT immediately
after the second transaction starts to allow the INSERT in the first
transaction to succeed:

Step TX 1 TX 2
==== ================ ================
1 <start>
2 CREATE TABLE X
3 <start>
4 COMMIT
5 INSERT INTO X
6 SELECT * FROM X
<returns empty result set>
7 COMMIT

At first glance this looked a lot more like what I'd expected. However,
after thinking about it, there was still something odd about this too:
given that transaction 2 started before the CREATE TABLE statement was
committed, shouldn't it be complaining that table X doesn't exist
instead of returning an empty result set?

To put it another way, assume the SELECT statement in the second
transaction is moved after the second COMMIT in the first transaction:

Step TX 1 TX 2
==== ================ ================
1 <start>
2 CREATE TABLE X
3 <start>
4 COMMIT
5 INSERT INTO X
6 COMMIT
7 SELECT * FROM X
<returns empty result set>

In this case, the SELECT statement in the second transaction can't see
the inserted (and committed) records from the first transaction, yet it
can see the table created (and committed) by the first transaction.

Again, I expect I'm not understanding some aspect of the MGA. Please
don't take any of the above as criticism of the MGA or of Firebird; the
behavior demonstrated above whether bug, feature, or entirely normal
hasn't caused me any real headaches - it's just something I happened to
run across and was rather confused by. If someone can help me understand
it, so much the better.

For reference, I tested the above on Firebird embedded 1.5.3 on Windows,
and Firebird SS 1.5.3 on a Gentoo Linux box (oh, and DB2 UDB 8, FP11 on
the same Gentoo box).

In the meantime, I'll get back to having fun with embedded Firebird :-)


--
Dave Hughes

email: dave@...