SQLite + Multiple Threads

[Stamp]

Using latest Unidac on Delphi 2010:

I get "database is locked" when two queries are being done simultaneously on different threads. Each query has its own tUniQuery with a separate instance of tUniConnection in the same application. I've tried it with both disconnected mode and DB pooling turned on and off.

This code was working previously when using the MyDac trial, and now I am adding SQLite support with Unidac.

Thoughts?

Code: Select all

function CreateDBConnection():tUniConnection;
begin
  result := tUniConnection.create(nil);

  result.ProviderName := 'SQLite';

  if result.ProviderName = 'MySQL' then
    begin
      result.Server := 'localhost';
      result.UserName := 'user';
      result.Password := 'pass';
      result.database := 'db';
      result.Port := 3306;
    end
  else if result.ProviderName = 'SQLite' then
    begin
      result.database := GetCurrentDir() + '\db.db';


    end;

  //Tried with on and off
  result.Pooling := true;
  result.PoolingOptions.MaxPoolSize := 100;
  result.PoolingOptions.ConnectionLifetime := 30000;  //30 seconds

  //tried with on and off. Worked fine with off using MyDac.
  result.Options.DisconnectedMode := true;

  //Macros
  SetDBMacros(result);

  result.Connect;
end;

procedure FreeDBConnection(DB : tUniConnection);
begin
  tUniConnection(DB).Disconnect;
  tUniConnection(DB).free;
end;

Edit: This is used with RemObjects SDK. A call to the Remobjects SDK would spawn a new thread.

Edit2: The queries in question are parameterized, hence the multiple connection instances.

[AlexP]

Hello,

I can not reproduce the problem.
Please try to execute the following code:

Code: Select all

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, DBAccess, Uni, UniProvider, SQLiteUniProvider, MemDS, StdCtrls;

type
  TMyThread = class(TThread)
    FMyConnection: TUniConnection;
    FMyQuery: TUniQuery;
  protected
    procedure execute; override;
  published
  public
    property MyConnection: TUniConnection read FMyConnection write FMyConnection;
    property MyQuery: TUniQuery read FMyQuery write FMyQuery;
  end;

  TMyThread1 = class(TThread)
    FMyConnection: TUniConnection;
    FMyQuery: TUniQuery;
  protected
    procedure execute; override;
  published
  public
    property MyConnection: TUniConnection read FMyConnection write FMyConnection;
    property MyQuery: TUniQuery read FMyQuery write FMyQuery;
  end;


  TForm1 = class(TForm)
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
    FConnection: TUniConnection;
    function CreateDBConnection:TUniConnection;
    function CreateQuery(Connection:TUniConnection):TUniQuery;
  public
    { Public declarations }
  end;

var
  Form1: TForm1;
  MyThread1: TMyThread;
  MyThread2: TMyThread1;

implementation

{$R *.dfm}

{ TMyThread }

procedure TMyThread.execute;
begin
  FMyQuery.Open;
end;


