Starting transaction fails with "SQL logic error"

[Graham88]

I am running Lazarus 2.2 under Windows 11 64 bit. I have a program inserting multiple rows into a sqlite3 database using Zeos 7.14.
The inserts are very slow so I tried to speed them up by grouping all the inserts into a transaction. Thus:

DM1.ZCDeflation.StartTransaction;
..
Insert some rows via simple INSERT statements
..
DM1.ZCDeflation.Commit;

but when I do this the StartTransaction command fails with a SQL logic error. Tracing through the code when it enters the CheckSQLiteError procedure the third parameter, ErrorCode, has been set to 1 but I'm having difficulty seeing how it gets this value.
Autocommit is set to true and isolation level is ReadCommitted.

I don't know if it is relevant but before inserting individual rows I start by running an INSERT .. SELECT statement. This has a StartTransaction and works fine:


DM1.ZCDeflation.StartTransaction;
DM1.ZQLoadInitialRating.ParamByName('RUN_ID').AsInteger := run_id;
DM1.ZQLoadInitialRating.ExecSQL; <--- this is the INSERT .. SELECT
DM1.ZCDeflation.Commit;

I'm puzzled as to why I get error in one case but not in the other. Any suggestions would be appreciated.
Graham

[marsupilami]

Hello Graham,

I am not sure, if SQLite transaction support is started by default on Zeos 7.2. Could you please add the following line to TZConnection.Properties?

ExtendedErrorMessage=1

This should enable SQLite extended error information. Maybe this will help in finding the problem.

Best regards,

Jan

[Graham88]

Hi Jan,

I added this line to the code some time earlier but it didn't seem to make any difference:


DM1.ZCDeflation.Properties.Add('ExtendedErrorMessage=true');

Running under the IDE I just get "SQL error. SQL Logic Error'

Running the exec directly (not under the IDE) gives the same message.
Graham

[Graham88]

I should add that substituting "Autocommit := false" in place of 'starttransaction' results in the same error.

[marsupilami]

Hmmm - for me the following works as expected:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  Conn: TZConnection;
begin
  Conn := TZConnection.Create(self);
  Conn.Protocol := 'sqlite';
  Conn.ControlsCodePage := cCP_UTF8;
  Conn.Database := 'D:\Projekte\Zeos\sqlite-x86-3390300\test.sqlite';
  Conn.LibraryLocation := 'D:\Projekte\Zeos\sqlite-dll-win32-x86-3420000\sqlite3.dll';
  Conn.Properties.Add('ExtendedErrorMessage=1');
  Conn.Connect;
  Conn.StartTransaction;
  ShowMessage('transaction started');
  Conn.Rollback;
  Conn.Disconnect;
end;

Could you maybe create a small example (code, database) that we can use to debug the problem?

[Graham88]

Hi marupilami,
The code I have works in part of the program but not in another, which is what I find confusing. I can upload all the code if you'd like but I don't think I can create a sample that will reliably reproduce the problem. Program begins with
action := 'ATTACH DATABASE ' + QuotedStr(history_db) + ' AS history;';
DM1.ZCDeflation.ExecuteDirect(action);
DM1.ZCDeflation.StartTransaction;
DM1.ZQLoadInitialRating.ParamByName('RUN_ID').AsInteger := run_id;
DM1.ZQLoadInitialRating.ExecSQL; // This runs an INSERT SELECT that inserts 1600 rows. This code all works fine.
DM1.ZCDeflation.Commit;
action := 'DETACH DATABASE history;';
DM1.ZCDeflation.ExecuteDirect(action);
Then the program runs a sql query and puts the output into a binary tree. Then it traverses the tree and does a singleton INSERT for each node.
This works correctly but is very slow. It takes about ten minutes when traversing 3,000 nodes.
So I added a transaction around the tree traversal:
DM1.ZCDeflation.StartTransaction;
traverse(root);
DM1.ZCDeflation.Commit;
and it fails on the StartTransaction command.
I set up a breakpoint on the first StartTransaction statement and it did what looks correct - setting Autocommit to false, checking transaction count, then actually starting the transaction.
A breakpoint on the second StartTransaction and a trace shows the difference;

procedure TZSQLiteConnection.ExecTransactionStmt(
Action: TSQLite3TransactionAction);
var
pzTail: PAnsiChar;
begin
with FTransactionStmts[Action] do begin
if Stmt = nil then <--- first StartTransaction command goes though this branch and CheckSQLiteError results in SQ_OK
CheckSQLiteError(GetPlainDriver, FHandle,
GetPlainDriver.Prepare(FHandle, Pointer(SQL), nBytes, Stmt, pzTail{%H-}),
lcTransaction, SQL, ConSettings, FExtendedErrorMessage);
try
CheckSQLiteError(FPlainDriver, FHandle, FPlainDriver.Step(Stmt), <-- second StartTransaction command goes directly here
and results in a code of SQLITE_ERROR
lcTransaction, SQL, ConSettings, FExtendedErrorMessage);
if Assigned(DriverManager) and DriverManager.HasLoggingListener then
DriverManager.LogMessage(lcTransaction, ConSettings^.Protocol, SQL);
finally
FPlainDriver.reset(Stmt);

So Stmt is NIL on the first call but not on the second. Why this results in an error I don't know.
end;
end;

I zipped the project but it's too large to be uploaded here.

[Graham88]

More news. I changed the program to use the standard sqlite3 objects supplied with Lazarus for the inserts and it now works fine. Inserts about 1000 rows in a tenth of a second. So now I have a hybrid program part Zeos, part standard. I'm not happy with this mixture so if I can't find a solution I'll drop Zeos and convert everything to TSqlite3Connection. What annoys me about this is its insistence on using transactions for everything, even for just reading rows. As an old DB2 guy, this strikes me as pointless overhead and is the major reason I started using Zeos.

[marsupilami]

Hmm - that sounds strange. Without a reproducible example it will be hard to debug.

I could create a link on our nextcloud instance and send it to you via PN, so you could upload your project there.

Could you create a log using TZSQLMonitor? Maybe that reveals something strange?

With best regards,

Jan