[Lazarus] SQLdb: Pseudo Transaction?

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

[Lazarus] SQLdb: Pseudo Transaction?

Free Pascal - Lazarus mailing list
Hi,

I am  using Lazarus 2.0.8, package SQLdb, under Win 10  (64). The following procedure raises an exception with message "TSQLite3Connection: Safety level may not be changed inside a transaction". This is strange, since I think there is no active transition in the moment when the exeption is raised.

Why this exception? How to fix? Is it maybe a bug in Lazarus 2.0.8 or in SQLdb?

Code:

procedure TForm1.TestButtonClick(Sender: TObject);
var XConnection:   TSQLite3Connection;
    XTransaction:  TSQLTransaction;
    XDatasource:   TDataSource;
    XQuery:        TSQLQuery;
begin
  XConnection:= TSQLite3Connection.Create(Form1);
  XTransaction:= TSQLTransaction.Create(Form1);
  XDatasource:= TDataSource.Create(Form1);
  XQuery:= TSQLQuery.Create(Form1);
  XQuery.DataBase:= XConnection;
  XQuery.Transaction:= XTransaction;
  XDatasource.DataSet:= XQuery;
  XTransaction.DataBase:= XConnection;
  XConnection.Transaction:= XTransaction;
  XConnection.DatabaseName:= ExtractFilePath (Application.ExeName) + 'D.sqlite';
  XQuery.SQL.Text:= 'PRAGMA synchronous=OFF';
  try
    XQuery.ExecSQL;
    XTransaction.Commit;
  except
    on e: Exception do
    ShowMessage ('Exception "' + e.Message + '"')
  end;
  XQuery.Free;
  XDatasource.Free;
  XTransaction.Free;
  XConnection.Free;
end;

Regards --  Joe

--
_______________________________________________
lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] SQLdb: Pseudo Transaction?

Free Pascal - Lazarus mailing list

Hi Joe,

I'm hardly a FPC/SQLdb expert, but I am somewhat familiar with SQL databases and SQLite in particular.

Your problem is exactly what is expected.

Basically you are trying to execute SQL code in SQLite that changes the underlying DB engine to change the journalling mode (or at least, a component of it, namely "synchronous") while you have already started a transaction "XConnection.Transaction:= XTransaction;".

Now in SQLite, since it is a file-based DB engine, it needs to be able to lock files (with standard OS file-locking semantics) during reading/writing as needed. It is able to provide full ACID transactions by using "Journals" of various types that funtion in various ways. The best is to use WAL journal mode, but the fastest would be DELETE mode, etc.  It aslo has the "synchronous" pragama switch that controls HOW the underlying engine confirms writes to actual hardware via the current VFS (virtual file system) in use. [Think of a VFS like a driver for EXT4, NTFS, whatever you are using.]

With "PRAGMA synchronous = OFF" you are telling the the VFS to stop FSYNCing after writes on your file system. i.e. to not care whether your data has been comitted to the physical hardware, but return immediately after writing and go on with the next thing. This is a nice hack to make it very fast, but it does cause a measure of risk for a power-cut right at the point between sending the write, and accepting it is completed when it really isn't comitted to hardware, will mean data corruption (detected when you next start up).

This is called "SAFETY LEVEL" and for obvious reasons, you cannot change how the safety mechanism acts while already inside a transaction which started on a certain safety level. You have to perform this swicth BEFORE starting that transaction.

Just remove all the transaction bits of your code, and it should work fine.

By the way, if you don't care about safety at all, also make sure the "PRAGMA journal_mode = DELETE;"[1] is called (also before any transaction), set the temp-store to memory[2] and set the cache nice and high[3]. 
[1] https://sqlite.org/pragma.html#pragma_journal_mode
[2] https://sqlite.org/pragma.html#pragma_temp_store
[3] https://sqlite.org/pragma.html#pragma_cache_size

(and if you do care about data safety, don't do any of those!)

(except the cache... you can still do the cache.) :)


Cheers,
Ryan


On 2020/06/30 00:25, Special via lazarus wrote:
Hi,

I am  using Lazarus 2.0.8, package SQLdb, under Win 10  (64). The following procedure raises an exception with message "TSQLite3Connection: Safety level may not be changed inside a transaction". This is strange, since I think there is no active transition in the moment when the exeption is raised.

