One active IB transaction the whole lifetime of a single user application

views:

178

answers:

+3 Q:

One active IB transaction the whole lifetime of a single user application

Are there any negative impacts when a single user application uses only one IB transaction, which is active as long the program runs? By using only CommitRetaining and RollbackRetaining.

Background: I want to use IBQuery(s) and connect them to a DB Grid(s) (DevExpress), which loads all records into memory at once. So I want to avoid re-fetching all data after every SQL insert command. IBTransaction.Commit would close the dataset.

+3 A:

It sounds to me like you're trying to use a database feature in order to avoid using TClientDataSet, which is clearly the preferred way of working in Delphi these days. Why go out of your way and use questionable transaction practices instead of following the more common pattern for database component use, which already is a better solution to the problem at hand?

Craig Stuntz 2010-01-21 17:30:24

You are somehow right :) I am already thinking a long time, which way I shall go. Using directly TIBQuery or TClientDataset. IBQuery has some nice design time features (e.g. SQLUpdate), which makes RAD developing obviously easier. I also have some concern, whether an additional layer could introduce more troubles, makes develoment more complicated and reduce performance. Actually, I am not sure what is the better approche for a single user application. Do you have some guidlines?

max 2010-01-21 18:12:37

There is no question but that it is, at least at a superficial level, more complicated to use `TClientDataSet` then to not use it. However, once you learn to use it, then you can use these skills to solve a wide variety of problems which you cannot solve without it (or without some other component which does what it does). I use `TClientDataSet` for every Delphi database application I write, including my free IB performance analysis tools.

Craig Stuntz 2010-01-21 18:37:06

thanks for your information

max 2010-01-21 18:52:56

+1 A:

CommitRetaining and RollbackRetaining are not good.

transaction have to be very short.

Hugues Van Landeghem 2010-01-21 18:06:45

Could you elaborate, please? Why are the XRetaining methods bad? Why do transactions need to be short? How short must they be?

Rob Kennedy 2010-01-21 18:31:55

This was true with archaic versions of IB, but not true anymore with current versions unless you're using snapshot isolation. Rob, if you're using snapshot isolation, then the server must keep old record versions around, which prevents the IB server's garbage collector from doing work, meaning that every SELECT must trawl through many, many record versions to find the one which is visible to the user's transaction. This is true even if the current transaction is not snapshot. In old versions of IB, even non-snapshot transactions would interfere with garbage collection. This was fixed years ago

Craig Stuntz 2010-01-21 18:35:09

@Craig: Do you know if this is true for firebird also?

jachguate 2010-01-22 03:03:23

No idea; sorry. IIRC this was fixed in IB 7 or 7.1.

Craig Stuntz 2010-01-22 04:09:06

If you are using Firebird, I advice you not to use this features.

Hugues Van Landeghem 2010-01-22 12:28:15

@Hugues: Can you explain why?

max 2010-01-25 13:02:39

ansaurus

tags:

views:

answers:

One active IB transaction the whole lifetime of a single user application

related questions