Re: [sqlite] Shared Cache vs. Serialized

[Jesse Rittner]

Keith Medcalf wrote
> See also Write Ahead Logging journal mode (WAL) where in the case you
> specified B's operation will succeed even if it is updating a table being
> read by connection A. 

Oh, good to know! For anyone who stumbles across this thread in the future,
note that this requires that you be using separate private caches between
connections A and B. With a shared cache, you still get SQLITE_LOCKED even
with write-ahead logging.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Shared Cache vs. Serialized

[Keith Medcalf]

>Another interesting distinction between shared and private cache mode
>I found while experimenting. Ordinarily if connection A is in the
>middle of fetching result rows from a SELECT (i.e., sqlite3_step was called,
>but not sqlite3_reset), and connection B tries to do a CREATE/UPDATE/DELETE,
>B will get back SQLITE_BUSY. However, with a shared cache, B's operation
>will immediately succeed, provided it doesn't affect the same table(s).

See also Write Ahead Logging journal mode (WAL) where in the case you specified 
B's operation will succeed even if it is updating a table being read by 
connection A.

https://sqlite.org/wal.html

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] Shared Cache vs. Serialized

[Jesse Rittner]

Jens Alfke-2 wrote
> But since the connections are sharing a cache, they still end up seeing
> uncommitted writes.

I believe this is managed by the  read_uncommitted pragma
  .

Another interesting distinction between shared and private cache mode I
found while experimenting. Ordinarily if connection A is in the middle of
fetching result rows from a SELECT (i.e., sqlite3_step was called, but not
sqlite3_reset), and connection B tries to do a CREATE/UPDATE/DELETE, B will
get back SQLITE_BUSY. However, with a shared cache, B's operation will
immediately succeed, provided it doesn't affect the same table(s).



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Shared Cache vs. Serialized

[Jens Alfke]

> On Dec 30, 2018, at 7:52 AM, Jesse Rittner  wrote:
> 
> It
> seems to me that there are two ways we can have multiple threads all
> accessing a database via a "single" connection.

Actually there is at least one more way: Create a global connection pool. When 
a thread needs to access the database, it borrows a connection from the pool, 
does its work, and then returns it back to the pool. This approach even 
preserves isolation, as long as a thread that opens a transaction doesn’t 
return the connection until after it commits.

If a thread wants a connection but the pool is empty, it can either open a new 
connection or it can block until another thread returns one. It’s a tradeoff 
between availability and memory usage, basically.

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

Re: [sqlite] Shared Cache vs. Serialized

[Jens Alfke]

> On Dec 30, 2018, at 7:52 AM, Jesse Rittner  wrote:
> 
> One, establish a single
> serialized private cache connection up front, and give it to all the
> threads. Two, have each thread independently establish a multi-thread shared
> cache connection. What are the trade-offs between these two approaches?

Be careful — both of these approaches lack isolation (the I in ACID) between 
the threads.

If you share a connection, then if one thread begins a transaction, all the 
other threads of course share in that transaction. In other words, thread B can 
see intermediate uncommitted writes from an ongoing transaction in thread A. If 
you don’t want this (and it tends to be not what you want in a typical GUI 
application) you’ll have to set up your own mutex to lock out all other threads 
during a transaction. But this reduces availability.

If you use shared-cache mode, it _seems_ as though your threads should be 
isolated since they have separate connections. But since the connections are 
sharing a cache, they still end up seeing uncommitted writes. (Caveat: This is 
from memory and may be wrong. I briefly turned on shared-cache mode a few years 
ago, and had to back it out a month later after a series of weird application 
bugs. IIRC, this is the behavior that led to the bugs. There is a web page 
explaining this somewhere on the site.)

That’s not to say you can’t use these approaches, but you have to design your 
code such that a transaction is global, not per-thread.

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

Re: [sqlite] Shared Cache vs. Serialized

[Keith Medcalf]

The "normal" connection mode would be to have a separate connection for each 
thread, with no shared cache.  Each connection is opened with the FULLMUTEX 
(serialized) flags.  This is the default.  Each connection is fully isolated 
from every other connection.  Assuming that each "thread" has its own 
connection, each "thread" is isolated (database wise) from each other thread 
(by its different connection context).

You may "share" one connection amongst multiple threads provided that you 
"follow the entrance requirements".  Since database isolation is provided by 
the connection, multiple threads sharing the same connection share the same 
database context.

You may, if you must, optimize (ie, reduce) CPU usage by recovering the time 
spent executing the FULLMUTEX protections used in the default SERIALIZED mode 
by taking responsibility for this yourself and tell the SQLite3 library not to 
cover your ass but rather use YOUR OWN CODE or design to enforce the 
single-serial-entrance requirement of the underlying SQLite3 engine.  You will 
"recover" the time taken within the library to enforce this protection and 
instead you will spend that CPU time doing it yourself in your application, or 
will ensure the application design is such that violation of the rules is not 
possible (and choosing to perhaps forgo some operating system provided features 
that could "break" your design, such as DPC's or system managed thread pools in 
Winders).  However, the amount of CPU time saved is small and the added 
complication is large.  In other words you would do this only if it is cost 
effective and each nanosecond matters (though it would probably be more cost 
effective to just buy better hardware).

On the other hand, you may need to optimize memory usage at the expense of more 
CPU usage.  You do this by enabling "shared cache" so that the multiple 
connections "share" the same cache thus reducing memory used by multiple 
connections (since they now have one cache shared amongst them rather than one 
cache per connection).  This might permit an otherwise well designed 
application that uses multiple connections and threads to run on a device which 
is  memory constrained by trading memory for CPU (reduce memory increase CPU), 
after determining that you cannot fix the defect by simply adding more memory 
(which is likely far more cost efficient).  Since you are optimizing memory 
usage (ie, reducing it) by deliberately trading it for CPU (which will be 
increased to make up for the lack of memory) you have already decided that you 
do not want/need to optimize CPU usage (so there are pretty much no options 
here to optimize CPU usage).

Using a single connection in shared cache mode (vs that same one connection in 
non-shared cache mode) does nothing to optimize memory (you still have one 
cache being used for your one connection) but does still use all the extra CPU 
processing associated with managing the shared cache, because that shared cache 
still has to be managed even though you are only using one "shared cache" 
connection at the moment.

