Re: [sqlite] Connection philosophy

[Keith Medcalf]

On Tuesday, 22 January, 2013 19:14 MST, Ward Willats  
wrote:
 
> On Jan 22, 2013, at 5:54 PM, Keith Medcalf  wrote:
 
>> In my case, I only have one writer (I think!) during the big transaction,
>> so a long-lived, singleton connection or WAL should work for me. I guess I
>> would lean toward the former (KISS).

Just one additional comment -- if you do the same usage counting on the writer 
connection when it is opened/closed by the wrapper, you can ensure that only 
one set of updates is in progress at a time and it may be very helpful if you 
are ever requested to add such a "feature" (for example, you could re-queue 
overlapping updates to be processed after the current update transaction is 
properly completed to make sure that independent updates don't get mixed up 
with your long transaction and perhaps rolled back in error).  This could be as 
simple as only returning the actual connection handle when the usage count 
transitions 0 -> 1, otherwise return NUL so your application code knows that 
some other update is already in progress and to try this request again later 
(or tell the update source to try again later).

This sort of thing may be important if your processing/request queue could look 
like the following:

Query
BEGIN Data collection
Query 
Query
Instrument Data
Query
Query
Query
Instrument Data
END Data collection
UPDATE
Query
BEGIN Data collection
Instrument Data
Instrument Data
UPDATE
Instrument Data
Query
ABORT data collection
Query

You would need to know whether the last query should see the UPDATE or not.  If 
so, you need to ensure that it is processed only after the data collection 
transaction is cleared.

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




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

Re: [sqlite] Connection philosophy

[Keith Medcalf]

On Tuesday, 22 January, 2013 19:14 MST, Ward Willats  
wrote:
 
> On Jan 22, 2013, at 5:54 PM, Keith Medcalf  wrote:
 
>> I prefer the long-lived approach.  Continuously re-initialization of the
>> connection on open, the need to re-read pages into the page cache
>> repetitively, and the subsequent discard of a nicely loaded page-cache on
>> connection close usually incurs non-trivial overhead.
 
> Thanks for this thoughtful response (you too, Simon). The page-cache
> argument is compelling.
 
>> In my case, I only have one writer (I think!) during the big transaction,
>> so a long-lived, singleton connection or WAL should work for me. I guess I
>> would lean toward the former (KISS).

One other consideration is transaction visibility on a single connection.

I presume that you are using some kind of input-driven or event driven 
application which may get a request to process a query "in the middle" of your 
update transaction.  

When you run the SELECT on the same connection that has the database 
transaction in progress, that SELECT will "see" the database as it exists at 
that moment in the transaction (ie, such SELECTs will read uncommitted data).  
If the transaction is subsequently rolled back, those results may have been 
inaccurate (ie, the same select processed after the rollback may see a 
different result).

One of the advantages of WAL and using a separate reader and writer connection 
is that the reader connection will not see "uncommitted" data from the 
"writer".  In fact, if you do transactions on the connection processing the 
SELECT (especially if some requests process multiple SELECTs to answer a single 
request) they can be made to see a consistent view of the database, even if the 
update commits during the SELECT processing (if that is even possible in your 
particular application flow).

One way you might do this is to modify your wrappers slightly to take as input 
whether connection it wants is the "reader" or "writer".  Don't change anything 
in the case of the writer, just return the long-lived writer connection -- your 
existing BEGIN/COMMIT/ROLLBACK should not need changing.  For the reader though 
add a little bit of additional logic:  keep a "usage" count -- increment the 
count when your open wrapper is returning the long-lived reader connection 
handle and decrement it whenever the reader connection handle is returned to 
your close wrapper function.  When the count transition 0 -> 1 execute a 
"BEGIN" on the connection before returning the handle.  When the count 
transitions 1 -> 0 execute a "ROLLBACK" or "COMMIT" on the connection.  Of 
course, if the count goes <0 or never returns to 0, you have a problem ...

(Note that if you do not do explicit BEGIN/COMMIT on the reader connection in 
WAL mode, your read-consistent view will depend on when the connection 
autocommits -- even read-only SELECTs are processed inside a transaction which 
begins and ends automatically).

With the database in WAL mode, this will ensure that the "reader" sees THE SAME 
consistent view of the database even when an update is in progress.  The 
committed transactions will only become visible when "all" concurrently in use 
readers have returned (closed) their connection (all the same reader 
connection) even if the update commits "in the middle" of a multi-step query -- 
in other words a guaranteed "all or nothing" with respect to the updates.

