Re: [sqlite] Memory corruption in a mutilthreaded environment

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/08/12 18:42, Rittick Gupta wrote:
> I have a program with multiple threads.

You should run under valgrind/helgrind.

> This may or may not anything to do with the problem

I'd bet that your underlying problem is that some other part of your code
is freeing memory it shouldn't or trampling over SQLite's memory.  However
you won't see the crash in the bad code, only in the victim (SQLite).

> my question why is sqlite trying to close the database ?

It is discarding stale information it has cached, detected on starting a
new transaction.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlAlxPUACgkQmOOfHg372QS6mACgpLiAwk1+s1VWw0GYWMo2O3Jq
jU4AnRQS2W1tgqcfzq30stylhRcEyHlj
=B0Lb
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Memory corruption in a mutilthreaded environment

[Rittick Gupta]

I have a program with multiple threads. The threads do not share the same 
handle.Occcasionally the program dies with a core. I see a thread always with 
the following stack:

#3  0x6000d336b7c0:0 in closeDB () at ../lang/sql/generated/sqlite3.c:35553
#4  0x6000d3388c60:0 in btreeInvalidateHandleCache ()
at ../lang/sql/generated/sqlite3.c:37914
#5  0x6000d3387540:0 in sqlite3BtreeBeginTrans ()
at ../lang/sql/generated/sqlite3.c:37987
#6  0x6000d33d2cc0:0 in sqlite3InitOne ()
at ../lang/sql/generated/sqlite3.c:80206
#7  0x6000d33d1b80:0 in sqlite3Init ()
at ../lang/sql/generated/sqlite3.c:80378
#8  0x6000d33d1770:0 in sqlite3LocateTable ()
at ../lang/sql/generated/sqlite3.c:80416
#9  0x6000d33cebb0:0 in selectExpander ()
at ../lang/sql/generated/sqlite3.c:84064
#10 0x6000d33ce780:0 in sqlite3SelectExpand ()
at ../lang/sql/generated/sqlite3.c:60440
#11 0x6000d33ce470:0 in sqlite3SelectPrep ()
at ../lang/sql/generated/sqlite3.c:84343
#12 0x6000d3444580:0 in sqlite3Select ()
at ../lang/sql/generated/sqlite3.c:84599
#13 0x6000d33ab080:0 in yy_reduce ()
---Type  to continue, or q  to quit---
at ../lang/sql/generated/sqlite3.c:95943
#14 0x6000d33a9100:0 in sqlite3Parser ()
at ../lang/sql/generated/sqlite3.c:96910
#15 0x6000d33a3410:0 in sqlite3RunParser ()
   at ../lang/sql/generated/sqlite3.c:97749
#16 0x6000d33e7110:0 in sqlite3Prepare ()
at ../lang/sql/generated/sqlite3.c:80564
#17 0x6000d351b640:0 in sqlite3_prepare_v2 ()


This may or may not anything to do with the problem - my question why is sqlite 
trying to close the database ?

Thanks for your help.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT :Re: Multi-Thread Reads to SQLite Database

[Richard Hipp]

On Fri, Aug 10, 2012 at 4:43 PM, Black, Michael (IS)  wrote:

> Why should shared cached be serialized when all the threads are reading?
>  I can see it for writing, but not just for reading.  There must be some
> logic that be done to allow this I would think (he said without looking at
> the code).
>

The cache is written as content is loaded off of disk.


>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Richard Hipp [d...@sqlite.org]
> Sent: Friday, August 10, 2012 12:53 PM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Multi-Thread Reads to SQLite Database
>
> On Fri, Aug 10, 2012 at 12:18 PM, esum  wrote:
>
> >
> > However, when I ran this same test with SQLITE_OPEN_READWRITE |
> > SQLITE_OPEN_SHAREDCACHE for the flags, I get the following [slower]
> > results:
> >
> > Why am I seeing such a high increase in times as I add threads in shared
> > cache mode as opposed to without it?
> >
>
> In shared-cache mode, the page cache is shared across threads.  That means
> that each thread must acquire a mutex on the page cache in order to read
> it.  Which means that access to the page cache is serialized.
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT :Re: Multi-Thread Reads to SQLite Database

