www.digitalmars.com         C & C++   DMDScript  

digitalmars.D.learn - SQLite3 and threads

reply "Vitalie Colosov" <colosov hotmail.com> writes:
Hi,

I am not able to query SQLite3 database files using threads; 
without threads it is working fine.
I tried both etc.c.sqlite3 and d2sqlite3, and both seem to be 
facing the same issue:
They stuck when executing a select query (using sqlite3_exec(...) 
for etc.c.sqlite3 or using RowCache(db.execute(...)) in case of 
d2sqlite3).
Since d2sqlite3 is a wrapper for native sqlite3, I think it faces 
the same limitation which native sqlite does, so next lines will 
describe native SQLite3 code.

---------------------------------------------------------------
This works fine (non-relevant code and validations are omitted 
for simplicity)
---------------------------------------------------------------
import etc.c.sqlite3,...
...
extern(C) int myCallback(void *a_parm, int argc, char **argv, 
char **column)
{
	 printf("%s\n", argv[1] ? argv[1] : "NULL");	// this prints 
first column of each row, all is well
      return 0;
}
void querySQLite(string dbName)
{
	sqlite3* db;
	auto ret = sqlite3_open(toStringz(dbName), &db);
	string query = "SELECT * FROM my_table";
	sqlite3_exec(db,toStringz(query),&myCallback,null,null);
	sqlite3_close(db);
}
void main()
{
	querySQLite("db1.sl3");
	querySQLite("db2.sl3");
	...// in fact, here is a foreach loop which is calling 
querySQLite with about 30 database files
	querySQLite("db30.sl3");	
}

---------------------------------------------------------------
However, if I change main function to spawn querySQLite, instead 
of calling it in sequence from the main thread,
then "myCallback()" is not executed.
void main()
{
	spawn(&querySQLite,"db1.sl3");
	spawn(&querySQLite,"db2.sl3");
	...
	spawn(&querySQLite,"db30.sl3");
}
It is stuck inside this line in querySQLite():
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	sqlite3_exec(db,toStringz(query),&myCallback,null,null);
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
If I comment it, the flow continues and returns fine from all 
spawn-ed functions, so it is definitely something wrong in this 
line.
---------------------------------------------------------------

I think I am missing some kind of thread locking code in 
querySQLite() - since it is working with C code I think it needs 
more attention.

I tried to compile SQLite with different multithreading options, 
but that did not help.

Any advice is much appreciated.

Using dmd.2.066.1.linux
RedHat 5 64bit
Compiled using dmd
sqlite-amalgamation-3080803

Thanks,
Vitalie
Mar 01 2015
parent reply "Vitalie Colosov" <colosov hotmail.com> writes:
After some analysis, it looks like related to the code parts 
which I have omitted "for simplicity", and in particular - I was 
creating the query using global variable which was populated in 
main() function. It appears that when I spawn the function, it 
does not see the value of the global variable, thus, the query 
was not correct which made it execute full scan of table and 
never completed (table is huge).
I am checking more if anything like this is already documented 
anywhere.
Mar 01 2015
parent reply =?UTF-8?B?QWxpIMOHZWhyZWxp?= <acehreli yahoo.com> writes:
On 03/01/2015 09:47 PM, Vitalie Colosov wrote:


 global variable


A module-scope variable is thread-local by-default. Every thread will 
have a copy of that variable.

If you want to share data, you must define it as 'shared' (or __gshared).


 which was populated in main() function


In that case only the main thread's variable would be initialized. 
Shared variables should be initialized in 'shared static this()' scopes.

Ali
Mar 01 2015
parent reply "Vitalie Colosov" <colosov hotmail.com> writes:
Now it all makes sense. Thank you.
Maybe it would make also some sense if I would have gotten some 
kind of exception trying to access the variable which was not 
populated by the running thread, instead of successfully getting 
empty string... so this would be observed easily during the 
testing, but perhaps there are some reasons for it being 
implemented the way it is, will keep learning.
Mar 01 2015
next sibling parent Mike Parker <aldacron gmail.com> writes:
On 3/2/2015 4:20 PM, Vitalie Colosov wrote:

 Now it all makes sense. Thank you.
 Maybe it would make also some sense if I would have gotten some kind of
 exception trying to access the variable which was not populated by the
 running thread, instead of successfully getting empty string... so this
 would be observed easily during the testing, but perhaps there are some
 reasons for it being implemented the way it is, will keep learning.


No, it shouldn't throw an exception. There are good reasons to want each 
thread to have its own local copy of a variable. Most of the time, that 
should be precisely what you want anyway. So if you don't specifically 
tell the compiler you want the variable to be shared, then how is it, or 
the runtime, supposed to know that you didn't really intend for the 
variable to be thread-local?

Multithreaded programming is hard to reason about. If you aren't 
careful, shared variables can lead to race conditions and deadlocks. 
When variables are thread-local by default, you aren't going to run into 
these issues by accident. Adding that extra keyword is a reminder that 
you need to think carefully about what you are doing beforehand and 
actively opt-in to any potential variable sharing issues.
Mar 02 2015
prev sibling parent reply "Kagamin" <spam here.lot> writes:
On Monday, 2 March 2015 at 07:20:49 UTC, Vitalie Colosov wrote:

 Now it all makes sense. Thank you.
 Maybe it would make also some sense if I would have gotten some 
 kind of exception trying to access the variable which was not 
 populated by the running thread, instead of successfully 
 getting empty string... so this would be observed easily during 
 the testing, but perhaps there are some reasons for it being 
 implemented the way it is, will keep learning.


In your case you probably accessed uninitialized variable. 
Sentinel values can be used for this, i.e. a value that, when 
accessed by sqlite, gives you a descriptive error. So if this 
sentinel value is set as default value for a query structure, you 
will get it in uninitialized variables by default and receive 
errors when they are used by sqlite.
Mar 02 2015
parent "Vitalie Colosov" <colosov hotmail.com> writes:
Thanks for quick help. So this is related to Thread-local 
storage, which as I found it now, well documented, expected, and 
works as designed.
Mar 02 2015