Bookstore

Recent

Author Topic: SQLTransaction always active after sqlquery open/close  (Read 552 times)

wittbo

  • Full Member
  • ***
  • Posts: 103
SQLTransaction always active after sqlquery open/close
« on: May 28, 2019, 04:15:41 pm »
I have a small application using a sqlite database, 2 screens showing some contents with datagrids, inserting new records with sqlconnection.executedirect, no explicit transaction handling. Everything is working well.

Up to now this was a single user, single platform application. Now the application is intended to run on two or more computers and the database should be hold on a network drive. Therefore I must meet providence against concurrent write locks. The idea is to encapsulate the executedirect Statement into a StartTransaction try ... except on E: exception do ...rollback; frame.

Maybe a good idea, but: upon executing the starttransaction statement, I get an error message, that there is already an open transaction. When searching for the reason, I found, that after every open of a sqlquery the active flag of the corresponding transaction is set to true and it remains true even after closing the query. This behaviour prevents the starttransaction working correctly. As a workaround I tested an additional endtransaction statement before starttransaction, this seems to be OK.

Any hints of how to end a Transaction after closing a query correctly? It does not seem to be reasonable, that a transaction remains active and all queries are closed. Or do I understand something wrong?

-wittbo-
-wittbo-
MBAir with MacOS 10.14.6 / Lazarus 2.0.6
iMac with MacOS 10.13.6 / Lazarus 2.0.2

Zoran

  • Hero Member
  • *****
  • Posts: 1499
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: SQLTransaction always active after sqlquery open/close
« Reply #1 on: May 28, 2019, 08:10:10 pm »
I have not used sqlite, but I believe that sqlite is just not intended for concurrent usage.

dsiders

  • Sr. Member
  • ****
  • Posts: 277
Re: SQLTransaction always active after sqlquery open/close
« Reply #2 on: May 28, 2019, 09:53:28 pm »
I have a small application using a sqlite database, 2 screens showing some contents with datagrids, inserting new records with sqlconnection.executedirect, no explicit transaction handling. Everything is working well.

Up to now this was a single user, single platform application. Now the application is intended to run on two or more computers and the database should be hold on a network drive. Therefore I must meet providence against concurrent write locks. The idea is to encapsulate the executedirect Statement into a StartTransaction try ... except on E: exception do ...rollback; frame.

Maybe a good idea, but: upon executing the starttransaction statement, I get an error message, that there is already an open transaction. When searching for the reason, I found, that after every open of a sqlquery the active flag of the corresponding transaction is set to true and it remains true even after closing the query. This behaviour prevents the starttransaction working correctly. As a workaround I tested an additional endtransaction statement before starttransaction, this seems to be OK.

Any hints of how to end a Transaction after closing a query correctly? It does not seem to be reasonable, that a transaction remains active and all queries are closed. Or do I understand something wrong?

-wittbo-

The TSQLTransaction component has an Options property to control how trasactions are started. Make sure it contains stoExplicitStart and not stoUseImplicit.

Sqlite will not deliver what you're looking for in a multi-user environment. See https://www.sqlite.org/faq.html#q5. Use Firebird SQL.
Lazarus 2.0.6 / Lazarus 2.1 (SVN) / FPC 3.0.4 / Windows 8.1 64-bit

wittbo

  • Full Member
  • ***
  • Posts: 103
Re: SQLTransaction always active after sqlquery open/close
« Reply #3 on: May 29, 2019, 06:15:14 am »
When setting stoExplicitStart = True and stoUseImplicit = False there is an error message on opening the first sqlquery with a simple select statement:
"Error: attempt to implicitly start a transaction on Connection ..."
-wittbo-
MBAir with MacOS 10.14.6 / Lazarus 2.0.6
iMac with MacOS 10.13.6 / Lazarus 2.0.2