View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0034274||FPC||Database||public||2018-09-13 18:37||2019-09-10 10:46|
|Reporter||asdf121||Assigned To||Michael Van Canneyt|
|Fixed in Version||3.3.1|
|Summary||0034274: SQLite from SQLdb and Multithreading makes some trouble|
I already posted in forum (https://forum.lazarus.freepascal.org/index.php/topic,42212.0.html) but seems it's not the right place for the issue.
It's an issue with sqlite and several transaction to one connection. There is another example in my forum post on page 2 which shows this issues with using transactions for sqlite.
I cannot find anything about this in wiki, so if it's a known restriction it should be mentioned there.
The attached code is based on sqlite and using some threads.
|Steps To Reproduce||Compile and run the attached code.|
|Additional Information||Sometimes it works without problems but it also fails with one of the following errors:|
- EDatabaseError: No such dataset registered : ""
- EAccessViolation: Access violation
- *** Error in `main': realloc(): invalid pointer: 0x000000000262cf98 ***
- Not all threads were executed/prints the writeln line
Even the use of stoUseImplicit does not help and I also tried all kinds of variation between using those transactions.
(I've tested with 3.0.0, 3.0.2 and 3.0.4)
|Tags||multithreading, sqldb, sqlite|
|Fixed in Revision||42972|
sqlite-multithreading.pas (2,798 bytes)
The exception *** Error in `main': realloc(): invalid pointer: 0x000000000262cf98 *** stems from sqlite itself.
You should have followed my advice I gave on the forum. https://sqlite.org/threadsafe.html
Basically you have two options: serialized, or a sqlite built with
It is very important that you first determine how your sqlite version is built.
This is not a bug in the fcl-db code, as I explained.
Good example code, btw.
On second thought: The fcl-db standard components use sqlite3_open and not sqlite3_open_v2() which is needed for the multithreading control.
So to mitigate your issue - even if the error stems from sqlite itself - the sqlite components need implementation of sqlite3_open_v2() instead of sqlite3_open to support a sqlite binary compiled with threading support.
Unlike what I wrote above there are two things separate:
1) sqlite compiled for threading. This is not always the case and may differ per platform or even distribution.
2) sqlite components in FPC should use or support sqlite3_open_v2() with the correct flags.
sqlite-multithreading-with-threading-check.pas (3,096 bytes)
"If neither flag is specified or if sqlite3_open() or sqlite3_open16() are used instead of sqlite3_open_v2(), then the default mode determined by the compile-time and start-time settings is used." (https://www.sqlite.org/threadsafe.html)
and my library is compiled with threading support (some example code attached), so using sqlite3_open is not the issue here. (says that mode is 1)
The mode should be selected at runtime using SQLITE_OPEN_NOMUTEX or SQLITE_OPEN_FULLMUTEX and use sqlite3_open_v2, possibly replacing all legacy sqlite3_open calls. sqlite3_open_v2() enables that. SQLITE_OPEN_FULLMUTEX is what you should use for your particular example code.
The pascal code can query sqlite for the mode (threaded/not threaded) and can subsequently use either flag.
The reason is that this prevents the FPC code from making any assumptions and set the mode explicitly. In your case the code was possibly running in SQLITE_OPEN_NOMUTEX mode which subsequently led to a fail of sqlite's reallocmem.
If the code could specify SQLITE_OPEN_FULLMUTEX the barrier would have prevented that reallocation inside the sqlite engine to fail..
As per the documentation in the former mode you have to be very careful with the connections, basically do them separately for all threads.
To summarize: the solution is as I described.
I don't think your right:
2. Multi-thread. In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads.
3. Serialized. In serialized mode, SQLite can be safely used by multiple threads with no restriction.
The default mode is serialized.
I don't see why you should/need set any special flag with MUTEX. It should work out of the box and that's even the case with an older library I used which does directly calls to the sqlite functions without any big wrapper like SQLdb.
There I create one connection and then execute my stuff without any transaction or stuff like that. It even uses the old sqlite3_open() and there I've never had any issues as serialized mode is handling everything.
Well: your code also contains a bug:
The CreateConnection is not threadsafe and you can not get at that connection from within the other threads. You should create the connection on a per thread basis. Subsequently you can declare all vars as threadvar.
You use a scenario that is explicitly documented in the SQLITE documentation as a cause for trouble in multithreaded scenario's. The way around it is either create connections on a per thread basis (given SQLITE_OPEN_NOMUTEX) or to open the database with the SQLITE_OPEN_FULLMUTEX flag.
The first option is already possible. The second option needs implementation.
From my tests (on debian) the default mode seems to be SQLITE_OPEN_NOMUTEX which demands that you create *everything* on a per thread basis including the connection. I have tested with a modified version as well QandD using both flags and replacing sqlite3_open with sqlite3_open_v2. In that case your code works flawless with the SQLITE_OPEN_FULLMUTEX flag set on open. It also works flawless when I create the connection on a per thread basis and use threadvars.
Attached the crude example that doesn't crash
external_sqlite_lib.pas (2,716 bytes)
slsqlite.pas (32,434 bytes)
I've added another library (slsqlite) and some example code (external_sqlite_lib).
The lib does not use any of these _v2 functions from sqlite and does even not use any lockings inside it's class but it's working flawless for my tests.
To compile it you need Indy10:
fpc -MDelphi -O3 -FuIndy10\System external_sqlite_lib.pas
The problem has little to do with sqlite itself. (although there may be sqlite-specific thread-safety issues as well)
The link between database, dataset and transaction is not thread safe.
Till now, you had to use a TConnection per thread.
This is a long-known shortcoming, which I have now fixed.
|2018-09-13 18:37||asdf121||New Issue|
|2018-09-13 18:37||asdf121||File Added: sqlite-multithreading.pas|
|2018-09-13 18:38||asdf121||Tag Attached: sqldb|
|2018-09-13 18:38||asdf121||Tag Attached: sqlite|
|2018-09-13 18:38||asdf121||Tag Attached: multithreading|
|2018-09-14 08:54||Thaddy de Koning||Note Added: 0110730|
|2018-09-14 09:22||Thaddy de Koning||Note Added: 0110731|
|2018-09-14 11:33||asdf121||File Added: sqlite-multithreading-with-threading-check.pas|
|2018-09-14 11:40||asdf121||Note Added: 0110739|
|2018-09-14 11:44||asdf121||Note Edited: 0110739||View Revisions|
|2018-09-14 12:29||Thaddy de Koning||Note Added: 0110742|
|2018-09-14 13:38||asdf121||Note Added: 0110744|
|2018-09-14 13:42||asdf121||Note Edited: 0110744||View Revisions|
|2018-09-14 14:37||Thaddy de Koning||Note Added: 0110747|
|2018-09-14 15:01||Thaddy de Koning||Note Edited: 0110747||View Revisions|
|2018-09-19 11:06||Marco van de Voort||Relationship added||related to 0034278|
|2018-09-20 17:00||asdf121||File Added: external_sqlite_lib.pas|
|2018-09-20 17:01||asdf121||File Added: slsqlite.pas|
|2018-09-20 17:07||asdf121||Note Added: 0110901|
|2018-10-28 15:28||Michael Van Canneyt||Assigned To||=> Michael Van Canneyt|
|2018-10-28 15:28||Michael Van Canneyt||Status||new => assigned|
|2019-09-10 10:46||Michael Van Canneyt||Status||assigned => resolved|
|2019-09-10 10:46||Michael Van Canneyt||Resolution||open => fixed|
|2019-09-10 10:46||Michael Van Canneyt||Fixed in Version||=> 3.3.1|
|2019-09-10 10:46||Michael Van Canneyt||Fixed in Revision||=> 42972|
|2019-09-10 10:46||Michael Van Canneyt||FPCTarget||=> 3.2.0|
|2019-09-10 10:46||Michael Van Canneyt||Note Added: 0118016|