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

[stoppok]

Hi,

I'm struggling with SQLdb's transaction handling, it closes all my datasets when commiting changes in one dataset.

Objects:

    FSQLite3Connection : TSQLite3Connection;
    FSQLTransaction: TSQLTransaction;
    FSQLQuery1 : TSQLQuery;
    FSQLQuery2 : TSQLQuery;

sample program flow:

   FSQLQuery1.Open;
   FSQLQuery2.Open;
   ....
   Displaying & editing data is fine
   ...
   FSQLQuery2.ApplyUpdates;
   FSQLTransaction.Commit;

Ouch! FSQLquery1 & FSQLQuery2 are closed now!

I've tried to create one Transaction-object per dataset but this results in "cannot start a transaction in a transaction" already when I open the second dataset.


Any ideas?

Best regards,

  Stefan

BTW: Lazarus 1.2 & 1.6, Win XP & Win 7 32bit

[LacaK]

Look for example at: http://wiki.freepascal.org/Working_With_TSQLQuery
Search for CommitRetaining or if you use FPC 3.0 use TSQLQuery.Options :  sqoKeepOpenOnCommit

[Thaddy]

Try CommitRetaining instead of commit,

[stoppok]

Thanks a lot,

sqoKeepOpenOnCommit & CommitRetaining together did the trick.

bye,

  Stefan

[LacaK]

It should work with only sqoKeepOpenOnCommit or CommitRetaining (they are alternatives)

[goodname]

Both sqoKeepOpenOnCommit and CommitRetaining will keep the result set open but they are not the same for transaction handling.

Using sqoKeepOpenOnCommit with Commit will keep the result set and close the transaction.

Using CommitRetaining will keep the result set and open a new transaction. I do not recommend using CommitRetaining unless you have a good reason for keeping an open transaction.

[stoppok]

Hi goodname,

thanks for your comment, I already thought to myelf that this solution is a little bit to simple:-(

The problem is that "Commit" instead of "CommitRetaining" will give other errors when I try to modify the second dataset. Maybe I have to open the transaction again, maybe I have to reopen all databases, just did'nt want to go into detail at the moment because my current application does not require multi-user handling so I use transactions ony because SQLDb requires them.

On the other hand I know the day will come.... Dealing with databases and database components (Delphi) for about 15 years I find it very difficult to deal with SQLDb's transaction handling. From my point of view the transaction handling is broken by design (maybe just my limited view), because it seems to open a transaction even for read-only operations.

Question: Any recommendation how to work with 2 or more open datasets and have proper transaction handling? I had a quick look at tutorials and examples but did not find the key to my problem.

Again a short summary of what  want:

  * open 2+ datasets
  * NO transaction active at all
  * let user change data in 1st datase
  * Database.StartTransaction
  * dataset1.applyupdates
  * Database.Commit
  * let user change data in 2nd dataset
  * Database.StartTransaction
  * dataset2.applyupdates
  * Database.Commit
  * ....

Best regards,

  Stefan

[Thaddy]

On the other hand I know the day will come.... Dealing with databases and database components (Delphi) for about 15 years I find it very difficult to deal with SQLDb's transaction handling. From my point of view the transaction handling is broken by design (maybe just my limited view), because it seems to open a transaction even for read-only operations.

Well, you are not forced to use transactions, certainly not in the case of Sqlite.
Transactions are not a concept native to Freepascal, they actually are also present in Delphi.
In complex applications they are in both flavors of Pascal the preferred method. Strange you find them hard to use or flawed: they aren't.
You can also use TSqlite3Dataset directly. Problem solved.

But indeed, transactions are tightly bound to a TYPE of transaction. Be it a select, insert or update. If you use CommitRetaining use it only for the same TYPE of transaction, otherwise commit and close.
Re-open the transaction ( or recreate) if the type of transaction differs is good practice and may help in your case.
Otherwise: use TSqlite3Dataset directly. No transactions needed. Again, Delphi offers the same options.

The reason that transactions are used even for read-only operations is that e.g. a database can be fully locked because of maintenance (re-writes, re-index) which can lead to invalid reads/indices/caches.
Transactions are really there for multi-user database use.

[stoppok]

Using TSqlite3Dataset is no option because some of my Delphi-applications can work with sqlite / MSSql / postgres / mysql without any change except creating the right connection-object object. (Of yourse there are some helper-routines, but not in the dataset-datamodules).

I'll give it another try taking you recommendations into account. In fact SQLDb seems very very nice and easy with the exception of transactions. I remember there have been discussions about this before...

bye,

  Stefan

[LacaK]

I use SQLite with sqlDB and CommitRetaining without problems ... what errors do you get in this scenario ?

[stoppok]

@LacaK: With CommitRetaining my app was working but Thaddy suggested to use it with care

@All:
Because I was still feeling uncomfortable with TSQLTransaction (eg. opening a transaction even for simple select statements, closing datasets on commit, etc....) I searched for a way to avoid this. Finally I found the following solution which seems to satisfy my needs:

  1) Using TSQLTransaction with Option "stoUseImplicit"
  ==> SQLDb / SQLQuery will no longer start a transaction on the SQLConnection

  2) Whenever I need a transaction I use low-level stuff: SQLConnection.ExecSQL ('begin') / ('commit') / ('rollback')
  ==> The application is responsible for transactions and refreshing it's datasets

