SQLTransaction always active after sqlquery open/close
lazarus / 16-Databases

----------------------------------------------------------------------
wittbo · 2019-05-28 14:15

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-

----------------------------------------------------------------------
Zoran · 2019-05-28 18:10

I have not used sqlite, but I believe that sqlite is just not intended
for concurrent usage.

----------------------------------------------------------------------
dsiders · 2019-05-28 19:53

Quote from: wittbo on May 28, 2019, 04:15:41 pmI 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.

----------------------------------------------------------------------
wittbo · 2019-05-29 04:15

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 ..."