> I seeking opinions regarding FB transactions in an application. My current apps call some complex lookup modules, usually a DLL. When I use the sqls (which are all read only selects) under the application main transaction (which allows for db updates) the lookup modules can take some time. I did experiment with a separate transaction for the lookup modules however some customer sites which use Windows 2003 servers upwards cause the modules to take some time to startup which defeats the purpose of the lookup.
> What is the correct way to approach this? Is it a matter of multiple transactions specific to the sql tasks (i.e. read only, retain commit)? or should it be one transaction per application?
> You opinions please would be appreciated.

Get used to transactions, understand them, understand the different
settings, play with them in different smaller test applications. A clear
understanding of Firebird transactions and their usage is IMHO THE
number one golden nugget in developing Firebird-based client
applications. Really! I've seen in various customer workshops, that a
bad transaction management in the client application is one of the most
important affecting issue on server slow down etc ...

Make use of the various transaction settings (isolation level,
read-only/read-write ...) depending on the application requirement.

Read-only transactions do have less overhead than read-write
transactions, thus explicitly use them when you don't need to write
something. In combination with read committed as isolation level, you
won't block moving OIT/OAT forward, even for long running transactions
or running them in some sort of auto commit mode, which basically calls
commit retaining behind the scene.

Again, grep everything on Firebird transactions from the Internet (or
Helen's book ...) and try to understand that. The time is very well
spent. ;-)


--
With regards,

Thomas Steinmaurer
Upscene Productions
http://www.upscene.com
http://blog.upscene.com/thomas/

Download LogManager Series, FB TraceManager today!
Continuous Database Monitoring Solutions supporting
Firebird, InterBase, Advantage Database, MS SQL Server
and NexusDB!