Why this exception? How to fix? Is it maybe a bug in Lazarus 2.0.8 or in SQLdb?

Code:

procedure TForm1.TestButtonClick(Sender: TObject);
var XConnection:   TSQLite3Connection;
    XTransaction:  TSQLTransaction;
    XDatasource:   TDataSource;
    XQuery:        TSQLQuery;
begin
  XConnection:= TSQLite3Connection.Create(Form1);
  XTransaction:= TSQLTransaction.Create(Form1);
  XDatasource:= TDataSource.Create(Form1);
  XQuery:= TSQLQuery.Create(Form1);
  XQuery.DataBase:= XConnection;
  XQuery.Transaction:= XTransaction;
  XDatasource.DataSet:= XQuery;
  XTransaction.DataBase:= XConnection;
  XConnection.Transaction:= XTransaction;
  XConnection.DatabaseName:= ExtractFilePath (Application.ExeName) + 'D.sqlite';
  XQuery.SQL.Text:= 'PRAGMA synchronous=OFF';
  try
    XQuery.ExecSQL;
    XTransaction.Commit;
  except
    on e: Exception do
    ShowMessage ('Exception "' + e.Message + '"')
  end;
  XQuery.Free;
  XDatasource.Free;
  XTransaction.Free;
  XConnection.Free;
end;

Regards --  Joe


--
_______________________________________________
lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] SQLdb: Pseudo Transaction?

Free Pascal - Lazarus mailing list
Am 30.06.2020 um 01:55 schrieb R.Smith via lazarus:
> Just remove all the transaction bits of your code, and it should work
> fine.

Ryan,

your answer helps a lot; thank you.

Another (maybe stupid) question: What exactly is the purpose of a
'transaction'? Do I need it for each SQL statement to be executed?

Thanx --  Joe
--
_______________________________________________
lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] SQLdb: Pseudo Transaction?

Free Pascal - Lazarus mailing list
>
> Ryan,
>
> your answer helps a lot; thank you.
>
> Another (maybe stupid) question: What exactly is the purpose of a
> 'transaction'? Do I need it for each SQL statement to be executed?


That's an excellent question in fact - lots of confusion out there.