Of course this brings some database-specific code into my application but this will be centralized in a helper class so it should be easy to use another database backend.

[goodname]

For many database engines stoUseImplicit means that the database opens a transaction long enough to complete your query then closes it automatically for you. The client application does not have to worry about the details. If your query does not depend on another query then this is a good solution.

It is more efficient to put as many queries into a single transaction as possible. For example if you have many read only Select queries then you can use sqoKeepOpenOnCommit with Commit. It should be more efficient then using stoUseImplicit which has the database open and close a new transaction for each query. stoUseImplicit is easier to implement.

If your queries do depend on each other such as when you need to get the last inserted ID so you can insert into another table then you must use transactions. SQLdb has done a good job of hiding the details of transaction handling and getting last inserted id handling between different database engines. There should be no need to create database specific code to handle these situations.

[stoppok]

For many database engines stoUseImplicit means that the database opens a transaction long enough to complete your query then closes it automatically for you. The client application does not have to worry about the details. If your query does not depend on another query then this is a good solution.

Correct, thats how I understand stoUseImplicit and it is exactly what I want it to do for 99% of all "Select" statements. Of courdes there may be situations (very large selects) where it is neccessary to explicitly start a transaction.

If your queries do depend on each other such as when you need to get the last inserted ID so you can insert into another table then you must use transactions. SQLdb has done a good job of hiding the details of transaction handling and getting last inserted id handling between different database engines. There should be no need to create database specific code to handle these situations.

Yes, I saw that SQLdb solves this issue and that is nice. The only thing I don't like is it's transaction handling because the transaction is open as long as my client needs to see the dataset(which may be very long). This is contrary to what I know as best practice: Keep an transaction as short as possible.

Concerning database specific code: I'll ask SQLdb developers if they can make TSQLConnection.StartDBTransaction/Commit/Rollbacl functions public. That way I would not need a helper class.

[goodname]

Of courdes there may be situations (very large selects) where it is neccessary to explicitly start a transaction.
...
 the transaction is open as long as my client needs to see the dataset(which may be very long). This is contrary to what I know as best practice: Keep an transaction as short as possible.

The size of a select, insert, update, or delete query has nothing to due with the need to manually manage transaction lifetime. There is nothing wrong with putting your insert, update, or delete in a stoUseImplicit transaction. The only time explicit transaction management is required is when two queries depend on each other either for business logic (moving money from one account to another, both updates must happen or fail) or data dependency (getting last inserted id to insert into another table).

Yes long running transactions is a bad thing and was a problem for SQLdb before stoUseImplicit and sqoKeepOpenOnCommit became available.

[gajahbengkak]

1) I got the "cannot start a transaction in a transaction" then i look into the sqlite3conn unit and found that TSqlite3connection doesn't have transaction handle and cannot have multiple TSQLTransaction in the same TSQLConnection, use TSQLConnection per TSQLTransaction.

2) In my knowledge, SQLite 3 have 3 transaction mode, deffered (default), immediate, exclusive. Deffered mode do nothing when start transaction executed and automatically lock the database when the program first access the database so no other transaction can start write lock but still able to read the database. Immediate will lock the database immediately. Exclusive mode will immediately lock the entire database execpt read uncommited transactions. So it's wise to close transaction (commit/rollback) transaction immediately after finish modifiying database.

Correct me if i'm wrong.