View Issue Details

IDProjectCategoryView StatusLast Update
0034274FPCDatabasepublic2019-09-10 10:46
Reporterasdf121 Assigned ToMichael Van Canneyt  
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionfixed 
Product Version3.0.4 
Fixed in Version3.3.1 
Summary0034274: SQLite from SQLdb and Multithreading makes some trouble
DescriptionHey,
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 ReproduceCompile and run the attached code.
Additional InformationSometimes 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)
Tagsmultithreading, sqldb, sqlite
Fixed in Revision42972
FPCOldBugId
FPCTarget3.2.0
Attached Files

Relationships

related to 0034278 resolvedMichael Van Canneyt feature request: support for sqlite3_open_v2() in all sqlite3 components 

Activities

asdf121

2018-09-13 18:37

reporter  

Thaddy de Koning

2018-09-14 08:54

reporter   ~0110730

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
 -DSQLITE_THREADSAFE=2
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.

Thaddy de Koning

2018-09-14 09:22

reporter   ~0110731

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.

asdf121

2018-09-14 11:33

reporter  

asdf121

2018-09-14 11:40

reporter   ~0110739

Last edited: 2018-09-14 11:44

View 2 revisions

"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)

Thaddy de Koning

2018-09-14 12:29

reporter   ~0110742

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.

asdf121

2018-09-14 13:38

reporter   ~0110744

Last edited: 2018-09-14 13:42

View 2 revisions

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.
from https://www.sqlite.org/threadsafe.html

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.

Thaddy de Koning

2018-09-14 14:37

reporter   ~0110747

Last edited: 2018-09-14 15:01

View 2 revisions

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

asdf121

2018-09-20 17:00

reporter  

asdf121

2018-09-20 17:01

reporter  

slsqlite.pas (32,434 bytes)

asdf121

2018-09-20 17:07

reporter   ~0110901

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

Michael Van Canneyt

2019-09-10 10:46

administrator   ~0118016

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.

Issue History

Date Modified Username Field Change
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