Two main reasons, which I will quickly show - 1 - ACID guarantees
(especially Isolation and Consistency, the others [Atomicity and
Durability] you get anyway), and 2 - Speed.
(https://en.wikipedia.org/wiki/ACID)

In most DB engines (SQLite definitely) most executed commands gets their
own little wrapped transaction if you did not start one explicitly -
just to make the nuts and bolts of the engine function correctly. This
might be a "lesser" form of transaction, but it has to at a minimum
prepare the table, lock tables/rows/whatever, do the reading/writing,
and then release those locks.

This also means that if you issue four commands, let's use an example
(based loosely on a contacts-list table), say you execute these three in
order:

SELECT MAX(ID) FROM contacts;
(The result of the previous statement is put into a variable, say: X)
INSERT INTO contacts(ID, FirstName, LastName, TimeAdded, ....) VALUES
(X+1, 'Joe', 'Soap', Now, ...);
INSERT INTO contacts(ID, FirstName, LastName, TimeAdded, ....) VALUES
(X+2, 'Joe', 'Jones', Now, ...);
INSERT INTO contacts(ID, FirstName, LastName, TimeAdded, ....) VALUES
(X+3, 'Joe', 'Smith', Now, ...);

Now inside the DB Engine, it is wrapped in pseudo-code like this
(Obviously there is more going on, but I'm just mentioning the
interesting bits to our example):

tt = Start_Transaction;
     qq = prepare query( SELECT MAX(ID) FROM contacts; ) :
         if (qq is READ Query)
             obtain_read_lock(tt, qq); // Executed
         ELSE
             obtain_write_lock(tt, qq);// Skipped
         run_query(qq);
         output_results;
         drop_locks(tt, qq);
     release_prepared_resources(qq);
     IF ERRORS > 0
         roll_back(tt);
     ELSE
         commit(tt);
release_transaction(tt);
tt = Start_Transaction;
     qq = prepare query( INSERT INTO contacts(ID, FirstName, LastName,
TimeAdded, ...) VALUES (X+1, 'Joe', 'Soap', Now, ...); ) :
         if (qq is READ Query)
             obtain_read_lock(tt, qq);// Skipped
         ELSE
             obtain_write_lock(tt, qq); // Executed
         run_query(qq);
         output_results;
         drop_locks(tt, qq);
     release_prepared_resources(qq);
     IF ERRORS > 0
         roll_back(tt);
     ELSE
         commit(tt);
release_transaction(tt);
tt = Start_Transaction;
     qq = prepare query(INSERT INTO contacts(ID, FirstName, LastName,
TimeAdded, ...) VALUES (X+2, 'Joe', 'Jones', Now, ...); ) :
         if (qq is READ Query)
             obtain_read_lock(tt, qq); // Skipped
         ELSE
             obtain_write_lock(tt, qq); // Executed
         run_query(qq);
         output_results;
         drop_locks(tt, qq);
     release_prepared_resources(qq);
     IF ERRORS > 0
         roll_back(tt);
     ELSE
         commit(tt);
release_transaction(tt);
tt = Start_Transaction;
     qq = prepare query(INSERT INTO contacts(ID, FirstName, LastName,
TimeAdded, ...) VALUES (X+3, 'Joe', 'Smith', Now, ...); ) :
         if (qq is READ Query)
             obtain_read_lock(tt, qq);   // Skipped
         ELSE
             obtain_write_lock(tt, qq); // Executed
         run_query(qq);
         output_results;
         drop_locks(tt, qq);
     release_prepared_resources(qq);
     IF ERRORS > 0
         roll_back(tt);
     ELSE
         commit(tt);
release_transaction(tt);

The "output_results;" command might be a no-op for INSERT queries,
though some DBs do return values.

Now note some things:
Towards Point 1 above:  If someone else also were trying to insert to
this database at the same time, they might get an X that is in-between
your inserts, and indeed insert values in between. This is called a
concurrancy problem and is fixed by the "isolation" in an ACID transaction.
   (I know that behaviour can well be fixed by declaring the ID column
with AUTO_INCREMENT, but let's imagine it wasn't in this example, you
may well have other columns that must be Unique).

Note also that the inserted values for "Now()" would be slightly
different every time since it's just a littlebit later when the next
INSERT happens.  Inside a transaction, the TimeAdded values will all be
the same.
Basically, while the transaction is in progress, and for the duration of
it, no other connection to the same database can mess with those values,
plus those values will remain consistent for the duration of the
transaction. This is a very important property to many people.
There are a few other considerations to get technical with, like
Serializability modes etc, allowing Dirty-reads, Read-uncommitted data
(they don't fall in the scope of this discussion, but you can look them
up if interested) - i'll only say here that not everyone wants their
transactions to be closed off completely so Database Engines have
switches/ways to allow breaking of the ACID model.

Point 2: Also note that the speed magnificently increases when you
explicitly START the transaction, because the above script and
pseudo-code changes to this:

BEGIN TRANSACTION;
SELECT MAX(ID) FROM contacts;
INSERT INTO contacts(ID, FirstName, LastName, TimeAdded, ....) VALUES
(X+1, 'Joe', 'Soap', Now, ...);
INSERT INTO contacts(ID, FirstName, LastName, TimeAdded, ....) VALUES
(X+2, 'Joe', 'Jones', Now, ...);
INSERT INTO contacts(ID, FirstName, LastName, TimeAdded, ....) VALUES
(X+3, 'Joe', 'Smith', Now, ...);
END TRANSACTION;

tt = Start_Transaction;
     obtain_read_lock(tt);
     qq = prepare query( SELECT MAX(ID) FROM contacts; ) :
         if (qq is WRITE Query)escalate_to_write_lock(tt, qq); // Skipped
         run_query(qq);
         output_results;**
     release_prepared_resources(qq);
     qq = prepare query( INSERT INTO contacts(ID, FirstName, LastName,
TimeAdded, ...) VALUES (X+1, 'Joe', 'Soap', Now, ...); ) :
         if (qq is WRITE Query) if (qq is WRITE
Query)escalate_to_write_lock(tt, qq); // Executed
         run_query(qq);
     release_prepared_resources(qq);
     qq = prepare query( INSERT INTO contacts(ID, FirstName, LastName,
TimeAdded, ...) VALUES (X+2, 'Joe', 'Jones', Now, ...); ) :
if (qq is WRITE Query) if (qq is WRITE Query)escalate_to_write_lock(tt,
qq); // No-op because write-lock is already held.
         run_query(qq);
     release_prepared_resources(qq);
     qq = prepare query( INSERT INTO contacts(ID, FirstName, LastName,
TimeAdded, ...) VALUES (X+3, 'Joe', 'Smith', Now, ...); ) :
if (qq is WRITE Query) if (qq is WRITE Query)escalate_to_write_lock(tt,
qq); // No-op because write-lock is already held.
         run_query(qq);
     release_prepared_resources(qq);
     IF ERRORS > 0
        roll_back(tt);
     ELSE
        commit(tt);
     output_results;**
release_transaction(tt);

** - The exact times and places when results are output in queries
differ from Engine to Engine and can be controlled with switches in
some. Typically inline after selects, but sometimes you only want to see
the results that actually made it into the database (i.e. at the END of
the transaction).

You can of course get even faster when using multi-line inserts and
prepared statements - but that's also another discussion.

Note also that only transactions with explicit errors will be rolled
back by the Engines typically. If there is an error (a thing that you
don't want to commit to the data) but it isn't a problem to commit it
for the DB engine, then you have to explicitly tell it to roll back. (In
sqlite this is just querying the command: ROLLBACK;)

There are also conflict handlers which you can specify on how you want
to proceed when a conflict (error) occurs, but again, that's another
topic, this one is too long already. :)

Cheers,
Ryan



--
_______________________________________________
lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] SQLdb: Pseudo Transaction?