[Igor Tandetnik]

On 8/10/2012 4:43 PM, Black, Michael (IS) wrote:

Why should shared cached be serialized when all the threads are reading?  I can 
see it for writing, but not just for reading.


Reading involves bringing pages from disk into the cache, and possibly 
removing pages from the cache when full. So the in-memory data structure 
representing the cache may be modified even by read-only transactions.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT :Re: Multi-Thread Reads to SQLite Database

[Larry Brasfield]

Black, Michael wrote:

Why should shared cached be serialized when all the threads are reading?  I can 
see it for writing, but not just for reading.  There must be some logic that be 
done to allow this I would think (he said without looking at the code).


(from another non-student of the relevant code)
While it may be true that reading specific cache content, at 
sufficiently fine granularity, would be immune to interference from 
other readers, the cache structure, book-keeping, and content lifetime 
is just as likely to be altered by the action of other readers as by 
writers.  The database could be read-only (such as when no writers are 
busy), but the cache certainly will be in flux when readers alone are busy.


Cheers,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use SQLite on VxWorks5.5

[Larry Brasfield]

Dear all,

 Hello, resently I have met some troubles when using SQLite on
VxWorks5.5. Does anybody know how to transplant SQLite to VxWorks5.5,
please help me, thank you very much.
 The VxWorks' vesion must be 5.5.


Having used VxWorks in the past, I knew that threading and file locking 
issues would be most likely to present issues for this port.  So I did a 
quick web search, which turned up this interesting result: 
http://marc.info/?l=sqlite-users=108060346504084


If you do not need multi-threading support, the VxWorks port should be 
fairly trivial as you can stub out those system calls.  For a 
single-threaded (and single-process) access pattern, SQLite is pretty 
much just another C program doing file I/O.  The above-linked page 
suggests preprocessor settings you need to consider.  It also mentions 
that there is a Berkeley DB port to VxWorks, which is likely to have 
done most of the work already.  (Berkeley DB is layered over SQLite.)


Best regards,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT :Re: Multi-Thread Reads to SQLite Database

[Black, Michael (IS)]

Why should shared cached be serialized when all the threads are reading?  I can 
see it for writing, but not just for reading.  There must be some logic that be 
done to allow this I would think (he said without looking at the code).

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Friday, August 10, 2012 12:53 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Multi-Thread Reads to SQLite Database

On Fri, Aug 10, 2012 at 12:18 PM, esum  wrote:

>
> However, when I ran this same test with SQLITE_OPEN_READWRITE |
> SQLITE_OPEN_SHAREDCACHE for the flags, I get the following [slower]
> results:
>
> Why am I seeing such a high increase in times as I add threads in shared
> cache mode as opposed to without it?
>

In shared-cache mode, the page cache is shared across threads.  That means
that each thread must acquire a mutex on the page cache in order to read
it.  Which means that access to the page cache is serialized.


--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Documentation typo in "ON CONFLICT clause"

[Jesse Weinstein]

On page http://www3.sqlite.org/lang_conflict.html

 

"the behavior proscribed the SQL standard" should be:

"the behavior prescribed by the SQL standard" 

 

"pre" not "pro". ;-)

 

Jesse Weinstein

Software Engineer

 

CliniComp, Intl.

9655 Towne Centre Drive

San Diego, CA 92121

+1 [858] 546 8202

jesse.weinst...@clinicomp.com

 

www.clinicomp.com

 

This message is for the designated recipient only and may contain
privileged, proprietary,

or otherwise private information. If you are not the intended recipient,
please notify the

sender immediately by reply email and delete this message. Any other use
by you of this