{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
begin
  FConnection:= TUniConnection.Create(nil);
  FConnection.ProviderName := 'SQLite';
  FConnection.database := 'D:\test.db3';
  FConnection.ExecSQL('CREATE TABLE test(name VARCHAR2(50),ID NUMBER(1));', [null]);
  FConnection.Free;
  MyThread1:= TMyThread.Create(true);
  MyThread1.FMyConnection:= CreateDBConnection;
  MyThread1.FMyQuery:= CreateQuery(MyThread1.FMyConnection);
  MyThread2:= TMyThread1.Create(true);
  MyThread2.FMyConnection:= CreateDBConnection;
  MyThread2.FMyQuery:= CreateQuery(MyThread2.FMyConnection);

  MyThread1.Execute;
  MyThread2.Execute;
end;

function TForm1.CreateDBConnection: TUniConnection;
begin
  result := TUniConnection.create(nil);

  result.ProviderName := 'SQLite';
  result.database := 'D:\test.db3';
  result.Pooling := true;
  result.PoolingOptions.MaxPoolSize := 100;
  result.PoolingOptions.ConnectionLifetime := 30000;
  result.Options.DisconnectedMode := true;

  result.Connect;
end;

function TForm1.CreateQuery(Connection:TUniConnection): TUniQuery;
begin
  result := TUniQuery.create(nil);
  result.Connection:=Connection;
  result.SpecificOptions.Values['FetchAll']:= 'true';
  result.CachedUpdates:= true;
  result.SQL.Text:= 'SELECT * FROM TEST';
end;

{ TMyThread1 }

procedure TMyThread1.execute;
begin
  FMyQuery.Open;
end;

end.

if the problem doesn't arise, please modify the code to reproduce the problem, and contact us again.

[Stamp]

There are two events: Form.Oncreate, and Button.click.

Code: Select all

unit Unit1;

interface 

uses 
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, 
  Dialogs, DB, DBAccess, Uni, UniProvider, SQLiteUniProvider, MemDS, StdCtrls; 

type 
  TMyThread = class(TThread) 
    FMyConnection: TUniConnection; 
    FMyQuery: TUniQuery; 
  protected 
    procedure execute; override; 
  published 
  public 
    property MyConnection: TUniConnection read FMyConnection write FMyConnection; 
    property MyQuery: TUniQuery read FMyQuery write FMyQuery; 
  end; 

  TMyThread1 = class(TThread) 
    FMyConnection: TUniConnection; 
    FMyQuery: TUniQuery; 
  protected 
    procedure execute; override; 
  published 
  public 
    property MyConnection: TUniConnection read FMyConnection write FMyConnection; 
    property MyQuery: TUniQuery read FMyQuery write FMyQuery; 
  end; 


  TForm1 = class(TForm) 
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private 
    { Private declarations } 
    FConnection: TUniConnection; 
    function CreateDBConnection:TUniConnection; 
    function CreateQuery(Connection:TUniConnection):TUniQuery; 
  public 
    { Public declarations } 
  end; 

var 
  Form1: TForm1; 
  MyThread1: TMyThread;
  MyThread2: TMyThread1;

implementation 

{$R *.dfm} 




{ TForm1 } 

procedure TForm1.Button1Click(Sender: TObject); 
begin
  MyThread1:= TMyThread.Create(true);
  MyThread1.FMyConnection:= CreateDBConnection; 
  MyThread1.FMyQuery:= CreateQuery(MyThread1.FMyConnection); 
  MyThread2:= TMyThread1.Create(true); 
  MyThread2.FMyConnection:= CreateDBConnection; 
  MyThread2.FMyQuery:= CreateQuery(MyThread2.FMyConnection); 

  MyThread2.Resume;
  MyThread1.Resume;
end; 

function TForm1.CreateDBConnection: TUniConnection; 
begin 
  result := TUniConnection.create(nil); 

  result.ProviderName := 'SQLite'; 
  result.database := 'C:\test.db3';
  result.Pooling := true; 
  result.PoolingOptions.MaxPoolSize := 100; 
  result.PoolingOptions.ConnectionLifetime := 30000; 
  result.Options.DisconnectedMode := true;

  result.Connect; 
end; 

function TForm1.CreateQuery(Connection:TUniConnection): TUniQuery; 
begin 
  result := TUniQuery.create(nil); 
  result.Connection:=Connection; 
  result.SpecificOptions.Values['FetchAll']:= 'true'; 
  result.CachedUpdates:= true;
end; 

{ TMyThread }

procedure TMyThread.execute;
var
  index : integer;
begin

  for index := 0 to 50000 do
    begin
      FMyQuery.SQL.Text:= 'SELECT * FROM thread';
      FMyQuery.Open;
    end;

  FMyQuery.Connection.free;
  FMyQuery.free;
end;

{ TMyThread1 } 

procedure TMyThread1.execute;
var
  index : integer;
begin
  FMyQuery.sql.Text := 'insert into thread1 (ID, NAME) values (:ID, :NAME)';
  FMyQuery.Prepare;
  for index := 0 to 50000 do
    begin
      FMyQuery.ParamByName('ID').AsInteger := index;
      FMyQuery.ParamByName('NAME').AsString := 'JOHN';
      FMyQuery.ExecSql;
    end;

  FMyQuery.Connection.free;
  FMyQuery.free;

end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  FConnection:= TUniConnection.Create(nil); 
  FConnection.ProviderName := 'SQLite';
  FConnection.database := 'C:\test.db3';
  FConnection.ExecSQL('DROP TABLE IF EXISTS thread;', [null]);
  FConnection.ExecSQL('CREATE TABLE thread (name VARCHAR2(50),ID NUMBER(1));', [null]);

  FConnection.ExecSQL('DROP TABLE IF EXISTS thread1;', [null]);
  FConnection.ExecSQL('CREATE TABLE thread1 (name VARCHAR2(50),ID NUMBER(1));', [null]);
  FConnection.Free;
end;

end.

[/code]

[peolsson]

As far as I know this is the way SQLite works. It will handle concurrent threads, but if two threads insert at the same time this might occur. The expected way to handle this is to try again. Another workaround is of course to use a mutex in the application.

/Peter

[Stamp]

I am alright with having it wait until the DB is unlocked. Perhaps a QueryTimeout specific option in the tUniConnection?

[AlexP]

Hello,

We have reproduced the problem.
We will investigate the possibility of adding the timeout feature in one of the next builds of UniDAC.

[Stamp]

Do you have a timeline on when the next version may be released? Would this feature be in there? Thank you!!

[AlexP]

Hello,

We plan to release the next build next week. But unfortunately we have no time to include this feature to it. We plan to add this feature in the next nearest build. This feature is of high priority for us.

[Stamp]

Sounds good! Thank you for the update!

[Stamp]

Hi there! Just wondering if uniDac is on schedule to release a version with the mentioned SQLite support above. Just wondering to time my update releases! Thank you!!

[AlexP]

Hello,

We plan to release the new version of UniDAC (with support for these features) in two or three weeks.

[Stamp]

Awesome, thank you!

[Jink]

Hi. What was the outcome of this? If the problem was solved in an update then what is the correct approach now?

Thanks,
Jink

[AlexP]

Hello,

We added the EnableSharedCache property (you must set it to True) to solve the problem discussed above. Also we added the following properties for similar situations: BusyTimeout and ReadUncommitted.
Below are short description of these properties (we will add descriptions of these properties to the UniDAC help in the next product version):

ReadUncommitted
Enables or disables Read-Uncommitted isolation mode. A database connection in read-uncommitted mode does not attempt to obtain read-locks before reading from database tables as described above. This can lead to inconsistent query results if another database connection modifies a table while it is being read, but it also means that a read-transaction opened by a connection in read-uncommitted mode can neither block nor be blocked by any other connection.
Default value of this option is False.

EnableSharedCache
Enables or disables the Shared-Cache mode for SQLite database
Default value of this option is False.
This option is available for Delphi 2007 and higher IDE versions.

BusyTimeout
Defines timeout of waiting for locked resource (database or table). If resource is not unlocked during the time specified in BusyTimeout, then SQlite returns the SQLITE_BUSY error.
Default value of this option is 0.