Free Pascal - Lazarus mailing list
Hi, Ryan,

I followed your advice and removed XTransaction and all references to it
from TestButtonClick. But now I get an exception with the message
"Transaction not set". Any hint?
Regards --  Joe
--
_______________________________________________
lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] SQLdb: Pseudo Transaction?

Free Pascal - Lazarus mailing list


On Tue, 30 Jun 2020, Special via lazarus wrote:

> Hi, Ryan,
>
> I followed your advice and removed XTransaction and all references to it
> from TestButtonClick. But now I get an exception with the message
> "Transaction not set". Any hint?

With SQLDB you always need a transaction component.

What you can do is set stoUseImplicit in the Transaction.Options:

https://www.freepascal.org/docs-html/current/fcl/sqldb/tsqltransaction.options.html

In that case the implicit transaction handling of the DB engine (SQLite in
this case) will be used, no explicit transaction statements will be emitted.

Michael.
--
_______________________________________________
lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] SQLdb: Pseudo Transaction?

Free Pascal - Lazarus mailing list
In reply to this post by Free Pascal - Lazarus mailing list
On 2020/06/30 22:52, Special via lazarus wrote:
> Hi, Ryan,
>
> I followed your advice and removed XTransaction and all references to
> it from TestButtonClick. But now I get an exception with the message
> "Transaction not set". Any hint?
> Regards --  Joe

I'm sorry, the previous problem was SQLite-related, which I'm
initimately familiar with, but this one seems to be a Lazarus DB
component requirement which I'm not so much familiar with, but probably
has an easy solution someone here might know about.

The question becomes:  How to execute a query, without a transaction
being set, using SQLdb in Lazarus?


PS: In case you wonder, we're in the process of porting an existing
SQLite management tool (https://sqlitespeed.com) to open-source
multi-platform from Delphi to FPC and Lazarus - It's been quite a job
but going well, and I love the Lazarus/FPC environment more and more,
will never go back to Delphi/VS. I do however access SQLite through the
API rather than the SQLdb provided components, which is why I can tell
you lots about SQL and SQLite but not so much about SQLdb... So
apologies for that - hope someone can answer this with some more
pertinent knowledge.



--
_______________________________________________
lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] SQLdb: Pseudo Transaction?

Free Pascal - Lazarus mailing list
El 1/7/20 a les 0:40, R.Smith via lazarus ha escrit:

> On 2020/06/30 22:52, Special via lazarus wrote:
>> Hi, Ryan,
>>
>> I followed your advice and removed XTransaction and all references to
>> it from TestButtonClick. But now I get an exception with the message
>> "Transaction not set". Any hint?
>> Regards --  Joe
>
> I'm sorry, the previous problem was SQLite-related, which I'm
> initimately familiar with, but this one seems to be a Lazarus DB
> component requirement which I'm not so much familiar with, but probably
> has an easy solution someone here might know about.
>
> The question becomes:  How to execute a query, without a transaction
> being set, using SQLdb in Lazarus?

