mORMot Open Source

migajek

Member

Registered: 2010-10-01

Posts: 89

SQLite and multi-threading

Hi,
I'm using SQLite3 (although not your 'version' yet, just some old dll bindings for pascal). Since I'm about to start rebuilding the part of application which uses SQLite, I'd like to incorporate your 'version' of SQLite for Delphi, mostly because it doesn't require external DLL and it should be faster (using FastMM memory manager).
I ended up with messy code which uses your low-level headers (functions & types declaration) and a simple wrapper for SQLite which I used previously.

Now, my question is - does your implementation support multithreading? Please excuse me that question but the amount of code released as Synopse SQLite Framework is astonishing, but - unfortunately - making it very hard to find the answer

I'd also like to ask you for advice for my case - I'm building an application which parses lots of data and needs to keep them in SQLite database, than sometimes do SELECT on them or UPDATE, but from different threads. Thats why I'm asking for multithreading Is there any other interface to communicate with your framework than JSON format and plain SQL queries? Like, some wrappers for executing queries? Or some ORM-like native delphi objects?

I'd love to use the framework, yet avoiding time needed for generating and parsing JSON data on both "sides" since it is completely unnecessary in that case (as I don't need to send it over the network, nor between applications...)

Thanks in advance!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: SQLite and multi-threading

The SQlite3 engine itself if multi-thread ready, but it's not aimed to be very concurrent-access friendly.

When we compiled the engine, we get rid of all thread-safe code in the C source.
Because our framework uses SQlite through a Critical Section, therefore all calls to the SQlite3 engine from our framework is thread-safe.

You've all necessary SQLite3 engine direct access functions in the SQLite3.pas unit.
See the definitions in this unit, just below this line:

{ ************ direct access to sqlite3.c / sqlite3.obj consts and functions }

Take a look at the TSQLDataBase and TSQLRequest classes and object. These are high-level wrappers of the engine, used by the framework, but without any JSON.

{ ************ quick objects to access SQLite3 database engine }

