Hi,

Following a performance problem with one of our application, I've read a lot of things about transactions. Unfortunately, it raised some questions that are still unanswered. Maybe these questions should be addressed in the Firebird dbExpress driver forum, but I'll give it a try here.

I use dbExpress for Delphi to connect to a Firebird 2.1 database.
I always use the following commands.
- TSQLConnection.StartTransaction(TTransactionDesc);
- TSQLConnection.Commit(TTransactionDesc); or Rollback.
All my queries are opened or executed under a started transaction and my TTransactionDesc record is configured as follow :
TransactionDesc.TransactionID := 1;
TransactionDesc.IsolationLevel := xilREADCOMMITTED;

I noticed that eveytime I open or exec a SQL command, a new transaction ID is used on the Firebird server.
By using the gstat -h command line between each query, I can see the Next Transaction ID going up each time.
In my application, it doesn't take long for the gap between the Oldest Transaction and the Next Transaction to become large which will probably lead to some performance problems. (According to what I've read.)

Then I found the TransactionLevel property of the TSQLQuery and I noticed that if I explicitly set it to my TransactionDesc.TransactionID value (1) , then it won't create new transactions on the Firebird server. When using the gstat -h command, the Next Transaction ID doesn't go up anymore.

So I think it is better to set the TransactionLevel property manually so it won't create new transaction IDs on the server. Am I right?


Then I wondered how does a Commit or Rollback really work?
I never had any problems commiting or rollbacking my transactions in the past.
Does it use some kind of starting point created when I call the StartTransaction procedure?
If I open 30 queries between the StartTransaction and the next Commit call, I will have created about 30 transaction IDs on the server.
Will it commit all transactions from the ID created at the StartTransction call until the last ID before the Commit call?

If I use the TransactionLevel property, I won't create a bunch of transactions on the server, I will only create transaction X and all queries will run in transaction X. Looks easier to understand how the commit will work but maybe I'm missing something.

If anyone can bring me some light about all this... It would be really appreciated.
Thank you!

Vincent