Re: [sqlite] c-api

[John Deal]

Hello Baruch,

You may want to look at sqlite3_exec() (http://www.sqlite.org/c3ref/exec.html).

John

--- On Wed, 7/27/11, Baruch Burstein  wrote:

> From: Baruch Burstein 
> Subject: [sqlite] c-api
> To: "General Discussion of SQLite Database" 
> Date: Wednesday, July 27, 2011, 9:22 AM
> Is there an easier way to get a
> single value (for instance "select
> last_insert_rowid();" ) then prepare -> step ->
> column -> finalize?
> ___
> 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

Re: [sqlite] year, month & day problem

[John Deal]

Hello,

I am new and have received much information from this list so I hope I am not 
wasting bandwidth. I don't know if it is my misunderstanding or typos but 
should your sqlite3_column_int() use indexes 0,1 and 2 instead of 1, 12 and 13? 
 If this is the case, according to the docs on sqlite3_column_int() "...if the 
column index is out of range, the result is undefined."

I hope I did not misunderstand the issue.

--- On Sun, 7/17/11, marco bianchini  wrote:

> From: marco bianchini 
> Subject: [sqlite] year, month & day problem
> To: sqlite-users@sqlite.org
> Date: Sunday, July 17, 2011, 6:05 AM
> Hi all,
> call me stupid but after some days of try and a lot of
> Googleing, im still
> wondering how to solve my problem:
> i need to execute a query that updates 3 integer fields
> (AA, MM, GG) of a
> table, containing respectively today year, today month and
> today day:
> 
> update settings set AA=strftime('%Y', 'now'),
> MM=strftime('%m', 'now'),
> GG=strftime('%d', 'now')
> 
> looks working well, but later, when i read that values:
> 
>     const char *sql="select AA,MM,GG from
> settings";
>     sqlite3_stmt *statmentS;
>     if (sqlite3_prepare_v2(database, sql 
> ,-1,, NULL)==SQLITE_OK)
> {
>         if
> (sqlite3_step(statmentS)==SQLITE_ROW) {
>             UserSettings *k =
> [UserSettings sharedUserSettings];
>            
> k.AA=sqlite3_column_int(statmentS, 1);
>            
> k.MM=sqlite3_column_int(statmentS, 12);
>            
> k.GG=sqlite3_column_int(statmentS, 13);
> ...
> 
> 
> i obtain correct values, but in inverse order:
> 
> AA (year) contains the day number
> MM (month) is correct
> GG (day) contains the year
> 
> using SQLIte Manager addons for Firefox, this query:
>  select  strftime('%Y', 'now'), strftime("%m", "now"),
> strftime("%d", "now")
> from settings
> returns correct values, running or loading value into XCode
> simulator looks
> not working and i dont know why. :'(
> 
> 
> does anyone can suggest me what to fix or check?
> thx in advance, marco
> ___
> 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

Re: [sqlite] Multi-threading Common Problem

[John Deal]

Hello Pavel,

Thanks.  This is the conclusion I was arriving at.  I do use the Sqlite3 
utility but only for looking at test results when the server is in an inactive 
state.

Thanks,

John

--- On Tue, 5/24/11, Pavel Ivanov <paiva...@gmail.com> wrote:

> From: Pavel Ivanov <paiva...@gmail.com>
> Subject: Re: [sqlite] Multi-threading Common Problem
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Date: Tuesday, May 24, 2011, 10:32 AM
> > I don't want per-say to remove
> my pthread_rwlock() but the main point of this discussion is
> I should not have to use pthread_rwlock().
> 
> I'd say pthread_rwlock and SQLite-specific mechanisms work
> completely
> differently and you should choose depending on what you
> want to do. As
> you saw to use SQLite's mechanisms you should write
> additional code
> waiting when write lock is released. As you said it's not
> effective
> and prone to starvation. But it works across process
> boundaries when
> pthread_rwlock works only inside your process although it
> does all
> waiting very effectively on a kernel level.
> So if you will ever want to connect to your database with
> sqlite3
> command line tool for example while your application is
> running, and
> you will do some manipulations with the database, then
> your
> pthread_rwlock won't work and you will still get
> SQLITE_BUSY. And now
> you decide what mechanism is better for you.
> 
> 
> Pavel
> 
> 
> On Tue, May 24, 2011 at 10:11 AM, John Deal <bassd...@yahoo.com>
> wrote:
> > Hello Pavel,
> >
> > I don't want per-say to remove my pthread_rwlock() but
> the main point of this discussion is I should not have to
> use pthread_rwlock().  Others have mentioned I should be
> using SQLite-specific mechanisms to achieve the same
> results.  I am just trying to understand how to do that.
>  Pthread_rwlock() works fine.
> >
> > Thanks,
> >
> > John
> >
> > --- On Tue, 5/24/11, Pavel Ivanov <paiva...@gmail.com>
> wrote:
> >
> >> From: Pavel Ivanov <paiva...@gmail.com>
> >> Subject: Re: [sqlite] Multi-threading Common
> Problem
> >> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> >> Date: Tuesday, May 24, 2011, 9:51 AM
> >> > I have all writes in
> >> transactions.  If I deactivate my
> pthread_rwlock() that
> >> enforce the above, several writes fail with a
> "database
> >> locked" error (I assume it is returning
> SQLITE_BUSY).
> >> >
> >> > So how do I implement the equivalent of a
> >> pthread_rwlock() using SQLite mechinisms?
> >>
> >> When SQLITE_BUSY in a reader transaction is
> returned just
> >> wait a
> >> little bit and try again. Also you can benefit
> from
> >> sqlite3_busy_timeout (http://www.sqlite.org/c3ref/busy_timeout.html).
> >>
> >> Another question is why do you want to get read
> of
> >> pthread_rwlock if
> >> it works for you?
> >>
> >>
> >> Pavel
> >>
> >>
> >> On Tue, May 24, 2011 at 7:43 AM, John Deal <bassd...@yahoo.com>
> >> wrote:
> >> > Hello Roger,
> >> >
> >> > Sorry to be so brain-dead but I am still
> confused.  I
> >> have multiple threads, each with their own DB
> connection.
> >>  I want to allow multiple readers accessing the
> DB at the
> >> same time since nothing is changing.  However, if
> a writes
> >> is to take place, I want all readers to finish
> their reads
> >> and give the writer exclusive access.  Once the
> writer is
> >> done, the readers can come back in.
> >> >
> >> > I have all writes in transactions.  If I
> deactivate
> >> my pthread_rwlock() that enforce the above,
> several writes
> >> fail with a "database locked" error (I assume it
> is
> >> returning SQLITE_BUSY).  With my
> pthread_rwlock(), I have
> >> multiple threads reading the DB and my writes get
> the
> >> exclusive access they need.  Now I could loop on
> the write
> >> until it gets in but that seems very wasteful.
> >> >
> >> > So how do I implement the equivalent of a
> >> pthread_rwlock() using SQLite mechinisms?
> >> >
> >> > Thanks,
> >> >
> >> > John
> >> >
> >> > --- On Tue, 5/24/11, Roger Binns <rog...@rogerbinns.com>
> >> wrote:
> >> >
> >> >> From: Roger

Re: [sqlite] Multi-threading Common Problem

[John Deal]

Hello Pavel,

I don't want per-say to remove my pthread_rwlock() but the main point of this 
discussion is I should not have to use pthread_rwlock().  Others have mentioned 
I should be using SQLite-specific mechanisms to achieve the same results.  I am 
just trying to understand how to do that.  Pthread_rwlock() works fine.

Thanks,

John

--- On Tue, 5/24/11, Pavel Ivanov <paiva...@gmail.com> wrote:

> From: Pavel Ivanov <paiva...@gmail.com>
> Subject: Re: [sqlite] Multi-threading Common Problem
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Date: Tuesday, May 24, 2011, 9:51 AM
> > I have all writes in
> transactions.  If I deactivate my pthread_rwlock() that
> enforce the above, several writes fail with a "database
> locked" error (I assume it is returning SQLITE_BUSY).
> >
> > So how do I implement the equivalent of a
> pthread_rwlock() using SQLite mechinisms?
> 
> When SQLITE_BUSY in a reader transaction is returned just
> wait a
> little bit and try again. Also you can benefit from
> sqlite3_busy_timeout (http://www.sqlite.org/c3ref/busy_timeout.html).
> 
> Another question is why do you want to get read of
> pthread_rwlock if
> it works for you?
> 
> 
> Pavel
> 
> 
> On Tue, May 24, 2011 at 7:43 AM, John Deal <bassd...@yahoo.com>
> wrote:
> > Hello Roger,
> >
> > Sorry to be so brain-dead but I am still confused.  I
> have multiple threads, each with their own DB connection.
>  I want to allow multiple readers accessing the DB at the
> same time since nothing is changing.  However, if a writes
> is to take place, I want all readers to finish their reads
> and give the writer exclusive access.  Once the writer is
> done, the readers can come back in.
> >
> > I have all writes in transactions.  If I deactivate
> my pthread_rwlock() that enforce the above, several writes
> fail with a "database locked" error (I assume it is
> returning SQLITE_BUSY).  With my pthread_rwlock(), I have
> multiple threads reading the DB and my writes get the
> exclusive access they need.  Now I could loop on the write
> until it gets in but that seems very wasteful.
> >
> > So how do I implement the equivalent of a
> pthread_rwlock() using SQLite mechinisms?
> >
> > Thanks,
> >
> > John
> >
> > --- On Tue, 5/24/11, Roger Binns <rog...@rogerbinns.com>
> wrote:
> >
> >> From: Roger Binns <rog...@rogerbinns.com>
> >> Subject: Re: [sqlite] Multi-threading Common
> Problem
> >> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> >> Date: Tuesday, May 24, 2011, 3:10 AM
> >> -BEGIN PGP SIGNED MESSAGE-
> >> Hash: SHA1
> >>
> >> On 05/23/2011 09:12 PM, John Deal wrote:
> >> > I guess I am lost on how to obtain a many
> reader or
> >> one writer mutex in SQLite.
> >>
> >> You are confusing locks on the database and locks
> in the
> >> library on a
> >> sqlite3 pointer.  The latter is what the mutex
> alloc
> >> function you reference
> >> is about and there there is no reader/writer
> >> mechanism.  Access has to be
> >> serialized.
> >>
> >> > You are correct in the locking article
> referenced I
> >> want a mutex that can have the lock states of
> shared,
> >> pending, and exclusive.
> >>
> >> Those are locks on the database which you get
> through
> >> regular operations and
> >> transactions.
> >>
> >> Roger
> >> -BEGIN PGP SIGNATURE-
> >> Version: GnuPG v1.4.11 (GNU/Linux)
> >>
> >>
> iEYEARECAAYFAk3bWeIACgkQmOOfHg372QQf8QCgjlawQMJWJ1I3/6OqMkczXswk
> >> VWQAmgLzGifXbh9UJpuEdUTTZl8e8xYp
> >> =rXCY
> >> -END PGP SIGNATURE-
> >> ___
> >> 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-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

Re: [sqlite] Multi-threading Common Problem

[John Deal]

Hello Roger,

Sorry to be so brain-dead but I am still confused.  I have multiple threads, 
each with their own DB connection.  I want to allow multiple readers accessing 
the DB at the same time since nothing is changing.  However, if a writes is to 
take place, I want all readers to finish their reads and give the writer 
exclusive access.  Once the writer is done, the readers can come back in.

I have all writes in transactions.  If I deactivate my pthread_rwlock() that 
enforce the above, several writes fail with a "database locked" error (I assume 
it is returning SQLITE_BUSY).  With my pthread_rwlock(), I have multiple 
threads reading the DB and my writes get the exclusive access they need.  Now I 
could loop on the write until it gets in but that seems very wasteful.

So how do I implement the equivalent of a pthread_rwlock() using SQLite 
mechinisms?

Thanks,

John 

--- On Tue, 5/24/11, Roger Binns <rog...@rogerbinns.com> wrote:

> From: Roger Binns <rog...@rogerbinns.com>
> Subject: Re: [sqlite] Multi-threading Common Problem
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Date: Tuesday, May 24, 2011, 3:10 AM
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 05/23/2011 09:12 PM, John Deal wrote:
> > I guess I am lost on how to obtain a many reader or
> one writer mutex in SQLite.
> 
> You are confusing locks on the database and locks in the
> library on a
> sqlite3 pointer.  The latter is what the mutex alloc
> function you reference
> is about and there there is no reader/writer
> mechanism.  Access has to be
> serialized.
> 
> > You are correct in the locking article referenced I
> want a mutex that can have the lock states of shared,
> pending, and exclusive.
> 
> Those are locks on the database which you get through
> regular operations and
> transactions.
> 
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
> 
> iEYEARECAAYFAk3bWeIACgkQmOOfHg372QQf8QCgjlawQMJWJ1I3/6OqMkczXswk
> VWQAmgLzGifXbh9UJpuEdUTTZl8e8xYp
> =rXCY
> -END PGP SIGNATURE-
> ___
> 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

Re: [sqlite] Multi-threading Common Problem

[John Deal]

Hello Roger,

Sorry for the long delay.  I did not see this in the swamp of recent email.

I guess I am lost on how to obtain a many reader or one writer mutex in SQLite. 
 I interpret the mutex returned by sqlite3_mutex_alloc() as being an exclusive 
mutex since the documentation states "The sqlite3_mutex_enter() and 
sqlite3_mutex_try() routines attempt to enter a mutex. If another thread is 
already within the mutex, sqlite3_mutex_enter() will block and 
sqlite3_mutex_try() will return SQLITE_BUSY."  If I interpret this correctly, 
this is to ensure serial access to the DB in a multi-connection/multi-thread 
environment.  It seems if a recursive mutex is requested, the same thread can 
enter the mutex multiple times if it also frees the mutex the same amount of 
time. I don't understand how to implement a multi-reader or one writer mutex 
with sqlite3_mutex_alloc().  Should I be looking at something else?

You are correct in the locking article referenced I want a mutex that can have 
the lock states of shared, pending, and exclusive. In other words, the same 
functionality as a pthread_rwlock() OS call.  How do I get this?

Sorry if I am missing something obvious.

Thanks,

John


--- On Fri, 5/13/11, Roger Binns <rog...@rogerbinns.com> wrote:

> From: Roger Binns <rog...@rogerbinns.com>
> Subject: Re: [sqlite] Multi-threading Common Problem
> To: sqlite-users@sqlite.org
> Date: Friday, May 13, 2011, 2:00 AM
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 05/12/2011 01:26 PM, John Deal wrote:
> > Good question.  Very possible my understanding is
> not complete.
> 
> This document has the full details:
> 
>   http://www.sqlite.org/lockingv3.html
> 
> >I have basically read and write transactions, each
> potentially with several accesses to the DB.  
> >I want to ensure that if a write transaction is
> happening, no read
> transactions are in progress
> 
> You do know you can use transactions for
> reads?   Or use multiple database
> connections to get isolation.  If you worry about the
> efficiency of the
> latter then don't - ie get your code correct and then worry
> about
> performance.  I recommend against the use of shared
> cache mode on general
> purpose computers (as opposed to embedded devices with
> trivial amounts of
> memory) because it changes some API behaviour (especially
> busy handling) and
> the amount of memory "wasted" is a rounding error.
> 
> In any event it looks like I am not understanding some
> deeper aspect of what
> you are doing.  My underlying point remains - there is
> absolutely no need to
> remove or workaround SQLite's builtin mutexes.  They
> ensure that threaded
> code does not screw things up and are thoroughly
> tested/developed.
> 
> Any question that starts with "so I
> removed/changed/subverted SQLite's
> existing mutexes" will be followed with a response where
> you'll need to
> prove that doing so isn't the cause.
> 
> It is ok to augment them with your own locking but even
> that should not be
> necessary.
> 
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
> 
> iEYEARECAAYFAk3MyPkACgkQmOOfHg372QTTXgCcCa2bDbYH9WKQ2J2fPYhKLHPX
> DBgAoLoj+uRJ3GDIHWGU7TfgNXxDAuAH
> =exlM
> -END PGP SIGNATURE-
> ___
> 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

Re: [sqlite] Common Multi-treaded Problem

[John Deal]

Hello Simon,

Yes I am following that sequence.  However I do my sqlite3_column(...) calls to 
extract data from selects between the step() and reset() calls.

My concern is the time between the step() and reset() calls if another select 
step() is attempted from another thread with a separate DB connection.  I am 
afraid that the second step() call will fail with a "database locked" error 
since the first step() call is complete but the reset() has not taken place yet 
(because I am doing column() calls to extract the retrieved data before 
resetting the prepared statement).

Thanks,

John

--- On Fri, 5/13/11, Simon Slavin <slav...@bigfraud.org> wrote:

> From: Simon Slavin <slav...@bigfraud.org>
> Subject: Re: [sqlite] Common Multi-treaded Problem
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Date: Friday, May 13, 2011, 9:05 PM
> 
> On 14 May 2011, at 12:29am, John Deal wrote:
> 
> > I actually never finalize prepared statements until
> the DB is closed and not being used anymore.  I reset
> the prepared statements.  For writes (inserts, updates,
> etc.) I use explicit transactions with commits (or rollbacks
> if errors).  I reuse the prepared statements by
> rebinding parameters (always changing anyway).  My
> understanding is you finalize a prepared statement when you
> know you will not use it anymore to free up resources. 
> My application uses the prepared statements until the DB is
> shutdown.  Is that not proper?  Please enlighten
> me if this is not what I should be doing.
> 
> You may be okay, as long as you conform to the order in
> 
> <http://www.sqlite.org/c3ref/stmt.html>
> 
> "The life of a statement object goes something like this:
> 
>     • Create the object using
> sqlite3_prepare_v2() or a related function.
>     • Bind values to host parameters using
> the sqlite3_bind_*() interfaces.
>     • Run the SQL by calling
> sqlite3_step() one or more times.
>     • Reset the statement using
> sqlite3_reset() then go back to step 2. Do this zero or more
> times.
>     • Destroy the object using
> sqlite3_finalize()."
> 
> So if you do a _reset() after the last _step() for each
> search, which is what you say you do, you should be
> okay.  Then do a _finalize() before closing the
> database handle.
> 
> I still think it should be possible to find out where your
> leak is, though I'm not sure how.  I would recommend
> that you check the result code for every call to a sqlite3_
> function, though, if you're not already doing that.
> 
> Simon.
> ___
> 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

Re: [sqlite] Common Multi-treaded Problem

[John Deal]

Hello Pavel,

Many thanks on the extensive information.  I think I understand what you are 
saying.  I do have a couple of questions.

If on thread #1 using connection #1 does a step on a prepared select statement 
and then is blocked before the sqlite3_column() statements and tailing reset 
statement and thread #2 using connection #2 tries to do a step of a different 
select prepared statement on the same DB, will the second select step return a 
database lock error?  It just seems with what I understand this is a race 
condition.  Of course with multi-core, thread #1 does not even have to block 
for this to happen.

If the above is true, then it seems the solution is to use a single DB 
connection and let the selects serialize.  I choose the separate 
connection/separate thread model since that was implemented before the separate 
thread/common connection model assuming the implementation of the former was 
more established than the later.

Again thanks for the information and I apologize for taking up so much list 
bandwidth.  I hope others can benefit.

John

--- On Thu, 5/12/11, Pavel Ivanov <paiva...@gmail.com> wrote:

> From: Pavel Ivanov <paiva...@gmail.com>
> Subject: Re: [sqlite] Common Multi-treaded Problem
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Date: Thursday, May 12, 2011, 9:53 PM
> > Humm.  Resetting each prepared
> statement right after use seemed to work.  So in review, a
> select prepared statement will lock the DB from other
> threads (or is it DB connections?) but not the current
> thread (or is it DB connection).
> 
> Yes, you are right. Transactions and database locks are
> per-connection. So if you work with only one connection
> (even sharing
> it between different threads) and not resetting your
> statements it
> means that all your application works in a one huge
> transaction,
> committing everything at the end (I guess if you hard kill
> your
> application in the middle you'll see that nothing was
> committed to the
> database). And if you work with several different
> connections (no
> matter in different threads or in a single thread) they
> will block
> each other, i.e. if you execute writing transaction in one
> connection
> you won't be able to write in a second connection and
> sometimes you
> won't even able to read in a second connection.
> 
> And answering your question from another email: you can
> step through
> any number of prepared statements simultaneously as long as
> they are
> all created from the same connection. They won't block each
> other from
> executing. You just can't call sqlite3_step() on one
> connection
> simultaneously in several threads - they will be
> serialized. Other
> than that you are free to step through any number of select
> statements
> and execute updates in parallel. But there's one rule of
> thumb to
> remember: never change data that should be returned in some
> active
> select statement. You can get very surprising behavior in
> this case.
> 
> 
> Pavel
> 
> 
> On Thu, May 12, 2011 at 8:33 PM, John Deal <bassd...@yahoo.com>
> wrote:
> > Hello Pavel,
> >
> > Humm.  Resetting each prepared statement right after
> use seemed to work.  So in review, a select prepared
> statement will lock the DB from other threads (or is it DB
> connections?) but not the current thread (or is it DB
> connection).
> >
> > Thanks for the help!
> >
> > John
> >
> > --- On Thu, 5/12/11, Pavel Ivanov <paiva...@gmail.com>
> wrote:
> >
> >> From: Pavel Ivanov <paiva...@gmail.com>
> >> Subject: Re: [sqlite] Common Multi-treaded
> Problem
> >> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> >> Date: Thursday, May 12, 2011, 5:58 PM
> >> > Interesting is the impression I
> >> had with prepared statements was the reset was
> only
> >> necessary if you wanted to reuse that statement.
>  Since
> >> each each DB connection is in its own instance of
> a class
> >> (with it own set of prepared statements) I would
> not think
> >> there would be any dependency on different
> physical prepared
> >> statements on different threads.  I would expect
> this with
> >> incomplete transactions.
> >>
> >> There's no dependency between different prepared
> >> statements, but there
> >> is dependency between transactions as they use the
> same
> >> database. And
> >> transaction cannot be finished (implicitly or
> explicitly)
> >> until all
> >> statements in this transaction are reset or
> finalized.
>

Re: [sqlite] Common Multi-treaded Problem

[John Deal]

Hello Simon,

I actually never finalize prepared statements until the DB is closed and not 
being used anymore.  I reset the prepared statements.  For writes (inserts, 
updates, etc.) I use explicit transactions with commits (or rollbacks if 
errors).  I reuse the prepared statements by rebinding parameters (always 
changing anyway).  My understanding is you finalize a prepared statement when 
you know you will not use it anymore to free up resources.  My application uses 
the prepared statements until the DB is shutdown.  Is that not proper?  Please 
enlighten me if this is not what I should be doing.

Thanks,

John

--- On Thu, 5/12/11, Simon Slavin <slav...@bigfraud.org> wrote:

> From: Simon Slavin <slav...@bigfraud.org>
> Subject: Re: [sqlite] Common Multi-treaded Problem
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Date: Thursday, May 12, 2011, 9:28 PM
> 
> On 13 May 2011, at 1:33am, John Deal wrote:
> 
> > Humm.  Resetting each prepared statement right
> after use seemed to work.  So in review, a select
> prepared statement will lock the DB from other threads (or
> is it DB connections?) but not the current thread (or is it
> DB connection).
> 
> I don't think it's meant to work like that.  Are you
> getting errors when you call _finalize() ?
> 
> <http://www.sqlite.org/c3ref/finalize.html>
> 
> Simon.
> ___
> 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

Re: [sqlite] Multi-threading Common Problem

[John Deal]

Hello Roger,

OK I see your point now.  I could most likely remove the OS mutexes.

Thanks,

John

--- On Thu, 5/12/11, Roger Binns <rog...@rogerbinns.com> wrote:

> From: Roger Binns <rog...@rogerbinns.com>
> Subject: Re: [sqlite] Multi-threading Common Problem
> To: sqlite-users@sqlite.org
> Date: Thursday, May 12, 2011, 4:01 PM
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 05/12/2011 09:38 AM, John Deal wrote:
> > I have been working for weeks on this and I feel there
> must be something simple I am overlooking.  
> 
> Why are you discarding SQLite's builtin and tested mutexes
> and then
> effectively reimplementing your own to get the same
> effect?
> 
> Or bigger picture question what is it you are trying to
> achieve in the first
> place?
> 
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
> 
> iEYEARECAAYFAk3MPIkACgkQmOOfHg372QQzjgCg3106pWiiUMuOQay+2ONv3G0c
> ZvQAnAvBFXI+A8ae8tV9yXRmz7IZgid6
> =jehy
> -END PGP SIGNATURE-
> ___
> 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

Re: [sqlite] Common Multi-treaded Problem

[John Deal]

Hello Pavel,

Humm.  Resetting each prepared statement right after use seemed to work.  So in 
review, a select prepared statement will lock the DB from other threads (or is 
it DB connections?) but not the current thread (or is it DB connection).

Thanks for the help!

John

--- On Thu, 5/12/11, Pavel Ivanov <paiva...@gmail.com> wrote:

> From: Pavel Ivanov <paiva...@gmail.com>
> Subject: Re: [sqlite] Common Multi-treaded Problem
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Date: Thursday, May 12, 2011, 5:58 PM
> > Interesting is the impression I
> had with prepared statements was the reset was only
> necessary if you wanted to reuse that statement.  Since
> each each DB connection is in its own instance of a class
> (with it own set of prepared statements) I would not think
> there would be any dependency on different physical prepared
> statements on different threads.  I would expect this with
> incomplete transactions.
> 
> There's no dependency between different prepared
> statements, but there
> is dependency between transactions as they use the same
> database. And
> transaction cannot be finished (implicitly or explicitly)
> until all
> statements in this transaction are reset or finalized.
> 
> 
> Pavel
> 
> 
> On Thu, May 12, 2011 at 4:01 PM, John Deal <bassd...@yahoo.com>
> wrote:
> > Hello Igor,
> >
> > That very well maybe it.  I am not at home so can't
> test for sure but I reset the prepared statements right
> before I use them so they are left hanging if another thread
> came in.
> >
> > Interesting is the impression I had with prepared
> statements was the reset was only necessary if you wanted to
> reuse that statement.  Since each each DB connection is in
> its own instance of a class (with it own set of prepared
> statements) I would not think there would be any dependency
> on different physical prepared statements on different
> threads.  I would expect this with incomplete
> transactions.
> >
> > Anyway, thanks for the insight.
> >
> > John
> >
> > --- On Thu, 5/12/11, Igor Tandetnik <itandet...@mvps.org>
> wrote:
> >
> >> From: Igor Tandetnik <itandet...@mvps.org>
> >> Subject: Re: [sqlite] Common Multi-treaded
> Problem
> >> To: sqlite-users@sqlite.org
> >> Date: Thursday, May 12, 2011, 12:35 PM
> >> On 5/12/2011 12:31 PM, John Deal
> >> wrote:
> >> > When I allow multiple readers with each
> thread using a
> >> different DB
> >> > connection (open with the same flags) and
> each thread
> >> having
> >> > exclusive use of its DB connection (no
> sharing of
> >> connections) and if
> >> > more than one thread is reading the DB at the
> same
> >> time, the DB
> >> > becomes locked for writing even when all the
> reads are
> >> finished.
> >>
> >> My first inclination would be to look for places
> where you
> >> leak active
> >> statement handles, by failing to reset or
> finalize
> >> statements. The read
> >> operation is not really finished until the
> statement is
> >> reset/finalized.
> >> --
> >> Igor Tandetnik
> >>
> >> ___
> >> 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-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

Re: [sqlite] Common Multi-treaded Problem

[John Deal]

Hello again Pavel,

OK but I am a bit confused.  What I have works as a single instance with one DB 
connection running under different threads as long as only one is using the DB 
connection at a time.  This is with prepared statements "hanging" (not reset) 
with different threads using different prepared statements (resetting the ones 
they use before using them).  With what you state this should not work (in this 
case both reads and writes work).

If I understand you correctly, on a single thread using one prepared statement 
(say a select) then using another prepared statement (say another select) 
should not work since the 1st prepared statement was not reset.  Also what 
about the time between the statement is executed (sqlite3_step()) and reading 
the return code and reading the retrieved data?  It is my understanding you 
can't reset the statement until after you have performed these activities. That 
would imply if what I interpret your statements correctly, that the database is 
locked until the reset is issued.

I appreacate your help.  I am just trying to understand what you are trying to 
tell me.

Thanks,

John 


--- On Thu, 5/12/11, Pavel Ivanov <paiva...@gmail.com> wrote:

> From: Pavel Ivanov <paiva...@gmail.com>
> Subject: Re: [sqlite] Common Multi-treaded Problem
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Date: Thursday, May 12, 2011, 5:58 PM
> > Interesting is the impression I
> had with prepared statements was the reset was only
> necessary if you wanted to reuse that statement.  Since
> each each DB connection is in its own instance of a class
> (with it own set of prepared statements) I would not think
> there would be any dependency on different physical prepared
> statements on different threads.  I would expect this with
> incomplete transactions.
> 
> There's no dependency between different prepared
> statements, but there
> is dependency between transactions as they use the same
> database. And
> transaction cannot be finished (implicitly or explicitly)
> until all
> statements in this transaction are reset or finalized.
> 
> 
> Pavel
> 
> 
> On Thu, May 12, 2011 at 4:01 PM, John Deal <bassd...@yahoo.com>
> wrote:
> > Hello Igor,
> >
> > That very well maybe it.  I am not at home so can't
> test for sure but I reset the prepared statements right
> before I use them so they are left hanging if another thread
> came in.
> >
> > Interesting is the impression I had with prepared
> statements was the reset was only necessary if you wanted to
> reuse that statement.  Since each each DB connection is in
> its own instance of a class (with it own set of prepared
> statements) I would not think there would be any dependency
> on different physical prepared statements on different
> threads.  I would expect this with incomplete
> transactions.
> >
> > Anyway, thanks for the insight.
> >
> > John
> >
> > --- On Thu, 5/12/11, Igor Tandetnik <itandet...@mvps.org>
> wrote:
> >
> >> From: Igor Tandetnik <itandet...@mvps.org>
> >> Subject: Re: [sqlite] Common Multi-treaded
> Problem
> >> To: sqlite-users@sqlite.org
> >> Date: Thursday, May 12, 2011, 12:35 PM
> >> On 5/12/2011 12:31 PM, John Deal
> >> wrote:
> >> > When I allow multiple readers with each
> thread using a
> >> different DB
> >> > connection (open with the same flags) and
> each thread
> >> having
> >> > exclusive use of its DB connection (no
> sharing of
> >> connections) and if
> >> > more than one thread is reading the DB at the
> same
> >> time, the DB
> >> > becomes locked for writing even when all the
> reads are
> >> finished.
> >>
> >> My first inclination would be to look for places
> where you
> >> leak active
> >> statement handles, by failing to reset or
> finalize
> >> statements. The read
> >> operation is not really finished until the
> statement is
> >> reset/finalized.
> >> --
> >> Igor Tandetnik
> >>
> >> ___
> >> 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-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

Re: [sqlite] Multi-threading Common Problem

[John Deal]

Hello Pavel,

This makes sense but I have shared cache on.

Thanks.

--- On Thu, 5/12/11, Pavel Ivanov <paiva...@gmail.com> wrote:

> From: Pavel Ivanov <paiva...@gmail.com>
> Subject: Re: [sqlite] Multi-threading Common Problem
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Date: Thursday, May 12, 2011, 5:55 PM
> > "After a BEGIN EXCLUSIVE, no
> other database connection except for read_uncommitted
> connections will be able to read the database and no other
> connection without exception will be able to write the
> database until the transaction is complete."
> >
> > This tells me that reads outside of a transaction
> would be permitted while an exclusive transaction is taking
> place.
> 
> This works only when shared cache mode is turned on and
> only within
> the same process. Nothing uncommitted can be read between
> processes or
> between different connections in the same process when
> shared cache is
> turned off.
> 
> 
> Pavel
> 
> 
> On Thu, May 12, 2011 at 4:26 PM, John Deal <bassd...@yahoo.com>
> wrote:
> > Good question.  Very possible my understanding is not
> complete.
> >
> > I have basically read and write transactions, each
> potentially with several accesses to the DB.  I want to
> ensure that if a write transaction is happening, no read
> transactions are in progress since it would be possible to
> have obtain incomplete data (mixture of some reads being
> valid but other no longer valid because the write
> transaction changed them).  In other words, a read
> "transaction" (I do not use a transaction for the reads)
> consists of multiple pieces of data that makeup a set that I
> want to ensure is valid as a set.
> >
> > It is my understanding that a transaction (which I do
> use for the write transaction which is also a set) locks the
> DB for writes but not reads. If a transaction does lock the
> DB for exclusive access then you are correct, I do not need
> the OS mutexes.  Maybe I do not understand the following:
> >
> > "After a BEGIN EXCLUSIVE, no other database connection
> except for read_uncommitted connections will be able to read
> the database and no other connection without exception will
> be able to write the database until the transaction is
> complete."
> >
> > This tells me that reads outside of a transaction
> would be permitted while an exclusive transaction is taking
> place.
> >
> > If a write transaction is not taking place, I want to
> allow multiple reads which the OS rwlock allows.
> >
> > Any enlightenment would be welcomed.
> >
> > Thanks.
> >
> > --- On Thu, 5/12/11, Roger Binns <rog...@rogerbinns.com>
> wrote:
> >
> >> From: Roger Binns <rog...@rogerbinns.com>
> >> Subject: Re: [sqlite] Multi-threading Common
> Problem
> >> To: sqlite-users@sqlite.org
> >> Date: Thursday, May 12, 2011, 4:01 PM
> >> -BEGIN PGP SIGNED MESSAGE-
> >> Hash: SHA1
> >>
> >> On 05/12/2011 09:38 AM, John Deal wrote:
> >> > I have been working for weeks on this and I
> feel there
> >> must be something simple I am overlooking.
> >>
> >> Why are you discarding SQLite's builtin and tested
> mutexes
> >> and then
> >> effectively reimplementing your own to get the
> same
> >> effect?
> >>
> >> Or bigger picture question what is it you are
> trying to
> >> achieve in the first
> >> place?
> >>
> >> Roger
> >> -BEGIN PGP SIGNATURE-
> >> Version: GnuPG v1.4.11 (GNU/Linux)
> >>
> >>
> iEYEARECAAYFAk3MPIkACgkQmOOfHg372QQzjgCg3106pWiiUMuOQay+2ONv3G0c
> >> ZvQAnAvBFXI+A8ae8tV9yXRmz7IZgid6
> >> =jehy
> >> -END PGP SIGNATURE-
> >> ___
> >> 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-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

Re: [sqlite] Multi-threading Common Problem

[John Deal]

Good question.  Very possible my understanding is not complete.

I have basically read and write transactions, each potentially with several 
accesses to the DB.  I want to ensure that if a write transaction is happening, 
no read transactions are in progress since it would be possible to have obtain 
incomplete data (mixture of some reads being valid but other no longer valid 
because the write transaction changed them).  In other words, a read 
"transaction" (I do not use a transaction for the reads) consists of multiple 
pieces of data that makeup a set that I want to ensure is valid as a set.

It is my understanding that a transaction (which I do use for the write 
transaction which is also a set) locks the DB for writes but not reads. If a 
transaction does lock the DB for exclusive access then you are correct, I do 
not need the OS mutexes.  Maybe I do not understand the following:

"After a BEGIN EXCLUSIVE, no other database connection except for 
read_uncommitted connections will be able to read the database and no other 
connection without exception will be able to write the database until the 
transaction is complete."

This tells me that reads outside of a transaction would be permitted while an 
exclusive transaction is taking place.

If a write transaction is not taking place, I want to allow multiple reads 
which the OS rwlock allows.

Any enlightenment would be welcomed.

Thanks.

--- On Thu, 5/12/11, Roger Binns <rog...@rogerbinns.com> wrote:

> From: Roger Binns <rog...@rogerbinns.com>
> Subject: Re: [sqlite] Multi-threading Common Problem
> To: sqlite-users@sqlite.org
> Date: Thursday, May 12, 2011, 4:01 PM
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 05/12/2011 09:38 AM, John Deal wrote:
> > I have been working for weeks on this and I feel there
> must be something simple I am overlooking.  
> 
> Why are you discarding SQLite's builtin and tested mutexes
> and then
> effectively reimplementing your own to get the same
> effect?
> 
> Or bigger picture question what is it you are trying to
> achieve in the first
> place?
> 
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
> 
> iEYEARECAAYFAk3MPIkACgkQmOOfHg372QQzjgCg3106pWiiUMuOQay+2ONv3G0c
> ZvQAnAvBFXI+A8ae8tV9yXRmz7IZgid6
> =jehy
> -END PGP SIGNATURE-
> ___
> 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

Re: [sqlite] Common Multi-treaded Problem

[John Deal]

Hello Igor,

That very well maybe it.  I am not at home so can't test for sure but I reset 
the prepared statements right before I use them so they are left hanging if 
another thread came in.

Interesting is the impression I had with prepared statements was the reset was 
only necessary if you wanted to reuse that statement.  Since each each DB 
connection is in its own instance of a class (with it own set of prepared 
statements) I would not think there would be any dependency on different 
physical prepared statements on different threads.  I would expect this with 
incomplete transactions.

Anyway, thanks for the insight.

John

--- On Thu, 5/12/11, Igor Tandetnik <itandet...@mvps.org> wrote:

> From: Igor Tandetnik <itandet...@mvps.org>
> Subject: Re: [sqlite] Common Multi-treaded Problem
> To: sqlite-users@sqlite.org
> Date: Thursday, May 12, 2011, 12:35 PM
> On 5/12/2011 12:31 PM, John Deal
> wrote:
> > When I allow multiple readers with each thread using a
> different DB
> > connection (open with the same flags) and each thread
> having
> > exclusive use of its DB connection (no sharing of
> connections) and if
> > more than one thread is reading the DB at the same
> time, the DB
> > becomes locked for writing even when all the reads are
> finished.
> 
> My first inclination would be to look for places where you
> leak active 
> statement handles, by failing to reset or finalize
> statements. The read 
> operation is not really finished until the statement is
> reset/finalized.
> -- 
> Igor Tandetnik
> 
> ___
> 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] Multi-threading Common Problem

[John Deal]

Hello All,

I have been using SQLite for a couple of years but have never posted to this 
list before.  I am sure my problem is common and am looking for ideas to solve 
it.

I have used SQLite extensively single-threaded with no problems (other than my 
own!).  I am currently working on another project adding SQLite functionality 
to a multi-threaded environment.  Here is my situation.

Ubuntu 10.04 64-bit.

Have used the SQLite3 library and compiled from source directly into the 
application.  Same results.

Multi-threaded compile flag configuration kept as the default and forced with 
sqlite3_config() to SQLITE_CONFIG_MULTITHREAD with no error reported.  Open is 
via SQLITE_OPEN_READWRITE | SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_SHAREDCACHE 
flags.  I have tried this with and without shared cache.

When I use the above configuration multi-threaded but using only one DB 
connection in which only one thread is accessing the DB at a time, it works 
fine for both read and write.  I use OS read/write mutex in which all threads 
must obtain a write lock to get at the DB effectively forcing only one thread 
accessing the DB at one time.

When I allow multiple readers with each thread using a different DB connection 
(open with the same flags) and each thread having exclusive use of its DB 
connection (no sharing of connections) and if more than one thread is reading 
the DB at the same time, the DB becomes locked for writing even when all the 
reads are finished.  The DB is locked, not the OS mutex.  There are no DB 
writes.  How can the DB be locked for writes in this situation?  I test this 
with the sqlite3 program and opening the database while the application is 
running and try to do an insert.

I have been working for weeks on this and I feel there must be something simple 
I am overlooking.  Thanks for any help.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[John Deal]

Hello All,

I have been using SQLite for a couple of years but have never posted to this 
list before.  I am sure my problem is common and am looking for ideas to solve 
it.

I have used SQLite extensively single-threaded with no problems (other than my 
own!).  I am currently working on another project adding SQLite functionality 
to a multi-threaded environment.  Here is my situation.

Ubuntu 10.04 64-bit.

Have used the SQLite3 library and compiled from source directly into the 
application.  Same results.

Multi-threaded compile flag configuration kept as the default and forced with 
sqlite3_config() to SQLITE_CONFIG_MULTITHREAD with no error reported.  Open is 
via SQLITE_OPEN_READWRITE | SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_SHAREDCACHE 
flags.  I have tried this with and without shared cache.

When I use the above configuration multi-threaded but using only one DB 
connection in which only one thread is accessing the DB at a time, it works 
fine for both read and write.  I use OS read/write mutex in which all threads 
must obtain a write lock to get at the DB effectively forcing only one thread 
accessing the DB at one time.

When I allow multiple readers with each thread using a different DB connection 
(open with the same flags) and each thread having exclusive use of its DB 
connection (no sharing of connections) and if more than one thread is reading 
the DB at the same time, the DB becomes locked for writing even when all the 
reads are finished.  The DB is locked, not the OS mutex.  There are no DB 
writes.  How can the DB be locked for writes in this situation?  I test this 
with the sqlite3 program and opening the database while the application is 
running and try to do an insert.

I have been working for weeks on this and I feel there must be something simple 
I am overlooking.  Thanks for any help.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Common Multi-treaded Problem

[John Deal]

Hello All,

I have been using SQLite for a couple of years but have never posted to this 
list before.  I am sure my problem is common and am looking for ideas to solve 
it.

I have basically the same situation as this thread:

---
On Wed, Jan 26, 2011 at 10:56 AM, Ian Hardingham  wrote:

> Hey guys.
>
> I am under the impression that there is no concurrent access to a single
> SQLite DB.  Ie if thread A is performing a query, and thread B trys to
> query, it will block until thread A is finished, no matter the query.
>
> 1.  Is this correct?
>

It is true if A and B are attempting to share the same database connection.
 Access to the database connection is protected by a mutex.


>
> 2.  Are there any fairly general workarounds of any kind?
>

Use a separate database connection for each thread.  Or better:  Use
processes instead of threads, as threads are evil.


>
> Thanks,
> Ian


I have used SQLite extensively single-threaded with no problems (other than my 
own!).  I am currently working on another project adding SQLite functionality 
to a multi-threaded environment.  Here is my situation.

Ubuntu 10.04 64-bit.

Have used the SQLite3 library and compiled from source directly into the 
applicaition.  Same results.

Multi-threaded compile flag configuration kept as the default and forced with 
sqlite3_config() to SQLITE_CONFIG_MULTITHREAD with no error reported.  Open is 
via SQLITE_OPEN_READWRITE | SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_SHAREDCACHE 
flags.  I have tried this with and without shared cache.

When I use the above configuration multi-threaded but using only one DB 
connection in which only one thread is accessing the DB at a time, it works 
fine for both read and write.  I use OS read/write mutex in which all threads 
must obtain a write lock to get at the DB effectively forcing only one thread 
accessing the DB at one time.

When I allow multiple readers with each thread using a different DB connection 
(open with the same flags) and each thread having exclusive use of its DB 
connection (no sharing of connections) and if more than one thread is reading 
the DB at the same time, the DB becomes locked for writing even when all the 
reads are finished.  The DB is locked, not the OS mutex.  There are no DB 
writes.  How can the DB be locked for writes in this situation?  I test this 
with the sqlite3 program and opening the database while the application is 
running and try to do an insert.

I have been working for weeks on this and I feel there must be something simple 
I am overlooking.  Thanks for any help.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users