I use zeos instead of sqldb. Most probably it's my fault and it could be
solved by doing what Michael said (use stoUseImplicit), but when I tried
to use sqldb with sqlite, as long as I kept the dataset open (to show
the data in a dbgrid) the table was locked and couldn't be modified by a
different thread/process.
Probably just a matter of different defaults (zeos doesn't need a
separate transaction component and the default TransactionIsolationLevel
is tiNone).

Bye
--
Luca Olivetti
Wetron Automation Technology http://www.wetron.es/
Tel. +34 93 5883004 (Ext.3010)  Fax +34 93 5883007
--
_______________________________________________
lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] SQLdb: Pseudo Transaction?

Free Pascal - Lazarus mailing list


On Wed, 1 Jul 2020, Luca Olivetti via lazarus wrote:

> El 1/7/20 a les 0:40, R.Smith via lazarus ha escrit:
>> On 2020/06/30 22:52, Special via lazarus wrote:
>>> Hi, Ryan,
>>>
>>> I followed your advice and removed XTransaction and all references to
>>> it from TestButtonClick. But now I get an exception with the message
>>> "Transaction not set". Any hint?
>>> Regards --  Joe
>>
>> I'm sorry, the previous problem was SQLite-related, which I'm
>> initimately familiar with, but this one seems to be a Lazarus DB
>> component requirement which I'm not so much familiar with, but probably
>> has an easy solution someone here might know about.
>>
>> The question becomes:  How to execute a query, without a transaction
>> being set, using SQLdb in Lazarus?
>
> I use zeos instead of sqldb. Most probably it's my fault and it could be
> solved by doing what Michael said (use stoUseImplicit), but when I tried
> to use sqldb with sqlite, as long as I kept the dataset open (to show
> the data in a dbgrid) the table was locked and couldn't be modified by a
> different thread/process.
The stoUseImplicit is one way to solve this.

An alternative was to disable the need to keep an active transaction using
TSQLQuery.Options : add sqoKeepOpenOnCommit.

see
https://www.freepascal.org/docs-html/current/fcl/sqldb/tsqlqueryoptions.html

Then you can do

Dataset.open;
MyTransaction.Commit;

And the table will not be locked.

Michael.
--
_______________________________________________
lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] SQLdb: Pseudo Transaction?

Free Pascal - Lazarus mailing list
Am 01.07.2020 um 12:54 schrieb Michael Van Canneyt via lazarus:


On Wed, 1 Jul 2020, Luca Olivetti via lazarus wrote:

El 1/7/20 a les 0:40, R.Smith via lazarus ha escrit:
On 2020/06/30 22:52, Special via lazarus wrote:
Hi, Ryan,

I followed your advice and removed XTransaction and all references to it from TestButtonClick. But now I get an exception with the message "Transaction not set". Any hint?
Regards --  Joe

I'm sorry, the previous problem was SQLite-related, which I'm initimately familiar with, but this one seems to be a Lazarus DB component requirement which I'm not so much familiar with, but probably has an easy solution someone here might know about.

The question becomes:  How to execute a query, without a transaction being set, using SQLdb in Lazarus?

I use zeos instead of sqldb. Most probably it's my fault and it could be solved by doing what Michael said (use stoUseImplicit), but when I tried to use sqldb with sqlite, as long as I kept the dataset open (to show the data in a dbgrid) the table was locked and couldn't be modified by a different thread/process.

The stoUseImplicit is one way to solve this.

An alternative was to disable the need to keep an active transaction using
TSQLQuery.Options : add sqoKeepOpenOnCommit.

see
https://www.freepascal.org/docs-html/current/fcl/sqldb/tsqlqueryoptions.html

Then you can do

Dataset.open;
MyTransaction.Commit;

And the table will not be locked.

Michael.


Hi, Ryan, Luca, Michael and all others,

to Transaction.Options I added 'stoUseImplicit'. Now I can get and set 'PRAGMA locking_mode' and 'PRAGMA  synchronize'.
Thank you. Regards --  Joe




--
_______________________________________________
lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus