How to work with SQLdb and Transaction objects (currently sqlite environment)

[goodname]

It is generally good database practice to have a transaction open only while needed. So opening a transaction when needed and doing commit or rollback as soon as possible is generally good practice. The alternative is a singleton model where as soon as you do a commit or rollback immediately open another transaction so there is always one open transaction ready to use. This wastes database server resources and is not recommended.

I'm not familiar with SQLite transaction modes so can't really help you there.

[mangakissa]

As far as I know SQLdb also uses the transactions internally. So it's not only working with the transactions of the database, but it has somekind of TDatasetprovider (Delphi) built in code. With applyupdate/commit you tell the TSQLQuery (which is a descendant of TBufdataset) to create an internal query to save the modified data to the database.
That's how I notice SQLdb through the year I'm working with.
There are  a several frameworks who's directly save data to a SQLite database without using transactions.
I'm not sure, but ZEOS talks to SQLite direcly.