Of course, whether any of this matters or is even possible depends on your 
application -- there may be no need for the additional complication at all.  On 
the other hand though, it may be something to consider especially if the 
application flow may permit inconsistencies to be visible to the marketing 
folks (or whomever) expecting to see "all or nothing" ... and it may be better 
to think about such possibilities before they notice it and claim your 
application is "broken" ... or even worse is acting "mysteriously" in their 
eyes.

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




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

Re: [sqlite] Connection philosophy

[Ward Willats]

On Jan 22, 2013, at 5:54 PM, Keith Medcalf  wrote:

> I prefer the long-lived approach.  Continuously re-initialization of the 
> connection on open, the need to re-read pages into the page cache 
> repetitively, and the subsequent discard of a nicely loaded page-cache on 
> connection close usually incurs non-trivial overhead.

Thanks for this thoughtful response (you too, Simon). The page-cache argument 
is compelling.

In my case, I only have one writer (I think!) during the big transaction, so a 
long-lived, singleton connection or WAL should work for me. I guess I would 
lean toward the former (KISS).

-- Ward

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

Re: [sqlite] Connection philosophy

[Keith Medcalf]

> All is well, EXCEPT, I have ONE big, long transaction that collects and
> stores a lot of data from some sensors.  If this data is big enough, it
> will eventually cause that connection to obtain an exclusive lock on the
> DB. Now if the data collection code subsequently calls any subroutine that
> instances up a quick and dirty DB object on the stack and tries to use it,
> a "database is locked" (or "database busy" on write) error will be
> returned.

> My app is single threaded.

> I am have a couple of options:

> 1. Pass the DB object containing the connection with the open data
> collection transaction to the subroutines that need a DB. (Which is what I
> have been doing.)

This runs the additional operations within the outstanding transaction.  This 
should never block since there is only ever one transaction in progress.

> 2. Secretly keep a single, global connection open and let all the
> instances of the wrapper object use it.

Does the same as (1) but invisibly.

> 3. Switch from rollback to WAL (or something) to keep the connections out
> of each other's way. That seems kind of a big deal since I then have to
> arrange the COMMITs.

WAL will solve the problem for Readers -- that is the writer will never block 
readers nor will readers block a writer.  It will still not permit multiple 
writers though.  You should not have to change any transaction processing 
though for it to work.  Of course, you can still only have a single write 
transaction in progress at any given time.
 
> Generally, I've had a "get in, get out" philosophy, but I can see the
> advantages of a single DB connection that exists for the life of the app.
> (Still, that seems like it might be more fragile than actually closing the
> DB file, dumping caches, and updating the directory.)

Both are equivalent except for the overhead associated with opening and closing 
the database -- bad programming practices such as not finalizing statements etc 
aside.

> So what do all you hot-shots think is best practice? In/out, long-lived or
> something else?

I prefer the long-lived approach.  Continuously re-initialization of the 
connection on open, the need to re-read pages into the page cache repetitively, 
and the subsequent discard of a nicely loaded page-cache on connection close 
usually incurs non-trivial overhead.  Provided that you are committing all 
transactions properly, closing the connection does not achieve any additional 
benefit -- it only increases cost.

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




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

Re: [sqlite] integrity_check "out of memory"

[Max Vlasov]

Thanks, Dominique

I will try asap, but looking at the arithmetics

25 (The length of "Page ? is never used")
*
800 (maximum I could get)

= 20,000 (limit mentioned in the ticket )

looks like this is a winner :)

Max



> Hi Max
>
> It looks perhaps like the bug that was fixed in this checkin:
>
> http://www.sqlite.org/src/info/120c82d56e
>
> Can you try and confirm?
>
> -- Dominique
> ___
> 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] Connection philosophy

[Ward Willats]

On Jan 22, 2013, at 10:07 AM, Simon Slavin  wrote:

> Change the code used in your one big thread so that it counts the number if 
> INSERT/UPDATEs it does and changes transactions and does a little pause after 
> every thousand ops.  Or hundred.  Whatever.
> 

Cool idea, except the folks in Marketing want all the data or none of the data 
each time we collect it. (That is, in my case only, I can't partially commit 
along the way.)

-- Ward

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

Re: [sqlite] Connection philosophy

[Simon Slavin]

On 22 Jan 2013, at 5:39pm, Ward Willats  wrote:

> I have a bunch of home-grown C++ wrappers for Sqlite and when my app needs to 
> use the DB, most routines just instance one of these DB wrapper objects on 
> the stack and go for it. The constructor creates a new DB connection, which 
> is closed at destructor time. Very convenient.
> 
> All is well, EXCEPT, I have ONE big, long transaction that collects and 
> stores a lot of data from some sensors. If this data is big enough, it will 
> eventually cause that connection to obtain an exclusive lock on the DB. Now 
> if the data collection code subsequently calls any subroutine that instances 
> up a quick and dirty DB object on the stack and tries to use it, a "database 
> is locked" (or "database busy" on write) error will be returned.
> 
> My app is single threaded.

Just on opinion in what I hope is many.

First, you should definitely set whatever timeout you want SQLite to use.  Five 
seconds is not unusual.



Change the code used in your one big thread so that it counts the number if 
INSERT/UPDATEs it does and changes transactions and does a little pause after 
every thousand ops.  Or hundred.  Whatever.

set pausecounter to 1000
BEGIN

with each SQL command ...

decrement pausecounter
if pausecounter < 0 {
COMMIT
pause for 1 second
BEGIN
}

COMMIT

If you do that you shouldn't have to change any of the quick and convenient 
coding you've used in other routines.

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

[sqlite] Connection philosophy

[Ward Willats]

Hello.

Just wondering what the group opinion is on something.

I have a bunch of home-grown C++ wrappers for Sqlite and when my app needs to 
use the DB, most routines just instance one of these DB wrapper objects on the 
stack and go for it. The constructor creates a new DB connection, which is 
closed at destructor time. Very convenient.

All is well, EXCEPT, I have ONE big, long transaction that collects and stores 
a lot of data from some sensors. If this data is big enough, it will eventually 
cause that connection to obtain an exclusive lock on the DB. Now if the data 
collection code subsequently calls any subroutine that instances up a quick and 
dirty DB object on the stack and tries to use it, a "database is locked" (or 
"database busy" on write) error will be returned.

My app is single threaded.

I am have a couple of options:

1. Pass the DB object containing the connection with the open data collection 
transaction to the subroutines that need a DB. (Which is what I have been 
doing.)

2. Secretly keep a single, global connection open and let all the instances of 
the wrapper object use it.

3. Switch from rollback to WAL (or something) to keep the connections out of 
each other's way. That seems kind of a big deal since I then have to arrange 
the COMMITs.

Generally, I've had a "get in, get out" philosophy, but I can see the 
advantages of a single DB connection that exists for the life of the app. 
(Still, that seems like it might be more fragile than actually closing the DB 
file, dumping caches, and updating the directory.)

So what do all you hot-shots think is best practice? In/out, long-lived or 
something else?

Thanks

-- Ward



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

Re: [sqlite] reasons for SQLITE_IOERR_FSTAT

[Simon Slavin]

On 22 Jan 2013, at 3:30pm, Tal Tabakman  wrote:

> a user of mine (run under Linux) encountered SQLITE_IOERR_FSTAT error while
> trying to use our application.
> what could be the reasons for such an error ?

That result code is meant to be triggered by the following situation:

A database file is opened creating a database handle, then the file is deleted 
(the unix 'unlink()' call), then another SQLite operation is preformed on the 
handle which already exists.

With a text file this sort of thing doesn't matter: the changes will be 
performed, then the file will be closed and it will disappear along with its 
changes.  But SQLite's use of more than one file for a database (there are 
journal files too) make deletion a problem, so an error should be generated.

There are some other situations which may trigger this result, mostly to do 
with using weird characters in the pathname of the file or doing something else 
that may make lookup of the file fail.  This error may also be triggered if a 
journal file (rather than the main database file) is deleted while a connection 
is open.

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

Re: [sqlite] integrity_check "out of memory"

[Dominique Pellé]

Max Vlasov wrote:

> Hi,
>
> I found with the web search that this error (out of memory  for PRAGMA
> integrity_check) appeared in the wild and there were some fixes related to
> it.
>
> Today I decided to do the check for a base that lives with its own
> encrypted vfs and with an sqlite version older than the current. But the
> check was made with the latest version (3.7.15.2). I got the same error
> (out of memory) for max values larger than 800, for example PRAGMA
> integrity_check(801). If I provide the number below or equal to 800, the
> result is only the (truncated) list of "Page .. is never used". What could
> go wrong? Can it be related to inadequate code of my vfs? The reason why I
> would like to see the entries after 800 is that there can be more serious
> errors than "Page is never used".
>
> Thanks,
>
> Max

Hi Max

It looks perhaps like the bug that was fixed in this checkin:

http://www.sqlite.org/src/info/120c82d56e

Can you try and confirm?

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