email or the information contained therein is strictly prohibited.

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-Thread Reads to SQLite Database

[Richard Hipp]

On Fri, Aug 10, 2012 at 12:18 PM, esum  wrote:

>
> However, when I ran this same test with SQLITE_OPEN_READWRITE |
> SQLITE_OPEN_SHAREDCACHE for the flags, I get the following [slower]
> results:
>
> Why am I seeing such a high increase in times as I add threads in shared
> cache mode as opposed to without it?
>

In shared-cache mode, the page cache is shared across threads.  That means
that each thread must acquire a mutex on the page cache in order to read
it.  Which means that access to the page cache is serialized.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Building System.Data.SQLite

[Joe Mistachkin]

Bernhard Mogens Ege wrote:
>
> I have been looking at
> https://system.data.sqlite.org/index.html/doc/trunk/www/build.wiki to
build
> System.Data.SQLite
>
 uild%20System.Data.SQLite>  but when using the manual build, I do not get
> the setup files built.. 
> 

The setup packages are created using Inno Setup, an open source tool
available
here:

http://www.jrsoftware.org/

Once that tool is installed, the "Setup\bake*.bat" tools may be used to
build
the setup packages for System.Data.SQLite.  Various other batch files in
that
same directory contain the environment variable settings needed by these
tools.

> 
> Then I use the automated build and follow the steps which work fine until
> step 11: bake_all.bat that complains about:
> 
> "Could not bake setup for Release/x86/2008."
> 

The "bake_all.bat" tool assumes that binaries have been built for all
supported
platforms and framework versions.  When that is not the case, the "bake.bat"
tool may be used instead.  However, that tool is a bit more complex because
it
was not really intended to be called directly from the command line.

Here is an example that will build the setup using the .NET 4.0-based setup
for
x86 (this assumes that the "build.bat" tool already completed successfully):

CD /D "%ROOT%\Setup"
vsSp.bat
set_common.bat
set_Release_x86_2010.bat
bake.bat

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multi-Thread Reads to SQLite Database

[esum]

Hi,

I am developing a multi-threaded sqlite database application on an Ubuntu
virtual machine that has 4 processors allocated to it. I am using sqlite
version 3.7.13. I created a test to verify that multiple threads/connections
can read from the database at the same time.

I have two executables.  The first executable simply creates a database,
creates 1 table in that database, inserts 50 items into that table, and then
closes the database.  This does not involve any multi-threading at all and
is simply meant to provide a database with entries in it.

The second executable creates multiple threads to read from the database and
waits for them to finish and records the time that it took for all of the
threads to complete.  Each thread does the following:
-create a database connection using sqlite_open_v2() so that
each thread has its own individual connection to the database created from
the first executable
-perform 10 SELECTS on the one database table (each
select queries for one row in the table)
-close the database connection

When I ran this test with SQLITE_OPEN_READWRITE specified as the flags for
sqlite_open_v2 in each thread, I get the following results for total time to
perform all queries:

1 Thread - 0.65 seconds
2 Threads - 0.70 seconds
3 Threads - 0.76 seconds
4 Threads - 0.91 seconds
5 Threads - 1.10 seconds
6 Threads - 1.28 seconds
7 Threads - 1.57 seconds
8 Threads - 1.78 seconds

These results were as expected as the times increase just a little (probably
from context switching between threads and other causes) as I add threads,
meaning that reads are basically being done in parallel.

However, when I ran this same test with SQLITE_OPEN_READWRITE |
SQLITE_OPEN_SHAREDCACHE for the flags, I get the following results:

1 Thread - 0.67 seconds
2 Threads - 2.43 seconds
3 Threads - 4.81 seconds
4 Threads - 6.60 seconds
5 Threads - 8.03 seconds
6 Threads - 9.41 seconds
7 Threads - 11.17 seconds
8 Threads - 12.79 seconds

