How to use SQLite with ZEOS in a transaction?

[freemaker]

Hi, friend! My function is to insert about 400 sentences, and it takes about 20 seconds in the execution. Could you tell me the problems ?

function ExecSQL: Boolean;
var
ZQuery: TZQuery;
begin
Result := True;
ZQuery := TZQuery.Create(nil);
try
try
ZConn.StartTransaction;
ZQuery.Connection := ZConn;
ZQuery.SQL.Clear;
ZQuery.SQL.LoadFromFile('D:\Init.sql');
ZQuery.ExecSQL;
ZConn.Commit;
except
Result := False;
ZConn.Rollback;
end;
finally
FreeAndNil(ZQuery);
end;
end;

"D:\Init.sql":
CREATE TABLE IF NOT EXISTS [test]([id] int PRIMARY KEY NOT NULL, [name] varchar(32) NOT NULL);
INSERT INTO [test]([id],[name]) VALUES(1,'New York');
INSERT INTO [test]([id],[name]) VALUES(2,'London');
INSERT INTO [test]([id],[name]) VALUES(3,'Paris');
INSERT INTO [test]([id],[name]) VALUES(4,'Hongkong');
..........

[trupka]

Don't leave ZConn.TransactionIsolationLevel to tiNone. Change it ( eg. readCommited ) or StartTransaction, Commit and Rollback methods wont work.

[freemaker]

trupka: thank you very much! it takes about 1 second at most now, so amazed!

[touchring]

Don't leave ZConn.TransactionIsolationLevel to tiNone. Change it ( eg. readCommited ) or StartTransaction, Commit and Rollback methods wont work.

Thanks, i've tried this method, it does speed up the inserts tremendously, but there's a problem - when i tried to do a select on the same table at the same time using another app (the query was successful), the 'insert app' gives a "database is locked" exemption and rolls back all the inserts.

INSERT:

Code: Select all

procedure TForm1.Button2Click(Sender: TObject);
var i:integer;
  siz : longint;
  hr, mins, se, s1  : word;
  hr2, min2, se2  : word;

begin
try
  GetTime (hr,mins,se,s1);

 if FileExists (ZConnection1.Database) then
  begin

  ZConnection1.connect;
 ZConnection1.StartTransaction; 
   ZQuery1.Close;
   ZQuery1.SQL.Clear;

   
   For i := 1 to 10000 do
   begin
   ZQuery1.SQL.Add ('INSERT INTO t2 VALUES(24999,6,'+inttostr(i)+');');
   end;
   ZQuery1.ExecSQL;
    ZConnection1.commit;          

  end
  else
    MessageDlg('Database file cannot be found: ' +
           ZConnection1.Database, mtInformation, [mbok], 0);
except
    on E : Exception do begin
      ShowMessage(E.ClassName+' error raised, with message : '+E.Message);
      ZConnection1.Rollback;
    end;
end;
 GetTime (hr2, min2, se2, s1);
  siz := se2-se+(min2-mins)*60+(hr2-hr)*60*60;
  ShowMessage (IntToStr(siz) + ' seconds');
 FreeAndNil(ZQuery1);
end;           
   

SELECT:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
try
if FileExists (ZConnection1.Database) then
  begin
  ZConnection1.connect;
   ZQuery1.Close;
   ZQuery1.SQL.Clear;
    ZQuery1.SQL.Text := Memo1.Text;
    if CheckBox1.Checked then begin
    showmessage(ZQuery1.SQL.Text);
      ZQuery1.Open;
    end
    else begin
      ZQuery1.ExecSQL;
      end;
      end
  else
    MessageDlg('Database file cannot be found: ' +
           ZConnection1.Database, mtInformation, [mbok], 0);

except
    on E : Exception do
      ShowMessage(E.ClassName+' error raised, with message : '+E.Message);
  end;
end;

Meanwhile, i searched through the Internet, and found out that if i amended the code as follows, it will speed up the INSERTS while at the same time avoid the "db locked" error if i try and do SELECTS on the table. Note this method only works if only if TransactionIsolationLevel is set to none.

Code: Select all

procedure TForm1.Button2Click(Sender: TObject);
var i:integer;
  siz : longint;
  hr, mins, se, s1  : word;
  hr2, min2, se2  : word;

begin
try
  GetTime (hr,mins,se,s1);

 if FileExists (ZConnection1.Database) then
  begin

  ZConnection1.connect;

   ZQuery1.Close;
   ZQuery1.SQL.Clear;

   //take sqlite out of autocommit mode.
   ZQuery1.SQL.text:= 'BEGIN TRANSACTION';
   ZQuery1.Open;
   ZQuery1.SQL.Clear;
   //

   For i := 1 to 10000 do
   begin
   ZQuery1.SQL.Add ('INSERT INTO t2 VALUES(24999,6,'+inttostr(i)+');');
   end;
   ZQuery1.ExecSQL;


   //revert to autocommit mode.
   ZQuery1.SQL.Clear;
   ZQuery1.SQL.text:= 'COMMIT TRANSACTION';
   ZQuery1.Open;
   //

  end
  else
    MessageDlg('Database file cannot be found: ' +
           ZConnection1.Database, mtInformation, [mbok], 0);
except
    on E : Exception do begin
      ShowMessage(E.ClassName+' error raised, with message : '+E.Message);
      ZConnection1.Rollback;
    end;
end;
 GetTime (hr2, min2, se2, s1);
  siz := se2-se+(min2-mins)*60+(hr2-hr)*60*60;
  ShowMessage (IntToStr(siz) + ' seconds');
 FreeAndNil(ZQuery1);
end;    




   

The question is, am i doing the right thing? Appreciate any pointers.

[trupka]

I'ts SQLite known "writer starvation" problem - selects can block inserts.
Zeos always keep transaction opened - "BEGIN TRANSACTION" is issued right after old transaction is closed and/or ZConnection.TransactionIsolationLevel <> tiNone. In other words - if TransactionIsolationLevel <> tiNone, application is always inside transaction. After first select, sqlite locks database and other app can't insert into it.
Your solution is good workaround. StartTransaction = tiNone ensures that BEGIN TRANSACTION command won't be prematurely (unnecessary) executed. Then transaction start when needed, inside insert SQL. Of course, you can do something like ZConnection.ExecuteDirect('BEGIN TRANSACTION'), it has same effect.