Therefore, one probably ought to use the default (one connection per thread, 
non-shared cache, in serialized mode) and optimize IF AND ONLY IF it is found  
that optimization is required.  Otherwise, one might find that they have 
prematurely optimized for the wrong thing and then spend inordinate amounts of 
time correcting the error (or griping about it on the mailing list).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Jesse Rittner
>Sent: Sunday, 30 December, 2018 08:53
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Shared Cache vs. Serialized
>
>I've been reading about the shared cache feature and I have a
>question. It
>seems to me that there are two ways we can have multiple threads all
>accessing a database via a "single" connection. One, establish a
>single
>serialized private cache connection up front, and give it to all the
>threads. Two, have each thread independently establish a multi-thread
>shared
>cache connection. What are the trade-offs between these two
>approaches? (I
>am using "serialized" and "multi-thread" as they are defined here:
>https://www.sqlite.org/threadsafe.html)
>
>Also, if my application only establishes a single connection to a
>particular
>database, does it matter at all if that connection uses a shared or
>private
>cache? In particular, I am establishing a single multi-thread
>connection
>that I protect with my own mutex. My assumption is "no", but I just
>want to
>confirm.
>
>
>

[sqlite] Shared Cache vs. Serialized

[Jesse Rittner]

I've been reading about the shared cache feature and I have a question. It
seems to me that there are two ways we can have multiple threads all
accessing a database via a "single" connection. One, establish a single
serialized private cache connection up front, and give it to all the
threads. Two, have each thread independently establish a multi-thread shared
cache connection. What are the trade-offs between these two approaches? (I
am using "serialized" and "multi-thread" as they are defined here:
https://www.sqlite.org/threadsafe.html)

Also, if my application only establishes a single connection to a particular
database, does it matter at all if that connection uses a shared or private
cache? In particular, I am establishing a single multi-thread connection
that I protect with my own mutex. My assumption is "no", but I just want to
confirm.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLIte Shared Cache In-Memory DB Persists After Application Shutdown

[yaro]

Thanks Clemens and Dan,
The issue is resolved, it was caused by not setting the URI Filename flag.

Yaro



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLIte Shared Cache In-Memory DB Persists After Application Shutdown

[yaro]

Dan,
Your question was well composed and illuminating. After going through my
code I realised that I didn't enable URI Filename in any of the ways you
mentioned, my guide to using URI Filename is 
https://www.sqlite.org/inmemorydb.html
  

May be that is the cause of my problem.

Yaro.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLIte Shared Cache In-Memory DB Persists After Application Shutdown

[Dan Kennedy]

On 11/02/2017 08:50 PM, yaro wrote:

Hello,
I have found the cause of the weird behaviour. The in Shared Cache In-Memory
DB isn't persisted, rather the problem is due to a temp file named "file"
that isn't deleted after my application closes. Whenever I delete this file
then everything works fine.

 From my search this file ought to be auto deleted by SQLite when closed but
it isn't. Could anyone please provide suggests on how to fix this, my PC has
win7.


How are you enabling URI filenames?

By building with -DSQLITE_USE_URI, or by enabling them globally at 
runtime using sqlite3_config(), or by passing the SQLITE_OPEN_URI flag 
to sqlite3_open_v2()?


Dan.

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

Re: [sqlite] SQLIte Shared Cache In-Memory DB Persists After Application Shutdown

[Clemens Ladisch]

yaro wrote:
> the problem is due to a temp file named "file" that isn't deleted after my 
> application closes.

SQLite does not create temp files named "file".


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLIte Shared Cache In-Memory DB Persists After Application Shutdown

[yaro]

Hello,
I have found the cause of the weird behaviour. The in Shared Cache In-Memory
DB isn't persisted, rather the problem is due to a temp file named "file"
that isn't deleted after my application closes. Whenever I delete this file
then everything works fine.

From my search this file ought to be auto deleted by SQLite when closed but
it isn't. Could anyone please provide suggests on how to fix this, my PC has
win7.

Yaro.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLIte Shared Cache In-Memory DB Persists After Application Shutdown

[yaro]

Hello,
I built TCl 8.6 with SQLite extension (3.2.0) which I embedded into a C++
application. Because I need SQLite in both TCL and C++ I defined the SQLite
macro of (the extension) to export all the SQLite C API's. So the the built
SQLite DLL library TCL extension exports both the TCL interface as well as
the C++ interface.

While using this SQLite DLL in C++ I created a shared-cache in-memory db
which I once mistakenly did not close. This in-memory db remains in memory
even after I restart/rebuild the C++ application. I know this because I
tried to create the same table with the same connect as before and I get
error "table ... already exists" from SQLite.

I wonder why this is so, because I thought that SQLite will free all its
resources everytime the application closes?

Yaro



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Shared cache mode and busy wait

[Bob Friesenhahn]

Today I saw a APSW note about shared cache mode at 
"https://rogerbinns.github.io/apsw/tips.html#shared-cache-mode;, which 
led me to 
"https://sqlite.org/src/tktview/ebde3f66fc64e21e61ef2854ed1a36dfff884a2f;.


Reading the sqlite page at "https://sqlite.org/sharedcache.html; I see 
that section "2.2. Table Level Locking" is not very clear or 
straight-forward.  One must read between the lines and surmise in 
order to understand that the sqlite connection timeout (or callback) 
mechanisms become useless in this mode in a multi-threaded program. 
It would be useful if there was a statement that the normal mechanism 
is rendered useless and that the calling thread must implement its own 
busy-wait if it must succeed.


The problem report was closed as "works as designed" (works as 
implemented?).


If the normal busy-wait or callback mechanisms are not supported, then 
it becomes prohibitive to enable this mode on an existing code base.


Given that each sqlite connection duplicates the entire database 
schema in RAM, this shared cache mode becomes quite useful on limited 
memory systems.  It would be good if it worked `properly'.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Shared-Cache Mode

[gwenn]

Hello,
Is there any way to known if one connection participate to shared-cache mode ?
I've read http://sqlite.org/sharedcache.html which specifies how to
set but not how to get the mode!
Regards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] shared cache mode and 'LOCKED'

[O'Neill, Owen]

Thankyou John and Tom !
I feel more enlightened now !

So I think I could summarise it by saying


It is assumed that every thread (within every process) has it's own
instance of a database connection object.

When multiple processes share a *database* file contention will be
indicated by SQL_BUSY being returned.

In addition when using shared cache mode within a single process *table*
contention will be indicated by SQL_LOCKED being returned.

In both cases the application should take care to release any locks it
has or is attempting to gain by finalising all the open handles it has
by calling finalize or reset before backing off for a period and trying
again.

Hence robust code should deal with both of these situations in a similar
( possibly identical) manner.

In shared cache mode this backing off period can be minimised by the use
of the 'notify' callback.

The 'busy' callback can be used to simplify application code's handling
of the busy situation.

Neither of these callbacks may be called and BUSY/ LOCKED returned
directly to the application if a potential deadlock is detected.

In addition SQLITE_IOERR_BLOCKED may be returned which is a more serious
condition under which a connection should immediately attempt to
finalise all it's open handles.

Deadlock is more likely to be detected when two connections have
existing open locks (e.g. a shared lock when doing non-dirty reads (read
uncommitted mode is switched off)) and attempt to either escalate the
existing lock or gain a new lock to write.

If you know your connection is going to write imminently then it may be
better to attempt to obtain a reserved or exclusive lock during begin.
This may reduce deadlock potential with a trade-off of reduced
concurrency.

(I guess these last two paragraphs are the most subjective and open to
different opinions)  



are there any corrections/ improvements to this ?? and does it bring
anything extra to what's already in the wiki on the subject - hence is
it worth adding ?

many thanks for everyone's help in clarifying this.

Owen.




-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Crenshaw
Sent: Thursday, October 29, 2009 5:49 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

Let's say we have the three connections in that diagram, and two tables
named t1 and t2.

I'll use a simple syntax to describe some concurrency scenarios:
con#>>t# will mean con# writes to t#
Commas will separate concurrent attempted operations
After the operations will be a pipe '|' followed by the error code that
would result, if any

Here goes:
1. con1 >> t1, con2 >> t2 | SQLITE_BUSY
2. con2 >> t1, con2 >> t2 | SQLITE_OK
3. con1 >> t1, con2 >> t1 | SQLITE_BUSY
4. con2 >> t1, con2 >> t1 | SQLITE_LOCKED

Does that clarify this?

John

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent
Sent: Wednesday, October 28, 2009 6:33 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

i guess this isn't that complicated.  the error codes even say basically
what you've said:

#define SQLITE_BUSY 5   /* The database file is locked */
#define SQLITE_LOCKED   6   /* A table in the database is locked */

i guess the point is that separate connections normally lock the entire
DB file but in shared cache mode two connections (in the same process)
can both have access to the DB file but not to the same table.  you've
said this below as well.

the point is that in the diagram here
(http://www.sqlite.org/sharedcache.html) if conn1 writes to tab1 then
conn2 and conn3 will get SQLITE_BUSY, yes?  if conn2 writes to tab1 then
conn1 will get SQLITE_BUSY but conn3 will get SQLITE_LOCKED (if trying
to write to tab1; will succeed if trying to write to tab2).

correct?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Crenshaw
Sent: Wednesday, October 28, 2009 12:49 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

Almost. Locking happens at a table level in this case, not a database
level. Three different threads can all write at the same time, if they
write to different tables. But, if two threads write try to the same
table at the same time, one of them will return SQLITE_LOCKED.

John

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent
Sent: Wednesday, October 28, 2009 3:46 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

oh, right.  my bad.  i don't mean to share a connection between two
threads, but rather that each thread (with its own connection) in the
same process where shared cache

Re: [sqlite] shared cache mode and 'LOCKED'

[John Crenshaw]

Let's say we have the three connections in that diagram, and two tables
named t1 and t2.

I'll use a simple syntax to describe some concurrency scenarios:
con#>>t# will mean con# writes to t#
Commas will separate concurrent attempted operations
After the operations will be a pipe '|' followed by the error code that
would result, if any

Here goes:
1. con1 >> t1, con2 >> t2 | SQLITE_BUSY
2. con2 >> t1, con2 >> t2 | SQLITE_OK
3. con1 >> t1, con2 >> t1 | SQLITE_BUSY
4. con2 >> t1, con2 >> t1 | SQLITE_LOCKED

Does that clarify this?

John

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent
Sent: Wednesday, October 28, 2009 6:33 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

i guess this isn't that complicated.  the error codes even say basically
what you've said:

#define SQLITE_BUSY 5   /* The database file is locked */
#define SQLITE_LOCKED   6   /* A table in the database is locked */

i guess the point is that separate connections normally lock the entire
DB file but in shared cache mode two connections (in the same process)
can both have access to the DB file but not to the same table.  you've
said this below as well.

the point is that in the diagram here
(http://www.sqlite.org/sharedcache.html) if conn1 writes to tab1 then
conn2 and conn3 will get SQLITE_BUSY, yes?  if conn2 writes to tab1 then
conn1 will get SQLITE_BUSY but conn3 will get SQLITE_LOCKED (if trying
to write to tab1; will succeed if trying to write to tab2).

correct?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Crenshaw
Sent: Wednesday, October 28, 2009 12:49 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

Almost. Locking happens at a table level in this case, not a database
level. Three different threads can all write at the same time, if they
write to different tables. But, if two threads write try to the same
table at the same time, one of them will return SQLITE_LOCKED.

John

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent
Sent: Wednesday, October 28, 2009 3:46 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

oh, right.  my bad.  i don't mean to share a connection between two
threads, but rather that each thread (with its own connection) in the
same process where shared cache mode is enabled will cause SQLITE_LOCKED
error rather than SQLITE_BUSY error when these threads contend for the
DB.

is this right?


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Crenshaw
Sent: Wednesday, October 28, 2009 12:38 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

I don't know. Elsewhere it says you really shouldn't use the same
connection in multiple threads. I use a different connection in each
thread. With the shared cache, this results in very little overhead, so
I'm unsure why you would need to do this the "not recommended" way.

The contention between connections only applies to other processes if
the shared cache is enabled. With the shared cache each process will
lock the whole database, but connections in threads within that process
will only lock individual tables. This is really the right way to do a
multithreaded application, because otherwise contention is too great.

John

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent
Sent: Wednesday, October 28, 2009 3:32 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

to be clear...

"in other words, two threads sharing a connection in shared cache mode
will always cause SQLITE_LOCKED (rather than SQLITE_BUSY) when
contention occurs _between the two threads_.  if contention occurs from
another connection (i.e. a connection in a different process)
SQLITE_BUSY will be returned."

i believe this is correct.  experts?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent
Sent: Wednesday, October 28, 2009 12:27 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

i'm no expert on this, but my understanding is that since shared cache
mode 'shares a connection' you won't get SQLITE_BUSY but rather
SQLITE_LOCKED since the contention is 'internal' to the connection.

in other words, two threads sharing a connection in shared cache mode
will always cause SQLITE_LOCKED (rather than SQLITE_BUSY) when
contention occurs.

experts: pls correct me if 

Re: [sqlite] shared cache mode and 'LOCKED'

[Tom Broadbent]

i guess this isn't that complicated.  the error codes even say basically what 
you've said:

#define SQLITE_BUSY 5   /* The database file is locked */
#define SQLITE_LOCKED   6   /* A table in the database is locked */

i guess the point is that separate connections normally lock the entire DB file 
but in shared cache mode two connections (in the same process) can both have 
access to the DB file but not to the same table.  you've said this below as 
well.

the point is that in the diagram here (http://www.sqlite.org/sharedcache.html) 
if conn1 writes to tab1 then conn2 and conn3 will get SQLITE_BUSY, yes?  if 
conn2 writes to tab1 then conn1 will get SQLITE_BUSY but conn3 will get 
SQLITE_LOCKED (if trying to write to tab1; will succeed if trying to write to 
tab2).

correct?

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of John Crenshaw
Sent: Wednesday, October 28, 2009 12:49 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

Almost. Locking happens at a table level in this case, not a database
level. Three different threads can all write at the same time, if they
write to different tables. But, if two threads write try to the same
table at the same time, one of them will return SQLITE_LOCKED.

John

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent
Sent: Wednesday, October 28, 2009 3:46 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

oh, right.  my bad.  i don't mean to share a connection between two
threads, but rather that each thread (with its own connection) in the
same process where shared cache mode is enabled will cause SQLITE_LOCKED
error rather than SQLITE_BUSY error when these threads contend for the
DB.

is this right?


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Crenshaw
Sent: Wednesday, October 28, 2009 12:38 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

I don't know. Elsewhere it says you really shouldn't use the same
connection in multiple threads. I use a different connection in each
thread. With the shared cache, this results in very little overhead, so
I'm unsure why you would need to do this the "not recommended" way.

The contention between connections only applies to other processes if
the shared cache is enabled. With the shared cache each process will
lock the whole database, but connections in threads within that process
will only lock individual tables. This is really the right way to do a
multithreaded application, because otherwise contention is too great.

John

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent
Sent: Wednesday, October 28, 2009 3:32 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

to be clear...

"in other words, two threads sharing a connection in shared cache mode
will always cause SQLITE_LOCKED (rather than SQLITE_BUSY) when
contention occurs _between the two threads_.  if contention occurs from
another connection (i.e. a connection in a different process)
SQLITE_BUSY will be returned."

i believe this is correct.  experts?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent
Sent: Wednesday, October 28, 2009 12:27 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

i'm no expert on this, but my understanding is that since shared cache
mode 'shares a connection' you won't get SQLITE_BUSY but rather
SQLITE_LOCKED since the contention is 'internal' to the connection.

in other words, two threads sharing a connection in shared cache mode
will always cause SQLITE_LOCKED (rather than SQLITE_BUSY) when
contention occurs.

experts: pls correct me if i'm wrong here.

here is the advice i received when asking about a similar situation:

>> If other threads may also need a write lock on that table, you should
>> handle SQLITE_LOCKED by incrementing a waiter count and calling
>> sqlite3_unlock_notify. The thread doing the inserting can check to
>> see if anybody is waiting (blocked) and yield by committing the
>> current transaction and waiting for the blocked thread to unblock. Be
>> aware, you should also close any open cursors before yielding,
>> because open cursors will prevent write locks and you'll waste time
>> yielding for nothing.
>>
>> John

hope this helps (and isn't incorrect).

thanks
tom

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of O'Neill, Owen
Sent

Re: [sqlite] shared cache mode and 'LOCKED'

[John Crenshaw]

Almost. Locking happens at a table level in this case, not a database
level. Three different threads can all write at the same time, if they
write to different tables. But, if two threads write try to the same
table at the same time, one of them will return SQLITE_LOCKED.

John

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent
Sent: Wednesday, October 28, 2009 3:46 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

oh, right.  my bad.  i don't mean to share a connection between two
threads, but rather that each thread (with its own connection) in the
same process where shared cache mode is enabled will cause SQLITE_LOCKED
error rather than SQLITE_BUSY error when these threads contend for the
DB.

is this right?


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Crenshaw
Sent: Wednesday, October 28, 2009 12:38 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

I don't know. Elsewhere it says you really shouldn't use the same
connection in multiple threads. I use a different connection in each
thread. With the shared cache, this results in very little overhead, so
I'm unsure why you would need to do this the "not recommended" way.

The contention between connections only applies to other processes if
the shared cache is enabled. With the shared cache each process will
lock the whole database, but connections in threads within that process
will only lock individual tables. This is really the right way to do a
multithreaded application, because otherwise contention is too great.

John

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent
Sent: Wednesday, October 28, 2009 3:32 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

to be clear...

"in other words, two threads sharing a connection in shared cache mode
will always cause SQLITE_LOCKED (rather than SQLITE_BUSY) when
contention occurs _between the two threads_.  if contention occurs from
another connection (i.e. a connection in a different process)
SQLITE_BUSY will be returned."

i believe this is correct.  experts?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent
Sent: Wednesday, October 28, 2009 12:27 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

i'm no expert on this, but my understanding is that since shared cache
mode 'shares a connection' you won't get SQLITE_BUSY but rather
SQLITE_LOCKED since the contention is 'internal' to the connection.

in other words, two threads sharing a connection in shared cache mode
will always cause SQLITE_LOCKED (rather than SQLITE_BUSY) when
contention occurs.

experts: pls correct me if i'm wrong here.

here is the advice i received when asking about a similar situation:

>> If other threads may also need a write lock on that table, you should
>> handle SQLITE_LOCKED by incrementing a waiter count and calling
>> sqlite3_unlock_notify. The thread doing the inserting can check to
>> see if anybody is waiting (blocked) and yield by committing the
>> current transaction and waiting for the blocked thread to unblock. Be
>> aware, you should also close any open cursors before yielding,
>> because open cursors will prevent write locks and you'll waste time
>> yielding for nothing.
>>
>> John

hope this helps (and isn't incorrect).

thanks
tom

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of O'Neill, Owen
Sent: Wednesday, October 28, 2009 10:45 AM
To: General Discussion of SQLite Database
Subject: [sqlite] shared cache mode and 'LOCKED'



Hi Everyone,
Does anyone know if this page is still up to date with respect to when
you get "SQLITE_LOCKED" when operating in shared cache mode ?
http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked


(I'm trying to solve a two writers problem and am trying to understand
the best way to solve it)
(and I think part of my problem is not understanding the difference
between  'locked' and 'busy' )

I've seen the notify example here.
http://www.sqlite.org/unlock_notify.html

and a 'busy' example here
http://www.sqlite.org/cvstrac/wiki?p=MultiThreading
http://www.sqlite.org/cvstrac/wiki?p=SampleCode

Is it possible for something to be 'locked' then after being unlocked at
it tries again it gets 'busy' ?

Should my re-try strategy be the same or different for 'busy' and
'locked' and I guess if I get SQLITE_IOERR_BLOCKED
(http://www.sqlite.org/c3ref/busy_handler.html) I should always back off
and wait a while ?

Any tip

Re: [sqlite] shared cache mode and 'LOCKED'

[Tom Broadbent]

oh, right.  my bad.  i don't mean to share a connection between two threads, 
but rather that each thread (with its own connection) in the same process where 
shared cache mode is enabled will cause SQLITE_LOCKED error rather than 
SQLITE_BUSY error when these threads contend for the DB.

is this right?


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of John Crenshaw
Sent: Wednesday, October 28, 2009 12:38 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

I don't know. Elsewhere it says you really shouldn't use the same
connection in multiple threads. I use a different connection in each
thread. With the shared cache, this results in very little overhead, so
I'm unsure why you would need to do this the "not recommended" way.

The contention between connections only applies to other processes if
the shared cache is enabled. With the shared cache each process will
lock the whole database, but connections in threads within that process
will only lock individual tables. This is really the right way to do a
multithreaded application, because otherwise contention is too great.

John

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent
Sent: Wednesday, October 28, 2009 3:32 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

to be clear...

"in other words, two threads sharing a connection in shared cache mode
will always cause SQLITE_LOCKED (rather than SQLITE_BUSY) when
contention occurs _between the two threads_.  if contention occurs from
another connection (i.e. a connection in a different process)
SQLITE_BUSY will be returned."

i believe this is correct.  experts?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent
Sent: Wednesday, October 28, 2009 12:27 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

i'm no expert on this, but my understanding is that since shared cache
mode 'shares a connection' you won't get SQLITE_BUSY but rather
SQLITE_LOCKED since the contention is 'internal' to the connection.

in other words, two threads sharing a connection in shared cache mode
will always cause SQLITE_LOCKED (rather than SQLITE_BUSY) when
contention occurs.

experts: pls correct me if i'm wrong here.

here is the advice i received when asking about a similar situation:

>> If other threads may also need a write lock on that table, you should
>> handle SQLITE_LOCKED by incrementing a waiter count and calling
>> sqlite3_unlock_notify. The thread doing the inserting can check to
>> see if anybody is waiting (blocked) and yield by committing the
>> current transaction and waiting for the blocked thread to unblock. Be
>> aware, you should also close any open cursors before yielding,
>> because open cursors will prevent write locks and you'll waste time
>> yielding for nothing.
>>
>> John

hope this helps (and isn't incorrect).

thanks
tom

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of O'Neill, Owen
Sent: Wednesday, October 28, 2009 10:45 AM
To: General Discussion of SQLite Database
Subject: [sqlite] shared cache mode and 'LOCKED'



Hi Everyone,
Does anyone know if this page is still up to date with respect to when
you get "SQLITE_LOCKED" when operating in shared cache mode ?
http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked


(I'm trying to solve a two writers problem and am trying to understand
the best way to solve it)
(and I think part of my problem is not understanding the difference
between  'locked' and 'busy' )

I've seen the notify example here.
http://www.sqlite.org/unlock_notify.html

and a 'busy' example here
http://www.sqlite.org/cvstrac/wiki?p=MultiThreading
http://www.sqlite.org/cvstrac/wiki?p=SampleCode

Is it possible for something to be 'locked' then after being unlocked at
it tries again it gets 'busy' ?

Should my re-try strategy be the same or different for 'busy' and
'locked' and I guess if I get SQLITE_IOERR_BLOCKED
(http://www.sqlite.org/c3ref/busy_handler.html) I should always back off
and wait a while ?

Any tips for the 'best' way to tackle this gratefully received.

(I have one thread writing a lot but it can block for a 'long' time and
still be ok (up to 5 seconds) - and another one mostly reading and doing
a few occasional writes, but it can't block for long (>250ms) because
it's servicing the UI and repainting will stop.)

many thanks
Owen


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

___

Re: [sqlite] shared cache mode and 'LOCKED'

[John Crenshaw]

I don't know. Elsewhere it says you really shouldn't use the same
connection in multiple threads. I use a different connection in each
thread. With the shared cache, this results in very little overhead, so
I'm unsure why you would need to do this the "not recommended" way.

The contention between connections only applies to other processes if
the shared cache is enabled. With the shared cache each process will
lock the whole database, but connections in threads within that process
will only lock individual tables. This is really the right way to do a
multithreaded application, because otherwise contention is too great.

John

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent
Sent: Wednesday, October 28, 2009 3:32 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

to be clear...

"in other words, two threads sharing a connection in shared cache mode
will always cause SQLITE_LOCKED (rather than SQLITE_BUSY) when
contention occurs _between the two threads_.  if contention occurs from
another connection (i.e. a connection in a different process)
SQLITE_BUSY will be returned."

i believe this is correct.  experts?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent
Sent: Wednesday, October 28, 2009 12:27 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

i'm no expert on this, but my understanding is that since shared cache
mode 'shares a connection' you won't get SQLITE_BUSY but rather
SQLITE_LOCKED since the contention is 'internal' to the connection.

in other words, two threads sharing a connection in shared cache mode
will always cause SQLITE_LOCKED (rather than SQLITE_BUSY) when
contention occurs.

experts: pls correct me if i'm wrong here.

here is the advice i received when asking about a similar situation:

>> If other threads may also need a write lock on that table, you should
>> handle SQLITE_LOCKED by incrementing a waiter count and calling
>> sqlite3_unlock_notify. The thread doing the inserting can check to
>> see if anybody is waiting (blocked) and yield by committing the
>> current transaction and waiting for the blocked thread to unblock. Be
>> aware, you should also close any open cursors before yielding,
>> because open cursors will prevent write locks and you'll waste time
>> yielding for nothing.
>>
>> John

hope this helps (and isn't incorrect).

thanks
tom

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of O'Neill, Owen
Sent: Wednesday, October 28, 2009 10:45 AM
To: General Discussion of SQLite Database
Subject: [sqlite] shared cache mode and 'LOCKED'



Hi Everyone,
Does anyone know if this page is still up to date with respect to when
you get "SQLITE_LOCKED" when operating in shared cache mode ?
http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked


(I'm trying to solve a two writers problem and am trying to understand
the best way to solve it)
(and I think part of my problem is not understanding the difference
between  'locked' and 'busy' )

I've seen the notify example here.
http://www.sqlite.org/unlock_notify.html

and a 'busy' example here
http://www.sqlite.org/cvstrac/wiki?p=MultiThreading
http://www.sqlite.org/cvstrac/wiki?p=SampleCode

Is it possible for something to be 'locked' then after being unlocked at
it tries again it gets 'busy' ?

Should my re-try strategy be the same or different for 'busy' and
'locked' and I guess if I get SQLITE_IOERR_BLOCKED
(http://www.sqlite.org/c3ref/busy_handler.html) I should always back off
and wait a while ?

Any tips for the 'best' way to tackle this gratefully received.

(I have one thread writing a lot but it can block for a 'long' time and
still be ok (up to 5 seconds) - and another one mostly reading and doing
a few occasional writes, but it can't block for long (>250ms) because
it's servicing the UI and repainting will stop.)

many thanks
Owen


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

__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__

__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__
___
sqlite-users mail

Re: [sqlite] shared cache mode and 'LOCKED'

[Tom Broadbent]

and here is the link to the thread where i received the below advice:

http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2009-October/016404.html


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Tom Broadbent
Sent: Wednesday, October 28, 2009 12:32 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

to be clear...

"in other words, two threads sharing a connection in shared cache mode will 
always cause SQLITE_LOCKED (rather than SQLITE_BUSY) when contention occurs 
_between the two threads_.  if contention occurs from another connection (i.e. 
a connection in a different process) SQLITE_BUSY will be returned."

i believe this is correct.  experts?

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Tom Broadbent
Sent: Wednesday, October 28, 2009 12:27 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache mode and 'LOCKED'

i'm no expert on this, but my understanding is that since shared cache mode 
'shares a connection' you won't get SQLITE_BUSY but rather SQLITE_LOCKED since 
the contention is 'internal' to the connection.

in other words, two threads sharing a connection in shared cache mode will 
always cause SQLITE_LOCKED (rather than SQLITE_BUSY) when contention occurs.

experts: pls correct me if i'm wrong here.

here is the advice i received when asking about a similar situation:

>> If other threads may also need a write lock on that table, you should
>> handle SQLITE_LOCKED by incrementing a waiter count and calling
>> sqlite3_unlock_notify. The thread doing the inserting can check to
>> see if anybody is waiting (blocked) and yield by committing the
>> current transaction and waiting for the blocked thread to unblock. Be
>> aware, you should also close any open cursors before yielding,
>> because open cursors will prevent write locks and you'll waste time
>> yielding for nothing.
>>
>> John

hope this helps (and isn't incorrect).

thanks
tom

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of O'Neill, Owen
Sent: Wednesday, October 28, 2009 10:45 AM
To: General Discussion of SQLite Database
Subject: [sqlite] shared cache mode and 'LOCKED'



Hi Everyone,
Does anyone know if this page is still up to date with respect to when
you get "SQLITE_LOCKED" when operating in shared cache mode ?
http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked


(I'm trying to solve a two writers problem and am trying to understand
the best way to solve it)
(and I think part of my problem is not understanding the difference
between  'locked' and 'busy' )

I've seen the notify example here.
http://www.sqlite.org/unlock_notify.html

and a 'busy' example here
http://www.sqlite.org/cvstrac/wiki?p=MultiThreading
http://www.sqlite.org/cvstrac/wiki?p=SampleCode

Is it possible for something to be 'locked' then after being unlocked at
it tries again it gets 'busy' ?

Should my re-try strategy be the same or different for 'busy' and
'locked' and I guess if I get SQLITE_IOERR_BLOCKED
(http://www.sqlite.org/c3ref/busy_handler.html) I should always back off
and wait a while ?

Any tips for the 'best' way to tackle this gratefully received.

(I have one thread writing a lot but it can block for a 'long' time and
still be ok (up to 5 seconds) - and another one mostly reading and doing
a few occasional writes, but it can't block for long (>250ms) because
it's servicing the UI and repainting will stop.)

many thanks
Owen


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

__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__

__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__

__
This email has been scann

Re: [sqlite] shared cache mode and 'LOCKED'

[John Crenshaw]

It appears to be up to date.

John

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of O'Neill, Owen
Sent: Wednesday, October 28, 2009 1:45 PM
To: General Discussion of SQLite Database
Subject: [sqlite] shared cache mode and 'LOCKED'



Hi Everyone,
Does anyone know if this page is still up to date with respect to when
you get "SQLITE_LOCKED" when operating in shared cache mode ?
http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked


(I'm trying to solve a two writers problem and am trying to understand
the best way to solve it)
(and I think part of my problem is not understanding the difference
between  'locked' and 'busy' )

I've seen the notify example here.
http://www.sqlite.org/unlock_notify.html

and a 'busy' example here
http://www.sqlite.org/cvstrac/wiki?p=MultiThreading
http://www.sqlite.org/cvstrac/wiki?p=SampleCode

Is it possible for something to be 'locked' then after being unlocked at
it tries again it gets 'busy' ?

Should my re-try strategy be the same or different for 'busy' and
'locked' and I guess if I get SQLITE_IOERR_BLOCKED
(http://www.sqlite.org/c3ref/busy_handler.html) I should always back off
and wait a while ?

Any tips for the 'best' way to tackle this gratefully received.

(I have one thread writing a lot but it can block for a 'long' time and
still be ok (up to 5 seconds) - and another one mostly reading and doing
a few occasional writes, but it can't block for long (>250ms) because
it's servicing the UI and repainting will stop.)

many thanks
Owen


___
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] shared cache mode and 'LOCKED'

[O'Neill, Owen]

Hi Everyone,
Does anyone know if this page is still up to date with respect to when
you get "SQLITE_LOCKED" when operating in shared cache mode ?
http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked


(I'm trying to solve a two writers problem and am trying to understand
the best way to solve it)
(and I think part of my problem is not understanding the difference
between  'locked' and 'busy' )

I've seen the notify example here.
http://www.sqlite.org/unlock_notify.html

and a 'busy' example here
http://www.sqlite.org/cvstrac/wiki?p=MultiThreading
http://www.sqlite.org/cvstrac/wiki?p=SampleCode

Is it possible for something to be 'locked' then after being unlocked at
it tries again it gets 'busy' ?

Should my re-try strategy be the same or different for 'busy' and
'locked' and I guess if I get SQLITE_IOERR_BLOCKED
(http://www.sqlite.org/c3ref/busy_handler.html) I should always back off
and wait a while ?

Any tips for the 'best' way to tackle this gratefully received.

(I have one thread writing a lot but it can block for a 'long' time and
still be ok (up to 5 seconds) - and another one mostly reading and doing
a few occasional writes, but it can't block for long (>250ms) because
it's servicing the UI and repainting will stop.)

many thanks
Owen


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

Re: [sqlite] Shared Cache unlock notification to multiple threads

[D. Richard Hipp]

On Mar 23, 2009, at 5:45 AM, Edzard Pasma wrote:

> Hello,
>
> The new sqlite3_unlock_notify API, described in 
> http://www.sqlite.org/draft/c3ref/unlock_notify.html 
>  and expected in SQLite 3.6.12, may have a restriction for use when  
> multiple threads share the same connection. The documents states:
>
> """ There may be at most one unlock-notify callback registered by a  
> blocked connection. If sqlite3_unlock_notify() is called when the  
> blocked connection already has a registered unlock-notify callback,  
> then the new callback replaces the old. """
>
> I expect the following goes wrong then:
>
> Connection A: UPDATE t1 SET ..
>
> Connection B, thread 1: SELECT * FROM t1  --> database table locked
>
> Connection B, thread 2: SELECT * FROM t1  --> database table locked
>
> If both threads register to be notified, only one will get called  
> when the lock is cleared. The other is forgotten?


The sqlite3_unlock_notify() interface assumes that each thread has its  
own connection.  Violate this assumption at your peril.

D. Richard Hipp
d...@hwaci.com



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

[sqlite] Shared Cache unlock notification to multiple threads

[Edzard Pasma]

Hello,

The new sqlite3_unlock_notify API, described in 
http://www.sqlite.org/draft/c3ref/unlock_notify.html and expected in SQLite 
3.6.12, may have a restriction for use when multiple threads share the same 
connection. The documents states:

""" There may be at most one unlock-notify callback registered by a blocked 
connection. If sqlite3_unlock_notify() is called when the blocked connection 
already has a registered unlock-notify callback, then the new callback replaces 
the old. """

I expect the following goes wrong then:

Connection A: UPDATE t1 SET ..

Connection B, thread 1: SELECT * FROM t1  --> database table locked

Connection B, thread 2: SELECT * FROM t1  --> database table locked

If both threads register to be notified, only one will get called when the lock 
is cleared. The other is forgotten?

I would naively propose to keep any unlock-notify callback registered, also if 
for the same connection. But would not wish to complicate things. It is a 
somewhat crazy case and may as well be dealt with in the application that 
wishes to support it.

Thanks for this most interesting development,

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

[sqlite] Shared-cache mode doc page needs a version

[Dennis Volodomanov]

On this page: http://sqlite.org/sharedcache.html in item 3.0 there's a
missing version number at the end of the last sentence.

Best regards,

   Dennis


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

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Marcus Grimm]

strange... I can hardly believe that...

did your test app probably reading only ?

Marcus

> Without wishing to complicate the subject, I have found that retrying a
> sqlite3_step() after receiving SQLITE_LOCKED works fine for me without
> needing sqlite3_reset(). I do this in a multi-threaded test app (based
> on some old SQLite test code) that I have left running with 10 threads
> for a long time and did not encounter any errors. I was using
> shared-cache.
>
> Are there perhaps some cases where a reset is required on SQLITE_LOCKED,
> and other cases where it can be interpreted the same as SQLITE_BUSY?
>
> Cheers,
> Dave.
>
>
> -Original Message-
> From: Hynes, Tom [mailto:tom.hy...@inin.com]
> Sent: 06 March 2009 05:10
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
>
> Thanks Dan!
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan
> Sent: Thursday, March 05, 2009 10:08 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
>
>
> On Mar 6, 2009, at 2:53 AM, Hynes, Tom wrote:
>
>> Dan,
>>
>> I am a little confused now about how to treat a SQLITE_LOCKED
>> error.  Is this to be treated the same as SQLITE_BUSY (i.e. can
>> retry, unless an explicit transaction is in effect and the statement
>> is not a COMMIT, in which case should roll back), or should it be
>> treated differently?  If the latter, should it generally be handled
>> like most other db errors (e.g. SQLITE_CONSTRAINT, SQLITE_CANTOPEN,
>> etc.), or is there some specific error handling for SQLITE_LOCKED
>> that is recommended?
>
> Usually when sqlite3_step() returns something other than SQLITE_ROW, the
> statement needs to be reset (sqlite3_reset()) before execution
> can be re-attempted. However, SQLITE_BUSY errors are an exception.
> After SQLITE_BUSY is returned, you can call sqlite3_step() again
> immediately.
> This I either didn't know, or forgot.
>
> An SQLITE_LOCKED error means there is something preventing execution
> within the same process. For example a lock on a shared-cache table.
> For some applications, for example if another thread may release the
> lock shortly, it may be appropriate to retry the query. In other apps,
> for example single-threaded apps, it may not.
>
> To retry a query that has failed with an SQLITE_LOCKED error, you need
> to reset the statement before trying sqlite3_step() again.
>
> Dan.
>
>
>
>
>> Thanks,
>>
>> Tom
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org
>> ] On Behalf Of Dan
>> Sent: Thursday, March 05, 2009 12:38 PM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
>>
>>
>> On Mar 6, 2009, at 12:22 AM, Marcus Grimm wrote:
>>
>>> Dan,
>>>
>>> I'm not sure what you mean by technically the SQLITE_BUSY is
>>> also wrong. In the test program I get the SQLITE_BUSY quite
>>> often and by retrying the sqlite3_step() it will sooner or
>>> later succeed.
>>
>> Huh. My mistake.
>>
>>
>>
>>> Thanks for confirming that it may happend only for the
>>> first call to sqlite3_step(). This is an important point and
>>> it makes it in fact easy to workaround.
>>> I've changed my test application accordingly and now it can
>>> run endless, I even increased now to 10 reader threads and 2 writer
>>> threads in
>>> parallel without making sqlite particular nervous. Wonderful... :-)
>>>
>>> Thanks again
>>>
>>> Marcus
>>>
>>> Dan wrote:
>>>> On Mar 5, 2009, at 11:14 PM, Marcus Grimm wrote:
>>>>
>>>>> Hi Dan,
>>>>>
>>>>> thank you!
>>>>>
>>>>> Yes! I was not aware that it is wrong to redo a step
>>>>> when it previously returned the LOCKED state.
>>>>> That was the reason for the (correct) MISSUSE return on
>>>>> the 2nd attempt.
>>>>>
>>>>> I think this is the main difference in my case between
>>>>> shared cache on/off: Without shared cache I never
>>>>> get this lock state and it just does the BUSY handling correctly.
>>>>
>>>> Technically the SQLITE_BUSY handling is also wrong, for the
>>>> same reasons. But it's harder t

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Dave Toll]

Without wishing to complicate the subject, I have found that retrying a
sqlite3_step() after receiving SQLITE_LOCKED works fine for me without
needing sqlite3_reset(). I do this in a multi-threaded test app (based
on some old SQLite test code) that I have left running with 10 threads
for a long time and did not encounter any errors. I was using
shared-cache.

Are there perhaps some cases where a reset is required on SQLITE_LOCKED,
and other cases where it can be interpreted the same as SQLITE_BUSY?

Cheers,
Dave.


-Original Message-
From: Hynes, Tom [mailto:tom.hy...@inin.com] 
Sent: 06 March 2009 05:10
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

Thanks Dan!

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan
Sent: Thursday, March 05, 2009 10:08 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()


On Mar 6, 2009, at 2:53 AM, Hynes, Tom wrote:

> Dan,
>
> I am a little confused now about how to treat a SQLITE_LOCKED
> error.  Is this to be treated the same as SQLITE_BUSY (i.e. can
> retry, unless an explicit transaction is in effect and the statement
> is not a COMMIT, in which case should roll back), or should it be
> treated differently?  If the latter, should it generally be handled
> like most other db errors (e.g. SQLITE_CONSTRAINT, SQLITE_CANTOPEN,
> etc.), or is there some specific error handling for SQLITE_LOCKED
> that is recommended?

Usually when sqlite3_step() returns something other than SQLITE_ROW, the
statement needs to be reset (sqlite3_reset()) before execution
can be re-attempted. However, SQLITE_BUSY errors are an exception.
After SQLITE_BUSY is returned, you can call sqlite3_step() again
immediately.
This I either didn't know, or forgot.

An SQLITE_LOCKED error means there is something preventing execution
within the same process. For example a lock on a shared-cache table.
For some applications, for example if another thread may release the
lock shortly, it may be appropriate to retry the query. In other apps,
for example single-threaded apps, it may not.

To retry a query that has failed with an SQLITE_LOCKED error, you need
to reset the statement before trying sqlite3_step() again.

Dan.




> Thanks,
>
> Tom
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org
> ] On Behalf Of Dan
> Sent: Thursday, March 05, 2009 12:38 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
>
>
> On Mar 6, 2009, at 12:22 AM, Marcus Grimm wrote:
>
>> Dan,
>>
>> I'm not sure what you mean by technically the SQLITE_BUSY is
>> also wrong. In the test program I get the SQLITE_BUSY quite
>> often and by retrying the sqlite3_step() it will sooner or
>> later succeed.
>
> Huh. My mistake.
>
>
>
>> Thanks for confirming that it may happend only for the
>> first call to sqlite3_step(). This is an important point and
>> it makes it in fact easy to workaround.
>> I've changed my test application accordingly and now it can
>> run endless, I even increased now to 10 reader threads and 2 writer
>> threads in
>> parallel without making sqlite particular nervous. Wonderful... :-)
>>
>> Thanks again
>>
>> Marcus
>>
>> Dan wrote:
>>> On Mar 5, 2009, at 11:14 PM, Marcus Grimm wrote:
>>>
>>>> Hi Dan,
>>>>
>>>> thank you!
>>>>
>>>> Yes! I was not aware that it is wrong to redo a step
>>>> when it previously returned the LOCKED state.
>>>> That was the reason for the (correct) MISSUSE return on
>>>> the 2nd attempt.
>>>>
>>>> I think this is the main difference in my case between
>>>> shared cache on/off: Without shared cache I never
>>>> get this lock state and it just does the BUSY handling correctly.
>>>
>>> Technically the SQLITE_BUSY handling is also wrong, for the
>>> same reasons. But it's harder to hit a real SQLITE_BUSY than it
>>> is to hit an SQLITE_LOCKED in shared-cache mode.
>>>
>>>> I'll change my locking handling accordingly, seems to be a problem
>>>> of the very first step after a prepare, so that's should to be easy
>>>> to reset...
>>>
>>> That's correct. An SQLITE_LOCKED or SQLITE_BUSY may only occur
>>> on the first call to sqlite3_step().
>>>
>>> Dan.
>>>
>>>
>>>
>>>> Thanks again for your feedback
>>>>
>>>> Marcus
>>

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Hynes, Tom]

Thanks Dan!

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dan
Sent: Thursday, March 05, 2009 10:08 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()


On Mar 6, 2009, at 2:53 AM, Hynes, Tom wrote:

> Dan,
>
> I am a little confused now about how to treat a SQLITE_LOCKED
> error.  Is this to be treated the same as SQLITE_BUSY (i.e. can
> retry, unless an explicit transaction is in effect and the statement
> is not a COMMIT, in which case should roll back), or should it be
> treated differently?  If the latter, should it generally be handled
> like most other db errors (e.g. SQLITE_CONSTRAINT, SQLITE_CANTOPEN,
> etc.), or is there some specific error handling for SQLITE_LOCKED
> that is recommended?

Usually when sqlite3_step() returns something other than SQLITE_ROW, the
statement needs to be reset (sqlite3_reset()) before execution
can be re-attempted. However, SQLITE_BUSY errors are an exception.
After SQLITE_BUSY is returned, you can call sqlite3_step() again
immediately.
This I either didn't know, or forgot.

An SQLITE_LOCKED error means there is something preventing execution
within the same process. For example a lock on a shared-cache table.
For some applications, for example if another thread may release the
lock shortly, it may be appropriate to retry the query. In other apps,
for example single-threaded apps, it may not.

To retry a query that has failed with an SQLITE_LOCKED error, you need
to reset the statement before trying sqlite3_step() again.

Dan.




> Thanks,
>
> Tom
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org
> ] On Behalf Of Dan
> Sent: Thursday, March 05, 2009 12:38 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
>
>
> On Mar 6, 2009, at 12:22 AM, Marcus Grimm wrote:
>
>> Dan,
>>
>> I'm not sure what you mean by technically the SQLITE_BUSY is
>> also wrong. In the test program I get the SQLITE_BUSY quite
>> often and by retrying the sqlite3_step() it will sooner or
>> later succeed.
>
> Huh. My mistake.
>
>
>
>> Thanks for confirming that it may happend only for the
>> first call to sqlite3_step(). This is an important point and
>> it makes it in fact easy to workaround.
>> I've changed my test application accordingly and now it can
>> run endless, I even increased now to 10 reader threads and 2 writer
>> threads in
>> parallel without making sqlite particular nervous. Wonderful... :-)
>>
>> Thanks again
>>
>> Marcus
>>
>> Dan wrote:
>>> On Mar 5, 2009, at 11:14 PM, Marcus Grimm wrote:
>>>
>>>> Hi Dan,
>>>>
>>>> thank you!
>>>>
>>>> Yes! I was not aware that it is wrong to redo a step
>>>> when it previously returned the LOCKED state.
>>>> That was the reason for the (correct) MISSUSE return on
>>>> the 2nd attempt.
>>>>
>>>> I think this is the main difference in my case between
>>>> shared cache on/off: Without shared cache I never
>>>> get this lock state and it just does the BUSY handling correctly.
>>>
>>> Technically the SQLITE_BUSY handling is also wrong, for the
>>> same reasons. But it's harder to hit a real SQLITE_BUSY than it
>>> is to hit an SQLITE_LOCKED in shared-cache mode.
>>>
>>>> I'll change my locking handling accordingly, seems to be a problem
>>>> of the very first step after a prepare, so that's should to be easy
>>>> to reset...
>>>
>>> That's correct. An SQLITE_LOCKED or SQLITE_BUSY may only occur
>>> on the first call to sqlite3_step().
>>>
>>> Dan.
>>>
>>>
>>>
>>>> Thanks again for your feedback
>>>>
>>>> Marcus
>>>>
>>>> Dan wrote:
>>>>> On Mar 5, 2009, at 9:57 PM, Marcus Grimm wrote:
>>>>>
>>>>>> Hi again,
>>>>>>
>>>>>> I did a little test program that simulates the multi threading
>>>>>> issue to understand better why I get a MISSUSE error even
>>>>>> when sqlite_prepare returns no error.
>>>>>> The test program will printout some errors on the console
>>>>>> and exits in that case. If I don't use the shared cache it
>>>>>> runs endless without any problem.
>>>>>>
>>>>>> The code is to long t

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Kees Nuyt]

On Fri, 06 Mar 2009 10:26:38 +0100, Marcus Grimm
 wrote:

>The website is allready excellent, I'm not at all complaining...
>
>Anyway, I've placed an updated version of the thread test program on the
>web:
>
>http://www.exomio.de/sqlitethreadtest.c
>
>If somebody found it useful or good enough: Feel free to use it, change it,
>or put on wiki pages.

I added it to the Wiki :
http://www.sqlite.org/cvstrac/wiki?p=SampleCode 
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Marcus Grimm]

Dan wrote:
> On Mar 6, 2009, at 1:48 PM, Marcus Grimm wrote:
> 
>> I just want to add another hint that I learned yesterday:
>>
>> when sqlite3_step() returns SQLITE_LOCKED be aware
>> that sqlite3_reset() will most likely also return
>> SQLITE_LOCKED and thus it is necessary to repeat calling
>> it until it returns SQLITE_OK.
> 
> This is incorrect. If an error is encountered by sqlite3_step() and
> an error code returned, the same error code is returned by the  
> subsequent
> call to sqlite3_reset() or sqlite3_finalize(). sqlite3_reset() returning
> SQLITE_LOCKED does not mean you have to call it again.

Uhh... you are right. Sorry for that.

However: I've checked what sqlite3_reset() returns in this case:
The first attempt returns SQLITE_LOCKED, the 2nd try returns SQLITE_OK.

But it works, as you say, to just call sqlite3_reset() one time and proceed
with sqlite3_step().

> 
>> Maybe it would be agood idea to have these kind of
>> info somewhere bundled in the sqlite docs?
>> I found it sometimes difficult to get such issues
>> extracted or collected from the sqlite pages, resulting
>> in a lot of try and error loops and generating traffic
>> on the mailing list... :-)
> 
> The website could be much improved by including examples. Feel free
> to create a wiki page or two if you have any ideas for them.

Yes, but I don't consider my selve as an expert -- which is true as one can see
from my wrong hint above... :-)

The website is allready excellent, I'm not at all complaining...

Anyway, I've placed an updated version of the thread test program on the
web:

http://www.exomio.de/sqlitethreadtest.c

If somebody found it useful or good enough: Feel free to use it, change it,
or put on wiki pages.

Thanks Dan, once more

Marcus

> 
> Dan.
> 
> ___
> 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] shared cache and SQLITE_MISUSE on sqlite3_step()

[Dan]

On Mar 6, 2009, at 1:48 PM, Marcus Grimm wrote:

> I just want to add another hint that I learned yesterday:
>
> when sqlite3_step() returns SQLITE_LOCKED be aware
> that sqlite3_reset() will most likely also return
> SQLITE_LOCKED and thus it is necessary to repeat calling
> it until it returns SQLITE_OK.

This is incorrect. If an error is encountered by sqlite3_step() and
an error code returned, the same error code is returned by the  
subsequent
call to sqlite3_reset() or sqlite3_finalize(). sqlite3_reset() returning
SQLITE_LOCKED does not mean you have to call it again.

> Maybe it would be agood idea to have these kind of
> info somewhere bundled in the sqlite docs?
> I found it sometimes difficult to get such issues
> extracted or collected from the sqlite pages, resulting
> in a lot of try and error loops and generating traffic
> on the mailing list... :-)

The website could be much improved by including examples. Feel free
to create a wiki page or two if you have any ideas for them.

Dan.

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

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Marcus Grimm]

I just want to add another hint that I learned yesterday:

when sqlite3_step() returns SQLITE_LOCKED be aware
that sqlite3_reset() will most likely also return
SQLITE_LOCKED and thus it is necessary to repeat calling
it until it returns SQLITE_OK.

Maybe it would be agood idea to have these kind of
info somewhere bundled in the sqlite docs?
I found it sometimes difficult to get such issues
extracted or collected from the sqlite pages, resulting
in a lot of try and error loops and generating traffic
on the mailing list... :-)

Marcus

>
> On Mar 6, 2009, at 2:53 AM, Hynes, Tom wrote:
>
>> Dan,
>>
>> I am a little confused now about how to treat a SQLITE_LOCKED
>> error.  Is this to be treated the same as SQLITE_BUSY (i.e. can
>> retry, unless an explicit transaction is in effect and the statement
>> is not a COMMIT, in which case should roll back), or should it be
>> treated differently?  If the latter, should it generally be handled
>> like most other db errors (e.g. SQLITE_CONSTRAINT, SQLITE_CANTOPEN,
>> etc.), or is there some specific error handling for SQLITE_LOCKED
>> that is recommended?
>
> Usually when sqlite3_step() returns something other than SQLITE_ROW, the
> statement needs to be reset (sqlite3_reset()) before execution
> can be re-attempted. However, SQLITE_BUSY errors are an exception.
> After SQLITE_BUSY is returned, you can call sqlite3_step() again
> immediately.
> This I either didn't know, or forgot.
>
> An SQLITE_LOCKED error means there is something preventing execution
> within the same process. For example a lock on a shared-cache table.
> For some applications, for example if another thread may release the
> lock shortly, it may be appropriate to retry the query. In other apps,
> for example single-threaded apps, it may not.
>
> To retry a query that has failed with an SQLITE_LOCKED error, you need
> to reset the statement before trying sqlite3_step() again.
>
> Dan.
>
>
>
>
>> Thanks,
>>
>> Tom
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org
>> [mailto:sqlite-users-boun...@sqlite.org
>> ] On Behalf Of Dan
>> Sent: Thursday, March 05, 2009 12:38 PM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
>>
>>
>> On Mar 6, 2009, at 12:22 AM, Marcus Grimm wrote:
>>
>>> Dan,
>>>
>>> I'm not sure what you mean by technically the SQLITE_BUSY is
>>> also wrong. In the test program I get the SQLITE_BUSY quite
>>> often and by retrying the sqlite3_step() it will sooner or
>>> later succeed.
>>
>> Huh. My mistake.
>>
>>
>>
>>> Thanks for confirming that it may happend only for the
>>> first call to sqlite3_step(). This is an important point and
>>> it makes it in fact easy to workaround.
>>> I've changed my test application accordingly and now it can
>>> run endless, I even increased now to 10 reader threads and 2 writer
>>> threads in
>>> parallel without making sqlite particular nervous. Wonderful... :-)
>>>
>>> Thanks again
>>>
>>> Marcus
>>>
>>> Dan wrote:
>>>> On Mar 5, 2009, at 11:14 PM, Marcus Grimm wrote:
>>>>
>>>>> Hi Dan,
>>>>>
>>>>> thank you!
>>>>>
>>>>> Yes! I was not aware that it is wrong to redo a step
>>>>> when it previously returned the LOCKED state.
>>>>> That was the reason for the (correct) MISSUSE return on
>>>>> the 2nd attempt.
>>>>>
>>>>> I think this is the main difference in my case between
>>>>> shared cache on/off: Without shared cache I never
>>>>> get this lock state and it just does the BUSY handling correctly.
>>>>
>>>> Technically the SQLITE_BUSY handling is also wrong, for the
>>>> same reasons. But it's harder to hit a real SQLITE_BUSY than it
>>>> is to hit an SQLITE_LOCKED in shared-cache mode.
>>>>
>>>>> I'll change my locking handling accordingly, seems to be a problem
>>>>> of the very first step after a prepare, so that's should to be easy
>>>>> to reset...
>>>>
>>>> That's correct. An SQLITE_LOCKED or SQLITE_BUSY may only occur
>>>> on the first call to sqlite3_step().
>>>>
>>>> Dan.
>>>>
>>>>
>>>>
>>>>> Thanks again for your feedback
>>>>>
>>>>> Marcus
>>>>>
>>>>&g

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Dan]

On Mar 6, 2009, at 2:53 AM, Hynes, Tom wrote:

> Dan,
>
> I am a little confused now about how to treat a SQLITE_LOCKED  
> error.  Is this to be treated the same as SQLITE_BUSY (i.e. can  
> retry, unless an explicit transaction is in effect and the statement  
> is not a COMMIT, in which case should roll back), or should it be  
> treated differently?  If the latter, should it generally be handled  
> like most other db errors (e.g. SQLITE_CONSTRAINT, SQLITE_CANTOPEN,  
> etc.), or is there some specific error handling for SQLITE_LOCKED  
> that is recommended?

Usually when sqlite3_step() returns something other than SQLITE_ROW, the
statement needs to be reset (sqlite3_reset()) before execution
can be re-attempted. However, SQLITE_BUSY errors are an exception.
After SQLITE_BUSY is returned, you can call sqlite3_step() again  
immediately.
This I either didn't know, or forgot.

An SQLITE_LOCKED error means there is something preventing execution
within the same process. For example a lock on a shared-cache table.
For some applications, for example if another thread may release the
lock shortly, it may be appropriate to retry the query. In other apps,
for example single-threaded apps, it may not.

To retry a query that has failed with an SQLITE_LOCKED error, you need
to reset the statement before trying sqlite3_step() again.

Dan.




> Thanks,
>
> Tom
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org 
> ] On Behalf Of Dan
> Sent: Thursday, March 05, 2009 12:38 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
>
>
> On Mar 6, 2009, at 12:22 AM, Marcus Grimm wrote:
>
>> Dan,
>>
>> I'm not sure what you mean by technically the SQLITE_BUSY is
>> also wrong. In the test program I get the SQLITE_BUSY quite
>> often and by retrying the sqlite3_step() it will sooner or
>> later succeed.
>
> Huh. My mistake.
>
>
>
>> Thanks for confirming that it may happend only for the
>> first call to sqlite3_step(). This is an important point and
>> it makes it in fact easy to workaround.
>> I've changed my test application accordingly and now it can
>> run endless, I even increased now to 10 reader threads and 2 writer
>> threads in
>> parallel without making sqlite particular nervous. Wonderful... :-)
>>
>> Thanks again
>>
>> Marcus
>>
>> Dan wrote:
>>> On Mar 5, 2009, at 11:14 PM, Marcus Grimm wrote:
>>>
>>>> Hi Dan,
>>>>
>>>> thank you!
>>>>
>>>> Yes! I was not aware that it is wrong to redo a step
>>>> when it previously returned the LOCKED state.
>>>> That was the reason for the (correct) MISSUSE return on
>>>> the 2nd attempt.
>>>>
>>>> I think this is the main difference in my case between
>>>> shared cache on/off: Without shared cache I never
>>>> get this lock state and it just does the BUSY handling correctly.
>>>
>>> Technically the SQLITE_BUSY handling is also wrong, for the
>>> same reasons. But it's harder to hit a real SQLITE_BUSY than it
>>> is to hit an SQLITE_LOCKED in shared-cache mode.
>>>
>>>> I'll change my locking handling accordingly, seems to be a problem
>>>> of the very first step after a prepare, so that's should to be easy
>>>> to reset...
>>>
>>> That's correct. An SQLITE_LOCKED or SQLITE_BUSY may only occur
>>> on the first call to sqlite3_step().
>>>
>>> Dan.
>>>
>>>
>>>
>>>> Thanks again for your feedback
>>>>
>>>> Marcus
>>>>
>>>> Dan wrote:
>>>>> On Mar 5, 2009, at 9:57 PM, Marcus Grimm wrote:
>>>>>
>>>>>> Hi again,
>>>>>>
>>>>>> I did a little test program that simulates the multi threading
>>>>>> issue to understand better why I get a MISSUSE error even
>>>>>> when sqlite_prepare returns no error.
>>>>>> The test program will printout some errors on the console
>>>>>> and exits in that case. If I don't use the shared cache it
>>>>>> runs endless without any problem.
>>>>>>
>>>>>> The code is to long to be placed here, but I would be very happy
>>>>>> if somebody could take a look. I've put it on a web server to
>>>>>> download:
>>>>>>
>>>>>> http://www.exomio.de/sqlitethreadtest.c
>>>>>>
>>&

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Hynes, Tom]

Dan, 

I am a little confused now about how to treat a SQLITE_LOCKED error.  Is this 
to be treated the same as SQLITE_BUSY (i.e. can retry, unless an explicit 
transaction is in effect and the statement is not a COMMIT, in which case 
should roll back), or should it be treated differently?  If the latter, should 
it generally be handled like most other db errors (e.g. SQLITE_CONSTRAINT, 
SQLITE_CANTOPEN, etc.), or is there some specific error handling for 
SQLITE_LOCKED that is recommended?

Thanks,

Tom

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dan
Sent: Thursday, March 05, 2009 12:38 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()


On Mar 6, 2009, at 12:22 AM, Marcus Grimm wrote:

> Dan,
>
> I'm not sure what you mean by technically the SQLITE_BUSY is
> also wrong. In the test program I get the SQLITE_BUSY quite
> often and by retrying the sqlite3_step() it will sooner or
> later succeed.

Huh. My mistake.



> Thanks for confirming that it may happend only for the
> first call to sqlite3_step(). This is an important point and
> it makes it in fact easy to workaround.
> I've changed my test application accordingly and now it can
> run endless, I even increased now to 10 reader threads and 2 writer  
> threads in
> parallel without making sqlite particular nervous. Wonderful... :-)
>
> Thanks again
>
> Marcus
>
> Dan wrote:
>> On Mar 5, 2009, at 11:14 PM, Marcus Grimm wrote:
>>
>>> Hi Dan,
>>>
>>> thank you!
>>>
>>> Yes! I was not aware that it is wrong to redo a step
>>> when it previously returned the LOCKED state.
>>> That was the reason for the (correct) MISSUSE return on
>>> the 2nd attempt.
>>>
>>> I think this is the main difference in my case between
>>> shared cache on/off: Without shared cache I never
>>> get this lock state and it just does the BUSY handling correctly.
>>
>> Technically the SQLITE_BUSY handling is also wrong, for the
>> same reasons. But it's harder to hit a real SQLITE_BUSY than it
>> is to hit an SQLITE_LOCKED in shared-cache mode.
>>
>>> I'll change my locking handling accordingly, seems to be a problem
>>> of the very first step after a prepare, so that's should to be easy
>>> to reset...
>>
>> That's correct. An SQLITE_LOCKED or SQLITE_BUSY may only occur
>> on the first call to sqlite3_step().
>>
>> Dan.
>>
>>
>>
>>> Thanks again for your feedback
>>>
>>> Marcus
>>>
>>> Dan wrote:
>>>> On Mar 5, 2009, at 9:57 PM, Marcus Grimm wrote:
>>>>
>>>>> Hi again,
>>>>>
>>>>> I did a little test program that simulates the multi threading
>>>>> issue to understand better why I get a MISSUSE error even
>>>>> when sqlite_prepare returns no error.
>>>>> The test program will printout some errors on the console
>>>>> and exits in that case. If I don't use the shared cache it
>>>>> runs endless without any problem.
>>>>>
>>>>> The code is to long to be placed here, but I would be very happy
>>>>> if somebody could take a look. I've put it on a web server to
>>>>> download:
>>>>>
>>>>> http://www.exomio.de/sqlitethreadtest.c
>>>>>
>>>>> I'm using MS Visual Studio 2008, the program is started within a
>>>>> dosbox to see the error output. Using it on my dual core PC
>>>>> it allmost immediately stops with the missuse error, but why?
>>>>do
>>>>{
>>>>rc = sqlite3_step(hs);
>>>>
>>>>if( (rc == SQLITE_BUSY) || (rc == SQLITE_LOCKED) )
>>>>{
>>>>Sleep(SQLTM_TIME);
>>>>
>>>>n++;
>>>>}
>>>>}while( (n < SQLTM_COUNT) && ((rc == SQLITE_BUSY) || (rc ==
>>>> SQLITE_LOCKED)));
>>>>
>>>> If sqlite3_step() returns SQLITE_BUSY or SQLITE_LOCKED, you need to
>>>> reset the statement before trying the sqlite3_step() again.
>>>>
>>>> Dan.
>>>>
>>>>
>>>>
>>>>
>>>>> Thanks you
>>>>>
>>>>> kind regards
>>>>>
>>>>> Marcus Grimm
>>>>>
>>>>> Marcus Grimm wrote:
>>>>>> Hi Ken,
>>>>>>
>>

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Dan]

On Mar 6, 2009, at 12:22 AM, Marcus Grimm wrote:

> Dan,
>
> I'm not sure what you mean by technically the SQLITE_BUSY is
> also wrong. In the test program I get the SQLITE_BUSY quite
> often and by retrying the sqlite3_step() it will sooner or
> later succeed.

Huh. My mistake.



> Thanks for confirming that it may happend only for the
> first call to sqlite3_step(). This is an important point and
> it makes it in fact easy to workaround.
> I've changed my test application accordingly and now it can
> run endless, I even increased now to 10 reader threads and 2 writer  
> threads in
> parallel without making sqlite particular nervous. Wonderful... :-)
>
> Thanks again
>
> Marcus
>
> Dan wrote:
>> On Mar 5, 2009, at 11:14 PM, Marcus Grimm wrote:
>>
>>> Hi Dan,
>>>
>>> thank you!
>>>
>>> Yes! I was not aware that it is wrong to redo a step
>>> when it previously returned the LOCKED state.
>>> That was the reason for the (correct) MISSUSE return on
>>> the 2nd attempt.
>>>
>>> I think this is the main difference in my case between
>>> shared cache on/off: Without shared cache I never
>>> get this lock state and it just does the BUSY handling correctly.
>>
>> Technically the SQLITE_BUSY handling is also wrong, for the
>> same reasons. But it's harder to hit a real SQLITE_BUSY than it
>> is to hit an SQLITE_LOCKED in shared-cache mode.
>>
>>> I'll change my locking handling accordingly, seems to be a problem
>>> of the very first step after a prepare, so that's should to be easy
>>> to reset...
>>
>> That's correct. An SQLITE_LOCKED or SQLITE_BUSY may only occur
>> on the first call to sqlite3_step().
>>
>> Dan.
>>
>>
>>
>>> Thanks again for your feedback
>>>
>>> Marcus
>>>
>>> Dan wrote:
>>>> On Mar 5, 2009, at 9:57 PM, Marcus Grimm wrote:
>>>>
>>>>> Hi again,
>>>>>
>>>>> I did a little test program that simulates the multi threading
>>>>> issue to understand better why I get a MISSUSE error even
>>>>> when sqlite_prepare returns no error.
>>>>> The test program will printout some errors on the console
>>>>> and exits in that case. If I don't use the shared cache it
>>>>> runs endless without any problem.
>>>>>
>>>>> The code is to long to be placed here, but I would be very happy
>>>>> if somebody could take a look. I've put it on a web server to
>>>>> download:
>>>>>
>>>>> http://www.exomio.de/sqlitethreadtest.c
>>>>>
>>>>> I'm using MS Visual Studio 2008, the program is started within a
>>>>> dosbox to see the error output. Using it on my dual core PC
>>>>> it allmost immediately stops with the missuse error, but why?
>>>>do
>>>>{
>>>>rc = sqlite3_step(hs);
>>>>
>>>>if( (rc == SQLITE_BUSY) || (rc == SQLITE_LOCKED) )
>>>>{
>>>>Sleep(SQLTM_TIME);
>>>>
>>>>n++;
>>>>}
>>>>}while( (n < SQLTM_COUNT) && ((rc == SQLITE_BUSY) || (rc ==
>>>> SQLITE_LOCKED)));
>>>>
>>>> If sqlite3_step() returns SQLITE_BUSY or SQLITE_LOCKED, you need to
>>>> reset the statement before trying the sqlite3_step() again.
>>>>
>>>> Dan.
>>>>
>>>>
>>>>
>>>>
>>>>> Thanks you
>>>>>
>>>>> kind regards
>>>>>
>>>>> Marcus Grimm
>>>>>
>>>>> Marcus Grimm wrote:
>>>>>> Hi Ken,
>>>>>>
>>>>>> yes, I do check the return values also for the
>>>>>> sqlite3_prepare_v2 call. This is allways
>>>>>> successful, the first sqlite_step call right after
>>>>>> this one returns the magic MISSUSE.
>>>>>>
>>>>>> Yes, I also tried to call sqlite_reset right
>>>>>> after this first error and in this case
>>>>>> sqlite_reset return the LOCK state. I didn't
>>>>>> yet go on to handle this...
>>>>>>
>>>>>> I can't post the code of the original implementation here,
>>>>>> but also for my debugging and tests I'll produce
>>>>>> a simplified test application. J

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Marcus Grimm]

Dan,

I'm not sure what you mean by technically the SQLITE_BUSY is
also wrong. In the test program I get the SQLITE_BUSY quite
often and by retrying the sqlite3_step() it will sooner or
later succeed. I guess you mean that there are condition when
a BUSY state will NOT allow to retry the sqlite3_step like mentioned
in the docs for sqlite3_step: "If the statement is
not a COMMIT and occurs within a explicit transaction then you
should rollback the transaction before continuing." I have to think
about if I have such a condition.


Thanks for confirming that it may happend only for the
first call to sqlite3_step(). This is an important point and
it makes it in fact easy to workaround.
I've changed my test application accordingly and now it can
run endless, I even increased now to 10 reader threads and 2 writer threads in
parallel without making sqlite particular nervous. Wonderful... :-)

Thanks again

Marcus

Dan wrote:
> On Mar 5, 2009, at 11:14 PM, Marcus Grimm wrote:
> 
>> Hi Dan,
>>
>> thank you!
>>
>> Yes! I was not aware that it is wrong to redo a step
>> when it previously returned the LOCKED state.
>> That was the reason for the (correct) MISSUSE return on
>> the 2nd attempt.
>>
>> I think this is the main difference in my case between
>> shared cache on/off: Without shared cache I never
>> get this lock state and it just does the BUSY handling correctly.
> 
> Technically the SQLITE_BUSY handling is also wrong, for the
> same reasons. But it's harder to hit a real SQLITE_BUSY than it
> is to hit an SQLITE_LOCKED in shared-cache mode.
> 
>> I'll change my locking handling accordingly, seems to be a problem
>> of the very first step after a prepare, so that's should to be easy
>> to reset...
> 
> That's correct. An SQLITE_LOCKED or SQLITE_BUSY may only occur
> on the first call to sqlite3_step().
> 
> Dan.
> 
> 
> 
>> Thanks again for your feedback
>>
>> Marcus
>>
>> Dan wrote:
>>> On Mar 5, 2009, at 9:57 PM, Marcus Grimm wrote:
>>>
>>>> Hi again,
>>>>
>>>> I did a little test program that simulates the multi threading
>>>> issue to understand better why I get a MISSUSE error even
>>>> when sqlite_prepare returns no error.
>>>> The test program will printout some errors on the console
>>>> and exits in that case. If I don't use the shared cache it
>>>> runs endless without any problem.
>>>>
>>>> The code is to long to be placed here, but I would be very happy
>>>> if somebody could take a look. I've put it on a web server to
>>>> download:
>>>>
>>>> http://www.exomio.de/sqlitethreadtest.c
>>>>
>>>> I'm using MS Visual Studio 2008, the program is started within a
>>>> dosbox to see the error output. Using it on my dual core PC
>>>> it allmost immediately stops with the missuse error, but why?
>>> do
>>> {
>>> rc = sqlite3_step(hs);
>>>
>>> if( (rc == SQLITE_BUSY) || (rc == SQLITE_LOCKED) )
>>> {
>>> Sleep(SQLTM_TIME);
>>>
>>> n++;
>>> }
>>> }while( (n < SQLTM_COUNT) && ((rc == SQLITE_BUSY) || (rc ==
>>> SQLITE_LOCKED)));
>>>
>>> If sqlite3_step() returns SQLITE_BUSY or SQLITE_LOCKED, you need to
>>> reset the statement before trying the sqlite3_step() again.
>>>
>>> Dan.
>>>
>>>
>>>
>>>
>>>> Thanks you
>>>>
>>>> kind regards
>>>>
>>>> Marcus Grimm
>>>>
>>>> Marcus Grimm wrote:
>>>>> Hi Ken,
>>>>>
>>>>> yes, I do check the return values also for the
>>>>> sqlite3_prepare_v2 call. This is allways
>>>>> successful, the first sqlite_step call right after
>>>>> this one returns the magic MISSUSE.
>>>>>
>>>>> Yes, I also tried to call sqlite_reset right
>>>>> after this first error and in this case
>>>>> sqlite_reset return the LOCK state. I didn't
>>>>> yet go on to handle this...
>>>>>
>>>>> I can't post the code of the original implementation here,
>>>>> but also for my debugging and tests I'll produce
>>>>> a simplified test application. Just to see if that
>>>>> can be used to reproduce the effect. If that one runs
>>>>> without any problem, it's my fault, if not: I'll post
>>>>> 

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Dan]

On Mar 5, 2009, at 11:14 PM, Marcus Grimm wrote:

> Hi Dan,
>
> thank you!
>
> Yes! I was not aware that it is wrong to redo a step
> when it previously returned the LOCKED state.
> That was the reason for the (correct) MISSUSE return on
> the 2nd attempt.
>
> I think this is the main difference in my case between
> shared cache on/off: Without shared cache I never
> get this lock state and it just does the BUSY handling correctly.

Technically the SQLITE_BUSY handling is also wrong, for the
same reasons. But it's harder to hit a real SQLITE_BUSY than it
is to hit an SQLITE_LOCKED in shared-cache mode.

> I'll change my locking handling accordingly, seems to be a problem
> of the very first step after a prepare, so that's should to be easy
> to reset...

That's correct. An SQLITE_LOCKED or SQLITE_BUSY may only occur
on the first call to sqlite3_step().

Dan.



> Thanks again for your feedback
>
> Marcus
>
> Dan wrote:
>> On Mar 5, 2009, at 9:57 PM, Marcus Grimm wrote:
>>
>>> Hi again,
>>>
>>> I did a little test program that simulates the multi threading
>>> issue to understand better why I get a MISSUSE error even
>>> when sqlite_prepare returns no error.
>>> The test program will printout some errors on the console
>>> and exits in that case. If I don't use the shared cache it
>>> runs endless without any problem.
>>>
>>> The code is to long to be placed here, but I would be very happy
>>> if somebody could take a look. I've put it on a web server to
>>> download:
>>>
>>> http://www.exomio.de/sqlitethreadtest.c
>>>
>>> I'm using MS Visual Studio 2008, the program is started within a
>>> dosbox to see the error output. Using it on my dual core PC
>>> it allmost immediately stops with the missuse error, but why?
>>
>> do
>> {
>> rc = sqlite3_step(hs);
>>
>> if( (rc == SQLITE_BUSY) || (rc == SQLITE_LOCKED) )
>> {
>> Sleep(SQLTM_TIME);
>>
>> n++;
>> }
>> }while( (n < SQLTM_COUNT) && ((rc == SQLITE_BUSY) || (rc ==
>> SQLITE_LOCKED)));
>>
>> If sqlite3_step() returns SQLITE_BUSY or SQLITE_LOCKED, you need to
>> reset the statement before trying the sqlite3_step() again.
>>
>> Dan.
>>
>>
>>
>>
>>> Thanks you
>>>
>>> kind regards
>>>
>>> Marcus Grimm
>>>
>>> Marcus Grimm wrote:
>>>> Hi Ken,
>>>>
>>>> yes, I do check the return values also for the
>>>> sqlite3_prepare_v2 call. This is allways
>>>> successful, the first sqlite_step call right after
>>>> this one returns the magic MISSUSE.
>>>>
>>>> Yes, I also tried to call sqlite_reset right
>>>> after this first error and in this case
>>>> sqlite_reset return the LOCK state. I didn't
>>>> yet go on to handle this...
>>>>
>>>> I can't post the code of the original implementation here,
>>>> but also for my debugging and tests I'll produce
>>>> a simplified test application. Just to see if that
>>>> can be used to reproduce the effect. If that one runs
>>>> without any problem, it's my fault, if not: I'll post
>>>> it here for further discussions... ;)
>>>>
>>>> Thanks for your feedback
>>>>
>>>> Marcus
>>>>
>>>>
>>>>> Marcus,
>>>>>
>>>>> I'm not sure if this will help or not...
>>>>>
>>>>> But I noticed your sample code does not test the return value from
>>>>> the
>>>>> sqlite3_prepare_v2 call. Could the code be entering the do  loop
>>>>> when an
>>>>> error was returned from prepare?
>>>>>
>>>>> Just an idea.
>>>>>
>>>>> Have you tried the reset call as DRH had suggested?
>>>>> Your prior post indicated you had more code and another loop. Can
>>>>> you post
>>>>> the full code for both loops?
>>>>>
>>>>> HTH
>>>>>
>>>>>
>>>>>
>>>>> --- On Wed, 3/4/09, Marcus Grimm <mgr...@medcom-online.de> wrote:
>>>>>
>>>>>> From: Marcus Grimm <mgr...@medcom-online.de>
>>>>>> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on
>>>>>> sqlite3_step()
>>>>>> To: 

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Marcus Grimm]

Hi Dan,

thank you!

Yes! I was not aware that it is wrong to redo a step
when it previously returned the LOCKED state.
That was the reason for the (correct) MISSUSE return on
the 2nd attempt.

I think this is the main difference in my case between
shared cache on/off: Without shared cache I never
get this lock state and it just does the BUSY handling correctly.

I'll change my locking handling accordingly, seems to be a problem
of the very first step after a prepare, so that's should to be easy
to reset...


Thanks again for your feedback

Marcus

Dan wrote:
> On Mar 5, 2009, at 9:57 PM, Marcus Grimm wrote:
> 
>> Hi again,
>>
>> I did a little test program that simulates the multi threading
>> issue to understand better why I get a MISSUSE error even
>> when sqlite_prepare returns no error.
>> The test program will printout some errors on the console
>> and exits in that case. If I don't use the shared cache it
>> runs endless without any problem.
>>
>> The code is to long to be placed here, but I would be very happy
>> if somebody could take a look. I've put it on a web server to  
>> download:
>>
>> http://www.exomio.de/sqlitethreadtest.c
>>
>> I'm using MS Visual Studio 2008, the program is started within a
>> dosbox to see the error output. Using it on my dual core PC
>> it allmost immediately stops with the missuse error, but why?
> 
>  do
>  {
>  rc = sqlite3_step(hs);
> 
>  if( (rc == SQLITE_BUSY) || (rc == SQLITE_LOCKED) )
>  {
>  Sleep(SQLTM_TIME);
> 
>  n++;
>  }
>  }while( (n < SQLTM_COUNT) && ((rc == SQLITE_BUSY) || (rc ==  
> SQLITE_LOCKED)));
> 
> If sqlite3_step() returns SQLITE_BUSY or SQLITE_LOCKED, you need to
> reset the statement before trying the sqlite3_step() again.
> 
> Dan.
> 
> 
> 
> 
>> Thanks you
>>
>> kind regards
>>
>> Marcus Grimm
>>
>> Marcus Grimm wrote:
>>> Hi Ken,
>>>
>>> yes, I do check the return values also for the
>>> sqlite3_prepare_v2 call. This is allways
>>> successful, the first sqlite_step call right after
>>> this one returns the magic MISSUSE.
>>>
>>> Yes, I also tried to call sqlite_reset right
>>> after this first error and in this case
>>> sqlite_reset return the LOCK state. I didn't
>>> yet go on to handle this...
>>>
>>> I can't post the code of the original implementation here,
>>> but also for my debugging and tests I'll produce
>>> a simplified test application. Just to see if that
>>> can be used to reproduce the effect. If that one runs
>>> without any problem, it's my fault, if not: I'll post
>>> it here for further discussions... ;)
>>>
>>> Thanks for your feedback
>>>
>>> Marcus
>>>
>>>
>>>> Marcus,
>>>>
>>>> I'm not sure if this will help or not...
>>>>
>>>> But I noticed your sample code does not test the return value from  
>>>> the
>>>> sqlite3_prepare_v2 call. Could the code be entering the do  loop  
>>>> when an
>>>> error was returned from prepare?
>>>>
>>>> Just an idea.
>>>>
>>>> Have you tried the reset call as DRH had suggested?
>>>> Your prior post indicated you had more code and another loop. Can  
>>>> you post
>>>> the full code for both loops?
>>>>
>>>> HTH
>>>>
>>>>
>>>>
>>>> --- On Wed, 3/4/09, Marcus Grimm <mgr...@medcom-online.de> wrote:
>>>>
>>>>> From: Marcus Grimm <mgr...@medcom-online.de>
>>>>> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on  
>>>>> sqlite3_step()
>>>>> To: kennethinbox-sql...@yahoo.com, "General Discussion of SQLite
>>>>> Database" <sqlite-users@sqlite.org>
>>>>> Date: Wednesday, March 4, 2009, 4:31 PM
>>>>> Ken,
>>>>>
>>>>> you are of course right that it needs some checks
>>>>> for locks and busy states. I left that out to
>>>>> simplify the code given below. My original code
>>>>> checks that and it usually works quite well.
>>>>> that's basically the reason why I was puzzled
>>>>> by the randomly MISUSE results after I added the shared
>>>>> cache.
>>>>>
>>>>> I'm not yet finished with my debugging since it is a
>>>>&

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Dan]

On Mar 5, 2009, at 9:57 PM, Marcus Grimm wrote:

> Hi again,
>
> I did a little test program that simulates the multi threading
> issue to understand better why I get a MISSUSE error even
> when sqlite_prepare returns no error.
> The test program will printout some errors on the console
> and exits in that case. If I don't use the shared cache it
> runs endless without any problem.
>
> The code is to long to be placed here, but I would be very happy
> if somebody could take a look. I've put it on a web server to  
> download:
>
> http://www.exomio.de/sqlitethreadtest.c
>
> I'm using MS Visual Studio 2008, the program is started within a
> dosbox to see the error output. Using it on my dual core PC
> it allmost immediately stops with the missuse error, but why?

 do
 {
 rc = sqlite3_step(hs);

 if( (rc == SQLITE_BUSY) || (rc == SQLITE_LOCKED) )
 {
 Sleep(SQLTM_TIME);

 n++;
 }
 }while( (n < SQLTM_COUNT) && ((rc == SQLITE_BUSY) || (rc ==  
SQLITE_LOCKED)));

If sqlite3_step() returns SQLITE_BUSY or SQLITE_LOCKED, you need to
reset the statement before trying the sqlite3_step() again.

Dan.




> Thanks you
>
> kind regards
>
> Marcus Grimm
>
> Marcus Grimm wrote:
>> Hi Ken,
>>
>> yes, I do check the return values also for the
>> sqlite3_prepare_v2 call. This is allways
>> successful, the first sqlite_step call right after
>> this one returns the magic MISSUSE.
>>
>> Yes, I also tried to call sqlite_reset right
>> after this first error and in this case
>> sqlite_reset return the LOCK state. I didn't
>> yet go on to handle this...
>>
>> I can't post the code of the original implementation here,
>> but also for my debugging and tests I'll produce
>> a simplified test application. Just to see if that
>> can be used to reproduce the effect. If that one runs
>> without any problem, it's my fault, if not: I'll post
>> it here for further discussions... ;)
>>
>> Thanks for your feedback
>>
>> Marcus
>>
>>
>>> Marcus,
>>>
>>> I'm not sure if this will help or not...
>>>
>>> But I noticed your sample code does not test the return value from  
>>> the
>>> sqlite3_prepare_v2 call. Could the code be entering the do  loop  
>>> when an
>>> error was returned from prepare?
>>>
>>> Just an idea.
>>>
>>> Have you tried the reset call as DRH had suggested?
>>> Your prior post indicated you had more code and another loop. Can  
>>> you post
>>> the full code for both loops?
>>>
>>> HTH
>>>
>>>
>>>
>>> --- On Wed, 3/4/09, Marcus Grimm <mgr...@medcom-online.de> wrote:
>>>
>>>> From: Marcus Grimm <mgr...@medcom-online.de>
>>>> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on  
>>>> sqlite3_step()
>>>> To: kennethinbox-sql...@yahoo.com, "General Discussion of SQLite
>>>> Database" <sqlite-users@sqlite.org>
>>>> Date: Wednesday, March 4, 2009, 4:31 PM
>>>> Ken,
>>>>
>>>> you are of course right that it needs some checks
>>>> for locks and busy states. I left that out to
>>>> simplify the code given below. My original code
>>>> checks that and it usually works quite well.
>>>> that's basically the reason why I was puzzled
>>>> by the randomly MISUSE results after I added the shared
>>>> cache.
>>>>
>>>> I'm not yet finished with my debugging since it is a
>>>> multithreading/collision issue - ugly to trace.
>>>>
>>>> My feeling is that it is related to the condition when
>>>> one thread is attempting or holding an exclusive lock
>>>> while another thread is just doing an sqlite_step (read
>>>> only)
>>>> on an allready created statement. Both threads use their
>>>> own
>>>> DB connections. For example: when I do a sqlite_reset
>>>> right after sqlite_step returns SQLITE_MISUSE, as Richard
>>>> suggest,
>>>> I get immediately a SQLITE_LOCK return code from
>>>> sqlite_reset in this case. Why I didn't get that before
>>>> or
>>>> from the sqlite_prepare ?
>>>>
>>>> Anyway, I'm going on to workaround this ALso I
>>>> would like
>>>> to mention once more that it is only during an artificial
>>>> stress
>>>> test, mainly to verify my implementa

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Marcus Grimm]

Hi again,

I did a little test program that simulates the multi threading
issue to understand better why I get a MISSUSE error even
when sqlite_prepare returns no error.
The test program will printout some errors on the console
and exits in that case. If I don't use the shared cache it
runs endless without any problem.

The code is to long to be placed here, but I would be very happy
if somebody could take a look. I've put it on a web server to download:

http://www.exomio.de/sqlitethreadtest.c

I'm using MS Visual Studio 2008, the program is started within a
dosbox to see the error output. Using it on my dual core PC
it allmost immediately stops with the missuse error, but why?

Thanks you

kind regards

Marcus Grimm

Marcus Grimm wrote:
> Hi Ken,
> 
> yes, I do check the return values also for the
> sqlite3_prepare_v2 call. This is allways
> successful, the first sqlite_step call right after
> this one returns the magic MISSUSE.
> 
> Yes, I also tried to call sqlite_reset right
> after this first error and in this case
> sqlite_reset return the LOCK state. I didn't
> yet go on to handle this...
> 
> I can't post the code of the original implementation here,
> but also for my debugging and tests I'll produce
> a simplified test application. Just to see if that
> can be used to reproduce the effect. If that one runs
> without any problem, it's my fault, if not: I'll post
> it here for further discussions... ;)
> 
> Thanks for your feedback
> 
> Marcus
> 
> 
>> Marcus,
>>
>> I'm not sure if this will help or not...
>>
>> But I noticed your sample code does not test the return value from the
>> sqlite3_prepare_v2 call. Could the code be entering the do  loop when an
>> error was returned from prepare?
>>
>> Just an idea.
>>
>> Have you tried the reset call as DRH had suggested?
>> Your prior post indicated you had more code and another loop. Can you post
>> the full code for both loops?
>>
>> HTH
>>
>>
>>
>> --- On Wed, 3/4/09, Marcus Grimm <mgr...@medcom-online.de> wrote:
>>
>>> From: Marcus Grimm <mgr...@medcom-online.de>
>>> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
>>> To: kennethinbox-sql...@yahoo.com, "General Discussion of SQLite
>>> Database" <sqlite-users@sqlite.org>
>>> Date: Wednesday, March 4, 2009, 4:31 PM
>>> Ken,
>>>
>>> you are of course right that it needs some checks
>>> for locks and busy states. I left that out to
>>> simplify the code given below. My original code
>>> checks that and it usually works quite well.
>>> that's basically the reason why I was puzzled
>>> by the randomly MISUSE results after I added the shared
>>> cache.
>>>
>>> I'm not yet finished with my debugging since it is a
>>> multithreading/collision issue - ugly to trace.
>>>
>>> My feeling is that it is related to the condition when
>>> one thread is attempting or holding an exclusive lock
>>> while another thread is just doing an sqlite_step (read
>>> only)
>>> on an allready created statement. Both threads use their
>>> own
>>> DB connections. For example: when I do a sqlite_reset
>>> right after sqlite_step returns SQLITE_MISUSE, as Richard
>>> suggest,
>>> I get immediately a SQLITE_LOCK return code from
>>> sqlite_reset in this case. Why I didn't get that before
>>> or
>>> from the sqlite_prepare ?
>>>
>>> Anyway, I'm going on to workaround this ALso I
>>> would like
>>> to mention once more that it is only during an artificial
>>> stress
>>> test, mainly to verify my implementation. Under normal
>>> usercondition it is very unlikely to happend and sqlite
>>> works perfect as expected.
>>>
>>> Thanks
>>>
>>> Marcus
>>>
>>>> Marcus,
>>>>
>>>> You might want to also add some checks in for
>>> sqlite_busy as on the result
>>>> of the prepare and the first call to sqlite_step.
>>>>
>>>>
>>>> On the inner loop test for the most common case first
>>> (SQLITE_ROW) then
>>>> test for errors... Slight performance improvement...
>>>>
>>>>
>>>> --- On Wed, 3/4/09, Marcus Grimm
>>> <mgr...@medcom-online.de> wrote:
>>>>> From: Marcus Grimm <mgr...@medcom-online.de>
>>>>> Subject: Re: [sqlite] shared cache and
>>> SQLITE_MISUSE on sqlite3_step()
>>>>>

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Marcus Grimm]

Hi Ken,

yes, I do check the return values also for the
sqlite3_prepare_v2 call. This is allways
successful, the first sqlite_step call right after
this one returns the magic MISSUSE.

Yes, I also tried to call sqlite_reset right
after this first error and in this case
sqlite_reset return the LOCK state. I didn't
yet go on to handle this...

I can't post the code of the original implementation here,
but also for my debugging and tests I'll produce
a simplified test application. Just to see if that
can be used to reproduce the effect. If that one runs
without any problem, it's my fault, if not: I'll post
it here for further discussions... ;)

Thanks for your feedback

Marcus


>
> Marcus,
>
> I'm not sure if this will help or not...
>
> But I noticed your sample code does not test the return value from the
> sqlite3_prepare_v2 call. Could the code be entering the do  loop when an
> error was returned from prepare?
>
> Just an idea.
>
> Have you tried the reset call as DRH had suggested?
> Your prior post indicated you had more code and another loop. Can you post
> the full code for both loops?
>
> HTH
>
>
>
> --- On Wed, 3/4/09, Marcus Grimm <mgr...@medcom-online.de> wrote:
>
>> From: Marcus Grimm <mgr...@medcom-online.de>
>> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
>> To: kennethinbox-sql...@yahoo.com, "General Discussion of SQLite
>> Database" <sqlite-users@sqlite.org>
>> Date: Wednesday, March 4, 2009, 4:31 PM
>> Ken,
>>
>> you are of course right that it needs some checks
>> for locks and busy states. I left that out to
>> simplify the code given below. My original code
>> checks that and it usually works quite well.
>> that's basically the reason why I was puzzled
>> by the randomly MISUSE results after I added the shared
>> cache.
>>
>> I'm not yet finished with my debugging since it is a
>> multithreading/collision issue - ugly to trace.
>>
>> My feeling is that it is related to the condition when
>> one thread is attempting or holding an exclusive lock
>> while another thread is just doing an sqlite_step (read
>> only)
>> on an allready created statement. Both threads use their
>> own
>> DB connections. For example: when I do a sqlite_reset
>> right after sqlite_step returns SQLITE_MISUSE, as Richard
>> suggest,
>> I get immediately a SQLITE_LOCK return code from
>> sqlite_reset in this case. Why I didn't get that before
>> or
>> from the sqlite_prepare ?
>>
>> Anyway, I'm going on to workaround this ALso I
>> would like
>> to mention once more that it is only during an artificial
>> stress
>> test, mainly to verify my implementation. Under normal
>> usercondition it is very unlikely to happend and sqlite
>> works perfect as expected.
>>
>> Thanks
>>
>> Marcus
>>
>> >
>> > Marcus,
>> >
>> > You might want to also add some checks in for
>> sqlite_busy as on the result
>> > of the prepare and the first call to sqlite_step.
>> >
>> >
>> > On the inner loop test for the most common case first
>> (SQLITE_ROW) then
>> > test for errors... Slight performance improvement...
>> >
>> >
>> > --- On Wed, 3/4/09, Marcus Grimm
>> <mgr...@medcom-online.de> wrote:
>> >
>> >> From: Marcus Grimm <mgr...@medcom-online.de>
>> >> Subject: Re: [sqlite] shared cache and
>> SQLITE_MISUSE on sqlite3_step()
>> >> To: "General Discussion of SQLite
>> Database" <sqlite-users@sqlite.org>
>> >> Date: Wednesday, March 4, 2009, 10:25 AM
>> >> Richard, thanks again for the feedback.
>> >>
>> >> However, I don't see how it can happend that
>> the
>> >> statement
>> >> is completed internally without returning
>> SQLITE_DONE.
>> >> In the particular code of the "reading
>> thread" I
>> >> do something like:
>> >>
>> >> --
>> >> sqlite3_prepare_v2(db, "SELECT * FROM
>> TableA",
>> >> -1, , 0);
>> >> /** step throu table result **/
>> >> do
>> >> {
>> >>rc = sqlite3_step(stmt);
>> >>if( rc == SQLITE_MISUSE )
>> >>{  fprintf(stderr, "ERROR...\n");
>> break; }
>> >>else
>> >>if( rc == SQLITE_ROW )
>> >>  read_data_etc();
>> >>else
>> >>  break;
>> >> }while( rc

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Ken]

Marcus,

I'm not sure if this will help or not...

But I noticed your sample code does not test the return value from the 
sqlite3_prepare_v2 call. Could the code be entering the do  loop when an error 
was returned from prepare?

Just an idea.

Have you tried the reset call as DRH had suggested? 
Your prior post indicated you had more code and another loop. Can you post the 
full code for both loops?

HTH



--- On Wed, 3/4/09, Marcus Grimm <mgr...@medcom-online.de> wrote:

> From: Marcus Grimm <mgr...@medcom-online.de>
> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
> To: kennethinbox-sql...@yahoo.com, "General Discussion of SQLite Database" 
> <sqlite-users@sqlite.org>
> Date: Wednesday, March 4, 2009, 4:31 PM
> Ken,
> 
> you are of course right that it needs some checks
> for locks and busy states. I left that out to
> simplify the code given below. My original code
> checks that and it usually works quite well.
> that's basically the reason why I was puzzled
> by the randomly MISUSE results after I added the shared
> cache.
> 
> I'm not yet finished with my debugging since it is a
> multithreading/collision issue - ugly to trace.
> 
> My feeling is that it is related to the condition when
> one thread is attempting or holding an exclusive lock
> while another thread is just doing an sqlite_step (read
> only)
> on an allready created statement. Both threads use their
> own
> DB connections. For example: when I do a sqlite_reset
> right after sqlite_step returns SQLITE_MISUSE, as Richard
> suggest,
> I get immediately a SQLITE_LOCK return code from
> sqlite_reset in this case. Why I didn't get that before
> or
> from the sqlite_prepare ?
> 
> Anyway, I'm going on to workaround this ALso I
> would like
> to mention once more that it is only during an artificial
> stress
> test, mainly to verify my implementation. Under normal
> usercondition it is very unlikely to happend and sqlite
> works perfect as expected.
> 
> Thanks
> 
> Marcus
> 
> >
> > Marcus,
> >
> > You might want to also add some checks in for
> sqlite_busy as on the result
> > of the prepare and the first call to sqlite_step.
> >
> >
> > On the inner loop test for the most common case first
> (SQLITE_ROW) then
> > test for errors... Slight performance improvement...
> >
> >
> > --- On Wed, 3/4/09, Marcus Grimm
> <mgr...@medcom-online.de> wrote:
> >
> >> From: Marcus Grimm <mgr...@medcom-online.de>
> >> Subject: Re: [sqlite] shared cache and
> SQLITE_MISUSE on sqlite3_step()
> >> To: "General Discussion of SQLite
> Database" <sqlite-users@sqlite.org>
> >> Date: Wednesday, March 4, 2009, 10:25 AM
> >> Richard, thanks again for the feedback.
> >>
> >> However, I don't see how it can happend that
> the
> >> statement
> >> is completed internally without returning
> SQLITE_DONE.
> >> In the particular code of the "reading
> thread" I
> >> do something like:
> >>
> >> --
> >> sqlite3_prepare_v2(db, "SELECT * FROM
> TableA",
> >> -1, , 0);
> >> /** step throu table result **/
> >> do
> >> {
> >>rc = sqlite3_step(stmt);
> >>if( rc == SQLITE_MISUSE )
> >>{  fprintf(stderr, "ERROR...\n");
> break; }
> >>else
> >>if( rc == SQLITE_ROW )
> >>  read_data_etc();
> >>else
> >>  break;
> >> }while( rc != SQLITE_DONE );
> >>
> >> sqlite3_finalize(stmt);
> >> --
> >>
> >> The prepare statement pointer is defined locally
> and no
> >> other
> >> thread can access it, except sqlite internal
> maybe.
> >> To me it looks that in case a parallel thread is
> inserting
> >> or
> >> updating data, the above loop is somehow affected
> and
> >> returns the
> >> MISUSE.
> >>
> >> Your reply so far indicates either a bug on my
> side or a
> >> missusage.
> >> I'll go on and try to find the reason why the
> magic
> >> number is resetted
> >> without knowing from calling functions.
> >>
> >> calling sqlite3_reset in these cases is difficult
> for me
> >> since it might
> >> not be the first step and previous data is
> allready in use
> >> in the upper loop.
> >> But maybe an option for the very first step if
> that
> >> fails... hm... I'll try that.
> >>
> >>
> >> Thanks agai

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Marcus Grimm]

Ken,

you are of course right that it needs some checks
for locks and busy states. I left that out to
simplify the code given below. My original code
checks that and it usually works quite well.
that's basically the reason why I was puzzled
by the randomly MISUSE results after I added the shared cache.

I'm not yet finished with my debugging since it is a
multithreading/collision issue - ugly to trace.

My feeling is that it is related to the condition when
one thread is attempting or holding an exclusive lock
while another thread is just doing an sqlite_step (read only)
on an allready created statement. Both threads use their own
DB connections. For example: when I do a sqlite_reset
right after sqlite_step returns SQLITE_MISUSE, as Richard suggest,
I get immediately a SQLITE_LOCK return code from
sqlite_reset in this case. Why I didn't get that before or
from the sqlite_prepare ?

Anyway, I'm going on to workaround this ALso I would like
to mention once more that it is only during an artificial stress
test, mainly to verify my implementation. Under normal
usercondition it is very unlikely to happend and sqlite
works perfect as expected.

Thanks

Marcus

>
> Marcus,
>
> You might want to also add some checks in for sqlite_busy as on the result
> of the prepare and the first call to sqlite_step.
>
>
> On the inner loop test for the most common case first (SQLITE_ROW) then
> test for errors... Slight performance improvement...
>
>
> --- On Wed, 3/4/09, Marcus Grimm <mgr...@medcom-online.de> wrote:
>
>> From: Marcus Grimm <mgr...@medcom-online.de>
>> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
>> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
>> Date: Wednesday, March 4, 2009, 10:25 AM
>> Richard, thanks again for the feedback.
>>
>> However, I don't see how it can happend that the
>> statement
>> is completed internally without returning SQLITE_DONE.
>> In the particular code of the "reading thread" I
>> do something like:
>>
>> --
>> sqlite3_prepare_v2(db, "SELECT * FROM TableA",
>> -1, , 0);
>> /** step throu table result **/
>> do
>> {
>>rc = sqlite3_step(stmt);
>>if( rc == SQLITE_MISUSE )
>>{  fprintf(stderr, "ERROR...\n"); break; }
>>else
>>if( rc == SQLITE_ROW )
>>  read_data_etc();
>>else
>>  break;
>> }while( rc != SQLITE_DONE );
>>
>> sqlite3_finalize(stmt);
>> --
>>
>> The prepare statement pointer is defined locally and no
>> other
>> thread can access it, except sqlite internal maybe.
>> To me it looks that in case a parallel thread is inserting
>> or
>> updating data, the above loop is somehow affected and
>> returns the
>> MISUSE.
>>
>> Your reply so far indicates either a bug on my side or a
>> missusage.
>> I'll go on and try to find the reason why the magic
>> number is resetted
>> without knowing from calling functions.
>>
>> calling sqlite3_reset in these cases is difficult for me
>> since it might
>> not be the first step and previous data is allready in use
>> in the upper loop.
>> But maybe an option for the very first step if that
>> fails... hm... I'll try that.
>>
>>
>> Thanks again
>>
>> kind regards
>>
>> Marcus Grimm
>>
>>
>>
>> D. Richard Hipp wrote:
>> > On Mar 4, 2009, at 9:35 AM, Marcus Grimm wrote:
>> >
>> >> hi,
>> >>
>> >> OK, the value of p->magic is 519C2973
>> (VDBE_MAGIC_HALT)
>> >
>> > That means the prepared statement has run to
>> completion and needs to
>> > be reset using sqlite3_reset() before you continue.
>> >
>> > D. Richard Hipp
>> > d...@hwaci.com
>> >
>> >
>> >
>> > ___
>> > 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] shared cache and SQLITE_MISUSE on sqlite3_step()

[Ken]

Marcus,

You might want to also add some checks in for sqlite_busy as on the result of 
the prepare and the first call to sqlite_step.


On the inner loop test for the most common case first (SQLITE_ROW) then test 
for errors... Slight performance improvement...


--- On Wed, 3/4/09, Marcus Grimm <mgr...@medcom-online.de> wrote:

> From: Marcus Grimm <mgr...@medcom-online.de>
> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Date: Wednesday, March 4, 2009, 10:25 AM
> Richard, thanks again for the feedback.
> 
> However, I don't see how it can happend that the
> statement
> is completed internally without returning SQLITE_DONE.
> In the particular code of the "reading thread" I
> do something like:
> 
> --
> sqlite3_prepare_v2(db, "SELECT * FROM TableA",
> -1, , 0);
> /** step throu table result **/
> do
> {
>rc = sqlite3_step(stmt);
>if( rc == SQLITE_MISUSE )
>{  fprintf(stderr, "ERROR...\n"); break; }
>else
>if( rc == SQLITE_ROW )
>  read_data_etc();
>else
>  break;
> }while( rc != SQLITE_DONE );
> 
> sqlite3_finalize(stmt);
> --
> 
> The prepare statement pointer is defined locally and no
> other
> thread can access it, except sqlite internal maybe.
> To me it looks that in case a parallel thread is inserting
> or
> updating data, the above loop is somehow affected and
> returns the
> MISUSE.
> 
> Your reply so far indicates either a bug on my side or a
> missusage.
> I'll go on and try to find the reason why the magic
> number is resetted
> without knowing from calling functions.
> 
> calling sqlite3_reset in these cases is difficult for me
> since it might
> not be the first step and previous data is allready in use
> in the upper loop.
> But maybe an option for the very first step if that
> fails... hm... I'll try that.
> 
> 
> Thanks again
> 
> kind regards
> 
> Marcus Grimm
> 
> 
> 
> D. Richard Hipp wrote:
> > On Mar 4, 2009, at 9:35 AM, Marcus Grimm wrote:
> > 
> >> hi,
> >>
> >> OK, the value of p->magic is 519C2973
> (VDBE_MAGIC_HALT)
> > 
> > That means the prepared statement has run to
> completion and needs to  
> > be reset using sqlite3_reset() before you continue.
> > 
> > D. Richard Hipp
> > d...@hwaci.com
> > 
> > 
> > 
> > ___
> > 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] shared cache and SQLITE_MISUSE on sqlite3_step()

[Marcus Grimm]

Richard, thanks again for the feedback.

However, I don't see how it can happend that the statement
is completed internally without returning SQLITE_DONE.
In the particular code of the "reading thread" I do something like:

--
sqlite3_prepare_v2(db, "SELECT * FROM TableA", -1, , 0);
/** step throu table result **/
do
{
   rc = sqlite3_step(stmt);
   if( rc == SQLITE_MISUSE )
   {  fprintf(stderr, "ERROR...\n"); break; }
   else
   if( rc == SQLITE_ROW )
 read_data_etc();
   else
 break;
}while( rc != SQLITE_DONE );

sqlite3_finalize(stmt);
--

The prepare statement pointer is defined locally and no other
thread can access it, except sqlite internal maybe.
To me it looks that in case a parallel thread is inserting or
updating data, the above loop is somehow affected and returns the
MISUSE.

Your reply so far indicates either a bug on my side or a missusage.
I'll go on and try to find the reason why the magic number is resetted
without knowing from calling functions.

calling sqlite3_reset in these cases is difficult for me since it might
not be the first step and previous data is allready in use in the upper loop.
But maybe an option for the very first step if that fails... hm... I'll try 
that.


Thanks again

kind regards

Marcus Grimm



D. Richard Hipp wrote:
> On Mar 4, 2009, at 9:35 AM, Marcus Grimm wrote:
> 
>> hi,
>>
>> OK, the value of p->magic is 519C2973 (VDBE_MAGIC_HALT)
> 
> That means the prepared statement has run to completion and needs to  
> be reset using sqlite3_reset() before you continue.
> 
> D. Richard Hipp
> d...@hwaci.com
> 
> 
> 
> ___
> 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] shared cache and SQLITE_MISUSE on sqlite3_step()

[D. Richard Hipp]

On Mar 4, 2009, at 9:35 AM, Marcus Grimm wrote:

> hi,
>
> OK, the value of p->magic is 519C2973 (VDBE_MAGIC_HALT)

That means the prepared statement has run to completion and needs to  
be reset using sqlite3_reset() before you continue.

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Marcus Grimm]

hi,

OK, the value of p->magic is 519C2973 (VDBE_MAGIC_HALT)

that should mean that "VDBE has completed execution"...

I don't know... in that case I should get a SQLITE_DONE
when stepping throu the result set, right ?

Just some additional info:
It is the last sqlite version, threadsafe is true and all
db connections are opened using SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX in 
the flags
for sqlite3_open_v2, it is a dual core PC.

kind regards

Marcus

D. Richard Hipp wrote:
> On Mar 4, 2009, at 8:31 AM, Marcus Grimm wrote:
> 
>> Richard,
>> Thanks for looking into this.
>>
>> I've placed some debug output in the sqlite2_step function
>> and I found that it returns SQLITE_MISUSE here:
>>
>> --
>>   static int sqlite3Step(Vdbe *p){
>>   sqlite3 *db;
>>   int rc;
>>
>>   assert(p);
>>   if( p->magic!=VDBE_MAGIC_RUN )
>>   {
>> return SQLITE_MISUSE;
>>   }
>> --
> 
> What is the value of p->magic at the point of failure?  (In hex, please)
> 
>>
>> I have no idea what this MAGIC_RUN means.
>>
>> Does it indicate that infact I'm using an allready released  
>> statement ?
>>
>> Please note that I'm not able to reproduce the problem when
>> I switch the shared cache off.
>>
>> Thank you
>>
>> Kind regards
>>
>> Marcus Grimm
>>
>>
>> D. Richard Hipp wrote:
>>> On Mar 4, 2009, at 5:19 AM, Marcus Grimm wrote:
>>>
 Hi all,

 I'm doing a little stress test on a server application and run into
 a problem when two threads are trying to access the database.
 Here is the background:
 1. shared cache is enabled prior open any DB connection.
 2. Each thread then opens a DB connection.
 3. Thread A just reads table entries continuosly by
   doing sqlite3_prepare_v2 and followed by some sqlite3_step to
 parse the result set.
   He then uses sqlite3_finalize and after a few ms he repeats
 everything.
 4. Thread B is triggered to update or insert some a new values
   in some tables.
   To do so I obtain an EXCLUSIVE transaction, do the insert/update
 and COMIT.

 Now, a problem arises occasionally that thread A gets an
 SQLITE_MISUSE when
 trying to call sqlite3_step, most likely because thread B currently
 writes into
 the DB, I guess.

 Now, my question:

 How to handle the SQLITE_MISUSE ?
>>> My guess is that the SQLITE_MISUSE is being returned because you are
>>> calling sqlite3_step() with a statement that has already been
>>> destroyed by sqlite3_finalize().
>>>
>>>
>>> D. Richard Hipp
>>> d...@hwaci.com
>>>
>>>
>>>
>>> ___
>>> 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...@hwaci.com
> 
> 
> 
> ___
> 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] shared cache and SQLITE_MISUSE on sqlite3_step()

[D. Richard Hipp]

On Mar 4, 2009, at 8:31 AM, Marcus Grimm wrote:

> Richard,
> Thanks for looking into this.
>
> I've placed some debug output in the sqlite2_step function
> and I found that it returns SQLITE_MISUSE here:
>
> --
>   static int sqlite3Step(Vdbe *p){
>   sqlite3 *db;
>   int rc;
>
>   assert(p);
>   if( p->magic!=VDBE_MAGIC_RUN )
>   {
> return SQLITE_MISUSE;
>   }
> --

What is the value of p->magic at the point of failure?  (In hex, please)

>
>
> I have no idea what this MAGIC_RUN means.
>
> Does it indicate that infact I'm using an allready released  
> statement ?
>
> Please note that I'm not able to reproduce the problem when
> I switch the shared cache off.
>
> Thank you
>
> Kind regards
>
> Marcus Grimm
>
>
> D. Richard Hipp wrote:
>> On Mar 4, 2009, at 5:19 AM, Marcus Grimm wrote:
>>
>>> Hi all,
>>>
>>> I'm doing a little stress test on a server application and run into
>>> a problem when two threads are trying to access the database.
>>> Here is the background:
>>> 1. shared cache is enabled prior open any DB connection.
>>> 2. Each thread then opens a DB connection.
>>> 3. Thread A just reads table entries continuosly by
>>>   doing sqlite3_prepare_v2 and followed by some sqlite3_step to
>>> parse the result set.
>>>   He then uses sqlite3_finalize and after a few ms he repeats
>>> everything.
>>> 4. Thread B is triggered to update or insert some a new values
>>>   in some tables.
>>>   To do so I obtain an EXCLUSIVE transaction, do the insert/update
>>> and COMIT.
>>>
>>> Now, a problem arises occasionally that thread A gets an
>>> SQLITE_MISUSE when
>>> trying to call sqlite3_step, most likely because thread B currently
>>> writes into
>>> the DB, I guess.
>>>
>>> Now, my question:
>>>
>>> How to handle the SQLITE_MISUSE ?
>>
>> My guess is that the SQLITE_MISUSE is being returned because you are
>> calling sqlite3_step() with a statement that has already been
>> destroyed by sqlite3_finalize().
>>
>>
>> D. Richard Hipp
>> d...@hwaci.com
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> -- 
> Marcus Grimm, MedCom GmbH Darmstadt, Rundeturmstr. 12, 64283 Darmstadt
> Tel: +49(0)6151-95147-10
> Fax: +49(0)6151-95147-20
> --
> MedCom slogans of the month:
> "Vacation ? -- Every day at MedCom is a paid vacation!"
> "Friday I have monday in my mind."
> "MedCom -- Every week a vacation, every day an event, every hour a  
> cliffhanger,
>every minute a climax."
> "Damned, it's weekend again!"
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Marcus Grimm]

Richard,
Thanks for looking into this.

I've placed some debug output in the sqlite2_step function
and I found that it returns SQLITE_MISUSE here:

--
   static int sqlite3Step(Vdbe *p){
   sqlite3 *db;
   int rc;

   assert(p);
   if( p->magic!=VDBE_MAGIC_RUN )
   {
 return SQLITE_MISUSE;
   }
--

I have no idea what this MAGIC_RUN means.

Does it indicate that infact I'm using an allready released statement ?

Please note that I'm not able to reproduce the problem when
I switch the shared cache off.

Thank you

Kind regards

Marcus Grimm


D. Richard Hipp wrote:
> On Mar 4, 2009, at 5:19 AM, Marcus Grimm wrote:
> 
>> Hi all,
>>
>> I'm doing a little stress test on a server application and run into
>> a problem when two threads are trying to access the database.
>> Here is the background:
>> 1. shared cache is enabled prior open any DB connection.
>> 2. Each thread then opens a DB connection.
>> 3. Thread A just reads table entries continuosly by
>>doing sqlite3_prepare_v2 and followed by some sqlite3_step to  
>> parse the result set.
>>He then uses sqlite3_finalize and after a few ms he repeats  
>> everything.
>> 4. Thread B is triggered to update or insert some a new values
>>in some tables.
>>To do so I obtain an EXCLUSIVE transaction, do the insert/update  
>> and COMIT.
>>
>> Now, a problem arises occasionally that thread A gets an  
>> SQLITE_MISUSE when
>> trying to call sqlite3_step, most likely because thread B currently  
>> writes into
>> the DB, I guess.
>>
>> Now, my question:
>>
>> How to handle the SQLITE_MISUSE ?
> 
> My guess is that the SQLITE_MISUSE is being returned because you are  
> calling sqlite3_step() with a statement that has already been  
> destroyed by sqlite3_finalize().
> 
> 
> D. Richard Hipp
> d...@hwaci.com
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

-- 
Marcus Grimm, MedCom GmbH Darmstadt, Rundeturmstr. 12, 64283 Darmstadt
Tel: +49(0)6151-95147-10
Fax: +49(0)6151-95147-20
--
MedCom slogans of the month:
"Vacation ? -- Every day at MedCom is a paid vacation!"
"Friday I have monday in my mind."
"MedCom -- Every week a vacation, every day an event, every hour a cliffhanger,
every minute a climax."
"Damned, it's weekend again!"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[D. Richard Hipp]

On Mar 4, 2009, at 5:19 AM, Marcus Grimm wrote:

> Hi all,
>
> I'm doing a little stress test on a server application and run into
> a problem when two threads are trying to access the database.
> Here is the background:
> 1. shared cache is enabled prior open any DB connection.
> 2. Each thread then opens a DB connection.
> 3. Thread A just reads table entries continuosly by
>doing sqlite3_prepare_v2 and followed by some sqlite3_step to  
> parse the result set.
>He then uses sqlite3_finalize and after a few ms he repeats  
> everything.
> 4. Thread B is triggered to update or insert some a new values
>in some tables.
>To do so I obtain an EXCLUSIVE transaction, do the insert/update  
> and COMIT.
>
> Now, a problem arises occasionally that thread A gets an  
> SQLITE_MISUSE when
> trying to call sqlite3_step, most likely because thread B currently  
> writes into
> the DB, I guess.
>
> Now, my question:
>
> How to handle the SQLITE_MISUSE ?

My guess is that the SQLITE_MISUSE is being returned because you are  
calling sqlite3_step() with a statement that has already been  
destroyed by sqlite3_finalize().


D. Richard Hipp
d...@hwaci.com



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

[sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Marcus Grimm]

Hi all,

I'm doing a little stress test on a server application and run into
a problem when two threads are trying to access the database.
Here is the background:
1. shared cache is enabled prior open any DB connection.
2. Each thread then opens a DB connection.
3. Thread A just reads table entries continuosly by
doing sqlite3_prepare_v2 and followed by some sqlite3_step to parse the 
result set.
He then uses sqlite3_finalize and after a few ms he repeats everything.
4. Thread B is triggered to update or insert some a new values
in some tables.
To do so I obtain an EXCLUSIVE transaction, do the insert/update and COMIT.

Now, a problem arises occasionally that thread A gets an SQLITE_MISUSE when
trying to call sqlite3_step, most likely because thread B currently writes into
the DB, I guess.

Now, my question:

How to handle the SQLITE_MISUSE ?
I'm prepared to deal with SQLITE_BUSY or SQLITE_LOCKED but I don't know what
to do when a sqlite3_step returns the SQLITE_MISUSE error.

Without the shared_cache enabled I don't get SQLITE_MISUSE but the usual
busy or locked states and I can just wait until the locking dissapeared.

Is it recommended to use additionally the "PRAGMA read_uncommitted = True;" ?

Maybe I should use an exclusive transaction on the simple "SELECT * FROM table"
calls but that will block any parallel reads, not very useful.

Actually, I don't understand why I don't get a LOCK state in shared
cache mode as well, although I think it is by design.

thank you

Marcus Grimm

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

Re: [sqlite] shared cache and journal files

[Dave Toll]

It's funny how explaining an issue to a mailing list can make you spot
the problem yourself... there was indeed a problem with my test code,
database connections were not being opened for every thread as I
expected them to be.

I love SQLite - the bug is always in my code :)

Cheers,
Dave.


-Original Message-
From: Dave Toll 
Sent: 29 January 2009 12:27
To: sqlite-users@sqlite.org
Subject: RE: shared cache and journal files

A little more detail on this issue:

It seems that my reading thread is not getting a shared lock on the
database before accessing the journal file. In pagerSharedLock() the
shared-cache Pager state is already PAGER_EXCLUSIVE because another
thread is writing to a different table in the same database - so a
shared lock is assumed and the function is a no-op. Does this imply some
misuse by my test code?

Cheers,
Dave.


-Original Message-
From: Dave Toll 
Sent: 28 January 2009 17:06
To: sqlite-users@sqlite.org
Subject: shared cache and journal files

Hello list

I have been using a modified version of threadtest1.c from the SQLite
test code to test the robustness of multi-threaded read/write
connections (SQLite 3.6.10) on an embedded platform (with my own VFS
implementation). The test code spawns 10 threads, each of which creates,
populates and queries its own table in one of 5 database files. No two
threads access the same table in the same database, but multiple threads
can access the same database. Every thread opens its own database
connection.

In shared cache mode (please correct me if I'm wrong), it seems that
every connection to the same database shares the same SQLite file handle
(sqlite3_file*). It is up to the platform's VFS to manage how this file
handle is used across multiple threads. Are journal file handles also
shared? I am seeing a situation where one thread starts a SELECT, which
triggers sqlite3OsRead() on a journal file that is still in use by
another thread writing to a different table in the same database (rough
callstack below in case it helps). Is this expected behaviour? As the
journal file was opened with SQLITE_OPEN_EXCLUSIVE, I had assumed that
it would not be accessed by multiple connections at the same time. Do I
have to handle concurrent access to journal files in my VFS?

sqlite3OsRead()
syncJournal()
pagerStress()
sqlite3PcacheFetch()
sqlite3PagerAcquire()
sqlite3BtreeGetPage()
getAndInitPage()
btreeCursor()
sqlite3BtreeCursor()
sqlite3VdbeExec()
sqlite3Step()
sqlite3_step()


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

Re: [sqlite] shared cache and journal files

[Dave Toll]

A little more detail on this issue:

It seems that my reading thread is not getting a shared lock on the
database before accessing the journal file. In pagerSharedLock() the
shared-cache Pager state is already PAGER_EXCLUSIVE because another
thread is writing to a different table in the same database - so a
shared lock is assumed and the function is a no-op. Does this imply some
misuse by my test code?

Cheers,
Dave.


-Original Message-
From: Dave Toll 
Sent: 28 January 2009 17:06
To: sqlite-users@sqlite.org
Subject: shared cache and journal files

Hello list

I have been using a modified version of threadtest1.c from the SQLite
test code to test the robustness of multi-threaded read/write
connections (SQLite 3.6.10) on an embedded platform (with my own VFS
implementation). The test code spawns 10 threads, each of which creates,
populates and queries its own table in one of 5 database files. No two
threads access the same table in the same database, but multiple threads
can access the same database. Every thread opens its own database
connection.

In shared cache mode (please correct me if I'm wrong), it seems that
every connection to the same database shares the same SQLite file handle
(sqlite3_file*). It is up to the platform's VFS to manage how this file
handle is used across multiple threads. Are journal file handles also
shared? I am seeing a situation where one thread starts a SELECT, which
triggers sqlite3OsRead() on a journal file that is still in use by
another thread writing to a different table in the same database (rough
callstack below in case it helps). Is this expected behaviour? As the
journal file was opened with SQLITE_OPEN_EXCLUSIVE, I had assumed that
it would not be accessed by multiple connections at the same time. Do I
have to handle concurrent access to journal files in my VFS?

sqlite3OsRead()
syncJournal()
pagerStress()
sqlite3PcacheFetch()
sqlite3PagerAcquire()
sqlite3BtreeGetPage()
getAndInitPage()
btreeCursor()
sqlite3BtreeCursor()
sqlite3VdbeExec()
sqlite3Step()
sqlite3_step()


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

[sqlite] shared cache and journal files

[Dave Toll]

Hello list

I have been using a modified version of threadtest1.c from the SQLite test code 
to test the robustness of multi-threaded read/write connections (SQLite 3.6.10) 
on an embedded platform (with my own VFS implementation). The test code spawns 
10 threads, each of which creates, populates and queries its own table in one 
of 5 database files. No two threads access the same table in the same database, 
but multiple threads can access the same database. Every thread opens its own 
database connection.

In shared cache mode (please correct me if I'm wrong), it seems that every 
connection to the same database shares the same SQLite file handle 
(sqlite3_file*). It is up to the platform's VFS to manage how this file handle 
is used across multiple threads. Are journal file handles also shared? I am 
seeing a situation where one thread starts a SELECT, which triggers 
sqlite3OsRead() on a journal file that is still in use by another thread 
writing to a different table in the same database (rough callstack below in 
case it helps). Is this expected behaviour? As the journal file was opened with 
SQLITE_OPEN_EXCLUSIVE, I had assumed that it would not be accessed by multiple 
connections at the same time. Do I have to handle concurrent access to journal 
files in my VFS?

sqlite3OsRead()
syncJournal()
pagerStress()
sqlite3PcacheFetch()
sqlite3PagerAcquire()
sqlite3BtreeGetPage()
getAndInitPage()
btreeCursor()
sqlite3BtreeCursor()
sqlite3VdbeExec()
sqlite3Step()
sqlite3_step()


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

Re: [sqlite] Shared cache clarification.

[Hynes, Tom]

Excellent, that's what I was hoping for.  Thanks, Dan!

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dan
Sent: Wednesday, October 22, 2008 5:54 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Shared cache clarification.


On Oct 22, 2008, at 11:32 PM, Hynes, Tom wrote:

> Hello,
>
>
>
> I noticed that the documentation for PRAGMA read_uncommitted in
> http://www.sqlite.org/pragma.html says:
>
>
>
> "...Cache sharing is enabled using the sqlite3_enable_shared_cache()
> <http://www.sqlite.org/c3ref/enable_shared_cache.html>  API and is  
> only
> available between connections running the same thread. ..."
>
>
>
> But, my impression from reading the sqlite3_enable_shared_cache()
> <http://www.sqlite.org/c3ref/enable_shared_cache.html>   
> documentation is
> that this is no longer true:
>
> "... Cache sharing is enabled and disabled for an entire process. This
> is a change as of SQLite version 3.5.0. In prior versions of SQLite,
> sharing was enabled or disabled for each thread separately. ..."
>
> If I am understanding this correctly, it seems that with 3.5.0 and
> beyond connections do *not* have to live in the same thread to share a
> cache.   So, is the PRAGMA documentation just out of date, or am I
> misunderstanding this?

The pragma documentation is out of date. Pager caches may now be shared
between connections opened and used by different threads.

Dan.

___
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] Shared cache clarification.

[Dan]

On Oct 22, 2008, at 11:32 PM, Hynes, Tom wrote:

> Hello,
>
>
>
> I noticed that the documentation for PRAGMA read_uncommitted in
> http://www.sqlite.org/pragma.html says:
>
>
>
> "...Cache sharing is enabled using the sqlite3_enable_shared_cache()
>   API and is  
> only
> available between connections running the same thread. ..."
>
>
>
> But, my impression from reading the sqlite3_enable_shared_cache()
>    
> documentation is
> that this is no longer true:
>
> "... Cache sharing is enabled and disabled for an entire process. This
> is a change as of SQLite version 3.5.0. In prior versions of SQLite,
> sharing was enabled or disabled for each thread separately. ..."
>
> If I am understanding this correctly, it seems that with 3.5.0 and
> beyond connections do *not* have to live in the same thread to share a
> cache.   So, is the PRAGMA documentation just out of date, or am I
> misunderstanding this?

The pragma documentation is out of date. Pager caches may now be shared
between connections opened and used by different threads.

Dan.

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

[sqlite] Shared cache question

[Robert Simpson]

What should be the expected behavior of existing connections when
sqlite3_enable_shared_cache() is enabled, and then another database is
ATTACH'ed to the pre-cache-enabled connection?

 

And further complicate things by saying that the attached database is using
vtables and the original connection is not.

 

What I'm wondering is . could we enhance sqlite3_open_v2() to add a flag for
shared cache enabled, rather than have it be a global setting?  I'd like to
have some connections on a shared cache, and some not . and be able to
attach non-cache-enabled databases to existing connections and have fts3
work.

 

Robert

 

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

Re: [sqlite] Shared cache benefit question

[Ken]

Just give it a try and see what happens. You just need to enable the shared 
cache once.
   
  I'd think the blocking would not be any different with the shared cache 
enabled. But you should get reduced I/O load since the cache will be larger and 
accessible to all threads.
   
   
  HTH,
  Ken

Doug <[EMAIL PROTECTED]> wrote:
  I have a heavily threaded app (I know, evil) where there might be 50 threads
accessing 10 databases. Each thread always calls sqlite3_open when it
starts working with a database and sqlite3_close when it's done (so no
sharing of handles across threads). A thread might have two or more handles
open to separate databases at once. And separate threads can be working on
the same database at once. It's extremely rare for a second process to ever
access the databases. I'd guess typically 70% of the database activity is
INSERTS or UPDATES, with 25% simple single-table SELECTS, and the occasional
large SELECT (joining tables, etc).



Right now every database connection has its own page cache, all the default
size. Some threads do very little work and don't use their full cache,
where others could definitely benefit from a larger cache. I'd like to have
a single, quite large, cache that the threads share with the hopes that the
'smaller' threads would use what they need and the 'larger' threads would be
able to take advantage of the larger cache size available to them.



Given that, is this a good scenario for using the shared cache? I've read
http://www.sqlite.org/sharedcache.html but I'm not confident enough in my
understanding to know whether I'll run into more or less blocking. 



Thanks for any insight.



Doug





___
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] Shared cache benefit question

[Doug]

I have a heavily threaded app (I know, evil) where there might be 50 threads
accessing 10 databases.  Each thread always calls sqlite3_open when it
starts working with a database and sqlite3_close when it's done (so no
sharing of handles across threads).  A thread might have two or more handles
open to separate databases at once.  And separate threads can be working on
the same database at once.  It's extremely rare for a second process to ever
access the databases.  I'd guess typically 70% of the database activity is
INSERTS or UPDATES, with 25% simple single-table SELECTS, and the occasional
large SELECT (joining tables, etc).

 

Right now every database connection has its own page cache, all the default
size.  Some threads do very little work and don't use their full cache,
where others could definitely benefit from a larger cache.  I'd like to have
a single, quite large, cache that the threads share with the hopes that the
'smaller' threads would use what they need and the 'larger' threads would be
able to take advantage of the larger cache size available to them.

 

Given that, is this a good scenario for using the shared cache?  I've read
http://www.sqlite.org/sharedcache.html but I'm not confident enough in my
understanding to know whether I'll run into more or less blocking.  

 

Thanks for any insight.

 

Doug

 

 

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

Re: [sqlite] Shared Cache for Processes

[Ed Pasma]

Hi,

Speaking only as a non-professional, I still try to answer. I don't  
want to comment on the benefits of shared cache mode now, but only on  
the question if it can be enabled in Apache. And I believe it can. As  
you say Apache pre-forks different processes, but within each process  
it pre-establishes different threads and I think the default is in  
the order of 100. So practically you will only use a single process  
and hence can easily enable shared-cache mode. This assumes that your  
application is loaded into Apache, and does not run via cgi.


Regards, Edzard Pasma

Op 24-jan-2008, om 16:48 heeft Brandon, Nicholas (UK) het volgende  
geschreven:




Hi all,

Could the 'Shared Cache' option in SQLite theoretically improve the
performance of the db if used by multiple processes? The  
application in

particular is Apache using pre-fork processes accessing the same db.
The info at http://www.sqlite.org/sharedcache.html seems to  
indicate it

could benefit threads only.

I believe it would not but would like confirmation from someone else.

Thanks
Nick


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.



-- 
---

To unsubscribe, send email to [EMAIL PROTECTED]
-- 
---







-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Shared Cache for Processes

[Brandon, Nicholas (UK)]

Hi all,

Could the 'Shared Cache' option in SQLite theoretically improve the
performance of the db if used by multiple processes? The application in
particular is Apache using pre-fork processes accessing the same db.
The info at http://www.sqlite.org/sharedcache.html seems to indicate it
could benefit threads only.

I believe it would not but would like confirmation from someone else.

Thanks
Nick


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache and 'no such table' issue

[Pathompong Puengrostham]

On Dec 27, 2007 10:15 PM, Dan <[EMAIL PROTECTED]> wrote:

> Should be fixed by:
>
>http://www.sqlite.org/cvstrac/chngview?cn=4643
>
> Please post if you find this is not the case (or if you find it is).
>

It fixes the problem. Thanks a lot for your help.

Jay

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache and 'no such table' issue

[Dan]

On Dec 27, 2007, at 5:51 PM, Pathompong Puengrostham wrote:


I think I found a bug in sqlite3_close but don't know how to fix it.
There is a race condition between sqlite3SchemaFree and sqlite3Init.
The problem is if there are two threads with its own connection to the
same database file with shared-cache mode enabled. Db.pSchema of both
connections point to the same schema. When the first thread is closing
the connection and in the middle of sqlite3SchemaFree, where all the
hash tables are freed but DB_SchemaLoaded in pSchema->flags is not
cleared yet. If the other thread calls to sqlite3LocateTable at this
time, it'll return no such table. Because sqlite3Init will see that
DB_SchemaLoaded is still set and will not do anything.

The only mutex I see involved is db->mutex which not help in this
case. Should there be a pSchema->mutex? Does anyone have this same
problem?


Should be fixed by:

  http://www.sqlite.org/cvstrac/chngview?cn=4643

Please post if you find this is not the case (or if you find it is).

Dan.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache and 'no such table' issue

[Pathompong Puengrostham]

I think I found a bug in sqlite3_close but don't know how to fix it.
There is a race condition between sqlite3SchemaFree and sqlite3Init.
The problem is if there are two threads with its own connection to the
same database file with shared-cache mode enabled. Db.pSchema of both
connections point to the same schema. When the first thread is closing
the connection and in the middle of sqlite3SchemaFree, where all the
hash tables are freed but DB_SchemaLoaded in pSchema->flags is not
cleared yet. If the other thread calls to sqlite3LocateTable at this
time, it'll return no such table. Because sqlite3Init will see that
DB_SchemaLoaded is still set and will not do anything.

The only mutex I see involved is db->mutex which not help in this
case. Should there be a pSchema->mutex? Does anyone have this same
problem?

Jay

PS.
Sorry for the bad English. It's not my native language.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache and 'no such table' issue

[Darío Mariani]

I found that the sqlite3_open call is not thread safe even if you open
different files in separate connections. Cannot tell you about
sqlite3_close but may be the same problem.
Darío

On Dec 26, 2007 6:40 AM, Pathompong Puengrostham <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I have a multi-threaded program. Each thread opens a connection to the same
> database file. It runs fine most of the time but sometime I get a 'no such
> table' error from a SQL select statement. It appears that if one thread
> close the database connection, it closes all the schema without checking
> nRef in BtShared. This make other threads receive a 'no such table' error.
>
> Do I get it right that schema is also shared together with Btree? When
> shared cache mode is enabled, does SQLite really share cache within one
> thread and not between other threads? I don't know much about internal of
> SQLite. But from looking at the source code, there is only one
> sqlite3SharedCacheList variable that is shared with every threads.
>
> Jay
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] shared cache and 'no such table' issue

[Pathompong Puengrostham]

Hi,

I have a multi-threaded program. Each thread opens a connection to the same
database file. It runs fine most of the time but sometime I get a 'no such
table' error from a SQL select statement. It appears that if one thread
close the database connection, it closes all the schema without checking
nRef in BtShared. This make other threads receive a 'no such table' error.

Do I get it right that schema is also shared together with Btree? When
shared cache mode is enabled, does SQLite really share cache within one
thread and not between other threads? I don't know much about internal of
SQLite. But from looking at the source code, there is only one
sqlite3SharedCacheList variable that is shared with every threads.

Jay

Re: [sqlite] shared cache mode locking

[Ken]

So did my post. We are talking about the same thing. Definately confusing, at 
least to me..

The problem exists wherein you have two shared connections and one connection 
performs a begin exclusive... The other connection was just ignoring the 
exclusivity lock and continuing on its merry way and acquiring a table level 
lock. Which causes the first connection to get a SQLITE_LOCKED upon an insert 
to a table that the second connection is reading. The documentation is quite 
clear that once a connection acquires an EXCLUSIVE lock that it has controll 
and should not be locked out from writing by any other connections.

The dual locking model (prior to the resolution) is ambiguous and  could  
possibly lead application to deadlocks.

These are just my thoughts on the matter, and are probably not 100% correct.
Ken

Ed Pasma <[EMAIL PROTECTED]> wrote: No, you did not confuse me. We are talking 
about different things it  
appears. My post refers to the shared-cache locking model (http:// 
sqlite.org/sharedcache.html). The document is clear by itself. What  
makes it confusing, is that a shared cache instance exist as a single  
normal connection which may coincide with other, "normal" database  
connections. Quite a generous feature. But it means that the two  
locking models do apply at the same time. The joint connections  
within a shared cache are subject to the regular locking model in  
relation to possible other connections to the same database.  
Confusing or not?

Ken wrote:

> Ed,
>
> Sorry if I confused you, a "Write" lock is really an EXCLUSIVE lock  
> per sqlite documentation. I used the two interchangeably, pardon my  
> error.
>
> A begin exclusive indicates the beginning of a transaction, It  
> escalates the database lock to an EXCLUSIVE lock. The begin  
> transaction does not immediately do this, rather it waits until the  
> buffer cache spills to disk. At this point it attempts to escalate  
> the Reserved lock to a Pending then an Exclusive  lock.
>
> There is only 1 type of EXCLUSIVE (write) lock,  It is database  
> wide and is all or nothing.  Once you have the lock, it prevents  
> other access to the DB.
>
> Ken
>
>
> Ed Pasma  wrote: The ticket has already been  
> resolved, I see. So it has been
> considered a bug. In my earlier reply I tried to defend the current
> behavour to be in line with the document, http://sqlite.org/
> sharedcache.html. I'm happy to change my mind now. Only I miss
> something in the model as described in the document. This may  
> either be:
> - exclusive transactions as a new kind of transactions, apart form
> read- and write-transactions
> or
> - database-level locking as a new level above transaction-level  
> locking.
> May be this suggestion is too naive, anyway it helps me explain the
> wonderful cache sharing.
>
> Ken wrote:
>
>> Ed,
>>
>> Dan opened a ticket. I agree the documentation isn't clear on the
>> Exlusive locking state.
>>
>> Not really sure, if this is by design or a bug at this stage. I do
>> think its a great feature of the Shared cache mode to allow table
>> level locking. But I'm curious with this table level locking what
>> would happen if two threads performed writes to two seperate tables
>> concurrently using only a begin immediate.
>>
>> Thread a writes to tab1,
>> Thread b writes to tab2,  (Is this allowed ? or is a sqlite_locked
>> kicked returned?)
>>
>> If it is allowed then would there be two journal files concurrently
>> existing? And What happens during a crash with two journals ?
>>
>> This gets complicated very quickly.
>>
>> Ken
>>
>> Ed Pasma  wrote: Hello,`
>> Empirically I found that it is exactly true.
>> Must admit I'm confused but may it is in line with the Shared-Cache
>> locking model.
>> This does not mention the EXCLUSIVE locking state.
>> The most 'secure' locking state it mentions is a write-transaction
>> and this can coexist with read-transactions from others.
>> Thus "begin exclusive" starts a write-transaction and the on-going
>> read does not interfere.
>> The error message seems to clarify the situation further: database
>> table is locked.  Thus the collision occurs at the table-level. And
>> yes, taking different tables for read and write, it does not occur.
>> Practically this may not help very much. But may be the following
>> does in case you have a busy_timeout setting.
>> When having Shared-Cache mode enabled, the timeout setting appears to
>> be ignored by SQLite. This makes locking situations surface rather
>> soon, also when there is no dead-lock.
>> The situation may be handled by a programmatic retry?
>> Regards, Ed
>>
>> Op 19-dec-2007, om 19:12 heeft Ken het volgende geschreven:
>>
>>> Some additional info:
>>>
>>> when the sqlite_lock is returned there is another thread that
>>> appears to be reading the same table. Does the sqlite3 step return
>>> sqlite_locked in this case?
>>>
>>> Thanks,
>>> Ken
>>>
>>>
>>> Ken  wrote:
>>> While using the new 3.5.4 

Re: [sqlite] shared cache mode locking

[Ed Pasma]

No, you did not confuse me. We are talking about different things it  
appears. My post refers to the shared-cache locking model (http:// 
sqlite.org/sharedcache.html). The document is clear by itself. What  
makes it confusing, is that a shared cache instance exist as a single  
normal connection which may coincide with other, "normal" database  
connections. Quite a generous feature. But it means that the two  
locking models do apply at the same time. The joint connections  
within a shared cache are subject to the regular locking model in  
relation to possible other connections to the same database.  
Confusing or not?


Ken wrote:


Ed,

Sorry if I confused you, a "Write" lock is really an EXCLUSIVE lock  
per sqlite documentation. I used the two interchangeably, pardon my  
error.


A begin exclusive indicates the beginning of a transaction, It  
escalates the database lock to an EXCLUSIVE lock. The begin  
transaction does not immediately do this, rather it waits until the  
buffer cache spills to disk. At this point it attempts to escalate  
the Reserved lock to a Pending then an Exclusive  lock.


There is only 1 type of EXCLUSIVE (write) lock,  It is database  
wide and is all or nothing.  Once you have the lock, it prevents  
other access to the DB.


Ken


Ed Pasma <[EMAIL PROTECTED]> wrote: The ticket has already been  
resolved, I see. So it has been

considered a bug. In my earlier reply I tried to defend the current
behavour to be in line with the document, http://sqlite.org/
sharedcache.html. I'm happy to change my mind now. Only I miss
something in the model as described in the document. This may  
either be:

- exclusive transactions as a new kind of transactions, apart form
read- and write-transactions
or
- database-level locking as a new level above transaction-level  
locking.

May be this suggestion is too naive, anyway it helps me explain the
wonderful cache sharing.

Ken wrote:


Ed,

Dan opened a ticket. I agree the documentation isn't clear on the
Exlusive locking state.

Not really sure, if this is by design or a bug at this stage. I do
think its a great feature of the Shared cache mode to allow table
level locking. But I'm curious with this table level locking what
would happen if two threads performed writes to two seperate tables
concurrently using only a begin immediate.

Thread a writes to tab1,
Thread b writes to tab2,  (Is this allowed ? or is a sqlite_locked
kicked returned?)

If it is allowed then would there be two journal files concurrently
existing? And What happens during a crash with two journals ?

This gets complicated very quickly.

Ken

Ed Pasma  wrote: Hello,`
Empirically I found that it is exactly true.
Must admit I'm confused but may it is in line with the Shared-Cache
locking model.
This does not mention the EXCLUSIVE locking state.
The most 'secure' locking state it mentions is a write-transaction
and this can coexist with read-transactions from others.
Thus "begin exclusive" starts a write-transaction and the on-going
read does not interfere.
The error message seems to clarify the situation further: database
table is locked.  Thus the collision occurs at the table-level. And
yes, taking different tables for read and write, it does not occur.
Practically this may not help very much. But may be the following
does in case you have a busy_timeout setting.
When having Shared-Cache mode enabled, the timeout setting appears to
be ignored by SQLite. This makes locking situations surface rather
soon, also when there is no dead-lock.
The situation may be handled by a programmatic retry?
Regards, Ed

Op 19-dec-2007, om 19:12 heeft Ken het volgende geschreven:


Some additional info:

when the sqlite_lock is returned there is another thread that
appears to be reading the same table. Does the sqlite3 step return
sqlite_locked in this case?

Thanks,
Ken


Ken  wrote:
While using the new 3.5.4 sqlite3_enable_shared_cache I ran into a
strange lock situation.

  SQLITE_LOCK is returned from an insert statement, even though
the thread/connection performed a successful "begin exclusive"
transaction.

   begin exclusive
insert into table...   ---> returns SQLITE_LOCKED

Is it possible for both connections to begin exclusive transactions
whilst having the shared cache anabled?

Thanks,
ken






- 
-

---
To unsubscribe, send email to [EMAIL PROTECTED]
- 
-

---






-- 
---

To unsubscribe, send email to [EMAIL PROTECTED]
-- 
---







-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache mode locking

[Ken]

Ed,

Sorry if I confused you, a "Write" lock is really an EXCLUSIVE lock per sqlite 
documentation. I used the two interchangeably, pardon my error.

A begin exclusive indicates the beginning of a transaction, It escalates the 
database lock to an EXCLUSIVE lock. The begin transaction does not immediately 
do this, rather it waits until the buffer cache spills to disk. At this point 
it attempts to escalate the Reserved lock to a Pending then an Exclusive  lock. 

There is only 1 type of EXCLUSIVE (write) lock,  It is database wide and is all 
or nothing.  Once you have the lock, it prevents other access to the DB.

Ken


Ed Pasma <[EMAIL PROTECTED]> wrote: The ticket has already been resolved, I 
see. So it has been  
considered a bug. In my earlier reply I tried to defend the current  
behavour to be in line with the document, http://sqlite.org/ 
sharedcache.html. I'm happy to change my mind now. Only I miss  
something in the model as described in the document. This may either be:
- exclusive transactions as a new kind of transactions, apart form  
read- and write-transactions
or
- database-level locking as a new level above transaction-level locking.
May be this suggestion is too naive, anyway it helps me explain the  
wonderful cache sharing.

Ken wrote:

> Ed,
>
> Dan opened a ticket. I agree the documentation isn't clear on the  
> Exlusive locking state.
>
> Not really sure, if this is by design or a bug at this stage. I do  
> think its a great feature of the Shared cache mode to allow table  
> level locking. But I'm curious with this table level locking what  
> would happen if two threads performed writes to two seperate tables  
> concurrently using only a begin immediate.
>
> Thread a writes to tab1,
> Thread b writes to tab2,  (Is this allowed ? or is a sqlite_locked  
> kicked returned?)
>
> If it is allowed then would there be two journal files concurrently  
> existing? And What happens during a crash with two journals ?
>
> This gets complicated very quickly.
>
> Ken
>
> Ed Pasma  wrote: Hello,`
> Empirically I found that it is exactly true.
> Must admit I'm confused but may it is in line with the Shared-Cache
> locking model.
> This does not mention the EXCLUSIVE locking state.
> The most 'secure' locking state it mentions is a write-transaction
> and this can coexist with read-transactions from others.
> Thus "begin exclusive" starts a write-transaction and the on-going
> read does not interfere.
> The error message seems to clarify the situation further: database
> table is locked.  Thus the collision occurs at the table-level. And
> yes, taking different tables for read and write, it does not occur.
> Practically this may not help very much. But may be the following
> does in case you have a busy_timeout setting.
> When having Shared-Cache mode enabled, the timeout setting appears to
> be ignored by SQLite. This makes locking situations surface rather
> soon, also when there is no dead-lock.
> The situation may be handled by a programmatic retry?
> Regards, Ed
>
> Op 19-dec-2007, om 19:12 heeft Ken het volgende geschreven:
>
>> Some additional info:
>>
>> when the sqlite_lock is returned there is another thread that
>> appears to be reading the same table. Does the sqlite3 step return
>> sqlite_locked in this case?
>>
>> Thanks,
>> Ken
>>
>>
>> Ken  wrote:
>> While using the new 3.5.4 sqlite3_enable_shared_cache I ran into a
>> strange lock situation.
>>
>>   SQLITE_LOCK is returned from an insert statement, even though
>> the thread/connection performed a successful "begin exclusive"
>> transaction.
>>
>>begin exclusive
>> insert into table...   ---> returns SQLITE_LOCKED
>>
>> Is it possible for both connections to begin exclusive transactions
>> whilst having the shared cache anabled?
>>
>> Thanks,
>> ken
>>
>>
>
>
>
> -- 
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> -- 
> ---
>
>



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache mode locking

[Ed Pasma]

The ticket has already been resolved, I see. So it has been  
considered a bug. In my earlier reply I tried to defend the current  
behavour to be in line with the document, http://sqlite.org/ 
sharedcache.html. I'm happy to change my mind now. Only I miss  
something in the model as described in the document. This may either be:
- exclusive transactions as a new kind of transactions, apart form  
read- and write-transactions

or
- database-level locking as a new level above transaction-level locking.
May be this suggestion is too naive, anyway it helps me explain the  
wonderful cache sharing.


Ken wrote:


Ed,

Dan opened a ticket. I agree the documentation isn't clear on the  
Exlusive locking state.


Not really sure, if this is by design or a bug at this stage. I do  
think its a great feature of the Shared cache mode to allow table  
level locking. But I'm curious with this table level locking what  
would happen if two threads performed writes to two seperate tables  
concurrently using only a begin immediate.


Thread a writes to tab1,
Thread b writes to tab2,  (Is this allowed ? or is a sqlite_locked  
kicked returned?)


If it is allowed then would there be two journal files concurrently  
existing? And What happens during a crash with two journals ?


This gets complicated very quickly.

Ken

Ed Pasma <[EMAIL PROTECTED]> wrote: Hello,`
Empirically I found that it is exactly true.
Must admit I'm confused but may it is in line with the Shared-Cache
locking model.
This does not mention the EXCLUSIVE locking state.
The most 'secure' locking state it mentions is a write-transaction
and this can coexist with read-transactions from others.
Thus "begin exclusive" starts a write-transaction and the on-going
read does not interfere.
The error message seems to clarify the situation further: database
table is locked.  Thus the collision occurs at the table-level. And
yes, taking different tables for read and write, it does not occur.
Practically this may not help very much. But may be the following
does in case you have a busy_timeout setting.
When having Shared-Cache mode enabled, the timeout setting appears to
be ignored by SQLite. This makes locking situations surface rather
soon, also when there is no dead-lock.
The situation may be handled by a programmatic retry?
Regards, Ed

Op 19-dec-2007, om 19:12 heeft Ken het volgende geschreven:


Some additional info:

when the sqlite_lock is returned there is another thread that
appears to be reading the same table. Does the sqlite3 step return
sqlite_locked in this case?

Thanks,
Ken


Ken  wrote:
While using the new 3.5.4 sqlite3_enable_shared_cache I ran into a
strange lock situation.

  SQLITE_LOCK is returned from an insert statement, even though
the thread/connection performed a successful "begin exclusive"
transaction.

   begin exclusive
insert into table...   ---> returns SQLITE_LOCKED

Is it possible for both connections to begin exclusive transactions
whilst having the shared cache anabled?

Thanks,
ken






-- 
---

To unsubscribe, send email to [EMAIL PROTECTED]
-- 
---







-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache mode locking

[Ken]

Ed,

Dan opened a ticket. I agree the documentation isn't clear on the Exlusive 
locking state. 

Not really sure, if this is by design or a bug at this stage. I do think its a 
great feature of the Shared cache mode to allow table level locking. But I'm 
curious with this table level locking what would happen if two threads 
performed writes to two seperate tables concurrently using only a begin 
immediate.

Thread a writes to tab1,   
Thread b writes to tab2,  (Is this allowed ? or is a sqlite_locked kicked 
returned?)

If it is allowed then would there be two journal files concurrently existing? 
And What happens during a crash with two journals ? 

This gets complicated very quickly.

Ken

Ed Pasma <[EMAIL PROTECTED]> wrote: Hello,`
Empirically I found that it is exactly true.
Must admit I'm confused but may it is in line with the Shared-Cache  
locking model.
This does not mention the EXCLUSIVE locking state.
The most 'secure' locking state it mentions is a write-transaction  
and this can coexist with read-transactions from others.
Thus "begin exclusive" starts a write-transaction and the on-going  
read does not interfere.
The error message seems to clarify the situation further: database  
table is locked.  Thus the collision occurs at the table-level. And  
yes, taking different tables for read and write, it does not occur.
Practically this may not help very much. But may be the following  
does in case you have a busy_timeout setting.
When having Shared-Cache mode enabled, the timeout setting appears to  
be ignored by SQLite. This makes locking situations surface rather  
soon, also when there is no dead-lock.
The situation may be handled by a programmatic retry?
Regards, Ed

Op 19-dec-2007, om 19:12 heeft Ken het volgende geschreven:

> Some additional info:
>
> when the sqlite_lock is returned there is another thread that  
> appears to be reading the same table. Does the sqlite3 step return  
> sqlite_locked in this case?
>
> Thanks,
> Ken
>
>
> Ken  wrote:
> While using the new 3.5.4 sqlite3_enable_shared_cache I ran into a  
> strange lock situation.
>
>   SQLITE_LOCK is returned from an insert statement, even though  
> the thread/connection performed a successful "begin exclusive"  
> transaction.
>
>begin exclusive
> insert into table...   ---> returns SQLITE_LOCKED
>
> Is it possible for both connections to begin exclusive transactions  
> whilst having the shared cache anabled?
>
> Thanks,
> ken
>
>



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache mode locking

[Ed Pasma]

Hello,`
Empirically I found that it is exactly true.
Must admit I'm confused but may it is in line with the Shared-Cache  
locking model.

This does not mention the EXCLUSIVE locking state.
The most 'secure' locking state it mentions is a write-transaction  
and this can coexist with read-transactions from others.
Thus "begin exclusive" starts a write-transaction and the on-going  
read does not interfere.
The error message seems to clarify the situation further: database  
table is locked.  Thus the collision occurs at the table-level. And  
yes, taking different tables for read and write, it does not occur.
Practically this may not help very much. But may be the following  
does in case you have a busy_timeout setting.
When having Shared-Cache mode enabled, the timeout setting appears to  
be ignored by SQLite. This makes locking situations surface rather  
soon, also when there is no dead-lock.

The situation may be handled by a programmatic retry?
Regards, Ed

Op 19-dec-2007, om 19:12 heeft Ken het volgende geschreven:


Some additional info:

when the sqlite_lock is returned there is another thread that  
appears to be reading the same table. Does the sqlite3 step return  
sqlite_locked in this case?


Thanks,
Ken


Ken <[EMAIL PROTECTED]> wrote:
While using the new 3.5.4 sqlite3_enable_shared_cache I ran into a  
strange lock situation.


  SQLITE_LOCK is returned from an insert statement, even though  
the thread/connection performed a successful "begin exclusive"  
transaction.


   begin exclusive
insert into table...   ---> returns SQLITE_LOCKED

Is it possible for both connections to begin exclusive transactions  
whilst having the shared cache anabled?


Thanks,
ken






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache mode locking

[Ken]

Some additional info:

when the sqlite_lock is returned there is another thread that appears to be 
reading the same table. Does the sqlite3 step return sqlite_locked in this case?

Thanks,
Ken


Ken <[EMAIL PROTECTED]> wrote: 
While using the new 3.5.4 sqlite3_enable_shared_cache I ran into a strange lock 
situation.

  SQLITE_LOCK is returned from an insert statement, even though the 
thread/connection performed a successful "begin exclusive" transaction.
 
   begin exclusive
insert into table...   ---> returns SQLITE_LOCKED

Is it possible for both connections to begin exclusive transactions whilst 
having the shared cache anabled?

Thanks,
ken
 

[sqlite] shared cache mode locking

[Ken]

While using the new 3.5.4 sqlite3_enable_shared_cache I ran into a strange lock 
situation.

  SQLITE_LOCK is returned from an insert statement, even though the 
thread/connection performed a successful "begin exclusive" transaction.
 
   begin exclusive
insert into table...   ---> returns SQLITE_LOCKED

Is it possible for both connections to begin exclusive transactions whilst 
having the shared cache anabled?

Thanks,
ken
 

[sqlite] Shared cache and PRAGMA cache_size

[Doug]

I have a highly threaded application that writes to a number of database
files.  Each thread opens a database file, uses its own handle, and then
closes that handle when finished.  As the threads come and go, some will
likely overlap with others using the same database, so it seems like the
process-wide shared cache available in 3.5 would be a good optimization.
I'm wondering if I should adjust the PRAGMA cache_size setting.

 

Imagine 5 threads each hitting a single database, and 5 other threads each
hitting individual databases.  That would be 10 x 2000 (default cache size)
= 20,000 pages of cache by default in the process.  If I call
sqlite3_enable_shared_cache for the process, I assume that there is now only
one cache that is still the default size of 2000 pages.  It seems like there
would be some cache thrashing since the same amount of work is being done,
but squeezed into a smaller cache.  I'm assuming it would make sense to at
least set the cache size to 6 x 2000 (2000 for the 5 independent threads
plus 2000 for the 5 threads sharing a database) and probably higher.

 

Are there any good rules of thumb here?  Are my assumptions faulty?

 

Thanks for any input.

 

 

Re: [sqlite] shared cache/ test_server.c

[John Stanton]

Scott Hess wrote:

On 7/27/07, John Stanton <[EMAIL PROTECTED]> wrote:


Scott Hess wrote:


On 7/26/07, Richard Klein <[EMAIL PROTECTED]> wrote:


According to the Mozilla article referenced above, it's even worse than
that:  *All* cache pages, dirty or not, are freed at the end of *every*
transaction, even if the transaction consisted of only read operations.


I believe this is no longer true, as of a couple months ago.  Now
SQLite tracks whether any other database connection has written data,
and doesn't invalidate the pages if nobody else is writing.

I think this is completely orthogonal to shared cache.


A few questions:
 o Is this behaviour general or just in shared cache mode?  You imply
that it is the general case.
 o If it is general, how does it detect a stale cache?  By an indicator
on the file?



The sqlite changes list has the following back in April:



Do not flush the page cache (and thus avoiding a cache refill)
unless another process changes the underlying database file.



My understanding is that writes to the database increment a counter
somewhere, and after getting the shared lock readers can check that
counter to see if they can continue using their existing cache or not.
 I think the writer knows to increment both counters, so if you only
have one writer it doesn't have to flush (one writer with many readers
would flush the reader's caches).

This is the general case.  I don't know how it impacts shared cache
mode, but I would _imagine_ that the shared cache would operate the
same way.  I've not used shared cache mode anywhere, though, so my
opinion really is in the realm of imagination :-).

-scott


Thankyou.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache/ test_server.c

[Scott Hess]

On 7/27/07, John Stanton <[EMAIL PROTECTED]> wrote:
> Scott Hess wrote:
> > On 7/26/07, Richard Klein <[EMAIL PROTECTED]> wrote:
> >>According to the Mozilla article referenced above, it's even worse than
> >>that:  *All* cache pages, dirty or not, are freed at the end of *every*
> >>transaction, even if the transaction consisted of only read operations.
> >
> > I believe this is no longer true, as of a couple months ago.  Now
> > SQLite tracks whether any other database connection has written data,
> > and doesn't invalidate the pages if nobody else is writing.
> >
> > I think this is completely orthogonal to shared cache.
>
> A few questions:
>   o Is this behaviour general or just in shared cache mode?  You imply
> that it is the general case.
>   o If it is general, how does it detect a stale cache?  By an indicator
> on the file?

The sqlite changes list has the following back in April:

> Do not flush the page cache (and thus avoiding a cache refill)
> unless another process changes the underlying database file.

My understanding is that writes to the database increment a counter
somewhere, and after getting the shared lock readers can check that
counter to see if they can continue using their existing cache or not.
 I think the writer knows to increment both counters, so if you only
have one writer it doesn't have to flush (one writer with many readers
would flush the reader's caches).

This is the general case.  I don't know how it impacts shared cache
mode, but I would _imagine_ that the shared cache would operate the
same way.  I've not used shared cache mode anywhere, though, so my
opinion really is in the realm of imagination :-).

-scott

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache/ test_server.c

[John Stanton]

Scott Hess wrote:

On 7/26/07, Richard Klein <[EMAIL PROTECTED]> wrote:


According to the Mozilla article referenced above, it's even worse than
that:  *All* cache pages, dirty or not, are freed at the end of *every*
transaction, even if the transaction consisted of only read operations.



I believe this is no longer true, as of a couple months ago.  Now
SQLite tracks whether any other database connection has written data,
and doesn't invalidate the pages if nobody else is writing.

I think this is completely orthogonal to shared cache.

-scott



A few questions:
 o Is this behaviour general or just in shared cache mode?  You imply 
that it is the general case.
 o If it is general, how does it detect a stale cache?  By an indicator 
on the file?


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache/ test_server.c

[John Stanton]

Richard Klein wrote:



John Stanton wrote:


Richard Klein wrote:



Joe Wilson wrote:


You've probably read this. It's useful information for any performance
minded developer using SQLite:

  http://developer.mozilla.org/en/docs/Storage:Performance




 >> [snip]



If the above is correct, it is not enough for the server thread to open
connections on behalf of client threads:  The server must also open its
own connection, do a BEGIN TRANSACTION, create a dummy table containing
a single element, and then keep this dummy transaction open by *not*
doing a COMMIT.

Is this really true? I don't remember seeing this sort of dummy 
transaction code in the src/test_server.c file.


I looked through the test_server.c program and it is clearly a 
diagnostic rather than production program.  I suspect that the reason 
a transaction needs to be kept open is that Sqlite flushes all the 
cache rather than writing through it to clear dirty pages, but I don't 
understand the code enough to be sure.



According to the Mozilla article referenced above, it's even worse than
that:  *All* cache pages, dirty or not, are freed at the end of *every*
transaction, even if the transaction consisted of only read operations.

If this is true, then it seems that a shared cache would be useful only
in apps for which there are many concurrent transactions in progress.

In other words, unless your app exhibits a high degree of concurrency, a
shared cache doesn't buy you much.

- Richard



I suspect that you are correct, with the one exception that the shared 
cache mode cuts back on memory usage by avoiding replicated caches.


I have coded up a test and shall perform some measurements.  One other 
option is to look at Sqlite's flush logic and make a change for shared 
cache mode so that it writes through the cache to clean dirty pages.


Some time ago we built a product which was an embedded ISAM file manager 
using much the same principles as Sqlite.  We designed it to operate in 
two modes, one like Sqlite where each connection had its own cache and 
would write through the cache and detect a stale cache and refresh (for 
older non-POSIX OS's) it and the other mode mapped the file into VM and 
used the virtual memory as its shared cache.  The second mode runs very 
fast, but is inherently not ACID because it generally is associated with 
some form of lazy write.


In the ISAM case the data was not held in the B-tree like Sqlite and 
could be independently sync'd to disk and a damaged index recreated from 
the data to recover from a disaster.  That approach has proven to be 
robust and productive over many years of fairly large scale usage.


Perhaps there is a case for an Sqlitesuperlite stripped back for small 
scale embedded use and an Sqmediumlite for shared usage.  The "medium" 
implementation would retain the endearing features of simplicity of use 
but also be optimized towards multiple users.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache/ test_server.c

[Scott Hess]

On 7/26/07, Richard Klein <[EMAIL PROTECTED]> wrote:
> According to the Mozilla article referenced above, it's even worse than
> that:  *All* cache pages, dirty or not, are freed at the end of *every*
> transaction, even if the transaction consisted of only read operations.

I believe this is no longer true, as of a couple months ago.  Now
SQLite tracks whether any other database connection has written data,
and doesn't invalidate the pages if nobody else is writing.

I think this is completely orthogonal to shared cache.

-scott

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache/ test_server.c

[Richard Klein]

John Stanton wrote:

Richard Klein wrote:


Joe Wilson wrote:


You've probably read this. It's useful information for any performance
minded developer using SQLite:

  http://developer.mozilla.org/en/docs/Storage:Performance



>> [snip]


If the above is correct, it is not enough for the server thread to open
connections on behalf of client threads:  The server must also open its
own connection, do a BEGIN TRANSACTION, create a dummy table containing
a single element, and then keep this dummy transaction open by *not*
doing a COMMIT.

Is this really true? I don't remember seeing this sort of dummy 
transaction code in the src/test_server.c file.


I looked through the test_server.c program and it is clearly a 
diagnostic rather than production program.  I suspect that the reason a 
transaction needs to be kept open is that Sqlite flushes all the cache 
rather than writing through it to clear dirty pages, but I don't 
understand the code enough to be sure.


According to the Mozilla article referenced above, it's even worse than
that:  *All* cache pages, dirty or not, are freed at the end of *every*
transaction, even if the transaction consisted of only read operations.

If this is true, then it seems that a shared cache would be useful only
in apps for which there are many concurrent transactions in progress.

In other words, unless your app exhibits a high degree of concurrency, a
shared cache doesn't buy you much.

- Richard


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache/ test_server.c

[John Stanton]

Richard Klein wrote:


Joe Wilson wrote:


You've probably read this. It's useful information for any performance
minded developer using SQLite:

  http://developer.mozilla.org/en/docs/Storage:Performance



I read it, and I'm now weeping!  Below I've reproduced the paragraphs that
cause me some consternation:

--

"Sqlite has a cache of database pages in memory. It keeps pages associated
with the current transaction so it can roll them back, and it also keeps
recently used ones so it can run faster.

"By default, it only keeps the pages in memory during a transaction (if you
don't explicitly open a transaction, one will be opened for you enclosing
each individual statement). At the end of a transaction, the cache is
flushed. If you subsequently begin a new transaction, the pages you need
will be re-read from disk.

"[Enabling] the sqlite shared-cache mode ... makes multiple connections to
the same database share the same cache. Because the cache is not 
threadsafe,

this unfortunately means that you can not have different connections from
different threads accessing the same database. However, the shared cache
allows us to keep it live between transactions, instead of clearing it
after each transaction as sqlite does by default.

"If your application uses many small transactons, you can get a significant
performance improvement by keeping the cache live between transactions.
This is done by using an extra "dummy" connection to the same database.
The dummy connection keeps a perpetually open transaction which locks the
cache in memory. Since the cache is shared with the main connection, the
cache never expires.

"The dummy transaction must be one that locks a page in memory. A simple
BEGIN TRANSACTION statement doesn't do this because sqlite does the locking
lazily. Therefore, you must have a statement that modifies data. It might
be tempting to run a statement on the sqlite_master which contains the
information on the tables and indices in the database. However, if your
application is initializing the database for the first time, this table
will be empty and the cache won't be locked. nsNavHistory::StartDummyState-
ment creates a dummy table with a single element in it for this purpose.

"It is important to note that when a statement is open, the database schema
cannot be modified. This means that when the dummy transaction is running,
you cannot create or modify any tables or indices, or vacuum the database.
You will have to stop the dummy transaction, do the schema-modifying
operation, and restart it."

--

If the above is correct, it is not enough for the server thread to open
connections on behalf of client threads:  The server must also open its
own connection, do a BEGIN TRANSACTION, create a dummy table containing
a single element, and then keep this dummy transaction open by *not*
doing a COMMIT.

Is this really true? I don't remember seeing this sort of dummy transaction
code in the src/test_server.c file.

Regards,
- Richard

I looked through the test_server.c program and it is clearly a 
diagnostic rather than production program.  I suspect that the reason a 
transaction needs to be kept open is that Sqlite flushes all the cache 
rather than writing through it to clear dirty pages, but I don't 
understand the code enough to be sure.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache/ test_server.c

[Richard Klein]

Joe Wilson wrote:


You've probably read this. It's useful information for any performance
minded developer using SQLite:

  http://developer.mozilla.org/en/docs/Storage:Performance


I read it, and I'm now weeping!  Below I've reproduced the paragraphs that
cause me some consternation:

--

"Sqlite has a cache of database pages in memory. It keeps pages associated
with the current transaction so it can roll them back, and it also keeps
recently used ones so it can run faster.

"By default, it only keeps the pages in memory during a transaction (if you
don't explicitly open a transaction, one will be opened for you enclosing
each individual statement). At the end of a transaction, the cache is
flushed. If you subsequently begin a new transaction, the pages you need
will be re-read from disk.

"[Enabling] the sqlite shared-cache mode ... makes multiple connections to
the same database share the same cache. Because the cache is not threadsafe,
this unfortunately means that you can not have different connections from
different threads accessing the same database. However, the shared cache
allows us to keep it live between transactions, instead of clearing it
after each transaction as sqlite does by default.

"If your application uses many small transactons, you can get a significant
performance improvement by keeping the cache live between transactions.
This is done by using an extra "dummy" connection to the same database.
The dummy connection keeps a perpetually open transaction which locks the
cache in memory. Since the cache is shared with the main connection, the
cache never expires.

"The dummy transaction must be one that locks a page in memory. A simple
BEGIN TRANSACTION statement doesn't do this because sqlite does the locking
lazily. Therefore, you must have a statement that modifies data. It might
be tempting to run a statement on the sqlite_master which contains the
information on the tables and indices in the database. However, if your
application is initializing the database for the first time, this table
will be empty and the cache won't be locked. nsNavHistory::StartDummyState-
ment creates a dummy table with a single element in it for this purpose.

"It is important to note that when a statement is open, the database schema
cannot be modified. This means that when the dummy transaction is running,
you cannot create or modify any tables or indices, or vacuum the database.
You will have to stop the dummy transaction, do the schema-modifying
operation, and restart it."

--

If the above is correct, it is not enough for the server thread to open
connections on behalf of client threads:  The server must also open its
own connection, do a BEGIN TRANSACTION, create a dummy table containing
a single element, and then keep this dummy transaction open by *not*
doing a COMMIT.

Is this really true? I don't remember seeing this sort of dummy transaction
code in the src/test_server.c file.

Regards,
- Richard

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache/ test_server.c

[John Stanton]

I read that.  The Mozilla people point out that their advantage was with 
a large volume of small transactions.  They benefit from not destroying 
the cache between transactions and by conserving memory with large 
numbers of users.  They point out that relaxing the ACID requirements 
also aids throughput and when you think about it is actually necessary 
to get large gains from the persistent cache.


If the ratio of reads to writes is great the ACID capability should not 
impact performance much.  Our AJAX has reads outnumbering writes and may 
benefit from an approach like test_server.c.


Joe Wilson wrote:
Shared cache mode would be better named "persistent cache mode" because 
its main effect is to permit one thread to not flush the cache after 
each transaction.  The people at Mozilla report that they use it and get 
better throughput on small transactions.



You've probably read this. It's useful information for any performance
minded developer using SQLite:

  http://developer.mozilla.org/en/docs/Storage:Performance

Mozilla no doubt benefits from a shared cache, but its transaction throughput 
improvement is largely due to deferring all database write operations to a 
background thread, making the foreground database writes seem instantaneous. 
However, this lazy writing drops Durability in ACID. This is described in the 
above link.




   

Be a better Heartthrob. Get better relationship answers from someone who knows. Yahoo! Answers - Check it out. 
http://answers.yahoo.com/dir/?link=list=396545433


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache/ test_server.c

[Joe Wilson]

> Shared cache mode would be better named "persistent cache mode" because 
> its main effect is to permit one thread to not flush the cache after 
> each transaction.  The people at Mozilla report that they use it and get 
> better throughput on small transactions.

You've probably read this. It's useful information for any performance
minded developer using SQLite:

  http://developer.mozilla.org/en/docs/Storage:Performance

Mozilla no doubt benefits from a shared cache, but its transaction throughput 
improvement is largely due to deferring all database write operations to a 
background thread, making the foreground database writes seem instantaneous. 
However, this lazy writing drops Durability in ACID. This is described in the 
above link.



   

Be a better Heartthrob. Get better relationship answers from someone who knows. 
Yahoo! Answers - Check it out. 
http://answers.yahoo.com/dir/?link=list=396545433

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache/ test_server.c

[John Stanton]

Now you have made me worry.  If cache can only be shared by connections 
created in one thread then there is no shared cache.  I must investigate 
this more closely.  Perhaps my reading of the documentation included a 
dose of wishful thinking and a belief that "shared" meant shared! 
Looking through the code shows shared cache mode introducing table 
locking and gives the impression that "shared cache mode" is actually an 
implementation of finer granularity locking, to table level, and the 
multiple connections just store the cursor context state.


I shall need to write some test programs to be certain, but at this 
stage it does look like there is no shared cache mode as such and that a 
server needs to single stream Sqlite access in one thread to avoid 
having large amounts of data replicated in memory, but at the cost of 
restricting concurrent read access.


Shared cache mode would be better named "persistent cache mode" because 
its main effect is to permit one thread to not flush the cache after 
each transaction.  The people at Mozilla report that they use it and get 
better throughput on small transactions.


Thankyou to those people who contributed to this discussion.

Ken wrote:

John,
 
 According to the Sqlite documentation on sqlite3_enable_shared_cache:

 "There is no mechanism for sharing cache between database connections running in 
different threads."
 
 This means exactly what I said in the first place: You cannot have a "shared cache" access across threads.  I really wish that you could have multiple threads each with a database connection using shared cache running concurrently.
 
 Can you provide sample code showing the concept you are describing? 
 
 I totally understand what you are getting at with the locking. Indeed handling locking internally in memory will always be faster (assuming speed of ram access is faster than disk I/O). 
 
 John Stanton <[EMAIL PROTECTED]> wrote:  I think that you misunderstood the shared cache description.  Cache is 
shared by many connections but connections may not be passed between 
threads.  Each thread must maintain and use its its own connection.  In 
our case a thread has an associated control block and the connection 
handle resides there.


As long as you only access the Sqlite connection from the thread which 
created it you share the cache and it works fine.


The locking is a seperate issue and is aimed at avoiding the dreaded 
"busy wait".  We use a leisurely busy wait to handle mutli-process 
Sqlite using file locks.  The technique is to not delay after a busy is 
intercepted but to force a process time slice yield but in a server our 
intention is to avoid these inefficiencies by using the more efficient 
synchronization features.   As you would appreciate a few percent better 
efficiency on your server means a corresponding increase in the number 
of possible users.


Ken wrote:

John, 
The server can maintaine a "shared cache" but if a thread also opens the DB then that execution line will not have a "shared cache" but rather a cache per thread. Only the server thread may open and act upon the connection utilizing a shared cache on behalf of the client. The client may not do things unto the connection handle such as open, prepare, step, reset, close, finalize.


At least thats my understanding of the "shared_cache" mode.

Using a locking primitive internally does simplify the code. But I'll contend 
that if you are using multiple threads and each having a connection to a DB 
with a locking structure for internal synchronization. Then you are not using 
the sqlite shared cache. And you will not benefit from sqlites locking 
internals (read/writer starvation ). And if it is write intensive and 
concurrent you might as well have a single connections that is shared across 
all threads.

I guess my point was that inside the server thread, once a transaction is 
entered upon behalf of a client then only that activity may continue and no 
others. So in my design i only had two choices, re-enqueu the message inside 
the server until the transactional thread completed or return an error to the 
client. I preferred keeping the message on the queue waiting to be serviced. 
This is also programatically a pain in the arse since you must guarantee the 
client doesn't abandon its responsiblities and exit without sending a close 
command into the server thread, resulting in a permanently blocked server queue.

You can test this behavouir using the src/test_server.c code and some client 
connections into the test_server thread.

Or I may just be totally off my rocker.. and thats ok too. 
Ken



John Stanton  wrote: That is why the Sqlite locking is not a good fit for a threaded server. 
 Why not use thread locks instead and achieve the synchronization with 
minimum overhead and latency?  You do miss out on a couple of Sqlite 
features doing that (the pend

Re: [sqlite] shared cache/ test_server.c

[Ken]

John,
 
 According to the Sqlite documentation on sqlite3_enable_shared_cache:
 "There is no mechanism for sharing cache between database connections running 
in different threads."
 
 This means exactly what I said in the first place: You cannot have a "shared 
cache" access across threads.  I really wish that you could have multiple 
threads each with a database connection using shared cache running concurrently.
 
 Can you provide sample code showing the concept you are describing? 
 
 I totally understand what you are getting at with the locking. Indeed handling 
locking internally in memory will always be faster (assuming speed of ram 
access is faster than disk I/O). 
 
 John Stanton <[EMAIL PROTECTED]> wrote:  I think that you misunderstood the 
shared cache description.  Cache is 
shared by many connections but connections may not be passed between 
threads.  Each thread must maintain and use its its own connection.  In 
our case a thread has an associated control block and the connection 
handle resides there.

As long as you only access the Sqlite connection from the thread which 
created it you share the cache and it works fine.

The locking is a seperate issue and is aimed at avoiding the dreaded 
"busy wait".  We use a leisurely busy wait to handle mutli-process 
Sqlite using file locks.  The technique is to not delay after a busy is 
intercepted but to force a process time slice yield but in a server our 
intention is to avoid these inefficiencies by using the more efficient 
synchronization features.   As you would appreciate a few percent better 
efficiency on your server means a corresponding increase in the number 
of possible users.

Ken wrote:
> John, 
> The server can maintaine a "shared cache" but if a thread also opens the DB 
> then that execution line will not have a "shared cache" but rather a cache 
> per thread. Only the server thread may open and act upon the connection 
> utilizing a shared cache on behalf of the client. The client may not do 
> things unto the connection handle such as open, prepare, step, reset, close, 
> finalize.
>  
>  At least thats my understanding of the "shared_cache" mode.
>  
> Using a locking primitive internally does simplify the code. But I'll contend 
> that if you are using multiple threads and each having a connection to a DB 
> with a locking structure for internal synchronization. Then you are not using 
> the sqlite shared cache. And you will not benefit from sqlites locking 
> internals (read/writer starvation ). And if it is write intensive and 
> concurrent you might as well have a single connections that is shared across 
> all threads.
>  
> I guess my point was that inside the server thread, once a transaction is 
> entered upon behalf of a client then only that activity may continue and no 
> others. So in my design i only had two choices, re-enqueu the message inside 
> the server until the transactional thread completed or return an error to the 
> client. I preferred keeping the message on the queue waiting to be serviced. 
> This is also programatically a pain in the arse since you must guarantee the 
> client doesn't abandon its responsiblities and exit without sending a close 
> command into the server thread, resulting in a permanently blocked server 
> queue.
>  
>  You can test this behavouir using the src/test_server.c code and some client 
> connections into the test_server thread.
>  
>  Or I may just be totally off my rocker.. and thats ok too. 
>  Ken
>  
>  
>  John Stanton  wrote: That is why the Sqlite locking is not a good fit for a 
> threaded server. 
>   Why not use thread locks instead and achieve the synchronization with 
> minimum overhead and latency?  You do miss out on a couple of Sqlite 
> features doing that (the pending and reserved locks which help with 
> concurrency and write starvation) so you need to balance the benefits of 
> them against the detrimental effects of polling.
> 
> In our older embedded Sqlite threaded applications we just serialized 
> Sqlite access using a mutex because concurrency was not a prime issue, 
> but use read/write locks in a higher traffic Sqlite based multi-threaded 
> application server.
> 
> After experimentation, which included some erroneous attempts at cache 
> sharing we have a strategy in place which uses Sqlite shared cache and 
> assigns a rwlock to each open database.  Each thread has its own DB 
> connection with a pointer to the locking structure for the open 
> database.  That gives good throughput since it holds each database open 
> while the server runs and maintains one cache per database, accelerating 
> reads.  The downside is that we have to figure out a replacement for the 
> FTS2 accesses used for text searchi

Re: [sqlite] shared cache/ test_server.c

[Joe Wilson]

--- John Stanton <[EMAIL PROTECTED]> wrote:
> I think that you misunderstood the shared cache description.  Cache is 
> shared by many connections but connections may not be passed between 
> threads.  Each thread must maintain and use its its own connection.  In 
> our case a thread has an associated control block and the connection 
> handle resides there.
> 
> As long as you only access the Sqlite connection from the thread which 
> created it you share the cache and it works fine.

The cache is only shared for all connections created and used on the same 
thread.  See point 2 below from test_server.c.
You seem to confused with point 1, which is just a tangential comment 
regarding a certain platform, rather than a condition on the actual sharing 
of the cache.

You have to use an asynchronous model to take advantage of the shared cache.
"Client" threads can post messages to the one designated "server" thread
which may process requests on their behalf via one or more connections
created and used on that server thread.

** This source file demonstrates how to use SQLite to create an SQL database
** server thread in a multiple-threaded program.  One or more client threads
** send messages to the server thread and the server thread processes those
** messages in the order received and returns the results to the client.
**
** One might ask:  "Why bother?  Why not just let each thread connect
** to the database directly?"  There are a several of reasons to
** prefer the client/server approach.
**
**(1)  Some systems (ex: Redhat9) have broken threading implementations
** that prevent SQLite database connections from being used in
** a thread different from the one where they were created.  With
** the client/server approach, all database connections are created
** and used within the server thread.  Client calls to the database
** can be made from multiple threads (though not at the same time!)
**
**(2)  Beginning with SQLite version 3.3.0, when two or more
** connections to the same database occur within the same thread,
** they can optionally share their database cache.  This reduces
** I/O and memory requirements.  Cache shared is controlled using
** the sqlite3_enable_shared_cache() API.
**
**(3)  Database connections on a shared cache use table-level locking
** instead of file-level locking for improved concurrency.
**
**(4)  Database connections on a shared cache can by optionally
** set to READ UNCOMMITTED isolation.  (The default isolation for
** SQLite is SERIALIZABLE.)  When this occurs, readers will
** never be blocked by a writer and writers will not be
** blocked by readers.  There can still only be a single writer
** at a time, but multiple readers can simultaneously exist with
** that writer.  This is a huge increase in concurrency.
**
** To summarize the rational for using a client/server approach: prior
** to SQLite version 3.3.0 it probably was not worth the trouble.  But
** with SQLite version 3.3.0 and beyond you can get significant performance
** and concurrency improvements and memory usage reductions by going
** client/server.



  

Park yourself in front of a world of choices in alternative vehicles. Visit the 
Yahoo! Auto Green Center.
http://autos.yahoo.com/green_center/ 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache/ test_server.c

[John Stanton]

I think that you misunderstood the shared cache description.  Cache is 
shared by many connections but connections may not be passed between 
threads.  Each thread must maintain and use its its own connection.  In 
our case a thread has an associated control block and the connection 
handle resides there.


As long as you only access the Sqlite connection from the thread which 
created it you share the cache and it works fine.


The locking is a seperate issue and is aimed at avoiding the dreaded 
"busy wait".  We use a leisurely busy wait to handle mutli-process 
Sqlite using file locks.  The technique is to not delay after a busy is 
intercepted but to force a process time slice yield but in a server our 
intention is to avoid these inefficiencies by using the more efficient 
synchronization features.   As you would appreciate a few percent better 
efficiency on your server means a corresponding increase in the number 
of possible users.


Ken wrote:
John, 
 The server can maintaine a "shared cache" but if a thread also opens the DB then that execution line will not have a "shared cache" but rather a cache per thread. Only the server thread may open and act upon the connection utilizing a shared cache on behalf of the client. The client may not do things unto the connection handle such as open, prepare, step, reset, close, finalize.
 
 At least thats my understanding of the "shared_cache" mode.
 
 Using a locking primitive internally does simplify the code. But I'll contend that if you are using multiple threads and each having a connection to a DB with a locking structure for internal synchronization. Then you are not using the sqlite shared cache. And you will not benefit from sqlites locking internals (read/writer starvation ). And if it is write intensive and concurrent you might as well have a single connections that is shared across all threads.
 
 I guess my point was that inside the server thread, once a transaction is entered upon behalf of a client then only that activity may continue and no others. So in my design i only had two choices, re-enqueu the message inside the server until the transactional thread completed or return an error to the client. I preferred keeping the message on the queue waiting to be serviced.  This is also programatically a pain in the arse  since  you must  guarantee the client doesn't abandon its responsiblities and exit without sending a close command into the server thread, resulting in a permanently blocked server queue.
 
 You can test this behavouir using the src/test_server.c code and some client connections into the test_server thread.
 
 Or I may just be totally off my rocker.. and thats ok too. 
 Ken
 
 
 John Stanton <[EMAIL PROTECTED]> wrote: That is why the Sqlite locking is not a good fit for a threaded server. 
  Why not use thread locks instead and achieve the synchronization with 
minimum overhead and latency?  You do miss out on a couple of Sqlite 
features doing that (the pending and reserved locks which help with 
concurrency and write starvation) so you need to balance the benefits of 
them against the detrimental effects of polling.


In our older embedded Sqlite threaded applications we just serialized 
Sqlite access using a mutex because concurrency was not a prime issue, 
but use read/write locks in a higher traffic Sqlite based multi-threaded 
application server.


After experimentation, which included some erroneous attempts at cache 
sharing we have a strategy in place which uses Sqlite shared cache and 
assigns a rwlock to each open database.  Each thread has its own DB 
connection with a pointer to the locking structure for the open 
database.  That gives good throughput since it holds each database open 
while the server runs and maintains one cache per database, accelerating 
reads.  The downside is that we have to figure out a replacement for the 
FTS2 accesses used for text searching.


Since we no longer user POSIX file locking we compile Sqlite without it 
to trim some redundant overhead.


It looks like we can replace FTS by user functions using a text indexing 
method recycled from another product.


The server in question services AJAX style WWW pages where there are 
large numbers of short read transactions and minimum latency is required 
to achieve a snappy response.  It manages to achieve sub-millisecond 
responses to database RPC's from the WWW browser.


BTW, with help from this forum we realized that our attempts to achieve 
shared cache and FTS were doomed to fail for fundamental architecture 
reasons and abandoned the effort.  In retrospect we were trying to 
implement PostgreSQL with Sqlite and that was not a rational project.


The Sqlite based application server allows a central site to support 
many databases, each one specific to sets of users located globally. 
Sqlite's single file databases make this very simple to administer. 
Each database does not have a large nu

Re: [sqlite] shared cache/ test_server.c

[Richard Klein]

Ken wrote:


I guess my point was that inside the server thread, once a transaction
is entered upon behalf of a client then only that activity may continue
and no others. So in my design i only had two choices, re-enqueue the
message inside the server until the transactional thread completed or
return an error to the client. I preferred keeping the message on the
queue waiting to be serviced.  This is also programmaticaly a pain in
the arse  since  you must  guarantee the client doesn't abandon its
responsibilities and exit without sending a close command into the
server thread, resulting in a permanently blocked server queue.


Good point!  I hadn't even thought of that additional responsibility
of the server thread.

As you say, the server could avoid this responsibility by simply
returning an error to the client.  One could even argue that this
is not imposing too heavy a burden on the client, since in a
traditional server-less architecture in which each client opens
his own connection, the client must always be prepared to deal
with a SQL_BUSY error return from SQLite.

However, I like your choice better:  If you're going to go to the
trouble of having a server, you may as well go the extra mile and
have the server make the client's life a little easier!

Regards,
- Richard



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache/ test_server.c

[Ken]

John, 
 The server can maintaine a "shared cache" but if a thread also opens the DB 
then that execution line will not have a "shared cache" but rather a cache per 
thread. Only the server thread may open and act upon the connection utilizing a 
shared cache on behalf of the client. The client may not do things unto the 
connection handle such as open, prepare, step, reset, close, finalize.
 
 At least thats my understanding of the "shared_cache" mode.
 
 Using a locking primitive internally does simplify the code. But I'll contend 
that if you are using multiple threads and each having a connection to a DB 
with a locking structure for internal synchronization. Then you are not using 
the sqlite shared cache. And you will not benefit from sqlites locking 
internals (read/writer starvation ). And if it is write intensive and 
concurrent you might as well have a single connections that is shared across 
all threads.
 
 I guess my point was that inside the server thread, once a transaction is 
entered upon behalf of a client then only that activity may continue and no 
others. So in my design i only had two choices, re-enqueu the message inside 
the server until the transactional thread completed or return an error to the 
client. I preferred keeping the message on the queue waiting to be serviced.  
This is also programatically a pain in the arse  since  you must  guarantee the 
client doesn't abandon its responsiblities and exit without sending a close 
command into the server thread, resulting in a permanently blocked server queue.
 
 You can test this behavouir using the src/test_server.c code and some client 
connections into the test_server thread.
 
 Or I may just be totally off my rocker.. and thats ok too. 
 Ken
 
 
 John Stanton <[EMAIL PROTECTED]> wrote: That is why the Sqlite locking is not 
a good fit for a threaded server. 
  Why not use thread locks instead and achieve the synchronization with 
minimum overhead and latency?  You do miss out on a couple of Sqlite 
features doing that (the pending and reserved locks which help with 
concurrency and write starvation) so you need to balance the benefits of 
them against the detrimental effects of polling.

In our older embedded Sqlite threaded applications we just serialized 
Sqlite access using a mutex because concurrency was not a prime issue, 
but use read/write locks in a higher traffic Sqlite based multi-threaded 
application server.

After experimentation, which included some erroneous attempts at cache 
sharing we have a strategy in place which uses Sqlite shared cache and 
assigns a rwlock to each open database.  Each thread has its own DB 
connection with a pointer to the locking structure for the open 
database.  That gives good throughput since it holds each database open 
while the server runs and maintains one cache per database, accelerating 
reads.  The downside is that we have to figure out a replacement for the 
FTS2 accesses used for text searching.

Since we no longer user POSIX file locking we compile Sqlite without it 
to trim some redundant overhead.

It looks like we can replace FTS by user functions using a text indexing 
method recycled from another product.

The server in question services AJAX style WWW pages where there are 
large numbers of short read transactions and minimum latency is required 
to achieve a snappy response.  It manages to achieve sub-millisecond 
responses to database RPC's from the WWW browser.

BTW, with help from this forum we realized that our attempts to achieve 
shared cache and FTS were doomed to fail for fundamental architecture 
reasons and abandoned the effort.  In retrospect we were trying to 
implement PostgreSQL with Sqlite and that was not a rational project.

The Sqlite based application server allows a central site to support 
many databases, each one specific to sets of users located globally. 
Sqlite's single file databases make this very simple to administer. 
Each database does not have a large number of users, relieving the 
concurrency load.

For further background on using Sqlite this way look at the way Mozilla 
implements it using shared cache.

Finally, it is important to recognize that Sqlite id not Oracle, it is a 
well conceived kit of tools to permit a developer to embed SQL database 
capability into an application and to make it fit transparently.  The 
developer has the source and nothing is chiselled in stone.

Ken wrote:
> John,
> The sqlite api won't block, it will return a sqlite_busy type error to any 
> other transactions that are attempted? Correct, so there is no sqlite 
> blocking which is a good thing when writing a server. The clients will always 
> block waiting upon a response from the server. The server simply keeps the 
> client requests enqueued until it can service them some time later.
>  

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache/ test_server.c

[John Stanton]

That is why the Sqlite locking is not a good fit for a threaded server. 
 Why not use thread locks instead and achieve the synchronization with 
minimum overhead and latency?  You do miss out on a couple of Sqlite 
features doing that (the pending and reserved locks which help with 
concurrency and write starvation) so you need to balance the benefits of 
them against the detrimental effects of polling.


In our older embedded Sqlite threaded applications we just serialized 
Sqlite access using a mutex because concurrency was not a prime issue, 
but use read/write locks in a higher traffic Sqlite based multi-threaded 
application server.


After experimentation, which included some erroneous attempts at cache 
sharing we have a strategy in place which uses Sqlite shared cache and 
assigns a rwlock to each open database.  Each thread has its own DB 
connection with a pointer to the locking structure for the open 
database.  That gives good throughput since it holds each database open 
while the server runs and maintains one cache per database, accelerating 
reads.  The downside is that we have to figure out a replacement for the 
FTS2 accesses used for text searching.


Since we no longer user POSIX file locking we compile Sqlite without it 
to trim some redundant overhead.


It looks like we can replace FTS by user functions using a text indexing 
method recycled from another product.


The server in question services AJAX style WWW pages where there are 
large numbers of short read transactions and minimum latency is required 
to achieve a snappy response.  It manages to achieve sub-millisecond 
responses to database RPC's from the WWW browser.


BTW, with help from this forum we realized that our attempts to achieve 
shared cache and FTS were doomed to fail for fundamental architecture 
reasons and abandoned the effort.  In retrospect we were trying to 
implement PostgreSQL with Sqlite and that was not a rational project.


The Sqlite based application server allows a central site to support 
many databases, each one specific to sets of users located globally. 
Sqlite's single file databases make this very simple to administer. 
Each database does not have a large number of users, relieving the 
concurrency load.


For further background on using Sqlite this way look at the way Mozilla 
implements it using shared cache.


Finally, it is important to recognize that Sqlite id not Oracle, it is a 
well conceived kit of tools to permit a developer to embed SQL database 
capability into an application and to make it fit transparently.  The 
developer has the source and nothing is chiselled in stone.


Ken wrote:

John,
 The sqlite api won't block, it will return a sqlite_busy type error to any 
other transactions that are attempted? Correct, so there is no sqlite blocking 
which is a good thing when writing a server. The clients will always block 
waiting upon a response from the server. The server simply keeps the client 
requests enqueued until it can service them some time later.
 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache/ test_server.c

[Ken]

John,
 The sqlite api won't block, it will return a sqlite_busy type error to any 
other transactions that are attempted? Correct, so there is no sqlite blocking 
which is a good thing when writing a server. The clients will always block 
waiting upon a response from the server. The server simply keeps the client 
requests enqueued until it can service them some time later.
 

John Stanton <[EMAIL PROTECTED]> wrote: Why not just bloock on the transation.  
That will queue waiting threads, 
serializing the access to the transaction.

Ken wrote:
> My assumption for the server thread was that it needed to process all 
> incoming requests in transaction order and to not loose outstanding requests. 
> You have two choices once a client initiates a transaction:
>  
> a. reject the incoming request since a transaction is active in the server. 
> The client would then be able to re-submit the request... This seemed to have 
> alot of overhead since the client would then need to have code to resubmit in 
> the event of a Reject. 
>And then it would simply sit in a loop re-posting the message until it 
> got a valid acknowledgment... 
>  
> b. re-Queue the request to the tail, causing the client to block waiting for 
> a response from the server. The active client will eventually complete its 
> transaction and the next in the queue will be serviced. 
>   
> I favored option b. Since it caused less thrashing about when the client 
> intiated a read request or another transaction request when a transaction was 
> already in progress.
>  
>  Hope that helps.
>  
> 
> John Stanton  wrote: Ken wrote:
> 
>>Richard,
>> 
>> You might want to look at src/test_server.c for an example of the 
>> shared_cache if you haven't found it already.
>> 
>>Personally, I think it makes a lot of sense (read simpler) to implement 
>>independent connections than to implement a server. But I can see why you 
>>might want a server if you have many threads and memory constraints.
>> 
>>The server still can only have one transaction running at a time, even though 
>>the cache is shared. However, it can run multiple select operations and 
>>perform dirty reads(when enabled). 
>> 
>>The biggest difficulty encountered with the server is how to handle client 
>>requests when a transaction was in progress... Do you re-queue or just fail 
>>and have the client resend? My solution was to keep a state of a client 
>>thread id when it started a transaction. If the server thread encountered a 
>>message that was not from the client thread that started the transaction it 
>>moved the message to the end of the queue.
>>
> 
> 
> Why not just block on the transaction?
> 
> 
>> 
>>Your welcome to call email me directly if you need more info or call if you'd 
>>like to discuss my experiences with the server/thread approach.
>> 
>> Regards,
>> Ken
>> 
>>Richard Klein  wrote: 
>>
>>Richard Klein wrote:
>>
>>
>>>[EMAIL PROTECTED] wrote:
>>>
>>>
John Stanton  wrote:


>Yes, each connection has a cache.  A lot of concurrent connections 
>means a lot of memory allocated to cache and potentially a lot of 
>duplicated cached items.  See shared cache mode for relief.
>

Yes.  But remember that shared cache mode has limitations:

  *  When shared cache mode is enabled, you cannot use
 a connection in a thread other than the thread in which
 it was originally created.

  *  Only connections opened in the same thread share a cache.

The shared cache mode is designed for building a "server thread"
that accepts connection requests and SQL statements via messages
>>>
from "client threads", acts upon those requests, and returns the
>>>
result.
-- 
D. Richard Hipp 

>>>
>>>I suppose that I could accomplish almost the same thing in 2.8.17,
>>>even though shared cache mode is not available in that version.
>>>
>>>I could have a server thread that opens the database, and then
>>>accepts and processes SQL statements via messages from client
>>>threads.
>>>
>>>The only difference would be that the client threads could not
>>>send connection requests.  There would be only one connection,
>>>and it would be opened implicitly by the server thread at system
>>>startup.
>>>
>>>The benefit would be that all the client threads would effectively
>>>share the same cache, since there would in fact be only one connection.
>>>
>>>The cost would be that each SQL statement would require an additional
>>>two context switches to execute.
>>>
>>>In my application (TiVo-like Personal Video Recorder functionality
>>>in a set-top box), the benefit of memory savings far outweighs the
>>>cost of a performance hit due to extra context switches.
>>>
>>>- Richard
>>>
>>
>>
>>Upon further reflection, I realized that the scheme outlined above
>>won't work.
>>
>>The problem can be summed up on one word:  TRANSACTIONS.  There needs
>>to be a way to make sure that the SQL statements composing a 

Re: [sqlite] shared cache/ test_server.c

[John Stanton]

Why not just bloock on the transation.  That will queue waiting threads, 
serializing the access to the transaction.


Ken wrote:

My assumption for the server thread was that it needed to process all incoming 
requests  in transaction order and to not loose outstanding requests. You have 
two choices once a client initiates a transaction:
 
   a. reject the incoming request since a transaction is active in the server. The client would then be able to re-submit the request... This seemed to have alot of overhead since the client would then need to have code to resubmit in the event of a Reject. 
   And then it would simply sit in a loop re-posting the message until it got a valid acknowledgment... 
 
   b. re-Queue the request to the tail,  causing the client to block waiting for a response from the server.  The active client will eventually complete its transaction and the next in the queue will be serviced. 
  
 I favored option b. Since it caused less thrashing about when the client intiated a read request or another transaction request when a transaction was already in progress.
 
 Hope that helps.
 


John Stanton <[EMAIL PROTECTED]> wrote: Ken wrote:


Richard,

You might want to look at src/test_server.c for an example of the shared_cache 
if you haven't found it already.

Personally, I think it makes a lot of sense (read simpler) to implement 
independent connections than to implement a server. But I can see why you might 
want a server if you have many threads and memory constraints.

The server still can only have one transaction running at a time, even though the cache is shared. However, it can run multiple select operations and perform dirty reads(when enabled). 


The biggest difficulty encountered with the server is how to handle client 
requests when a transaction was in progress... Do you re-queue or just fail and 
have the client resend? My solution was to keep a state of a client thread id 
when it started a transaction. If the server thread encountered a message that 
was not from the client thread that started the transaction it moved the 
message to the end of the queue.




Why not just block on the transaction?




Your welcome to call email me directly if you need more info or call if you'd 
like to discuss my experiences with the server/thread approach.

Regards,
Ken

Richard Klein  wrote: 


Richard Klein wrote:



[EMAIL PROTECTED] wrote:



John Stanton  wrote:


Yes, each connection has a cache.  A lot of concurrent connections 
means a lot of memory allocated to cache and potentially a lot of 
duplicated cached items.  See shared cache mode for relief.




Yes.  But remember that shared cache mode has limitations:

 *  When shared cache mode is enabled, you cannot use
a connection in a thread other than the thread in which
it was originally created.

 *  Only connections opened in the same thread share a cache.

The shared cache mode is designed for building a "server thread"
that accepts connection requests and SQL statements via messages



from "client threads", acts upon those requests, and returns the



result.
--
D. Richard Hipp 



I suppose that I could accomplish almost the same thing in 2.8.17,
even though shared cache mode is not available in that version.

I could have a server thread that opens the database, and then
accepts and processes SQL statements via messages from client
threads.

The only difference would be that the client threads could not
send connection requests.  There would be only one connection,
and it would be opened implicitly by the server thread at system
startup.

The benefit would be that all the client threads would effectively
share the same cache, since there would in fact be only one connection.

The cost would be that each SQL statement would require an additional
two context switches to execute.

In my application (TiVo-like Personal Video Recorder functionality
in a set-top box), the benefit of memory savings far outweighs the
cost of a performance hit due to extra context switches.

- Richard




Upon further reflection, I realized that the scheme outlined above
won't work.

The problem can be summed up on one word:  TRANSACTIONS.  There needs
to be a way to make sure that the SQL statements composing a trans-
action in client thread 'A' aren't intermixed with those composing a
transaction in client thread 'B'.

The SQLite connection is the structure designed to keep track of state
information such as whether or not a transaction is in progress.  If
client threads 'A' and 'B' share the same connection, then the burden
of maintaining this state information falls on the server thread.  Not
a great idea.

Therefore, it would appear that I have two options:

(1) Have the server thread open separate connections for client threads
'A' and 'B', and enable shared cache mode so that the two connections
can share cached items.  This option requires upgrading to SQLite version
3.3.0 or higher.

(2) Abandon the idea of 

Re: [sqlite] shared cache/ test_server.c

[Richard Klein]

Ken wrote:

Richard,
 
 You might want to look at src/test_server.c for an example of the shared_cache if you haven't found it already.


I'll take a look at it, thanks!


 Personally, I think it makes a lot of sense (read simpler) to implement 
independent connections than to implement a server.


So do I.


Your welcome to call email me directly if you need more info or call if you'd 
like to discuss my experiences with the server/thread approach.


Thanks for the kind offer.  I may take you up on it!

Thanks again,
- Richard



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] shared cache/ test_server.c

[Ken]

My assumption for the server thread was that it needed to process all incoming 
requests  in transaction order and to not loose outstanding requests. You have 
two choices once a client initiates a transaction:
 
   a. reject the incoming request since a transaction is active in the 
server. The client would then be able to re-submit the request... This seemed 
to have alot of overhead since the client would then need to have code to 
resubmit in the event of a Reject. 
   And then it would simply sit in a loop re-posting the message until it 
got a valid acknowledgment... 
 
   b. re-Queue the request to the tail,  causing the client to block 
waiting for a response from the server.  The active client will eventually 
complete its transaction and the next in the queue will be serviced. 
  
 I favored option b. Since it caused less thrashing about when the client 
intiated a read request or another transaction request when a transaction was 
already in progress.
 
 Hope that helps.
 

John Stanton <[EMAIL PROTECTED]> wrote: Ken wrote:
> Richard,
>  
>  You might want to look at src/test_server.c for an example of the 
> shared_cache if you haven't found it already.
>  
> Personally, I think it makes a lot of sense (read simpler) to implement 
> independent connections than to implement a server. But I can see why you 
> might want a server if you have many threads and memory constraints.
>  
> The server still can only have one transaction running at a time, even though 
> the cache is shared. However, it can run multiple select operations and 
> perform dirty reads(when enabled). 
>  
> The biggest difficulty encountered with the server is how to handle client 
> requests when a transaction was in progress... Do you re-queue or just fail 
> and have the client resend? My solution was to keep a state of a client 
> thread id when it started a transaction. If the server thread encountered a 
> message that was not from the client thread that started the transaction it 
> moved the message to the end of the queue.
>

Why not just block on the transaction?

>  
> Your welcome to call email me directly if you need more info or call if you'd 
> like to discuss my experiences with the server/thread approach.
>  
>  Regards,
>  Ken
>  
> Richard Klein  wrote: 
> 
> Richard Klein wrote:
> 
>>
>>[EMAIL PROTECTED] wrote:
>>
>>>John Stanton  wrote:
>>>
Yes, each connection has a cache.  A lot of concurrent connections 
means a lot of memory allocated to cache and potentially a lot of 
duplicated cached items.  See shared cache mode for relief.

>>>
>>>Yes.  But remember that shared cache mode has limitations:
>>>
>>>   *  When shared cache mode is enabled, you cannot use
>>>  a connection in a thread other than the thread in which
>>>  it was originally created.
>>>
>>>   *  Only connections opened in the same thread share a cache.
>>>
>>>The shared cache mode is designed for building a "server thread"
>>>that accepts connection requests and SQL statements via messages
>>>from "client threads", acts upon those requests, and returns the
>>>result.
>>>-- 
>>>D. Richard Hipp 
>>>
>>
>>I suppose that I could accomplish almost the same thing in 2.8.17,
>>even though shared cache mode is not available in that version.
>>
>>I could have a server thread that opens the database, and then
>>accepts and processes SQL statements via messages from client
>>threads.
>>
>>The only difference would be that the client threads could not
>>send connection requests.  There would be only one connection,
>>and it would be opened implicitly by the server thread at system
>>startup.
>>
>>The benefit would be that all the client threads would effectively
>>share the same cache, since there would in fact be only one connection.
>>
>>The cost would be that each SQL statement would require an additional
>>two context switches to execute.
>>
>>In my application (TiVo-like Personal Video Recorder functionality
>>in a set-top box), the benefit of memory savings far outweighs the
>>cost of a performance hit due to extra context switches.
>>
>>- Richard
>>
> 
> 
> Upon further reflection, I realized that the scheme outlined above
> won't work.
> 
> The problem can be summed up on one word:  TRANSACTIONS.  There needs
> to be a way to make sure that the SQL statements composing a trans-
> action in client thread 'A' aren't intermixed with those composing a
> transaction in client thread 'B'.
> 
> The SQLite connection is the structure designed to keep track of state
> information such as whether or not a transaction is in progress.  If
> client threads 'A' and 'B' share the same connection, then the burden
> of maintaining this state information falls on the server thread.  Not
> a great idea.
> 
> Therefore, it would appear that I have two options:
> 
> (1) Have the server thread open separate connections for client threads
> 'A' and 'B', and enable shared cache mode so that the two connections
> can share 

Re: [sqlite] shared cache/ test_server.c

[John Stanton]

Ken wrote:

Richard,
 
 You might want to look at src/test_server.c for an example of the shared_cache if you haven't found it already.
 
 Personally, I think it makes a lot of sense (read simpler) to implement independent connections than to implement a server. But I can see why you might want a server if you have many threads and memory constraints.
 
 The server still can only have one transaction running at a time, even though the cache is shared. However, it can run multiple select operations and perform dirty reads(when enabled). 
 
 The biggest difficulty encountered with the server is how to handle client requests when  a transaction was in progress... Do you re-queue or just fail and have the client resend? My solution was to keep a state of a client thread id when it started a transaction.  If the server thread encountered a message that was not from the client thread that started the transaction it moved the message to the end of the queue.




Why not just block on the transaction?

 
Your welcome to call email me directly if you need more info or call if you'd like to discuss my experiences with the server/thread approach.
 
 Regards,

 Ken
 
Richard Klein <[EMAIL PROTECTED]> wrote: 


Richard Klein wrote:



[EMAIL PROTECTED] wrote:


John Stanton  wrote:

Yes, each connection has a cache.  A lot of concurrent connections 
means a lot of memory allocated to cache and potentially a lot of 
duplicated cached items.  See shared cache mode for relief.




Yes.  But remember that shared cache mode has limitations:

  *  When shared cache mode is enabled, you cannot use
 a connection in a thread other than the thread in which
 it was originally created.

  *  Only connections opened in the same thread share a cache.

The shared cache mode is designed for building a "server thread"
that accepts connection requests and SQL statements via messages
from "client threads", acts upon those requests, and returns the
result.
--
D. Richard Hipp 



I suppose that I could accomplish almost the same thing in 2.8.17,
even though shared cache mode is not available in that version.

I could have a server thread that opens the database, and then
accepts and processes SQL statements via messages from client
threads.

The only difference would be that the client threads could not
send connection requests.  There would be only one connection,
and it would be opened implicitly by the server thread at system
startup.

The benefit would be that all the client threads would effectively
share the same cache, since there would in fact be only one connection.

The cost would be that each SQL statement would require an additional
two context switches to execute.

In my application (TiVo-like Personal Video Recorder functionality
in a set-top box), the benefit of memory savings far outweighs the
cost of a performance hit due to extra context switches.

- Richard




Upon further reflection, I realized that the scheme outlined above
won't work.

The problem can be summed up on one word:  TRANSACTIONS.  There needs
to be a way to make sure that the SQL statements composing a trans-
action in client thread 'A' aren't intermixed with those composing a
transaction in client thread 'B'.

The SQLite connection is the structure designed to keep track of state
information such as whether or not a transaction is in progress.  If
client threads 'A' and 'B' share the same connection, then the burden
of maintaining this state information falls on the server thread.  Not
a great idea.

Therefore, it would appear that I have two options:

(1) Have the server thread open separate connections for client threads
'A' and 'B', and enable shared cache mode so that the two connections
can share cached items.  This option requires upgrading to SQLite version
3.3.0 or higher.

(2) Abandon the idea of a server thread; have threads 'A' and 'B' open
their own connections and access SQLite directly.  This option does *not*
allow the sharing of cached items, but allows me to stay with SQLite
version 2.8.17.

- Richard


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] shared cache/ test_server.c

[Ken]

Richard,
 
 You might want to look at src/test_server.c for an example of the shared_cache 
if you haven't found it already.
 
 Personally, I think it makes a lot of sense (read simpler) to implement 
independent connections than to implement a server. But I can see why you might 
want a server if you have many threads and memory constraints.
 
 The server still can only have one transaction running at a time, even though 
the cache is shared. However, it can run multiple select operations and perform 
dirty reads(when enabled). 
 
 The biggest difficulty encountered with the server is how to handle client 
requests when  a transaction was in progress... Do you re-queue or just fail 
and have the client resend? My solution was to keep a state of a client thread 
id when it started a transaction.  If the server thread encountered a message 
that was not from the client thread that started the transaction it moved the 
message to the end of the queue.
 
 
Your welcome to call email me directly if you need more info or call if you'd 
like to discuss my experiences with the server/thread approach.
 
 Regards,
 Ken
 
Richard Klein <[EMAIL PROTECTED]> wrote: 

Richard Klein wrote:
> 
> 
> [EMAIL PROTECTED] wrote:
>> John Stanton  wrote:
>>> Yes, each connection has a cache.  A lot of concurrent connections 
>>> means a lot of memory allocated to cache and potentially a lot of 
>>> duplicated cached items.  See shared cache mode for relief.
>>>
>>
>> Yes.  But remember that shared cache mode has limitations:
>>
>>*  When shared cache mode is enabled, you cannot use
>>   a connection in a thread other than the thread in which
>>   it was originally created.
>>
>>*  Only connections opened in the same thread share a cache.
>>
>> The shared cache mode is designed for building a "server thread"
>> that accepts connection requests and SQL statements via messages
>> from "client threads", acts upon those requests, and returns the
>> result.
>> -- 
>> D. Richard Hipp 
>>
> 
> I suppose that I could accomplish almost the same thing in 2.8.17,
> even though shared cache mode is not available in that version.
> 
> I could have a server thread that opens the database, and then
> accepts and processes SQL statements via messages from client
> threads.
> 
> The only difference would be that the client threads could not
> send connection requests.  There would be only one connection,
> and it would be opened implicitly by the server thread at system
> startup.
> 
> The benefit would be that all the client threads would effectively
> share the same cache, since there would in fact be only one connection.
> 
> The cost would be that each SQL statement would require an additional
> two context switches to execute.
> 
> In my application (TiVo-like Personal Video Recorder functionality
> in a set-top box), the benefit of memory savings far outweighs the
> cost of a performance hit due to extra context switches.
> 
> - Richard
> 

Upon further reflection, I realized that the scheme outlined above
won't work.

The problem can be summed up on one word:  TRANSACTIONS.  There needs
to be a way to make sure that the SQL statements composing a trans-
action in client thread 'A' aren't intermixed with those composing a
transaction in client thread 'B'.

The SQLite connection is the structure designed to keep track of state
information such as whether or not a transaction is in progress.  If
client threads 'A' and 'B' share the same connection, then the burden
of maintaining this state information falls on the server thread.  Not
a great idea.

Therefore, it would appear that I have two options:

(1) Have the server thread open separate connections for client threads
'A' and 'B', and enable shared cache mode so that the two connections
can share cached items.  This option requires upgrading to SQLite version
3.3.0 or higher.

(2) Abandon the idea of a server thread; have threads 'A' and 'B' open
their own connections and access SQLite directly.  This option does *not*
allow the sharing of cached items, but allows me to stay with SQLite
version 2.8.17.

- Richard


-
To unsubscribe, send email to [EMAIL PROTECTED]
-