type
  /// internaly store the SQLite3 database handle
  TSQLHandle = type cardinal;
  TSQLDatabase = class;

  /// Execute a SQL statement in the local SQLite3 database engine, and get
  // result in memory
  // - all DATA (even the BLOB fields) is converted into UTF-8 TEXT
  // - uses a TSQLTableJSON internaly: faster than sqlite3_get_table()
  // (less memory allocation) and allows efficient caching
  TSQLTableDB = class(TSQLTableJSON)
  private
  public
    {{ Execute a SQL statement, and init TSQLTable fields
     - FieldCount=0 if no result is returned
     - the BLOB data is converted into TEXT: you have to retrieve it with
      a special request explicitely (note that JSON format returns BLOB data)
     - uses a TSQLTableJSON internaly: all currency is transformed to its floating
       point TEXT representation, and allows efficient caching
     - if the SQL statement is in the DB cache, it's retrieved from its cached
       value: our JSON parsing is a lot faster than SQLite3 engine itself,
       and uses less memory
     - will raise an ESQLException on any error }
    constructor Create(aDB: TSQLDatabase; const Tables: array of TClass;
      const aSQL: RawUTF8; Expand: boolean);
  end;

  TSQLBlobStream = class;

  /// wrapper to a SQLite3 request
  TSQLRequest = object
  private
    fDB,
    fRequest: TSQLHandle;
    fNextSQL: PUTF8Char;
    fFieldCount: integer;

  // 1. general request process
  public
    {{ Prepare a UTF-8 encoded SQL statement
     - compile the SQL into byte-code
     - parameters ? ?NNN :VV @VV $VV can be bound with Bind*() functions below
     - raise an ESQLException on any error }
    function Prepare(DB: TSQLHandle; const SQL: RawUTF8): integer;
    {{ Prepare a WinAnsi SQL statement
     - behave the same as Prepare() }
    function PrepareAnsi(DB: TSQLHandle; const SQL: WinAnsiString): integer;
    {{ Prepare the next SQL command initialized in previous Prepare()
     - raise an ESQLException on any error }
    function PrepareNext: integer;
    {{ Evaluate An SQL Statement, returning the sqlite3_step() result status:
     - return SQLITE_ROW on success, with data read to be retrieved by Field*()
     - return SQLITE_DONE if the SQL commands were executed
     - raise an ESQLException on any error }
    function Step: integer;
    {{ Reset A Prepared Statement Object
     - reset a prepared statement object back to its initial state,
      ready to be re-executed.
     - any SQL statement variables that had values bound to them using the Bind*()
      function below retain their values. Use BindReset() to reset the bindings
     - return SQLITE_OK on success, or the previous Step error code }
    function Reset: integer;
    {{ Execute all SQL statements already prepared by a call to Prepare()
     - the statement is closed
     - raise an ESQLException on any error }
    procedure ExecuteAll; overload;
    {{ Execute all SQL statements in the aSQL UTF-8 encoded string
     - internaly call Prepare() then Step then PrepareNext until end of aSQL
     - Close is always called internaly
     - raise an ESQLException on any error }
    procedure ExecuteAll(aDB: TSQLHandle; const aSQL: RawUTF8); overload;
    {{ Execute one SQL statement already prepared by a call to Prepare()
     - the statement is closed
     - raise an ESQLException on any error }
    procedure Execute; overload;
    {{ Execute one SQL statement in the aSQL UTF-8 encoded string
     - Execute the first statement in aSQL: call Prepare() then Step once
     - Close is always called internaly
     - raise an ESQLException on any error }
    procedure Execute(aDB: TSQLHandle; const aSQL: RawUTF8); overload;
    {{ Execute a SQL statement which return integers from the aSQL UTF-8 encoded string
     - Execute the first statement in aSQL
     - this statement must get (at least) one field/column result of INTEGER
     - return result as a dynamic array of Int64 in ID
     - return count of row in integer function result (may be < length(ID))
     - raise an ESQLException on any error }
    function Execute(aDB: TSQLHandle; const aSQL: RawUTF8; var ID: TInt64DynArray): integer; overload;
    {{ Execute a SQL statement which return one integer from the aSQL UTF-8 encoded string
     - Execute the first statement in aSQL
     - this statement must get (at least) one field/column result of INTEGER
     - return result as an unique Int64 in ID
     - raise an ESQLException on any error }
    procedure Execute(aDB: TSQLHandle; const aSQL: RawUTF8; out ID: Int64); overload;
    {{ Execute a SQL statement which return one TEXT value from the aSQL UTF-8 encoded string
     - Execute the first statement in aSQL
     - this statement must get (at least) one field/column result of TEXT
     - raise an ESQLException on any error }
    procedure Execute(aDB: TSQLHandle; const aSQL: RawUTF8; out Value: RawUTF8); overload;
    {{ Execute a SQL statement which return TEXT from the aSQL UTF-8 encoded string
     - Execute the first statement in aSQL
     - this statement must get (at least) one field/column result of TEXT
     - return result as a dynamic array of RawUTF8 in ID
     - return count of row in integer function result (may be < length(ID))
     - raise an ESQLException on any error }
    function Execute(aDB: TSQLHandle; const aSQL: RawUTF8; var Values: TRawUTF8DynArray): integer; overload;
    /// Execute one SQL statement which return the results in JSON format
    // - JSON format is more compact than XML and well supported
    // - Execute the first statement in aSQL
    // - if SQL is '', the statement should have been prepared, reset and bound if necessary
    // - raise an ESQLException on any error
    // - JSON data is added to TStream, with UTF-8 encoding
    // - if Expand is true, JSON data is an array of objects, for direct use
    // with any Ajax or .NET client:
    // & [ {"col1":val11,"col2":"val12"},{"col1":val21,... ]
    // - if Expand is false, JSON data is serialized (used in TSQLTableJSON)
    // & { "FieldCount"=1,"Values"=["col1","col2",val11,"val12",val21,..] }
    // - BLOB field value is saved as Hexa (Base64 is not JSON-compatible),
    // following the SQLite3 BLOB literals (X'53514C697465' e.g.) format,
    // and contains true BLOB data (no conversion into TEXT, as with TSQLTableDB)
    procedure Execute(aDB: TSQLHandle; const aSQL: RawUTF8; JSON: TStream;
      Expand: boolean=false); overload;
    /// Execute one SQL statement which return the results in JSON format
    // - use internaly Execute() above with a TMemoryStream, and return a string
    // - the BLOB data is encoded as SQLite3 BLOB literals, but as true BLOB
    // (not TEXT, as with TSQLTableDB)
    // - if any error occurs, the ESQLException is handled and '' is returned
    function ExecuteJSON(aDB: TSQLHandle; const aSQL: RawUTF8; Expand: boolean=false): RawUTF8;
    {{ Execute all SQL statements in the aSQL UTF-8 encoded string, results will
      be written as ANSI text in OutFile }
    procedure ExecuteDebug(aDB: TSQLHandle; const aSQL: RawUTF8; const OutFile: Text);
    {{ close the Request handle
     - call it even if an ESQLException has been raised }
    procedure Close;

    {{ read-only access to the Request (SQLite3 statement) handle }
    property Request: TSQLHandle read fRequest;
    {{ read-only access to the SQLite3 database handle }
    property RequestDB: TSQLHandle read fDB;

  // 2. Bind parameters to a SQL query (for the last prepared statement)
  public
    {{ Reset All Bindings On A Prepared Statement
     - Contrary to the intuition of many, Reset() does not reset the bindings
      on a prepared statement. Use this routine to reset all host parameter }
    procedure BindReset;
    {{ bind an integer value to a parameter
     - the leftmost SQL parameter has an index of 1, but ?NNN may override it
     - raise an ESQLException on any error }
    procedure Bind(Param: Integer; Value: Int64); overload;
    {{ bind a double value to a parameter
     - the leftmost SQL parameter has an index of 1, but ?NNN may override it
     - raise an ESQLException on any error }
    procedure Bind(Param: Integer; Value: double); overload;
    {{ bind a UTF-8 encoded string to a parameter
     - the leftmost SQL parameter has an index of 1, but ?NNN may override it
     - raise an ESQLException on any error }
    procedure Bind(Param: Integer; const Value: RawUTF8); overload;
    {{ bind a Blob buffer to a parameter
     - the leftmost SQL parameter has an index of 1, but ?NNN may override it
     - raise an ESQLException on any error }
    procedure Bind(Param: Integer; Data: pointer; Size: integer); overload;
    {{ bind a ZeroBlob buffer to a parameter
     - uses a fixed amount of memory (just an integer to hold its size) while
      it is being processed. Zeroblobs are intended to serve as placeholders
      for BLOBs whose content is later written using incremental BLOB I/O routines
      (as with TSQLBlobStream created from TSQLDataBase.Blob() e.g.).
     - a negative value for the Size parameter results in a zero-length BLOB
     - the leftmost SQL parameter has an index of 1, but ?NNN may override it
     - raise an ESQLException on any error }
    procedure BindZero(Param: Integer; Size: integer);

  // 3. Field attributes after a sucessfull Step() (returned SQLITE_ROW)
  public
    {{ the field name of the current ROW  }
    function FieldName(Col: integer): RawUTF8;
    {{ return the field as a sqlite3_value object handle, first Col is 0 }
    function FieldValue(Col: integer): TSQLHandle;
    {{ return a field integer value, first Col is 0 }
    function FieldInt(Col: integer): Int64;
    {{ return a field floating point value, first Col is 0 }
    function FieldDouble(Col: integer): double;
    {{ return a field UTF-8 encoded text value, first Col is 0 }
    function FieldUTF8(Col: integer): RawUTF8;
    {{ return a field as Win-Ansi (i.e. code page 1252) encoded text value, first Col is 0 }
    function FieldA(Col: integer): WinAnsiString;
    {{ return a field RawUnicode encoded text value, first Col is 0 }
    function FieldW(Col: integer): RawUnicode;
    {{ return a field as a blob value (TSQLRawBlob is an AnsiString), first Col is 0 }
    function FieldBlob(Col: integer): TSQLRawBlob;
    {{ the column/field count of the current ROW
     - fields numerotation starts with 0 }
    property FieldCount: integer read fFieldCount;
  end;

  /// Stored Procedure prototype, used by TSQLDataBase.Execute() below
  // - called for every row of a Statement
  // - the implementation may update the database directly by using a
  // local or shared TSQLRequest
  // - the TSQLRequest may be shared and prepared before the call for even
  // faster access than with a local TSQLRequest 
  // - no TSQLDataBase or higher levels objects can be used inside this method,
  // since all locking and try..finally protection is outside it
  // - can optionnaly trigger a ESQLException on any error
  TOnSQLStoredProc = procedure(Statement: TSQLRequest) of object;

  /// simple wrapper for direct SQLite3 database manipulation
  // - embed the SQLite3 database calls into a common object
  // - thread-safe call of all SQLite3 queries (SQLITE_THREADSAFE 0 in sqlite.c)
  // - can cache last results for SELECT statements, if property UseCache is true:
  //  this can speed up most read queries, for web server or client UI e.g.
  TSQLDataBase = class
  private
    fDB: TSQLHandle;
    fFileName: TFileName;
    fTransactionActive: boolean;
    fLock: TRTLCriticalSection;
    /// if not nil, cache is used - see UseCache property
    fCache: TSQLCache;
    fInternalState: PCardinal;
    function GetUseCache: boolean;
    procedure SetUseCache(const Value: boolean);
  protected
    /// enter the TRTLCriticalSection: called before any DB access
    // - provide the SQL statement about to be executed: handle proper caching
    // - is the SQL statement is void, assume a SELECT statement (no cache flush)
    procedure Lock(const aSQL: RawUTF8);
    /// leave the TRTLCriticalSection: called after any DB access
    procedure UnLock;
    /// enter the TRTLCriticalSection: called before any DB access
    // - provide the SQL statement about to be executed: handle proper caching
    // - if this SQL statement has an already cached JSON response, return it and
    // don't enter the TRTLCriticalSection: no UnLockJSON() call is necessary
    // - if this SQL statement is not a SELECT, cache is flushed and
    // the next call to UnLockJSON() won't add any value to the cache since
    // this statement is not a SELECT and doesn't have to be cached!
    function LockJSON(const aSQL: RawUTF8): RawUTF8;
    /// leave the TRTLCriticalSection: called after any DB access
    // - caller must provide the JSON result for the SQL statement previously set
    //  by LockJSON()
    // - do proper caching of the JSON response for this SQL statement
    procedure UnLockJSON(const aJSONResult: RawUTF8);
    /// (re)open the database from file fFileName
    function DBOpen: integer;
  public
    {{ open a SQLite3 database file
     - open an existing database file or create a new one if no file exists
     - SYSTEMNOCASE collation is added (our custom fast UTF-8 case insensitive compare,
       which is used also in the SQLite3UI unit for coherency and efficiency)
     - ISO8601 collation is added (TDateTime stored as ISO-8601 encoded TEXT)
     - WIN32CASE and WIN32NOCASE collations are added (use slow but accurate Win32 CompareW)
     - initialize a TRTLCriticalSection to ensure that all access to the database is atomic
     - raise an ESQLException on any error }
    constructor Create(const aFileName: TFileName);
    {{ close a database and free its memory and context
      - if TransactionBegin was called but not commited, a RollBack is performed }
    destructor Destroy; override;
    {{ Execute all SQL statements in aSQL UTF-8 encoded string
     - can be prepared with TransactionBegin()
     - raise an ESQLException on any error }
    procedure ExecuteAll(const aSQL: RawUTF8);
    {{ Execute one SQL statements in aSQL UTF-8 encoded string
     - can be prepared with TransactionBegin()
     - raise an ESQLException on any error }
    procedure Execute(const aSQL: RawUTF8); overload;
    {{ Execute one SQL statement which return integers from the aSQL UTF-8 encoded string
     - Execute the first statement in aSQL
     - this statement must get a one field/column result of INTEGER
     - return result as a dynamic array of RawUTF8, as TEXT result
     - return count of row in integer function result (may be < length(ID))
     - raise an ESQLException on any error }
    function Execute(const aSQL: RawUTF8; var ID: TInt64DynArray): integer; overload;
    {{ Execute one SQL statement returning TEXT from the aSQL UTF-8 encoded string
     - Execute the first statement in aSQL
     - this statement must get (at least) one field/column result of TEXT
     - return result as a dynamic array of RawUTF8 in ID
     - return count of row in integer function result (may be < length(ID))
     - raise an ESQLException on any error }
    function Execute(const aSQL: RawUTF8; var Values: TRawUTF8DynArray): integer; overload;
    {{ Execute one SQL statement which return one integer from the aSQL UTF-8 encoded string
     - Execute the first statement in aSQL
     - this statement must get a one field/column result of INTEGER
     - return result as a dynamic array of RawUTF8, as TEXT result
     - raise an ESQLException on any error }
    procedure Execute(const aSQL: RawUTF8; out ID: Int64); overload;
    {{ Execute one SQL statement which return one UTF-8 encoded string value
     - Execute the first statement in aSQL
     - this statement must get a one field/column result of INTEGER
     - return result as a dynamic array of RawUTF8, as TEXT result
     - raise an ESQLException on any error }
    procedure Execute(const aSQL: RawUTF8; out ID: RawUTF8); overload;
    /// Execute one SQL statement returning its results in JSON format
    // - the BLOB data is encoded as hexa, but as true BLOB (not TEXT, as with TSQLTableDB)
    function ExecuteJSON(const aSQL: RawUTF8; Expand: boolean=false): RawUTF8;
    {{ begin a transaction
     - Execute SQL statements with Execute() procedure below
     - must be ended with Commit on success
     - must be aborted with Rollback after an ESQLException raised }
    procedure TransactionBegin;
    {{ end a transaction: write all Execute() statements to the disk }
    procedure Commit;
    {{ abort a transaction: restore the previous state of the database }
    procedure RollBack;
    {{ return the last Insert Rowid }
    function LastInsertRowID: Int64;
    {{ get all table names contained in this database file }
    procedure GetTableNames(var Names: TRawUTF8DynArray);
    {{ get all field names for a specified Table }
    procedure GetFieldNames(var Names: TRawUTF8DynArray; const TableName: RawUTF8);
    {{ retrieve the user_version stored in the SQLite3 database file
      - user-version is a 32-bit signed integers stored in the database header }
    function user_version: cardinal;
    {{ enable the Write-Ahead Logging for the database
      - beginning with version 3.7 of the SQLite3 engine, a new "Write-Ahead Log"
        option (hereafter referred to as "WAL") is optionaly available
      - WAL might be very slightly slower (perhaps 1% or 2% slower) than the
        traditional rollback-journal approach in applications that do mostly reads
        and seldom write; but WAL provides more concurrency as readers do not block
        writers and a writer does not block readers. Reading and writing can
        proceed concurrently. With our SQLite3 framework, it's not needed.
      - by default, this option is not set: only implement if you really need it,
        but our SQlite3 framework use locked access to the databse, so there
        should be no benefit of WAL for the framework; but if you call
        directly TSQLDatabase instances in your code, it may be useful to you }
    procedure SetWALMode(Value: Boolean);
                 
    {{ open a BLOB incrementally for read[/write] access
     - find a BLOB located in row RowID, column ColumnName, table TableName
      in database DBName; in other words, the same BLOB that would be selected by:
      ! SELECT ColumnName FROM DBName.TableName WHERE rowid = RowID;
     - use after a TSQLRequest.BindZero() to reserve Blob memory
     - if RowID=-1, then the last inserted RowID is used
     - will raise an ESQLException on any error }
    function Blob(const DBName, TableName, ColumnName: RawUTF8;
      RowID: Int64=-1; ReadWrite: boolean=false): TSQLBlobStream;
    {{ backup of the opened Database into an external file name
     - don't use the experimental SQLite Online Backup API
     - database is closed, VACCUUMed, copied, then reopened: it's very fast  }
    function Backup(const BackupFileName: TFileName): boolean;

    {{ read-only access to the SQLite3 database handle }
    property DB: TSQLHandle read fDB;
    {{ read-only access to the SQLite3 database filename opened }
    property FileName: TFileName read fFileName;
    /// if this property is set, all ExecuteJSON() responses will be cached
    // - cache is flushed on any write access to the DB (any not SELECT statement)
    // - cache is consistent only if ExecuteJSON() Expand parameter is constant
    // - cache is used by TSQLDataBase.ExecuteJSON() and TSQLTableDB.Create()
    property UseCache: boolean read GetUseCache write SetUseCache;
    /// this integer pointer (if not nil) is incremented when any SQL statement
    // changes the database contents (i.e. any not SELECT statement)
    // - this pointer is thread-safe updated, inside a critical section
    property InternalState: PCardinal read fInternalState write fInternalState;
    /// return TRUE if a Transaction begun
    property TransactionActive: boolean read fTransactionActive;
  end;

  /// used to read or write a BLOB Incrementaly
  // - data is read/written directly from/to the SQLite3 BTree
  // - data can be written after a TSQLRequest.BindZero() call to reserve memory
  // - this TStream has a fixed size, but Position property can be used to rewind
  TSQLBlobStream = class(TStream)
  protected
    fBlob: TSQLHandle;
    fDB: TSQLHandle;
    fSize,
    fPosition: longint;
    fWritable: boolean;
  public
    {{ Opens a BLOB located in row RowID, column ColumnName, table TableName
    in database DBName; in other words, the same BLOB that would be selected by:
    ! SELECT ColumnName FROM DBName.TableName WHERE rowid = RowID; }
    constructor Create(aDB: TSQLHandle; const DBName, TableName,
      ColumnName: RawUTF8; RowID: Int64; ReadWrite: boolean);
    {{ release the BLOB object }
    destructor Destroy; override;
    {{ read Count bytes from the opened BLOB in Buffer }
    function Read(var Buffer; Count: Longint): Longint; override;
    {{ write is allowed for in-place replacement (resizing is not allowed)
     - Create() must have been called with ReadWrite=true }
    function Write(const Buffer; Count: Longint): Longint; override;
    {{ change the current read position }
    function Seek(Offset: Longint; Origin: Word): Longint; override;
    {{ read-only access to the BLOB object handle }
    property Handle: TSQLHandle read fBlob;
  end;

So you don't need to use our framework, but beware that these functions have been compiled with the following defines:

#define SQLITE_DEFAULT_MEMSTATUS 0
//  don't need any debug here
#define SQLITE_THREADSAFE 2
//  assuming multi-thread safety is made by caller - in our framework, there is
// only one thread using the database connection at the same time, but there could
// be multiple database connection at the same time (previous was 0 could be unsafe)
#define SQLITE_OMIT_SHARED_CACHE 1
// no need of shared cache in a threadsafe calling model
#define SQLITE_OMIT_AUTOINIT 1
//  sqlite3_initialize() is done in initialization section below -> no AUTOINIT
#define SQLITE_OMIT_DEPRECATED 1
//  spare some code size
#define SQLITE_OMIT_TRACE 1
// we don't need sqlite3_profile() and sqlite3_trace() interfaces
#define SQLITE_OMIT_LOAD_EXTENSION 1
// we don't need extension in an embedded engine
#define SQLITE_OMIT_COMPILEOPTION_DIAGS 1
// we don't need Compilation Options Diagnostics in our embedded engine
#define SQLITE_OMIT_PROGRESS_CALLBACK 1
// we don't need sqlite3_progress_handler() API function
#define SQLITE_ENABLE_RTREE 1
// the RTREE extension is now (from v.1.8/3.7) compiled into the engine

So if you want to use direct access and multi-thread safe, you can either:
- protect concurrent access to the engine in your code, by using critical sections, just like our framework;
- recompile the c source code from our source code repository, but with #define SQLITE_THREADSAFE 0 or #define SQLITE_THREADSAFE 1

The JSON parsing and generation is VERY fast. And much faster than any SQLite3 SELECT request.
It's used as root of our ORM, for creating internal caching of SELECT.
So the very little time spent in JSON is well served when a SELECT is run multiple times.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: SQLite and multi-threading

Hi,
Is there any other interface to communicate with your framework than JSON format and plain SQL queries? Like, some wrappers for executing queries? Or some ORM-like native delphi objects?

If you use our framework, and its ORM classes, you'll have to write very few SQL statements.
The ORM will write the most needed SQL code: for table creation/update, for queries (see the Retrieve* methods), for update and adding items. Even BLOB access is available via direct methods, without any SQL to write.

Note that the framework can be called locally, in a thread-safe manner.
For example, in the tests available in the framework, you've some benchmark on a notebook at http://synopse.info/forum/viewtopic.php?id=90
Multiple connections with HTTP is handled very efficiently.
In your case, you should use direct in memory access, that is, for a SELECT request generating 4 KB of JSON content (i.e. not a "foo" data, but real data):

  - Direct in process access: 3001 assertions passed
     first in 68us, done in 68.32ms i.e. 14636/s, average 68us

With such a speed (which is much faster than the SQLite3 engine itself), due to JSON caching, the framework is multi-thread ready. 14000 requests of 4KB data per second. Ask Oracle.

On another NoteBook (running a Core i7), it serves more than 25000 requests per second.
So I'm quite sure that you won't suffer for performance in a multi-thread application.

migajek

Member

Registered: 2010-10-01

Posts: 89

Re: SQLite and multi-threading

Hi,
what I understood is that there are following methods of accessing DB:

• HTTP

• FastCGI

• NamedPipes

• Direct access

Are all of them thread-safe?

Which of these methods consumes less memory / CPU? My application is not a typical DB app, it's rather very uncommon to use ORM / SQL in such an application, but since it has rich GUI, it already uses quite a lot of memory ... and I'd like to avoid much more consumption.

I have two threads in my app:
main thread (obvious)
background worker thread

while the background worker thread keeps updating DB when necessary (it might be quite intense sometimes) with DELETE / UPDATE / INSERT queries, the main thread might want to do SELECT from time to time. The most crucial is SELECT time (it has to have highest priority) because I want to avoid UI from freezing (actually the SELECT itself is not a big deal, currently it is very poorly written, instead of SQLite it does three nested iterations and lots of slow string comparisons for building data list, so the only possible scenario for UI freeze would be waiting for Critical Section unlock).

Now, should I rely on client-server architecture (correct me if I'm wrong, but this would require me to use HTTP, FastCGI or Named Pipes?) to create a server in main thread, than use two clients -> one for main thread, and the second created in background worker? Under this scenario I don't have to care about synchronization, do I?

The second option would be using Direct access (create TSQLRestServerDB, than access it from both threads), but in that case I'd need to handle synchronization on my own, right?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: SQLite and multi-threading

All these methods are thread-safe.

The direct access is the less resource consuming.
Then named pipes.
Then FastCGI and HTTP (depending on your web server for FastCGI, the HTTP classes are very low consuming).

You can call the direct access from any thread, without any problem.
But there is a CriticalSection lock when you execute the SQlite3 statements. So everything is thread safe but the SQL statements will be executed in row. These statements could freeze any select. The JSON creation/parsing is done in multi-thread (in the caller thread, to be more precise), and is also thread safe.

For the UI refresh, a good idea is always using a timer, with 500 ms timing, to refresh the screen content.
that's what we do for the UI part of the framework.
It's still responsible, and user experience is pretty good.

migajek

Member

Registered: 2010-10-01

Posts: 89

Re: SQLite and multi-threading

Does it mean I can do the following:

// Database is a global object
procedure TMyThread.Execute; 
begin
Database.Add(TSQLSampleRecord.Create(), true);
end;

or should I still have it synchronized:

// Database is a global object
procedure TMyThread.AddEmpty;
begin
Database.Add(TSQLSampleRecord.Create(), true);
end;

procedure TMyThread.Execute; 
begin
Synchronize(AddEmpty);
end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: SQLite and multi-threading

The Synchronize is not necessary at all.
All high-level methods of the framework are thread-safe.

migajek

Member

Registered: 2010-10-01

Posts: 89

Re: SQLite and multi-threading

The Synchronize is not necessary at all.
All high-level methods of the framework are thread-safe.

Thank you.

Would you be so kind to explain me how is it done? So far I thought that any operation done on "shared" object (common for multiple threads) must be protected with "synchronize", no matter what. Apparently, I was wrong - in your code I found CriticalSection to be used in SQLite wrapper, but not SQLite3Commons at all. What is the difference, why isn't for example TSQLRestServerDB "protected" (locked) with CriticalSection?

How do I find what parts / methods of my code needs to be protected with CriticalSection and which not?

So far I haven't stumbled upon any interesting explanation / article / blog note, all google results are:

a) examples of synchronization between thread and the GUI. From simple progressbar to most complex, but still the topic is quite simple and obvious: each time you access / modify the property of GUI component, do that in "Synchronize"
b) articles explaining Critical Sections, Mutexes etc.
c) Examples of very very simple thread-safe classes (thread safe stack or list) - they all do the same - implement lock / unlock methods which do enter/leave critical section and return the actual stack/list pointer on locking.

Could you please provide me with a good reference to read which parts of code should be protected?

thank you!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: SQLite and multi-threading

1) Synchronize is used to execute a method in the main application thread, that is when a background thread has to access to the UI components.
This is not the case at all with our framework, so there is no need to use Synchronize.

2) There is a CriticalSection used in the Lock/UnLock method of the framework, which is responsible of a) implement caching of JSON data b) make the access to the engine multi-thread ready.

As I wrote above, all multi-thread sensitive part of the framework is protected by a CriticalSection.
Other not multi-thread sensitive part (e.g. the JSON parsing or the URL decoding) is run in the caller thread.

So when you use the high-level classes of the framework (TSQLRecord, TSQLRest, TSQLTableJSON), you don't have to worry about threading. All is thread-safe.

Then in your code, you'll have to worry about your own code to be thread-safe.

And I recommend you not to use Synchronize to refresh the UI. I always experienced than a WM_TIMER message, with 500 ms resolution, just launched once, is far better: the background thread send this message to the main UI thread, telling him that the screen is to be refreshed (you've parameters in the WM_TIMER message to state what should be refreshed) then this background thread can continue working. Synchronize will stop the background thread until the UI thread has finished refreshing itself. So you could have some locks here.
In the UI Thread, the WM_TIMER message will call the engine to retrieve the data. But in practice, thanks to the internal caching mechanism, this is very fast and safe.

I don't have a good reference about that, but I'll perhaps write a Blog article soon on that subject.