[Lazarus] SQLdb: Pseudo Transaction?

[R.Smith]

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus-ide.org/pipermail/lazarus/attachments/20200630/ae271143/attachment-0001.html>