>From these results, it appears that something in shared cache mode is
preventing multiple reads from happening in the database at the same time. 
I have verified that indeed different the threads are running in parallel
(thread 4 reads, thread 8 reads, thread 2 reads, etc. rather than thread 1
performs all its reads, thread 2 performs all its reads, thread 3 performs
all its reads, etc.).  However, it appears that the reads for each
individual transaction are being done in serial, or something else is
slowing down the database in shared cache.

Why am I seeing such a high increase in times as I add threads in shared
cache mode as opposed to without it?  Is there a way to fix this and still
use shared cache mode?

Thanks for any help.  It is much appreciated.




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Multi-Thread-Reads-to-SQLite-Database-tp63697.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Expert query execution speed

[Keith Medcalf]

First, are both using the same version of the SQLite3 engine?

Select sqlite_version(), sqlite_source_id();

Compiled with the same options?

Pragma compile_options;

Are they both using the same database file?

How do the query plan's compare -- I would expect that they are significantly 
different in order to see such a variance in query execution.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Gert Corthout
> Sent: Friday, 10 August, 2012 06:03
> To: sqlite-users@sqlite.org
> Subject: [sqlite] SQLite Expert query execution speed
> 
> 
> hello,
> 
> we have noticed that executing a select query in SQLite expert is 3 times as
> fast as executing it using our own C++ application. This seems quite odd as
> we are doing no funky stuff in the code, the sqlite3_step alone in our app
> takes longer then the entire process in (compiling the query, executing it,
> and showing the results) does in SQLIte Expert.
> Does SQLite Expert do something special?
> 
> We use the following pragma's:
> Pragma encoding  = 'UTF-16'
> Pragma page_size = 4096
> PRAGMA foreign_keys = ON
> 
> thanks in advance,
> Gert
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to use SQLite on VxWorks5.5

[张鹤]

Dear all,

 Hello, resently I have met some troubles when using SQLite on
VxWorks5.5. Does anybody know how to transplant SQLite to VxWorks5.5,
please help me, thank you very much.
 The VxWorks' vesion must be 5.5.

  ,

-- 
Best Regards
He Zhang
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Expert query execution speed

[Gert Corthout]

hello,

we have noticed that executing a select query in SQLite expert is 3 times as 
fast as executing it using our own C++ application. This seems quite odd as we 
are doing no funky stuff in the code, the sqlite3_step alone in our app takes 
longer then the entire process in (compiling the query, executing it, and 
showing the results) does in SQLIte Expert.
Does SQLite Expert do something special? 

We use the following pragma's:
Pragma encoding  = 'UTF-16'
Pragma page_size = 4096
PRAGMA foreign_keys = ON

thanks in advance,
Gert
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Building System.Data.SQLite

[Bernhard Mogens Ege]

I have been looking at
https://system.data.sqlite.org/index.html/doc/trunk/www/build.wiki to build
System.Data.SQLite
  but when using the manual build, I do not get
the setup files built..

 

Then I use the automated build and follow the steps which work fine until
step 11: bake_all.bat that complains about:

 

"Could not bake setup for Release/x86/2008."

 

Apparently, the 2008 files aren't built which is reasonable as I do not have
VS2008.

 

I have looked at some of the bat files, but I am not sure what the correct
path to build Win32/x64 files for VS2010 is (including Designer).

 

Any inside knowledge?

 

-- 

Bernhard

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] MATCH multiple

[Dan Kennedy]

On 08/10/2012 12:48 PM, E. Timothy Uy wrote:

I recently switched a table to FTS4. Usually I query this table using the
IN operator, e.g.,

SELECT * FROM Table WHERE term IN (a,b,c)

If I want to use MATCH, is my only option

SELECT * FROM Table WHERE term MATCH 'a or b or c'


I think the above is your best option. Don't forget to use
CAPITAL letters for "OR" or it won't work.

Dan.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users