Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Adam Jensen]

On 08/26/2016 01:20 PM, David Raymond wrote:
[snip]
> Many do not persist after closing your connection, including synchronous and 
> foreign_keys that you mentioned. In fact, I'd say that the number that do 
> persist is pretty small, and those that do usually require a vacuum or such 
> after being issued so that a new value can get put in the header, or the file 
> is reorganized, etc.
[snip]

Wow, thanks! That's a very relevant detail that doesn't seem to be
documented anywhere and I probably would have continued with the
misunderstanding until something went very wrong (or worse,
incomprehensible system behavior led to frustration and eventual
abandonment).

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

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[David Raymond]

Pragmas are listed out here: http://www.sqlite.org/pragma.html

Many do not persist after closing your connection, including synchronous and 
foreign_keys that you mentioned. In fact, I'd say that the number that do 
persist is pretty small, and those that do usually require a vacuum or such 
after being issued so that a new value can get put in the header, or the file 
is reorganized, etc.

Another way of looking at it is to look at what's in the header here: 
http://www.sqlite.org/fileformat2.html If it doesn't have a spot in the header 
then there's no way to save the change for subsequent connections.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Adam Jensen
Sent: Friday, August 26, 2016 12:55 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

On 08/25/2016 04:41 PM, Adam Jensen wrote:
...
And, as far as I can tell, all of these PRAGMA's seem to persist - they
do not need to be reasserted in any way during subsequent database
accesses.

Do all PRAGMA's, once set to a specific value, stay set? Does anyone
have links to the related documentation?

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

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Adam Jensen]

On 08/25/2016 04:41 PM, Adam Jensen wrote:
> On 08/20/2016 01:01 PM, Simon Slavin wrote:
>> On 20 Aug 2016, at 5:56pm, Lev  wrote:
> [snip]
>>> So this 'setting' is stored in the database file? Is it enough to do the
>>> PRAGMA when the database is created?
>>
>> Yes and yes, but do it this way.
>>
>> 1) Create the database file by opening it.
>> 2) Do something that makes the file non-blank, like creating a table.
>> 3) Issue "PRAGMA journal_mode=WAL"
>> 4) Close the file
>>
>> From that point onwards that database is in WAL mode and everything opening 
>> it automatically knows that.  You do the above sequence using your own 
>> software or the SQLite command-line shell tool.
> 
> 
> When in WAL mode, when would `PRAGMA synchronous = NORMAL` be issued and
> does it persist?

During database creation, this order seems to work:

 PRAGMA page_size = 2048;
 PRAGMA synchronous = NORMAL;
 PRAGMA foreign_keys = ON;

 CREATE TABLE ...;
 CREATE TABLE ...;

 PRAGMA journal_mode = WAL;

.quit

# https://www.sqlite.org/wal.html
# https://www.sqlite.org/pragma.html#pragma_wal_checkpoint

And, as far as I can tell, all of these PRAGMA's seem to persist - they
do not need to be reasserted in any way during subsequent database
accesses.

Do all PRAGMA's, once set to a specific value, stay set? Does anyone
have links to the related documentation?

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

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Adam Jensen]

On 08/20/2016 01:01 PM, Simon Slavin wrote:
> On 20 Aug 2016, at 5:56pm, Lev  wrote:
[snip]
>> So this 'setting' is stored in the database file? Is it enough to do the
>> PRAGMA when the database is created?
> 
> Yes and yes, but do it this way.
> 
> 1) Create the database file by opening it.
> 2) Do something that makes the file non-blank, like creating a table.
> 3) Issue "PRAGMA journal_mode=WAL"
> 4) Close the file
> 
> From that point onwards that database is in WAL mode and everything opening 
> it automatically knows that.  You do the above sequence using your own 
> software or the SQLite command-line shell tool.


When in WAL mode, when would `PRAGMA synchronous = NORMAL` be issued and
does it persist?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Kees Nuyt]

One minor optional addition below

On Sat, 20 Aug 2016 18:01:46 +0100, Simon Slavin
 wrote:

>
>On 20 Aug 2016, at 5:56pm, Lev  wrote:
[...] 
>> So this 'setting' is stored in the database file? Is it enough to do the
>> PRAGMA when the database is created?
>
> Yes and yes, but do it this way.
>
> 1) Create the database file by opening it.

1a) If you need it, before creating any tables:
PRAGMA page_size=;

> 2) Do something that makes the file non-blank,
>like creating a table.
> 3) Issue "PRAGMA journal_mode=WAL"
> 4) Close the file
>
> From that point onwards that database is in WAL mode
> and everything opening it automatically knows that. 
> You do the above sequence using your own software
> or the SQLite command-line shell tool.
>
> Simon.

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

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Lev]

On Sat, 20 Aug 2016 18:01:46 +0100
Simon Slavin  wrote:

> Yes and yes, but do it this way.
> 
> 1) Create the database file by opening it.
> 2) Do something that makes the file non-blank, like creating a table.
> 3) Issue "PRAGMA journal_mode=WAL"
> 4) Close the file
> 
> From that point onwards that database is in WAL mode and everything
> opening it automatically knows that.  You do the above sequence using
> your own software or the SQLite command-line shell tool.

Thanks!
Levente

-- 
73 de HA5OGL
Op.: Levente
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Simon Slavin]

On 20 Aug 2016, at 5:56pm, Lev  wrote:

> I read in the documentation:
> 
> The WAL journaling mode uses a write-ahead log instead of a rollback journal
> to implement transactions. The WAL journaling mode is persistent; after being
> set it stays in effect across multiple database connections and after closing
> and reopening the database. A database in WAL journaling mode can only be
> accessed by SQLite version 3.7.0 or later.
> 
> So this 'setting' is stored in the database file? Is it enough to do the
> PRAGMA when the database is created?

Yes and yes, but do it this way.

1) Create the database file by opening it.
2) Do something that makes the file non-blank, like creating a table.
3) Issue "PRAGMA journal_mode=WAL"
4) Close the file

From that point onwards that database is in WAL mode and everything opening it 
automatically knows that.  You do the above sequence using your own software or 
the SQLite command-line shell tool.

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

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Lev]

On Sat, 6 Aug 2016 17:03:30 -0400
Richard Hipp  wrote:

> Doing "PRAGMA journal_mode=WAL;" on your database (just once, perhaps
> from a command-line shell) will fix this for you.

I read in the documentation:

The WAL journaling mode uses a write-ahead log instead of a rollback journal
to implement transactions. The WAL journaling mode is persistent; after being
set it stays in effect across multiple database connections and after closing
and reopening the database. A database in WAL journaling mode can only be
accessed by SQLite version 3.7.0 or later.

So this 'setting' is stored in the database file? Is it enough to do the
PRAGMA when the database is created?


Thanks,
Levente

-- 
73 de HA5OGL
Op.: Levente
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Jean-Christophe Deschamps]

Rob,

At 18:27 07/08/2016, you wrote:

Too little sleep and far too much coffee.


I was in the same situation, multiplying by 2 instead of dividing, as 
Ryan pointed out.


Nice to see that WAL fits your use case. I for one found it rock solid 
and very useful.


--
Jean-Christophe 


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

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Rob Willett]

Jean-Christophe

Thanks for the update on wal-mode. Your explanation is clear and makes 
sense to us. We can see what we would have a 224MB -wal file, we 
experimented with killing processes whilst updating and generally 
messing around and SQLite did what is was supposed to do. I wouldn’t 
say we were exhaustively testing it and to be honest, I know we can’t 
push SQLite to its limits with the little things we use it for.


We did understand the differences in 3.11.10 and 3.8 re the size of the 
-wal mode, its just that I communicated it poorly. Too little sleep and 
far too much coffee.


We are going to do some more tests, more about familiarising ourselves 
with WAL rather than expecting it to break to be honest. WAL seems to 
work well enough for us and assuming our last conversion tests work OK, 
we’ll shine it in tomorrow night when we get some downtime.


Thanks for you help and elegant description

Rob

On 7 Aug 2016, at 9:59, Jean-Christophe Deschamps wrote:


Rob,

At 08:55 07/08/2016, you wrote:
We think that using WAL mode works for us, indeed inspection seems to 
indicate it does, but the size of the -wal file appears to be far 
larger than would be expected. Is there a problem here? It doesn't 
appear to be a problem but would welcome any comments.


After reading your post I'd like to clear up a few points about WAL 
mode.


We can also see that the main sqlite database is NOT updated (or at 
least the timestamp isn't) whilst we are running the updates in WAL 
mode. This appears to be correct as the updates would be in the -wal 
file.


The WAL mode is persistant and consistant. That means that once 
successfully put in his mode the DB itself will remain in WAL mode for 
every (new) connection. Thus your updates and the long-running query 
are both running under WAL mode. That is, provided the WAL mode was 
set prior to the start of the long-running query, but that detail 
doesn't matter for reads in this case.


It doesn't matter whether your query is a single query statement 
(hence in auto-commit mode) or a huge transaction extracting and 
massaging data in multiple temp tables and myriads of read/write 
statements, all inside an explicit transaction), ACID properties 
guarantee that once your query is started, it will see the DB in the 
state prior to any updates that could occur during its run. Else you 
would obtain potentially dangerously inconsistant data of course.


We have not set the journal_size_limit and we have a -wal file which 
is 224MB in size, somewhat larger than 4MB. We are running


3.8.2 2013-12-06 14:53:30 27392118af4c38c5203a04b8013e1afdb1cebd0d

which does not appear to have the code in 3.11.0 so that the WAL file 
is proportional to the size of the transaction. From the same page of 
the manual:


```
Very large write transactions.

A checkpoint can only complete when no other transactions are 
running, which
means the WAL file cannot be reset in the middle of a write 
transaction. So a large change to a large database
might result in a large WAL file. The WAL file will be checkpointed 
once the write transaction completes
(assuming there are no other readers blocking it) but in the 
meantime, the file can grow very big.


As of SQLite version 3.11.0, the WAL file for a single transaction 
should be proportional in size to
the transaction itself. Pages that are changed by the transaction 
should only be written into the WAL
file once. However, with older versions of SQLite, the same page 
might be written into the WAL file multiple

times if the transaction grows larger than the page cache.
```


Reread the quoted part again: only with SQLite versions 3.11.0 and 
above will a given page be written only once in the WAL file when 
initiated within a given transaction.


Since you're running a prior version, it's pretty logical to expect 
that your updates will cause writes of multiple distinct copies of the 
same pages in the WAL file. You should update your SQLite version to 
see a possible improvement there. That, or refer only to the old 3.8.2 
documentation, but this is an inferior option because there have been 
significant improvements meanwhile.


Also the ratio in the help file (1000 pages or about 4Mb) applies to 
the default page size (4Kb).


Finally, while the long-running query is running, no checkpoint can 
run to completion. Doc states under "Checkpoint starvation.":


However, if a database has many concurrent overlapping readers and 
there is always at least one active reader, then no checkpoints will 
be able to complete and hence the WAL file will grow without bound.


Since you clearly can't introduce a read-gap inside your read query, 
the .wal file will grow as large as it needs until completion of the 
query. You mentionned that you tested with much more frequent updates 
than the real-world case (120x actually), so .wal file size shouldn't 
be an issue in your actual use case.


HTH

--
Jean-Christophe

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Rob Willett]

Ryan,

Thanks for the update.

We have done a few more tests during the day and not had any issues to 
date. This is still on a test version but we are getting a warm, cuddly 
feeling about using WAL mode.


The -wal file grows as you describe and you have explained it very well. 
We were groping in the twilight to get to where we wanted to go, your 
explanation brought a bright beacon of light onto the proceedings. (I 
have been watching too many speeches from the various US political 
conventions in the US though I am British).


We will investigate changing the page size. We would need to work out 
the row size.


I will note in future your OCD and ensure that I am accurate in 
reporting numbers rather than have self inflicted rounding errors, 60x 
is a nicer number than 50x as it maps to mins and secs more easily :)


Thanks again for the help.

Rob

On 7 Aug 2016, at 12:11, R Smith wrote:


On 2016/08/07 8:55 AM, Rob Willett wrote:

Richard, Ryan,

Thanks for this. We were dimly aware of WAL but until now hadn’t 
needed to use it.


We’ve done a quick check with it and it *seems* to work on a test 
database. We’ve all read the docs again and paid attention to 
https://www.sqlite.org/wal.html#bigwal


To test if it works we started our long running analytics query, on 
our test machine it takes around 8 mins. We then speed up the rate of 
updating our database with external data. In the real world an update 
comes along every 3-5 mins, in our test system we queue them up so we 
have them every 6-10 secs so they are around 60x quicker. The updates 
are real data around 3-5MB in size.


We monitored the -wal and the .shm files created as we throw data in 
the database.


The .wal file gets larger and larger until it hits 224MB and then 
stays constant, the .shm file is only 1.8MB and seems to stay at that 
size. We can also see that the main sqlite database is NOT updated 
(or at least the timestamp isn’t) whilst we are running the updates 
in WAL mode. This appears to be correct as the updates would be in 
the -wal file.


I'm truncating this post for brevity - but basically your concern 
about the size (voiced later in the post) is not a concern. What 
happens is the stated 4MB is simply 1000 pages x 4KB default page size 
- your page size might be a lot bigger (and should be set higher 
looking at your DB size and data entry sizes - I think it is "nicer" 
if, at a minimum, a complete row can fit on a page). Further, the WAL 
for your version of SQLite will grow with copies of data and multiple 
inserts in it because of the long-running query not allowing push-back 
check points for the time - and looking at your insert frequency and 
size, your WAL size seems pretty normal. (If you manage it wrong, it 
will fill up Terrabytes - this is the situation you want to avoid, but 
I think you've got it sorted).


The Documentation simply describes the normal situation, which yours 
isn't.


Also, on a point of satisfying my OCD... going on your quoted averages 
- 5 minutes vs. 10 secs is a 30:1 ratio (5 mins = 300 seconds vs. 10 
secs) so the speed is only 30x faster, not 60) - And before anyone 
asks, yes the low end of the ranges given is 3mins (180s) vs. 6 secs 
which is also a 30:1 ratio.  Even if I take the opposite range 
extremes (5 mins vs. 6s) I still only get 50x speedup.  LoL - Sorry, 
I'll shush now :)


As an aside, I think Richard posted a small study of testing multiple 
DB ops with varying page sizes and varying hardware page sizes, and 
basically, IIRC, the Jury was out on best size in the general case 
with 8192 seeming to be a good standard and the idea that the page 
size should try match the underlying OS page size for best performance 
turned out to be a bit of a "sometimes maybe", but the point was made 
that every implementation should experiment to find the optimum size. 
That said, my memory cannot be trusted - could someone re-post that or 
point us to an on-line page somewhere? Thanks!


Cheers,
Ryan

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

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

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[R Smith]

On 2016/08/07 8:55 AM, Rob Willett wrote:

Richard, Ryan,

Thanks for this. We were dimly aware of WAL but until now hadn’t 
needed to use it.


We’ve done a quick check with it and it *seems* to work on a test 
database. We’ve all read the docs again and paid attention to 
https://www.sqlite.org/wal.html#bigwal


To test if it works we started our long running analytics query, on 
our test machine it takes around 8 mins. We then speed up the rate of 
updating our database with external data. In the real world an update 
comes along every 3-5 mins, in our test system we queue them up so we 
have them every 6-10 secs so they are around 60x quicker. The updates 
are real data around 3-5MB in size.


We monitored the -wal and the .shm files created as we throw data in 
the database.


The .wal file gets larger and larger until it hits 224MB and then 
stays constant, the .shm file is only 1.8MB and seems to stay at that 
size. We can also see that the main sqlite database is NOT updated (or 
at least the timestamp isn’t) whilst we are running the updates in WAL 
mode. This appears to be correct as the updates would be in the -wal 
file.


I'm truncating this post for brevity - but basically your concern about 
the size (voiced later in the post) is not a concern. What happens is 
the stated 4MB is simply 1000 pages x 4KB default page size - your page 
size might be a lot bigger (and should be set higher looking at your DB 
size and data entry sizes - I think it is "nicer" if, at a minimum, a 
complete row can fit on a page). Further, the WAL for your version of 
SQLite will grow with copies of data and multiple inserts in it because 
of the long-running query not allowing push-back check points for the 
time - and looking at your insert frequency and size, your WAL size 
seems pretty normal. (If you manage it wrong, it will fill up Terrabytes 
- this is the situation you want to avoid, but I think you've got it 
sorted).


The Documentation simply describes the normal situation, which yours isn't.

Also, on a point of satisfying my OCD... going on your quoted averages - 
5 minutes vs. 10 secs is a 30:1 ratio (5 mins = 300 seconds vs. 10 secs) 
so the speed is only 30x faster, not 60) - And before anyone asks, yes 
the low end of the ranges given is 3mins (180s) vs. 6 secs which is also 
a 30:1 ratio.  Even if I take the opposite range extremes (5 mins vs. 
6s) I still only get 50x speedup.  LoL - Sorry, I'll shush now :)


As an aside, I think Richard posted a small study of testing multiple DB 
ops with varying page sizes and varying hardware page sizes, and 
basically, IIRC, the Jury was out on best size in the general case with 
8192 seeming to be a good standard and the idea that the page size 
should try match the underlying OS page size for best performance turned 
out to be a bit of a "sometimes maybe", but the point was made that 
every implementation should experiment to find the optimum size. That 
said, my memory cannot be trusted - could someone re-post that or point 
us to an on-line page somewhere? Thanks!


Cheers,
Ryan

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

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Jean-Christophe Deschamps]

Rob,

At 08:55 07/08/2016, you wrote:
We think that using WAL mode works for us, indeed inspection seems to 
indicate it does, but the size of the -wal file appears to be far 
larger than would be expected. Is there a problem here? It doesn't 
appear to be a problem but would welcome any comments.


After reading your post I'd like to clear up a few points about WAL mode.

We can also see that the main sqlite database is NOT updated (or at 
least the timestamp isn't) whilst we are running the updates in WAL 
mode. This appears to be correct as the updates would be in the -wal file.


The WAL mode is persistant and consistant. That means that once 
successfully put in his mode the DB itself will remain in WAL mode for 
every (new) connection. Thus your updates and the long-running query 
are both running under WAL mode. That is, provided the WAL mode was set 
prior to the start of the long-running query, but that detail doesn't 
matter for reads in this case.


It doesn't matter whether your query is a single query statement (hence 
in auto-commit mode) or a huge transaction extracting and massaging 
data in multiple temp tables and myriads of read/write statements, all 
inside an explicit transaction), ACID properties guarantee that once 
your query is started, it will see the DB in the state prior to any 
updates that could occur during its run. Else you would obtain 
potentially dangerously inconsistant data of course.


We have not set the journal_size_limit and we have a -wal file which 
is 224MB in size, somewhat larger than 4MB. We are running


3.8.2 2013-12-06 14:53:30 27392118af4c38c5203a04b8013e1afdb1cebd0d

which does not appear to have the code in 3.11.0 so that the WAL file 
is proportional to the size of the transaction. From the same page of 
the manual:


```
Very large write transactions.

A checkpoint can only complete when no other transactions are running, 
which
means the WAL file cannot be reset in the middle of a write 
transaction. So a large change to a large database
might result in a large WAL file. The WAL file will be checkpointed 
once the write transaction completes
(assuming there are no other readers blocking it) but in the meantime, 
the file can grow very big.


As of SQLite version 3.11.0, the WAL file for a single transaction 
should be proportional in size to
the transaction itself. Pages that are changed by the transaction 
should only be written into the WAL
file once. However, with older versions of SQLite, the same page might 
be written into the WAL file multiple

times if the transaction grows larger than the page cache.
```


Reread the quoted part again: only with SQLite versions 3.11.0 and 
above will a given page be written only once in the WAL file when 
initiated within a given transaction.


Since you're running a prior version, it's pretty logical to expect 
that your updates will cause writes of multiple distinct copies of the 
same pages in the WAL file. You should update your SQLite version to 
see a possible improvement there. That, or refer only to the old 3.8.2 
documentation, but this is an inferior option because there have been 
significant improvements meanwhile.


Also the ratio in the help file (1000 pages or about 4Mb) applies to 
the default page size (4Kb).


Finally, while the long-running query is running, no checkpoint can run 
to completion. Doc states under "Checkpoint starvation.":


However, if a database has many concurrent overlapping readers and 
there is always at least one active reader, then no checkpoints will 
be able to complete and hence the WAL file will grow without bound.


Since you clearly can't introduce a read-gap inside your read query, 
the .wal file will grow as large as it needs until completion of the 
query. You mentionned that you tested with much more frequent updates 
than the real-world case (120x actually), so .wal file size shouldn't 
be an issue in your actual use case.


HTH

--
Jean-Christophe  


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

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Rob Willett]

Richard, Ryan,

Thanks for this. We were dimly aware of WAL but until now hadn’t 
needed to use it.


We’ve done a quick check with it and it *seems* to work on a test 
database. We’ve all read the docs again and paid attention to 
https://www.sqlite.org/wal.html#bigwal


To test if it works we started our long running analytics query, on our 
test machine it takes around 8 mins. We then speed up the rate of 
updating our database with external data. In the real world an update 
comes along every 3-5 mins, in our test system we queue them up so we 
have them every 6-10 secs so they are around 60x quicker. The updates 
are real data around 3-5MB in size.


We monitored the -wal and the .shm files created as we throw data in the 
database.


The .wal file gets larger and larger until it hits 224MB and then stays 
constant, the .shm file is only 1.8MB and seems to stay at that size. We 
can also see that the main sqlite database is NOT updated (or at least 
the timestamp isn’t) whilst we are running the updates in WAL mode. 
This appears to be correct as the updates would be in the -wal file.


The times taken for each updates seems a little slower (10% possibly but 
that could be just because we are looking at it) but since the data is 
real and variable in size, it might be just our subjective impression.


Once the long running read-only analytics query completes, the main 
sqlite database appears to get updated (or at least the timestamp on the 
file is updated) as we are still updating with our test data and the 
-wal files are still being used.


Once we stop updating with our test data, the -wal files and .shm files 
disappear (as expected).


A quick check of the database appears to show its correct.

One question though, the size of the -wal file worries us. 
https://www.sqlite.org/wal.html#bigwal states


```
Avoiding Excessively Large WAL Files

In normal cases, new content is appended to the WAL file until the WAL 
file accumulates about 1000 pages (and is
thus about 4MB in size)  at which point a checkpoint is automatically 
run and the WAL file is recycled.
The checkpoint does not normally truncate the WAL file (unless the 
journal_size_limit pragma is set).
Instead, it merely causes SQLite to start overwriting the WAL file from 
the beginning. This is done because
it is normally faster to overwrite an existing file than to append. When 
the last connection to a database
closes, that connection does one last checkpoint and then deletes the 
WAL and its associated shared-memory

file, to clean up the disk.
```

We have not set the journal_size_limit and we have a -wal file which is 
224MB in size, somewhat larger than 4MB. We are running


3.8.2 2013-12-06 14:53:30 27392118af4c38c5203a04b8013e1afdb1cebd0d

which does not appear to have the code in 3.11.0 so that the WAL file is 
proportional to the size of the transaction. From the same page of the 
manual:


```
Very large write transactions.

A checkpoint can only complete when no other transactions are running, 
which
means the WAL file cannot be reset in the middle of a write transaction. 
So a large change to a large database
might result in a large WAL file. The WAL file will be checkpointed once 
the write transaction completes
(assuming there are no other readers blocking it) but in the meantime, 
the file can grow very big.


As of SQLite version 3.11.0, the WAL file for a single transaction 
should be proportional in size to
the transaction itself. Pages that are changed by the transaction should 
only be written into the WAL
file once. However, with older versions of SQLite, the same page might 
be written into the WAL file multiple

times if the transaction grows larger than the page cache.
```

We think that using WAL mode works for us, indeed inspection seems to 
indicate it does, but the size of the -wal file appears to be far larger 
than would be expected. Is there a problem here? It doesn’t appear to 
be a problem but would welcome any comments.


Thanks for taking the time to reply.

Rob


On 6 Aug 2016, at 22:35, R Smith wrote:


On 2016/08/06 10:50 PM, Rob Willett wrote:


Our understanding of this is that many processes can READ the 
database at the same time but NO process can INSERT/UPDATE if another 
is reading. We had thought that one process can write and multiple 
processes can read. Our reading (no pun intended) now of this 
paragraph from the manual is that you cannot write if one or more 
processes is reading. Have we understood this correctly? If so is 
there an easy way to get around this?


The Write-Ahead-Log (WAL) journal mode will help you. It basically 
allows a writer to write to the WAL Log in stead of the main database 
so that any amount of readers can still do their thing reading the 
database (and the parts of the WAL journal that is already committed, 
or even parts still in progress if you use "read_uncommitted" mode). 
SQLite then pushes committed data into the DB file based on 
Checkpoints which 

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[R Smith]

On 2016/08/06 10:50 PM, Rob Willett wrote:


Our understanding of this is that many processes can READ the database 
at the same time but NO process can INSERT/UPDATE if another is 
reading. We had thought that one process can write and multiple 
processes can read. Our reading (no pun intended) now of this 
paragraph from the manual is that you cannot write if one or more 
processes is reading. Have we understood this correctly? If so is 
there an easy way to get around this?


The Write-Ahead-Log (WAL) journal mode will help you. It basically 
allows a writer to write to the WAL Log in stead of the main database so 
that any amount of readers can still do their thing reading the database 
(and the parts of the WAL journal that is already committed, or even 
parts still in progress if you use "read_uncommitted" mode). SQLite then 
pushes committed data into the DB file based on Checkpoints which you 
can invoke directly or set up to happen every so often.


This is the new way to do things and the way you should always use 
unless you have a specific reason not to (which might include file 
restrictions, needing read-only-ness, separate speedy DBs that doesn't 
fsync() so much, etc.)


More information here:
https://www.sqlite.org/wal.html

Your DB is quite big and it seems you write often, so please take 
special note of this section:

https://www.sqlite.org/wal.html#bigwal


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

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Richard Hipp]

On 8/6/16, Rob Willett  wrote:
>
> What we have now found is that when we are running the analytics query
> in one Perl process, we can no longer UPDATE the main database through
> another Perl process. We are getting “database is locked” errors.

Doing "PRAGMA journal_mode=WAL;" on your database (just once, perhaps
from a command-line shell) will fix this for you.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Locking databases - Possibly (probably?) a dumb question

[Rob Willett]

Hi,

We’ve been using Sqlite though Perl for some time now and have started 
to get more adventurous.


Our SQLite database is around 32GB in size, is created and manipulated 
by a single Perl process and is working well.


What we now want to do is mine the database using a very long running 
query to generate analytics.We have the SQL written to query the 
database, we have requested help on this list from last year and we are 
happy with the SQL query itself. It takes a long time (between 5 and 60 
mins) as its a complex query and collates an awful lot of data. Whilst 
we would love to have the query execute in 10 secs, thats not going to 
happen due to the size of the data and the queries we need to execute.


What we have now found is that when we are running the analytics query 
in one Perl process, we can no longer UPDATE the main database through 
another Perl process. We are getting “database is locked” errors.


We don’t need any help with our SQL but we are trying to understand 
how the locking works (at a high level) in SQL. Reading the docs 
(https://www.sqlite.org/lockingv3.html) for locking seems to indicate 
the problem


```
SHARED	The database may be read but not written. Any number of processes 
can hold SHARED locks at the same time, hence there can be many 
simultaneous readers. But no other thread or process is allowed to write 
to the database file while one or more SHARED locks are active.


```

Our understanding of this is that many processes can READ the database 
at the same time but NO process can INSERT/UPDATE if another is reading. 
We had thought that one process can write and multiple processes can 
read. Our reading (no pun intended) now of this paragraph from the 
manual is that you cannot write if one or more processes is reading. 
Have we understood this correctly? If so is there an easy way to get 
around this?


Further down the page we find

```
5.1 Writer starvation

In SQLite version 2, if many processes are reading from the database, it 
might be the case that there is never a time when there are no active 
readers. And if there is always at least one read lock on the database, 
no process would ever be able to make changes to the database because it 
would be impossible to acquire a write lock. This situation is called 
writer starvation.


SQLite version 3 seeks to avoid writer starvation through the use of the 
PENDING lock. The PENDING lock allows existing readers to continue but 
prevents new readers from connecting to the database. So when a process 
wants to write a busy database, it can set a PENDING lock which will 
prevent new readers from coming in. Assuming existing readers do 
eventually complete, all SHARED locks will eventually clear and the 
writer will be given a chance to make its changes.



```

We cannot do this as we cannot set the PENDING lock as we do not know 
when we need to do a write.


If we have a single reader and therefore no writers we do have a Plan B 
(and a plan C) so whilst there is some work for us (1-2 days), we can 
put a workflow into the system to get around it.


Thanks,

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

Re: [sqlite] Locking semantics are broken?

[Stephen Chrzanowski]

In Mr Smiths examples, one statement effect is a direct result of an action
you (subjectively speaking) knowingly did, or did not do, to the car.  You
did not fuel the car.  You did take the wheels off.  There by, your car is
essentially a hunk of metal taking up space.  It doesn't function as
designed.

The second, if you're driving along, blow the radiator hose, break a belt,
spark plug fails, coils and cables start shorting, push a rod through the
engine housing, whatever.  You've got a failure in the system that prevents
the car from function that happens due to indirect activities with the
vehicle.

Both cases essentially make the car not run, or becomes non-functional, or
causes damage.  Say a spark hitting the pool of drying oil on the engine
which sparks a fire?  Or the belt you break happens to be the timing belt?
Piston rod that goes through the case?  Sparks grounding to the engine
instead of in the cylinder head?

For SQLite, running your software where your data store is on the network,
your running the philosophy of the second statement.  Some
people/businesses have run their SQLite database system for YEARS without
an issue.  Myself, I've never been stranded in my 20 years of driving with
any of my vehicles, and trust me, I've driven wrecks of cars, so I've been
lucky.  My mother, however, one time managed to drive about 2 hours down
the highway, then come to a stop sign on an off-ramp, and the transmission
just would not engage in gear when she went to go.

I've NEVER heard of an instance of MySQL, Postgres, MSSQL, or any other
major database that reliably runs on a different machine.  Different
partitions, different file systems, or different hard drives all controlled
by a single OS, yes, but all run ON a single OS, not across a network.

My thoughts on this are all illustrated here :
http://randomthoughts.ca/index.php?/archives/7-Serverless-Servers-using-NFS-Why-it-cant-happen.html




On Tue, Jun 28, 2016 at 12:05 PM, R Smith  wrote:


> Ha, true but the point is only semantic though...
>
> The statement: "My car is reliable so long as I remember to add Fuel and
> not remove the wheels.", is not really self-contradicting, is it?
>
> "My car is reliable so long as it doesn't break" - is a different matter.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking semantics are broken?

[R Smith]

On 2016/06/28 5:46 PM, John Found wrote:

On Wed, 29 Jun 2016 01:03:28 +1000
"dandl"  wrote:


But if everything is configured right and working right and nothing bad
happens then it is highly reliable over very large volumes of transactions.

For me, this is a clear definition of the term "not reliable". Isn't it?



Ha, true but the point is only semantic though...

The statement: "My car is reliable so long as I remember to add Fuel and 
not remove the wheels.", is not really self-contradicting, is it?


"My car is reliable so long as it doesn't break" - is a different matter.

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

Re: [sqlite] Locking semantics are broken?

[John Found]

On Wed, 29 Jun 2016 01:03:28 +1000
"dandl"  wrote:

> But if everything is configured right and working right and nothing bad
> happens then it is highly reliable over very large volumes of transactions.

For me, this is a clear definition of the term "not reliable". Isn't it?

-- 
http://fresh.flatassembler.net
http://asm32.info
John Found 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking semantics are broken?

[dandl]

Fair comment.

We have seen problems:
1. In all versions of Windows based on the 95 kernel, and especially Windows
ME (but not the NT kernel since 3.5)
2. At any time if the network infrastructure is unreliable (too many errors
or retries)
3. At any time if a client machine misbehaves eg crashes while holding a
lock, or attempts its own file accesses etc
4. Recently, apparently related to SMB 3.0+, due to more aggressive
performance optimisations.

But if everything is configured right and working right and nothing bad
happens then it is highly reliable over very large volumes of transactions.

I guess my plea would be to emphasise the need to pay attention to all the
details and to warn that there is still a risk of uncontrolled data loss,
rather than just branding it as 'broken'.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org



> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
> Sent: Tuesday, 28 June 2016 7:28 PM
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] Locking semantics are broken?
> 
> 
> On 28 Jun 2016, at 9:07am, dandl <da...@andl.org> wrote:
> 
> >> Do not use SQLite for concurrent access over a network connection.
> >> Locking semantics are broken for most network filesystems, so you
> >> will have corruption issues that are no fault of SQLite.
> >
> > I have seen this comment made more than once on this list. Is there any
> reliable evidence to support this for a Windows-based network?
> 
> Actually, the problem that causes causes people to make that warning
occurs
> in POSIX.  See the beginning of section 6.0 in this page:
> 
> <https://www.sqlite.org/lockingv3.html>
> 
> However, while the above is a definitely known, verifiable problem, with
> every implementation of POSIX, we have had occasional reports about
locking
> problems with Windows as documented in section 9.1 here:
> 
> <https://www.sqlite.org/atomiccommit.html>
> 
> The problem is that with the numerous versions of Windows, File System and
> Network system, nobody has come up with a fault which can be reproduced by
> the developers.  But we do get enough vague reports of problems with
Windows
> to make us believe that there is something wrong somewhere.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Locking semantics are broken?

[Donald Shepherd]

On Tue, 28 Jun 2016 at 19:17 Rowan Worth  wrote:

> On 28 June 2016 at 16:07, dandl  wrote:
>
> > > Do not use SQLite for concurrent access over a network connection.
> > Locking
> > > semantics are broken for most network filesystems, so you will have
> > > corruption issues that are no fault of SQLite.
> >
> > I have seen this comment made more than once on this list. Is there any
> > reliable evidence to support this for a Windows-based network?
> >
> > Disclosure: we wrote and maintain an ISAM-based multi-user database
> > product which relies on network locking. We have conducted exhaustive
> tests
> > over many years and in our opinion, locking and multi-user semantics on
> > Windows XP and later networks are reliable and free of errors, if
> performed
> > correctly by the client software.
> >
>
> I can't comment on windows sorry, but in-house we use sqlite databases
> shared between many unix clients via network file systems. Over the years
> we've used nfs3, nfs4, and lustre.
>
> These databases are subject to highly concurrent usage every working day.
> In the past two years we've had maybe one corruption issue which implicated
> the file system (client side logging suggested that four RESERVED locks
> were obtained concurrently).
>
>
> More commonly corruption has been the result of user/application
> misbehaviour:
>
> 1. Users copying databases while they're being updated (leaving them with a
> corrupt copy)
> 2. Users inadvertently symlinking/hardlinking database files
> 3. Our application inadvertently discarding sqlite's locks after backing up
> the database (thanks POSIX locking semantics)
>
>
> (3) was the main offender for us. Since figuring that out we've been left
> with a very robust environment - but not bullet proof as indicated above. I
> can imagine this kind of thing being sensitive to network/file system
> configuration, which is not easy to diagnose as a sysadmin let alone via
> email so in that sense I understand why network file systems are
> discouraged on the list (aside from the fact that sqlite and its
> database-level single user lock was not designed for networked concurrent
> usage).
>

We are Windows-based and the only corruptions we've seen is naive copying
of an in-use database (#1 in your list) plus bugs such as crashes when
transferring the database between servers resulting in an incomplete copy.
Neither have been the responsibility of SQLite and we've generally
mitigated both by tool and usage improvement as best we can.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking semantics are broken?

[Simon Slavin]

On 28 Jun 2016, at 9:07am, dandl  wrote:

>> Do not use SQLite for concurrent access over a network connection. Locking
>> semantics are broken for most network filesystems, so you will have
>> corruption issues that are no fault of SQLite.
> 
> I have seen this comment made more than once on this list. Is there any 
> reliable evidence to support this for a Windows-based network?

Actually, the problem that causes causes people to make that warning occurs in 
POSIX.  See the beginning of section 6.0 in this page:



However, while the above is a definitely known, verifiable problem, with every 
implementation of POSIX, we have had occasional reports about locking problems 
with Windows as documented in section 9.1 here:



The problem is that with the numerous versions of Windows, File System and 
Network system, nobody has come up with a fault which can be reproduced by the 
developers.  But we do get enough vague reports of problems with Windows to 
make us believe that there is something wrong somewhere.

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

Re: [sqlite] Locking semantics are broken?

[Rowan Worth]

On 28 June 2016 at 16:07, dandl  wrote:

> > Do not use SQLite for concurrent access over a network connection.
> Locking
> > semantics are broken for most network filesystems, so you will have
> > corruption issues that are no fault of SQLite.
>
> I have seen this comment made more than once on this list. Is there any
> reliable evidence to support this for a Windows-based network?
>
> Disclosure: we wrote and maintain an ISAM-based multi-user database
> product which relies on network locking. We have conducted exhaustive tests
> over many years and in our opinion, locking and multi-user semantics on
> Windows XP and later networks are reliable and free of errors, if performed
> correctly by the client software.
>

I can't comment on windows sorry, but in-house we use sqlite databases
shared between many unix clients via network file systems. Over the years
we've used nfs3, nfs4, and lustre.

These databases are subject to highly concurrent usage every working day.
In the past two years we've had maybe one corruption issue which implicated
the file system (client side logging suggested that four RESERVED locks
were obtained concurrently).


More commonly corruption has been the result of user/application
misbehaviour:

1. Users copying databases while they're being updated (leaving them with a
corrupt copy)
2. Users inadvertently symlinking/hardlinking database files
3. Our application inadvertently discarding sqlite's locks after backing up
the database (thanks POSIX locking semantics)


(3) was the main offender for us. Since figuring that out we've been left
with a very robust environment - but not bullet proof as indicated above. I
can imagine this kind of thing being sensitive to network/file system
configuration, which is not easy to diagnose as a sysadmin let alone via
email so in that sense I understand why network file systems are
discouraged on the list (aside from the fact that sqlite and its
database-level single user lock was not designed for networked concurrent
usage).

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

Re: [sqlite] Locking semantics are broken?

[Clemens Ladisch]

dandl wrote:
>> Do not use SQLite for concurrent access over a network connection. Locking
>> semantics are broken for most network filesystems, so you will have
>> corruption issues that are no fault of SQLite.
>
> I have seen this comment made more than once on this list. Is there any
> reliable evidence to support this for a Windows-based network?

There have been locking bugs in quite a few Windows versions, but these
get fixed.

Opportunistic locks can produce errors when the network goes down
temporarily:
1. the client has an exclusive lock;
2. the server cannot tell the client to release it, and after some time
   assumes the client has crashed;
3. the server moves ownership to another client;
4. both clients assume they have the exclusive lock, and do writes.

But other than that, locking should work just fine.


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

[sqlite] Locking semantics are broken?

[dandl]

> Do not use SQLite for concurrent access over a network connection. Locking
> semantics are broken for most network filesystems, so you will have
> corruption issues that are no fault of SQLite.

I have seen this comment made more than once on this list. Is there any 
reliable evidence to support this for a Windows-based network?

Disclosure: we wrote and maintain an ISAM-based multi-user database product 
which relies on network locking. We have conducted exhaustive tests over many 
years and in our opinion, locking and multi-user semantics on Windows XP and 
later networks are reliable and free of errors, if performed correctly by the 
client software.

[We use the same semantics for a Linux or Unix-based system with multiple 
terminals, but not on any Unix-based network. This is only about Windows.]

If Sqlite has a problem then perhaps it can be fixed.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org


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

Re: [sqlite] Locking errors on network

[Eduardo Morras]

On Mon, 10 Nov 2014 20:33:04 +0200
RSmith  wrote:

> 
> On 2014/11/10 20:22, Mike McWhinney wrote:
> > So SQLite shouldn't be used at all on a network?  Aren't there any
> > other provisions to handled the locking errors if/when they occur?
> 
> It is not about SQLite, it is about the Networking systems lying
> about whether a file is locked or not. No RDBMS can trust the
> network, but the client-server types do not care since they control
> the locking and do not depend on the OS / file status. SQLite however
> depends on it and as such cannot accurately (or timeously I should
> say) verify such status via a Network. On a local drive this is never
> a problem.
> 
> If you need Networking or User-control, please use a client-server
> type database.
> 
> There is one Client-Server implementation of SQLite (SQLightening I
> think) but it is neither free nor easy to convert to. You can write
> your own server too, but the best bet is using MySQL or PostGres in
> these cases.

You can create your own sqlite server (I did and use it, with nanomsg for 
client-server communication), it's medium-hard and for tiny hardware, near 
embedded, works.

A good file to start with, as I did, is in Sqlite repository, check 
http://www.sqlite.org/src/artifact/a2615049954cbb9cfb4a62e18e2f0616e4dc38fe 
a.k.a. src/test_server.c

But, as others aim and hit, you should use a real C/S RDBMS, my preference, 
PostgreSQL server.

HTH

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

Re: [sqlite] Locking errors on network

[Scott Robison]

On Mon, Nov 10, 2014 at 12:56 PM,  wrote:

> On 2014-11-10 18:33, RSmith wrote:
> 
>
>> There is one Client-Server implementation of SQLite (SQLightening I
>> think) but it is neither free nor easy to convert to.
>>
>
> Doing some Googling, this looks like the thing:
>
>   http://sqlitening.com
>
> They don't seem to sell it any more (last version was released Dec
> 2012), though the support forums are still online.
>
>
>  You can write
>> your own server too, but the best bet is using MySQL or PostGres in
>> these cases.
>>
>
> Use PostgreSQL (www.postgresql.org). :)
>
> This is kinda interesting btw.  Keynote speaker for PGCon 2014 was
> Richard Hipp:
>
>   SQLite: Protégé of PostgreSQL
>   https://www.youtube.com/watch?v=ZvmMzI0X7fE


Sorry for the previous noise. Clicked send instead of expanding the quotes.

Anyway, I've given a little thought in the past to creating a VFS for
SQLite that actually communicates with a "server". That server would be a
replacement for the buggy network file system implementations that plague
SQLite for networked use. Of course, it would no longer be a zero
configuration completely embedded system, but it would be a fairly minimal
shim.

Of course, it would itself require debugging, so it's not like it would be
a magical solution. Still, if the SQLite "service" just exposed a virtual
block device with accurate locking, I could see it being a useful
complement.

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

Re: [sqlite] Locking errors on network

[Tim Streater]

On 10 Nov 2014 at 19:38, Mike McWhinney  wrote: 

> I am using Sqlite.NET client in C#. How would I go about defining a sqlite
> busy timeout handler?

Is the API not documented somewhere (I don't know what Sqlite.NET client or C# 
are, so can't help)? Surely you must have some doc or how else do you use the 
API at the moment?


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

Re: [sqlite] Locking errors on network

[Scott Robison]

On Mon, Nov 10, 2014 at 12:56 PM,  wrote:

> On 2014-11-10 18:33, RSmith wrote:
> 
>
>> There is one Client-Server implementation of SQLite (SQLightening I
>> think) but it is neither free nor easy to convert to.
>>
>
> Doing some Googling, this looks like the thing:
>
>   http://sqlitening.com
>
> They don't seem to sell it any more (last version was released Dec
> 2012), though the support forums are still online.
>
>
>  You can write
>> your own server too, but the best bet is using MySQL or PostGres in
>> these cases.
>>
>
> Use PostgreSQL (www.postgresql.org). :)
>
> This is kinda interesting btw.  Keynote speaker for PGCon 2014 was
> Richard Hipp:
>
>   SQLite: Protégé of PostgreSQL
>   https://www.youtube.com/watch?v=ZvmMzI0X7fE
>
> Just saying. ;)
>
> Regards and best wishes,
>
> Justin Clift
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Locking errors on network

[justin]

On 2014-11-10 18:33, RSmith wrote:


There is one Client-Server implementation of SQLite (SQLightening I
think) but it is neither free nor easy to convert to.


Doing some Googling, this looks like the thing:

  http://sqlitening.com

They don't seem to sell it any more (last version was released Dec
2012), though the support forums are still online.



You can write
your own server too, but the best bet is using MySQL or PostGres in
these cases.


Use PostgreSQL (www.postgresql.org). :)

This is kinda interesting btw.  Keynote speaker for PGCon 2014 was
Richard Hipp:

  SQLite: Protégé of PostgreSQL
  https://www.youtube.com/watch?v=ZvmMzI0X7fE

Just saying. ;)

Regards and best wishes,

Justin Clift

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

Re: [sqlite] Locking errors on network

[Mike McWhinney]

I am using Sqlite.NET client in C#. How would I go about defining a sqlite busy 
timeout handler?  


Thanks
Mike



On Monday, November 10, 2014 1:35 PM, Tim Streater  
wrote:
 


On 10 Nov 2014 at 18:22, Mike McWhinney  wrote: 

> So SQLite shouldn't be used at all on a network?  Aren't there any other
> provisions to handled the locking errors if/when
> they occur?

You tried setting a timeout as pointed to here?

  https://www.sqlite.org/faq.html#q5

(not that this would avoid any potential corruption issue).


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

Re: [sqlite] Locking errors on network

[Tim Streater]

On 10 Nov 2014 at 18:22, Mike McWhinney  wrote: 

> So SQLite shouldn't be used at all on a network?  Aren't there any other
> provisions to handled the locking errors if/when
> they occur?

You tried setting a timeout as pointed to here?

   https://www.sqlite.org/faq.html#q5

(not that this would avoid any potential corruption issue).


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

Re: [sqlite] Locking errors on network

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/10/2014 10:22 AM, Mike McWhinney wrote:
> So SQLite shouldn't be used at all on a network?  Aren't there any
> other provisions to handled the locking errors if/when they occur?

Network filesystems do not implement locking and other operations
*exactly* the same as for (most) local filesystems.  This is done due
to the protocols involved, race conditions, performance
considerations, latencies, and various other reasons.  You are seeing
the locking errors as a symptom of this.

If you use SQLite with a network then your data will eventually end up
corrupted.  Yes it is possible to sweep some stuff under the rug but
that does not mean corruption won't happen.  SQLite won't be able to
prevent it, and often may not detect it for a while.

This page describes how SQLite does locking as well as pointing to
some newer alternatives:

  https://www.sqlite.org/lockingv3.html

See also:

  https://www.sqlite.org/whentouse.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlRhBm8ACgkQmOOfHg372QTcLgCfblMaFauIRgE83WOcF9z2M6BV
BMYAnRSP1KwC+69vb5fUMsGeGbdImHU1
=1mbq
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking errors on network

[Simon Slavin]

On 10 Nov 2014, at 6:22pm, Mike McWhinney  wrote:

> So SQLite shouldn't be used at all on a network?

SQLite is not designed for hosting a database on a server for access by lots of 
different computers at the same time.  To do that efficiently you need a 
client/server design and SQLite doesn't have it.

> Aren't there any other provisions to handled the locking errors if/when
> they occur?

The problem is at the operating system and Network File System level.  The 
required support is often just too buggy to be usable.  See "Client/Server 
Applications" on this page:



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

Re: [sqlite] Locking errors on network

[RSmith]

On 2014/11/10 20:22, Mike McWhinney wrote:

So SQLite shouldn't be used at all on a network?  Aren't there any other 
provisions to handled the locking errors if/when
they occur?


It is not about SQLite, it is about the Networking systems lying about whether a file is locked or not. No RDBMS can trust the 
network, but the client-server types do not care since they control the locking and do not depend on the OS / file status. SQLite 
however depends on it and as such cannot accurately (or timeously I should say) verify such status via a Network. On a local drive 
this is never a problem.


If you need Networking or User-control, please use a client-server type 
database.

There is one Client-Server implementation of SQLite (SQLightening I think) but it is neither free nor easy to convert to. You can 
write your own server too, but the best bet is using MySQL or PostGres in these cases.



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

Re: [sqlite] Locking errors on network

[Mike McWhinney]

So SQLite shouldn't be used at all on a network?  Aren't there any other 
provisions to handled the locking errors if/when
they occur?




On Monday, November 10, 2014 12:10 PM, Roger Binns  
wrote:
 


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


On 11/10/2014 09:41 AM, Mike McWhinney wrote:
> Please let know if there are any other solutions to this database
> locking problem as used on a network.

Yes.  Do not do it.  See the FAQ:

  https://www.sqlite.org/faq.html#q5

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlRg/6wACgkQmOOfHg372QQAfgCeLCZ7I4uC/3p+bNSuGQN0uTUB
6LEAoLjp4/yJzVJSWzGDq7cam8pezRma
=jie1
-END PGP SIGNATURE-
ail_
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking errors on network

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/10/2014 09:41 AM, Mike McWhinney wrote:
> Please let know if there are any other solutions to this database
> locking problem as used on a network.

Yes.  Do not do it.  See the FAQ:

  https://www.sqlite.org/faq.html#q5

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlRg/6wACgkQmOOfHg372QQAfgCeLCZ7I4uC/3p+bNSuGQN0uTUB
6LEAoLjp4/yJzVJSWzGDq7cam8pezRma
=jie1
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Locking errors on network

[Mike McWhinney]

Hello,

I continue to have "database is locked errors" when running an application 
which accesses a SQLite dabase on a network.


I have tried many of the connection string options: pooling, changing default 
time out.


I just recently tried a block of code which does a BeginTransaction and Commit. 
Before I did not use this. However it does not seem to have an effect
on the locking problem.


  string sql;

sql = "UPDATE APPOINTMENTS SET " +
 // "PatientID=" + myPatientID + "," +
  "TimeStampIsSeenByMA='" + nowString + "'" +

  " WHERE (" +
  "PatientID=" + myPatientID + " AND Date=" + "'" +
  dateString + "'" + ")";

   
SQLiteCommand command = new SQLiteCommand(sql, OMConnection.sqConn);
int numRowsAffected = 0;
SQLiteTransaction tran;
tran = OMConnection.sqConn.BeginTransaction(false);
numRowsAffected = command.ExecuteNonQuery();
command.Dispose();
tran.Commit();


Please let know if there are any other solutions to this database locking 
problem as used on a network.

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

Re: [sqlite] Sqlite locking issue with ATTACH'ed databases

[Loren Keagle]

I've narrowed this locking issue down to a very simply test case.  It seems as 
though having the same file attached multiple times with different names 
prevents exclusive or immediate transactions from acquiring a lock.  Deferred 
transactions still seem to work fine.  Try the following code:

#define SQLITE_CHECK(s) do { int localret = (s); if (localret != SQLITE_OK) 
exit(__LINE__); } while (false);

int main (void)
{
::DeleteFile(L"Test.sqlite");
::DeleteFile(L"TestSub.sqlite");

// Open master database
sqlite3* db = NULL;
SQLITE_CHECK(sqlite3_open_v2("Test.sqlite", , SQLITE_OPEN_FULLMUTEX 
| SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr));
// Attach write database
SQLITE_CHECK(sqlite3_exec(db, "ATTACH DATABASE 'TestSub.sqlite' as 
write1;", NULL, NULL, NULL));
SQLITE_CHECK(sqlite3_exec(db, "CREATE TABLE write1.TestTable (id 
INTEGER PRIMARY KEY, IntColumn INTEGER);", NULL, NULL, NULL));
// Attach read database
SQLITE_CHECK(sqlite3_exec(db, "ATTACH DATABASE 'TestSub.sqlite' as 
read1;", NULL, NULL, NULL));

 Detach read db
//SQLITE_CHECK(sqlite3_exec(db, "DETACH DATABASE read1;", NULL, NULL, 
NULL));

// Exclusive transactions fail
SQLITE_CHECK(sqlite3_exec(db, "BEGIN EXCLUSIVE TRANSACTION;", NULL, 
NULL, NULL));
// Immediate transactions fail
//SQLITE_CHECK(sqlite3_exec(db, "BEGIN IMMEDIATE TRANSACTION;", NULL, 
NULL, NULL));
// Deferred transactions are fine
//SQLITE_CHECK(sqlite3_exec(db, "BEGIN DEFERRED TRANSACTION;", NULL, 
NULL, NULL));

// Write to the database via the write1 table
SQLITE_CHECK(sqlite3_exec(db, "INSERT INTO write1.TestTable (IntColumn) 
VALUES (1);", NULL, NULL, NULL));
SQLITE_CHECK(sqlite3_exec(db, "COMMIT TRANSACTION;", NULL, NULL, NULL));
return 0;
}

Currently my workaround is to conditionally detach the read database if it's 
pointed to the same file index as the write database.  I'd like to know whether 
this is intended behavior.  There doesn't seem to be any limitations in the 
documentation about attaching the same file multiple times.  It seems like it 
should be a bug, because it works for deferred transactions.

Can someone knowledgeable in the ATTACH/DETACH behavior comment on this?



> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Loren Keagle
> Sent: Monday, July 22, 2013 2:38 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] sqlite-users Digest, Vol 67, Issue 18
>
> > "The sqlite3_reset() function is called to reset a prepared statement
> > object back to its initial state, ready to be re-executed. Any SQL
> > statement variables that had values bound to them using the
> > sqlite3_bind_*() API retain their values. Use sqlite3_clear_bindings()
> > to reset the bindings."
> >
> > To close a prepared statement you need to use finalize.
> >
> > "The sqlite3_finalize() function is called to delete a prepared
> > statement. If the most recent evaluation of the statement encountered
> > no errors or if the statement is never been evaluated, then
> > sqlite3_finalize() returns SQLITE_OK. If the most recent evaluation of
> > statement S failed, then sqlite3_finalize(S) returns the appropriate
> > error code or extended error code."
> >
> > You have one prepared statement open and then try to start a transaction.
> > This gives you a busy error.
>
> I'm sorry, but this doesn't fit with my experience with sqlite at all.  I 
> create
> transactions all the time while having unfinalized prepared statements.  As
> long as the statements are reset, they should not be active, and a "BEGIN
> EXCLUSIVE" does not return SQLITE_BUSY.
>
> This issue seems to be related to having the same file attached with multiple
> database names.  See my earlier response with sample code that
> demonstrates the problem.
>
> One experiment I have not yet tried is to finalize and re-prepare any existing
> statements that were prepared against the first attached file, after attaching
> the same file a second time.  If that works, then I would still consider it a 
> bug
> in sqlite, as the locking subsystem should still behave consistently with no
> active statements.
>
> ~Loren
>
>
>
> > On 07/17/2013 06:56 PM, Du?an Paulovi? wrote:
> > > If you remove a busy check, does it output any statements?
> > > Do you have any custom functions/operations running so they could
> > > block sqlite in creating new statement?
> > >
> > >
> > > 2013/7/17 Loren Keagle 
> > >
> > >> Hi everyone,
> > >>
> > >> I have an interesting locking problem that I'm wondering if someone
> > >> can help with some insight.
> > >>
> > >> I have a master database with some metadata, and several
> > >> sub-databases to store logging events. I have one reader object and
> > >> one writer object that attach to the sub-databases and 

Re: [sqlite] Sqlite locking issue with ATTACH'ed databases

[Israel Lins Albuquerque]

I have the same issue here!

I have an replication system using sqlite where:
- I have 2 databases: one for output* and other for input*;
- I have 2 process accessing booth:
The first is the replicator:
- Get the data on remote server and write on input database;
- Get the data on output database and write on remote server;
The second process:
- Read the input database;
- Write on output database;

In booth of process the databases are attached,
I've used another empty database just to create a connection.

Then I have seen when I replicator commits and the second process tries to 
write the error happens,
but I can not sure about this...

I just saying you are not alone.

--
Israel Lins Albuquerque

Antes de imprimir, pense em sua responsabilidade com o MEIO AMBIENTE.



Em 18/07/2013, às 16:26, Loren Keagle <loren.kea...@braemarllc.com> escreveu:

>> Date: Wed, 17 Jul 2013 17:21:15 +0100
>> From: Simon Slavin <slav...@bigfraud.org>
>> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
>> Subject: Re: [sqlite] Sqlite locking issue with ATTACH'ed databases
>> Message-ID: <f0ad438b-1165-419d-99bf-57faf9de5...@bigfraud.org>
>> Content-Type: text/plain; charset=us-ascii
> 
> 
>> On 16 Jul 2013, at 11:24pm, Loren Keagle <loren.kea...@braemarllc.com> wrote:
> 
>>> Begin EXCLUSIVE TRANSACTION;
>>> insert several rows of data;
>>> Commit transaction;
>>> 
>>> Prepare query statement;
>>> Iterate through one or more rows;
>>> Reset statement;
>>> 
>>> Attempt to begin transaction; <--- SQLITE_BUSY
>>> Would like to write more here, but can't unless I close/open the
>>> connection;
> 
>> I assume you're checking the result codes returned by all the API calls 
>> before the second BEGIN to see that they all return SQLITE_OK.
> 
>> Please add a _finalize() after the _reset() just for testing purposes.  I 
>> know you may not want it as part of your production code.
> 
>> Is the statement that gets a busy a BEGIN or BEGIN IMMEDIATE or BEGIN 
>> EXCLUSIVE ?
> 
>> Simon.
> 
> 
> Thanks for the reply.  I've written wrapper classes in C++ that automatically 
> check all return codes for every sqlite API call I make.  The only return 
> error is the SQLITE_BUSY from the transaction statement (It's EXCLUSIVE, btw, 
> but it doesn't seem to matter in this context).
> 
> I've tried finalizing all statements.  It definitely seems to be related to 
> having the same database attached multiple times with different names.  I've 
> done this because my data is split up amongst multiple sub-databases, and I 
> simply have a reader and writer object that can work independently.  Of 
> course, they can both end up pointing at the same sub-database, but I never 
> would have thought this was a problem.
> 
> I've written some sample code to illustrate my problem.  I've commented out 
> the actions that don't seem to make any difference.  Simply the fact that 
> I've attached the second database causes the failure.  As soon as I detach 
> it, I can write on the first again:
> 
>// Open master database
>sqlite3* db = NULL;
>int ret = sqlite3_open_v2("Test.sqlite", , SQLITE_OPEN_FULLMUTEX | 
> SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr);
>if(ret != SQLITE_OK)
>{
>exit(1);
>}
>sqlite3_extended_result_codes(db, TRUE);
> 
>// Create table on main.  This probably serves no purpose.
>ret = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS MainTable (id 
> INTEGER PRIMARY KEY, IntColumn INTEGER);", NULL, NULL, NULL);
>if(ret != SQLITE_OK)
>exit(3);
> 
>// Attach write database
>ret = sqlite3_exec(db, "ATTACH DATABASE 'TestSub.sqlite' as write1;", 
> NULL, NULL, NULL);
>if(ret != SQLITE_OK)
>exit(2);
> 
>// Create table on subdb
>ret = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS write1.TestTable 
> (id INTEGER PRIMARY KEY, IntColumn INTEGER);", NULL, NULL, NULL);
>if(ret != SQLITE_OK)
>exit(3);
> 
>// Insert some data in write table
>sqlite3_stmt * insert = nullptr;
>const char* tail = nullptr;
>ret = sqlite3_prepare_v2(db, "INSERT INTO write1.TestTable (IntColumn) 
> VALUES (?1);", -1, , );
>if (ret != SQLITE_OK)
>exit(4);
> 
>for (int i = 0; i < 10; ++i)
>{
>ret = sqlite3_bind_int(insert, 1, i);
>if (ret != SQLITE_OK)
>   

Re: [sqlite] Sqlite locking issue with ATTACH'ed databases

[Simon Slavin]

On 18 Jul 2013, at 8:26pm, Loren Keagle  wrote:

> It definitely seems to be related to having the same database attached 
> multiple times with different names.

Okay.  I have absolutely no idea how well SQLite copes with that.  I'm not 
saying it's bad, I just have no idea at all.  I hope one of the other readers 
of this list can help.

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

Re: [sqlite] Sqlite locking issue with ATTACH'ed databases

[Loren Keagle]

>Date: Wed, 17 Jul 2013 17:21:15 +0100
>From: Simon Slavin <slav...@bigfraud.org>
>To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
>Subject: Re: [sqlite] Sqlite locking issue with ATTACH'ed databases
>Message-ID: <f0ad438b-1165-419d-99bf-57faf9de5...@bigfraud.org>
>Content-Type: text/plain; charset=us-ascii


>On 16 Jul 2013, at 11:24pm, Loren Keagle <loren.kea...@braemarllc.com> wrote:

>> Begin EXCLUSIVE TRANSACTION;
>> insert several rows of data;
>> Commit transaction;
>>
>> Prepare query statement;
>> Iterate through one or more rows;
>> Reset statement;
>>
>> Attempt to begin transaction; <--- SQLITE_BUSY
>> Would like to write more here, but can't unless I close/open the
>> connection;

>I assume you're checking the result codes returned by all the API calls before 
>the second BEGIN to see that they all return SQLITE_OK.

>Please add a _finalize() after the _reset() just for testing purposes.  I know 
>you may not want it as part of your production code.

>Is the statement that gets a busy a BEGIN or BEGIN IMMEDIATE or BEGIN 
>EXCLUSIVE ?

>Simon.


Thanks for the reply.  I've written wrapper classes in C++ that automatically 
check all return codes for every sqlite API call I make.  The only return error 
is the SQLITE_BUSY from the transaction statement (It's EXCLUSIVE, btw, but it 
doesn't seem to matter in this context).

I've tried finalizing all statements.  It definitely seems to be related to 
having the same database attached multiple times with different names.  I've 
done this because my data is split up amongst multiple sub-databases, and I 
simply have a reader and writer object that can work independently.  Of course, 
they can both end up pointing at the same sub-database, but I never would have 
thought this was a problem.

I've written some sample code to illustrate my problem.  I've commented out the 
actions that don't seem to make any difference.  Simply the fact that I've 
attached the second database causes the failure.  As soon as I detach it, I can 
write on the first again:

// Open master database
sqlite3* db = NULL;
int ret = sqlite3_open_v2("Test.sqlite", , SQLITE_OPEN_FULLMUTEX | 
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr);
if(ret != SQLITE_OK)
{
exit(1);
}
sqlite3_extended_result_codes(db, TRUE);

// Create table on main.  This probably serves no purpose.
ret = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS MainTable (id 
INTEGER PRIMARY KEY, IntColumn INTEGER);", NULL, NULL, NULL);
if(ret != SQLITE_OK)
exit(3);

// Attach write database
ret = sqlite3_exec(db, "ATTACH DATABASE 'TestSub.sqlite' as write1;", 
NULL, NULL, NULL);
if(ret != SQLITE_OK)
exit(2);

// Create table on subdb
ret = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS write1.TestTable (id 
INTEGER PRIMARY KEY, IntColumn INTEGER);", NULL, NULL, NULL);
if(ret != SQLITE_OK)
exit(3);

// Insert some data in write table
sqlite3_stmt * insert = nullptr;
const char* tail = nullptr;
ret = sqlite3_prepare_v2(db, "INSERT INTO write1.TestTable (IntColumn) 
VALUES (?1);", -1, , );
if (ret != SQLITE_OK)
exit(4);

for (int i = 0; i < 10; ++i)
{
ret = sqlite3_bind_int(insert, 1, i);
if (ret != SQLITE_OK)
exit(5);

ret = sqlite3_step(insert);
if(ret != SQLITE_DONE)
exit(6);

ret = sqlite3_reset(insert);
if (ret != SQLITE_OK)
exit(7);
}
ret = sqlite3_reset(insert);
if (ret != SQLITE_OK)
exit(8);

// Attach read database
ret = sqlite3_exec(db, "ATTACH DATABASE 'TestSub.sqlite' as read1;", 
NULL, NULL, NULL);
if(ret != SQLITE_OK)
exit(9);

//sqlite3_stmt * readRow = nullptr;
//ret = sqlite3_prepare_v2(db, "SELECT * FROM read1.TestTable;", -1, 
, );
//if (ret == SQLITE_BUSY || ret == SQLITE_LOCKED)
//  exit(10);

 Iterate through the inserted rows
//do
//{
//  ret = sqlite3_step(readRow);
//  if (ret == SQLITE_BUSY || ret == SQLITE_LOCKED)
//  exit(11);

//  int id = sqlite3_column_int(readRow, 0);
//  int val = sqlite3_column_int(readRow, 1);
//} while (ret != SQLITE_DONE);

//ret = sqlite3_reset(readRow);
//if (ret != SQLITE_OK)
//  exit(12);

 Finalize open read statement. Has no effect on the transaction,

Re: [sqlite] Sqlite locking issue with ATTACH'ed databases

[Patrik Nilsson]

>> Prepare query statement;
>> Iterate through one or more rows;
>> Reset statement;
>>
>> Attempt to begin transaction; <--- SQLITE_BUSY

"The sqlite3_reset() function is called to reset a prepared statement
object back to its initial state, ready to be re-executed. Any SQL
statement variables that had values bound to them using the
sqlite3_bind_*() API retain their values. Use sqlite3_clear_bindings()
to reset the bindings."

To close a prepared statement you need to use finalize.

"The sqlite3_finalize() function is called to delete a prepared
statement. If the most recent evaluation of the statement encountered no
errors or if the statement is never been evaluated, then
sqlite3_finalize() returns SQLITE_OK. If the most recent evaluation of
statement S failed, then sqlite3_finalize(S) returns the appropriate
error code or extended error code."

You have one prepared statement open and then try to start a
transaction. This gives you a busy error.

On 07/17/2013 06:56 PM, Dušan Paulovič wrote:
> If you remove a busy check, does it output any statements?
> Do you have any custom functions/operations running so they could block
> sqlite in creating new statement?
> 
> 
> 2013/7/17 Loren Keagle 
> 
>> Hi everyone,
>>
>> I have an interesting locking problem that I'm wondering if someone can
>> help with some insight.
>>
>> I have a master database with some metadata, and several sub-databases to
>> store logging events. I have one reader object and one writer object that
>> attach to the sub-databases and encapsulate the read/write operations
>> respectively.
>>
>> I've found a very unexpected locking behavior with the attached databases
>> and exclusive transactions. One of my unit tests does the following:
>>
>> Begin EXCLUSIVE TRANSACTION;
>> insert several rows of data;
>> Commit transaction;
>>
>> Prepare query statement;
>> Iterate through one or more rows;
>> Reset statement;
>>
>> Attempt to begin transaction; <--- SQLITE_BUSY
>> Would like to write more here, but can't unless I close/open the
>> connection;
>>
>> I can't seem to figure out any reason why I can't create a new exclusive
>> transaction here, and I feel it must have to do with the fact that I have
>> attached to sub-databases (possibly the same sub-database) with my
>> reader/writer objects. This is single threaded and only database connection
>> (with attach/detach logic).
>> I have verified that all statements prepared by the connection are
>> properly reset - this is handled by my C++ wrappers, and any errors will
>> throw an exception. I even iterated through all of the current statements
>> with the following code immediately before my transaction failure, with no
>> results:
>>
>> sqlite3_stmt *stmt = NULL;
>> while ((stmt = sqlite3_next_stmt(db, stmt)) != NULL)
>> {
>> if (sqlite3_stmt_busy(stmt))
>> {
>> const char* sql = sqlite3_sql(stmt);
>> std::cout << sql << "\r\n";
>> }
>> }
>>
>> Can anyone think of a reason why attached databases would prevent entering
>> a second transaction? BTW, it doesn't seem to work with immediate
>> transactions either. If I remove the query, everything works fine.
>>
>> Thanks!
>>
>>
>> 
>> This email, including any attachments and files transmitted with it, are
>> for the sole use of the intended recipient(s) to whom this email is
>> addressed, and may contain confidential and/or privileged information. Any
>> unauthorized review, use, disclosure or distribution is prohibited. If you
>> are not the intended recipient, please be advised that you have received
>> this email in error, and please contact the sender by reply email and
>> destroy all copies (including all electronic and hard copies) of the
>> original message. Thank you.
>> ___
>> 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
> 

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite locking issue with ATTACH'ed databases

[Dušan Paulovič]

If you remove a busy check, does it output any statements?
Do you have any custom functions/operations running so they could block
sqlite in creating new statement?


2013/7/17 Loren Keagle 

> Hi everyone,
>
> I have an interesting locking problem that I'm wondering if someone can
> help with some insight.
>
> I have a master database with some metadata, and several sub-databases to
> store logging events. I have one reader object and one writer object that
> attach to the sub-databases and encapsulate the read/write operations
> respectively.
>
> I've found a very unexpected locking behavior with the attached databases
> and exclusive transactions. One of my unit tests does the following:
>
> Begin EXCLUSIVE TRANSACTION;
> insert several rows of data;
> Commit transaction;
>
> Prepare query statement;
> Iterate through one or more rows;
> Reset statement;
>
> Attempt to begin transaction; <--- SQLITE_BUSY
> Would like to write more here, but can't unless I close/open the
> connection;
>
> I can't seem to figure out any reason why I can't create a new exclusive
> transaction here, and I feel it must have to do with the fact that I have
> attached to sub-databases (possibly the same sub-database) with my
> reader/writer objects. This is single threaded and only database connection
> (with attach/detach logic).
> I have verified that all statements prepared by the connection are
> properly reset - this is handled by my C++ wrappers, and any errors will
> throw an exception. I even iterated through all of the current statements
> with the following code immediately before my transaction failure, with no
> results:
>
> sqlite3_stmt *stmt = NULL;
> while ((stmt = sqlite3_next_stmt(db, stmt)) != NULL)
> {
> if (sqlite3_stmt_busy(stmt))
> {
> const char* sql = sqlite3_sql(stmt);
> std::cout << sql << "\r\n";
> }
> }
>
> Can anyone think of a reason why attached databases would prevent entering
> a second transaction? BTW, it doesn't seem to work with immediate
> transactions either. If I remove the query, everything works fine.
>
> Thanks!
>
>
> 
> This email, including any attachments and files transmitted with it, are
> for the sole use of the intended recipient(s) to whom this email is
> addressed, and may contain confidential and/or privileged information. Any
> unauthorized review, use, disclosure or distribution is prohibited. If you
> are not the intended recipient, please be advised that you have received
> this email in error, and please contact the sender by reply email and
> destroy all copies (including all electronic and hard copies) of the
> original message. Thank you.
> ___
> 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] Sqlite locking issue with ATTACH'ed databases

[Simon Slavin]

On 16 Jul 2013, at 11:24pm, Loren Keagle  wrote:

> Begin EXCLUSIVE TRANSACTION;
> insert several rows of data;
> Commit transaction;
> 
> Prepare query statement;
> Iterate through one or more rows;
> Reset statement;
> 
> Attempt to begin transaction; <--- SQLITE_BUSY
> Would like to write more here, but can't unless I close/open the connection;

I assume you're checking the result codes returned by all the API calls before 
the second BEGIN to see that they all return SQLITE_OK.

Please add a _finalize() after the _reset() just for testing purposes.  I know 
you may not want it as part of your production code.

Is the statement that gets a busy a BEGIN or BEGIN IMMEDIATE or BEGIN EXCLUSIVE 
?

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

[sqlite] Sqlite locking issue with ATTACH'ed databases

[Loren Keagle]

Hi everyone,

I have an interesting locking problem that I'm wondering if someone can help 
with some insight.

I have a master database with some metadata, and several sub-databases to store 
logging events. I have one reader object and one writer object that attach to 
the sub-databases and encapsulate the read/write operations respectively.

I've found a very unexpected locking behavior with the attached databases and 
exclusive transactions. One of my unit tests does the following:

Begin EXCLUSIVE TRANSACTION;
insert several rows of data;
Commit transaction;

Prepare query statement;
Iterate through one or more rows;
Reset statement;

Attempt to begin transaction; <--- SQLITE_BUSY
Would like to write more here, but can't unless I close/open the connection;

I can't seem to figure out any reason why I can't create a new exclusive 
transaction here, and I feel it must have to do with the fact that I have 
attached to sub-databases (possibly the same sub-database) with my 
reader/writer objects. This is single threaded and only database connection 
(with attach/detach logic).
I have verified that all statements prepared by the connection are properly 
reset - this is handled by my C++ wrappers, and any errors will throw an 
exception. I even iterated through all of the current statements with the 
following code immediately before my transaction failure, with no results:

sqlite3_stmt *stmt = NULL;
while ((stmt = sqlite3_next_stmt(db, stmt)) != NULL)
{
if (sqlite3_stmt_busy(stmt))
{
const char* sql = sqlite3_sql(stmt);
std::cout << sql << "\r\n";
}
}

Can anyone think of a reason why attached databases would prevent entering a 
second transaction? BTW, it doesn't seem to work with immediate transactions 
either. If I remove the query, everything works fine.

Thanks!



This email, including any attachments and files transmitted with it, are for 
the sole use of the intended recipient(s) to whom this email is addressed, and 
may contain confidential and/or privileged information. Any unauthorized 
review, use, disclosure or distribution is prohibited. If you are not the 
intended recipient, please be advised that you have received this email in 
error, and please contact the sender by reply email and destroy all copies 
(including all electronic and hard copies) of the original message. Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data>SQLite locking problem

[Serge Fournier]

I can confirm that the problem only exist when the database is encrypted.

On Sat, Sep 15, 2012 at 11:00 AM, Serge Fournier  wrote:

> I work with an encrypted database and it look like it could be part of the
> problem.
>
>
> On Wed, Sep 12, 2012 at 3:05 PM, Serge Fournier  wrote:
>
>> Doing more testing on this I can say that I have the same problems with
>> Windows 7.
>>
>>
>> On Wed, Sep 12, 2012 at 8:08 AM, Serge Fournier wrote:
>>
>>> The thing here is that my code was working perfectly with the prior
>>> version of the library and Windows 7.
>>> The same code with 1.0.82 and Windows 8 doesn't work anymore.
>>>
>>> I cannot use your solution, because the way my appliation works; it
>>> creates the database at first run and
>>> miust be able to run on many computers.
>>>
>>> I've tried recreating the database; and so many things; the only thing
>>> that works is to only
>>> open the database once in my application which is no a good solution for
>>> me since I have to
>>> remove some functions like VACUUM etc...
>>> Thanks.
>>> On Tue, Sep 11, 2012 at 3:18 PM, Brandon Pimenta <
>>> brandonskypime...@gmail.com> wrote:
>>>
 I've experienced this problem in the past. According to
 here,
 I found a solution.

 *Brandon's Tips on How to Unlock SQLite Databases*

1. Open a command line prompt window.
2. Type *sqlite* *dbname*.sqlite in the command line prompt,
 replacing
"dbname" with the SQLite database name.
3. In the secondary prompt that appears, type *.backup* full *dbname*
.sqlite, replacing "dbname" with the SQLite database name.
4. Type *.exit *to return to the ordinary command prompt.
5. Type *mv* *dbname*.sqlite old.sqlite, then *mv* backup.sqlite
 *dbname*
.sqlite in the command line, replacing "dbname" with the SQLite
 database
name.
6. Try the task you were attempting to do before you did all of the
above instructions.
7. *You are done!*

 *Brandon Sky Pimenta*


 On Tue, Sep 11, 2012 at 10:35 AM, Serge Fournier 
 wrote:

 > Hi,
 >
 > I'm not sure if it's Windows 8 or the latest version of the library
 > (1.0.82) but I have a new
 > problem with the same code that was working before.
 >
 > Simply put, i I open a database and then close it in an application
 then I
 > reopen it again,
 > I cannot write to it anymore; it is always locked. I tried doing a
 close,
 > dispose and shutdown
 > to my connection and then reopning it and do a simple vaccum; it tell
 me
 > that there's still
 > sql queries in progress when none were sent to the database.
 >
 > I cannot test this in Windows 7 yet, but I'm wandering if it's a
 problem
 > with Windows 8; or
 > a bug in the new version of System.Data.SQlite.
 >
 > I use Visual Studio 2010.
 >
 > Thanks.
 > ___
 > 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] System.Data>SQLite locking problem

[Serge Fournier]

Doing more testing on this I can say that I have the same problems with
Windows 7.

On Wed, Sep 12, 2012 at 8:08 AM, Serge Fournier  wrote:

> The thing here is that my code was working perfectly with the prior
> version of the library and Windows 7.
> The same code with 1.0.82 and Windows 8 doesn't work anymore.
>
> I cannot use your solution, because the way my appliation works; it
> creates the database at first run and
> miust be able to run on many computers.
>
> I've tried recreating the database; and so many things; the only thing
> that works is to only
> open the database once in my application which is no a good solution for
> me since I have to
> remove some functions like VACUUM etc...
> Thanks.
> On Tue, Sep 11, 2012 at 3:18 PM, Brandon Pimenta <
> brandonskypime...@gmail.com> wrote:
>
>> I've experienced this problem in the past. According to
>> here,
>> I found a solution.
>>
>> *Brandon's Tips on How to Unlock SQLite Databases*
>>
>>1. Open a command line prompt window.
>>2. Type *sqlite* *dbname*.sqlite in the command line prompt, replacing
>>"dbname" with the SQLite database name.
>>3. In the secondary prompt that appears, type *.backup* full *dbname*
>>.sqlite, replacing "dbname" with the SQLite database name.
>>4. Type *.exit *to return to the ordinary command prompt.
>>5. Type *mv* *dbname*.sqlite old.sqlite, then *mv* backup.sqlite
>> *dbname*
>>.sqlite in the command line, replacing "dbname" with the SQLite
>> database
>>name.
>>6. Try the task you were attempting to do before you did all of the
>>above instructions.
>>7. *You are done!*
>>
>> *Brandon Sky Pimenta*
>>
>>
>> On Tue, Sep 11, 2012 at 10:35 AM, Serge Fournier 
>> wrote:
>>
>> > Hi,
>> >
>> > I'm not sure if it's Windows 8 or the latest version of the library
>> > (1.0.82) but I have a new
>> > problem with the same code that was working before.
>> >
>> > Simply put, i I open a database and then close it in an application
>> then I
>> > reopen it again,
>> > I cannot write to it anymore; it is always locked. I tried doing a
>> close,
>> > dispose and shutdown
>> > to my connection and then reopning it and do a simple vaccum; it tell me
>> > that there's still
>> > sql queries in progress when none were sent to the database.
>> >
>> > I cannot test this in Windows 7 yet, but I'm wandering if it's a problem
>> > with Windows 8; or
>> > a bug in the new version of System.Data.SQlite.
>> >
>> > I use Visual Studio 2010.
>> >
>> > Thanks.
>> > ___
>> > 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] System.Data>SQLite locking problem

[Serge Fournier]

The thing here is that my code was working perfectly with the prior version
of the library and Windows 7.
The same code with 1.0.82 and Windows 8 doesn't work anymore.

I cannot use your solution, because the way my appliation works; it creates
the database at first run and
miust be able to run on many computers.

I've tried recreating the database; and so many things; the only thing that
works is to only
open the database once in my application which is no a good solution for me
since I have to
remove some functions like VACUUM etc...
Thanks.
On Tue, Sep 11, 2012 at 3:18 PM, Brandon Pimenta <
brandonskypime...@gmail.com> wrote:

> I've experienced this problem in the past. According to
> here,
> I found a solution.
>
> *Brandon's Tips on How to Unlock SQLite Databases*
>
>1. Open a command line prompt window.
>2. Type *sqlite* *dbname*.sqlite in the command line prompt, replacing
>"dbname" with the SQLite database name.
>3. In the secondary prompt that appears, type *.backup* full *dbname*
>.sqlite, replacing "dbname" with the SQLite database name.
>4. Type *.exit *to return to the ordinary command prompt.
>5. Type *mv* *dbname*.sqlite old.sqlite, then *mv* backup.sqlite
> *dbname*
>.sqlite in the command line, replacing "dbname" with the SQLite database
>name.
>6. Try the task you were attempting to do before you did all of the
>above instructions.
>7. *You are done!*
>
> *Brandon Sky Pimenta*
>
>
> On Tue, Sep 11, 2012 at 10:35 AM, Serge Fournier 
> wrote:
>
> > Hi,
> >
> > I'm not sure if it's Windows 8 or the latest version of the library
> > (1.0.82) but I have a new
> > problem with the same code that was working before.
> >
> > Simply put, i I open a database and then close it in an application then
> I
> > reopen it again,
> > I cannot write to it anymore; it is always locked. I tried doing a close,
> > dispose and shutdown
> > to my connection and then reopning it and do a simple vaccum; it tell me
> > that there's still
> > sql queries in progress when none were sent to the database.
> >
> > I cannot test this in Windows 7 yet, but I'm wandering if it's a problem
> > with Windows 8; or
> > a bug in the new version of System.Data.SQlite.
> >
> > I use Visual Studio 2010.
> >
> > Thanks.
> > ___
> > 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] System.Data>SQLite locking problem

[Serge Fournier]

Did you try it with a Windows 8? I have a feeling that the problem is more
with this OS
then the library.

Here's something simple that doesn't work:

SQLconnect.ConnectionString = "Data Source=" & Application.StartupPath &
"\data.db;"

 SQLconnect.SetPassword(infoConnection)

SQLconnect.Open()

sqlconnect.close()
sqlconnect.dispose()


Dim SQLconnect As New SQLite.SQLiteConnection

Dim SQLcommand As New SQLite.SQLiteCommand

Dim SQLreader As SQLite.SQLiteDataReader

Dim SQLquery As String

SQLconnect.ConnectionString = "Data Source=" & Application.StartupPath &
"\data.db;




SQLconnect.SetPassword(infoConnection)

SQLconnect.Open()

SQLquery = "VACUUM"

Try

SQLcommand = SQLconnect.CreateCommand

SQLcommand.CommandText = SQLquery

SQLcommand.ExecuteNonQuery()

SQLcommand.Dispose()

Catch sqlexception As SQLite.SQLiteException

MessageBox.Show(sqlexception.Message, "Error!", MessageBoxButtons.OK,
MessageBoxIcon.Error)

Catch ex As Exception

MessageBox.Show(ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon
.Error)

End Try

It gives the "Still SQL queries to process" error.


On Tue, Sep 11, 2012 at 10:07 PM, Joe Mistachkin wrote:

>
> Serge Fournier wrote:
> >
> > Simply put, it's only possible to open a database once in the application
> > for writing; it's like the lock
> > doesn't get released until the application is closed.
> >
>
> I'm unable to reproduce the issue you describe here.  Could you show us
> some
> sample code that demonstrates the issue?
>
> --
> Joe Mistachkin
>
> ___
> 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] System.Data>SQLite locking problem

[Brandon Pimenta]

I've experienced this problem in the past. According to
here,
I found a solution.

*Brandon's Tips on How to Unlock SQLite Databases*

   1. Open a command line prompt window.
   2. Type *sqlite* *dbname*.sqlite in the command line prompt, replacing
   "dbname" with the SQLite database name.
   3. In the secondary prompt that appears, type *.backup* full *dbname*
   .sqlite, replacing "dbname" with the SQLite database name.
   4. Type *.exit *to return to the ordinary command prompt.
   5. Type *mv* *dbname*.sqlite old.sqlite, then *mv* backup.sqlite *dbname*
   .sqlite in the command line, replacing "dbname" with the SQLite database
   name.
   6. Try the task you were attempting to do before you did all of the
   above instructions.
   7. *You are done!*

*Brandon Sky Pimenta*


On Tue, Sep 11, 2012 at 10:35 AM, Serge Fournier  wrote:

> Hi,
>
> I'm not sure if it's Windows 8 or the latest version of the library
> (1.0.82) but I have a new
> problem with the same code that was working before.
>
> Simply put, i I open a database and then close it in an application then I
> reopen it again,
> I cannot write to it anymore; it is always locked. I tried doing a close,
> dispose and shutdown
> to my connection and then reopning it and do a simple vaccum; it tell me
> that there's still
> sql queries in progress when none were sent to the database.
>
> I cannot test this in Windows 7 yet, but I'm wandering if it's a problem
> with Windows 8; or
> a bug in the new version of System.Data.SQlite.
>
> I use Visual Studio 2010.
>
> Thanks.
> ___
> 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] System.Data>SQLite locking problem

[Joe Mistachkin]

Serge Fournier wrote:
> 
> Simply put, it's only possible to open a database once in the application
> for writing; it's like the lock
> doesn't get released until the application is closed.
> 

I'm unable to reproduce the issue you describe here.  Could you show us some
sample code that demonstrates the issue?

--
Joe Mistachkin

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

Re: [sqlite] System.Data>SQLite locking problem

[Serge Fournier]

Yes, all command have been disposed; everything works until I close the
connection. Then when
I open it again it the same application; either I get a "database is
locked" when trying to write to it
or I get "SQL queries are still processing" when I just opened it and send
a VACUUM pragma to it.

Simply put, it's only possible to open a database once in the application
for writing; it's like the lock
doesn't get released until the application is closed.
On Tue, Sep 11, 2012 at 5:59 PM, Joe Mistachkin wrote:

>
> Serge Fournier wrote:
> >
> > Simply put, I open a database and then close it in an application then I
> > reopen it again,
> > I cannot write to it anymore; it is always locked. I tried doing a close,
> > dispose and shutdown
> > to my connection and then reopning it and do a simple vaccum; it tell me
> > that there's still
> > sql queries in progress when none were sent to the database.
> >
>
> Have all the SQLiteDataReader and SQLiteCommand objects been properly
> disposed?  What is the exception you are seeing?
>
> --
> Joe Mistachkin
>
> ___
> 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] System.Data>SQLite locking problem

[Joe Mistachkin]

Serge Fournier wrote:
> 
> Simply put, I open a database and then close it in an application then I
> reopen it again,
> I cannot write to it anymore; it is always locked. I tried doing a close,
> dispose and shutdown
> to my connection and then reopning it and do a simple vaccum; it tell me
> that there's still
> sql queries in progress when none were sent to the database.
> 

Have all the SQLiteDataReader and SQLiteCommand objects been properly
disposed?  What is the exception you are seeing?

--
Joe Mistachkin

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

[sqlite] System.Data>SQLite locking problem

[Serge Fournier]

Hi,

I'm not sure if it's Windows 8 or the latest version of the library
(1.0.82) but I have a new
problem with the same code that was working before.

Simply put, i I open a database and then close it in an application then I
reopen it again,
I cannot write to it anymore; it is always locked. I tried doing a close,
dispose and shutdown
to my connection and then reopning it and do a simple vaccum; it tell me
that there's still
sql queries in progress when none were sent to the database.

I cannot test this in Windows 7 yet, but I'm wandering if it's a problem
with Windows 8; or
a bug in the new version of System.Data.SQlite.

I use Visual Studio 2010.

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

Re: [sqlite] Locking/Concurrency

[Igor Tandetnik]

KUSHAL SHAH  wrote:
> My scenario: Multiple processes want to access the database. How do I 
> configure so that I can get as much concurrency w.r.t read
> and write. Meaning, have multiple readers and writers. 

The best concurrency between separate processes (and thus, necessarily, 
separate connections) you can achieve with SQLite is by enabling WAL mode. 
Which, as I said, gives you multiple readers and a single writer at the same 
time. Under no circumstances would SQLite allow two simultaneous writing 
transactions.

> That is fine, I can handle it in my application. However, I just want to 
> confirm, how to achieve the multi-read and 1 write
> scenario. My understanding: 
> Keep threading mode to Serialized.

If you don't have multiple threads sharing a single connection (and it sounds 
like you don't), then threading mode is irrelevant.

> Keep the locking mode to Normal. Again, this is the default. So I don't have 
> to do anything.
> Finally, the journal mode to WAL.
> 
> Is this correct?

Yes, sounds good to me.
-- 
Igor Tandetnik

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

Re: [sqlite] Locking/Concurrency

[KUSHAL SHAH]

Sorry, when I said mutex mode, I meant the Runtime selection of threading mode: 
http://sqlite.org/threadsafe.html
 
At this point, I am sorry, I am confused with threading mode, journal mode and 
locking modes.
 
My scenario: Multiple processes want to access the database. How do I configure 
so that I can get as much concurrency w.r.t read and write. Meaning, have 
multiple readers and writers.
 
I think we are saying we can only have multiple readers and 1 writer at any 
point in time. Any connection that tries to write when there is already a write 
happening, will get SQLite_Busy.
 
That is fine, I can handle it in my application. However, I just want to 
confirm, how to achieve the multi-read and 1 write scenario. My understanding:
Keep threading mode to Serialized. This is the default. So I don't have to do 
anything.
Keep the locking mode to Normal. Again, this is the default. So I don't have to 
do anything.
Finally, the journal mode to WAL.
 
Is this correct?
 
Also, in WAL mode, the simple answer for why there cant be multiple writes is 
because you cannot have multiple pages being written at the same time in the 
same file. It also complicates transaction behavior, crash recovery, 
etc. Correct?
 
Thanks,
Kushal.

  


 From: Igor Tandetnik <itandet...@mvps.org>
To: sqlite-users@sqlite.org 
Sent: Saturday, May 5, 2012 6:02 PM
Subject: Re: [sqlite] Locking/Concurrency
  
KUSHAL SHAH <adroi...@yahoo.com> wrote:
> I am starting simple where I have exe1 having 1 connection and exe2 having 
> another connection.
> Now if both of them open the connections in mutex mode, will the read/write 
> requests be serialized. It seems you are saying No.

I'm not sure what you mean by "mutex mode" - I'm not familiar with this term.

If you have multiple connections, each used by a single thread (whether in the 
same or different EXEs, doesn't matter), then:

- in "traditional" roll-back journal mode, at any point in time the database 
may be accessed by multiple readers, *or* by a single writer
- in WAL mode, the database may be accessed by multiple readers *and* a single 
writer.

> If so, then what is the advantage of mutex? Like, I would expect that a 
> single thread connection will anyways serialize its
> requests from the client. What am I missing? 

I don't understand the term "thread connection" either.

What scenario do you have in mind, exactly? How many threads are using how many 
connections? Which of these threads attempt to read, and which attempt to write?

> Finally, how do I enable WAL mode

The documentation at http://sqlite.org/wal.html explains this in detail.

> Specifically, can I do that in System.Data.SqLite client?

I'm unfamiliar with this client. Hopefully, someone more knowledgeable will 
chime in soon.

> Also, what happens when 2 connections(with mutex) try to write? Will one of 
> them get sqlite_busy?

Yes.
-- 
Igor Tandetnik

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

Re: [sqlite] Locking/Concurrency

[Igor Tandetnik]

KUSHAL SHAH  wrote:
> I am starting simple where I have exe1 having 1 connection and exe2 having 
> another connection.
> Now if both of them open the connections in mutex mode, will the read/write 
> requests be serialized. It seems you are saying No.

I'm not sure what you mean by "mutex mode" - I'm not familiar with this term.

If you have multiple connections, each used by a single thread (whether in the 
same or different EXEs, doesn't matter), then:

- in "traditional" roll-back journal mode, at any point in time the database 
may be accessed by multiple readers, *or* by a single writer
- in WAL mode, the database may be accessed by multiple readers *and* a single 
writer.

> If so, then what is the advantage of mutex? Like, I would expect that a 
> single thread connection will anyways serialize its
> requests from the client. What am I missing? 

I don't understand the term "thread connection" either.

What scenario do you have in mind, exactly? How many threads are using how many 
connections? Which of these threads attempt to read, and which attempt to write?

> Finally, how do I enable WAL mode

The documentation at http://sqlite.org/wal.html explains this in detail.

> Specifically, can I do that in System.Data.SqLite client?

I'm unfamiliar with this client. Hopefully, someone more knowledgeable will 
chime in soon.

> Also, what happens when 2 connections(with mutex) try to write? Will one of 
> them get sqlite_busy?

Yes.
-- 
Igor Tandetnik

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

Re: [sqlite] Locking/Concurrency

[KUSHAL SHAH]

Thank you Igor for the prompt reply.
 
Apologies, I am a beginner and hence some more naïve questions:
 
I am starting simple where I have exe1 having 1 connection and exe2 having 
another connection.
Now if both of them open the connections in mutex mode, will the read/write 
requests be serialized. It seems you are saying No.
 
If so, then what is the advantage of mutex? Like, I would expect that  a single 
thread connection will anyways serialize its requests from the client. What am 
I missing?
 
Finally, how do I enable WAL mode and this different locking mechanisms? 
Specifically, can I do that in System.Data.SqLite client? 
 
Also, what happens when 2 connections(with mutex) try to write? Will one of 
them get sqlite_busy?
 
 
Thanks,
Kushal.
 


 From: Igor Tandetnik <itandet...@mvps.org>
To: sqlite-users@sqlite.org 
Sent: Friday, May 4, 2012 11:04 AM
Subject: Re: [sqlite] Locking/Concurrency
  
On 5/4/2012 1:52 PM, KUSHAL SHAH wrote:
> I am trying to use SQLite in my .NET project. Client APIs are from 
> System.Data.SqLite. Can you please help with below:
> 
> It seemsthat multiple
> threads can actually read simultaneously from a sqlite Db. However, I am
> confused about the write part. Will SQLite manage the write requests or the
> user has to have specific flags/locks on the connection?

Every connection has an associated mutex, and every SQLite API function calls 
are synchronized on this mutex.

> I couldn’t find good documentation around it.

http://sqlite.org/threadsafe.html

> Specifically, I am looking for the
> following scenarios:
> 
> 1.       I am reading Db and on another thread is
> writing to that Db. Do I need specific flags on each of the connections? If 
> so,
> which ones?

Ah, so it's two separate connections? Earlier, you were talking about "the" 
connection. With multiple connections, different rules apply:

http://sqlite.org/lockingv3.html
http://sqlite.org/wal.html

Unless you enable WAL mode, you won't be able to read on one connection and 
write on another simultaneously.

> 2.       Both threads want to write to the Db? What
> flags/locks can I have in my code to achieve that, if at all?

You can't have two writing connections, even in WAL mode. The two threads may 
share the same connection, but then they would be effectively serialized 
anyway, so you won't get any benefit from having two of them.
-- Igor Tandetnik

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

Re: [sqlite] Locking/Concurrency

[Igor Tandetnik]

On 5/4/2012 1:52 PM, KUSHAL SHAH wrote:

I am trying to use SQLite in my .NET project. Client APIs are from 
System.Data.SqLite. Can you please help with below:

It seemsthat multiple
threads can actually read simultaneously from a sqlite Db. However, I am
confused about the write part. Will SQLite manage the write requests or the
user has to have specific flags/locks on the connection?


Every connection has an associated mutex, and every SQLite API function 
calls are synchronized on this mutex.



I couldn’t find good documentation around it.


http://sqlite.org/threadsafe.html


Specifically, I am looking for the
following scenarios:

1.   I am reading Db and on another thread is
writing to that Db. Do I need specific flags on each of the connections? If so,
which ones?


Ah, so it's two separate connections? Earlier, you were talking about 
"the" connection. With multiple connections, different rules apply:


http://sqlite.org/lockingv3.html
http://sqlite.org/wal.html

Unless you enable WAL mode, you won't be able to read on one connection 
and write on another simultaneously.



2.   Both threads want to write to the Db? What
flags/locks can I have in my code to achieve that, if at all?


You can't have two writing connections, even in WAL mode. The two 
threads may share the same connection, but then they would be 
effectively serialized anyway, so you won't get any benefit from having 
two of them.

--
Igor Tandetnik

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

[sqlite] Locking/Concurrency

[KUSHAL SHAH]

I am trying to use SQLite in my .NET project. Client APIs are from 
System.Data.SqLite. Can you please help with below:
 
It seemsthat multiple
threads can actually read simultaneously from a sqlite Db. However, I am
confused about the write part. Will SQLite manage the write requests or the
user has to have specific flags/locks on the connection? And if so, which
locks/flags to use and how? I couldn’t find good documentation around it.
 
Specifically, I am looking for the
following scenarios:
 
1.   I am reading Db and on another thread is
writing to that Db. Do I need specific flags on each of the connections? If so,
which ones?
2.   Both threads want to write to the Db? What
flags/locks can I have in my code to achieve that, if at all? 
 
Thanks,
Kushal.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Locking Issue From Another Process

[Simon Slavin]

On 22 Nov 2011, at 3:07pm, David Levinson wrote:

> So my basic question is why is Process B locking the database when it is
> opening the file for read-only access and not performing any writes on
> the database being written to by Process A. 
> 
> 
> 
> Is it possible that a select statement can lock a table?

Yep.  SELECT always locks a table.

Suppose process A a SELECT which has an ORDER BY clause, and returns 100 rows.  
Just as process A gets to row 50 process B writes to row 75, and changes a 
value which moves it to position 25 in the list.  Then the SELECT completes.  
But unfortunately, it now returns only 99 rows, because the changed row was 
never where it was looking.

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

Re: [sqlite] SQLite Locking Issue From Another Process

[Black, Michael (IS)]

You need WAL mode

http://www.sqlite.org/draft/wal.html





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of David Levinson [dlevin...@worldnow.com]
Sent: Tuesday, November 22, 2011 9:07 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] SQLite Locking Issue From Another Process

We have Process A which writes constantly to our SQLite database and we
have Process B which occasionally reads from this same database. When
Process B opens the database for read-only access and performs a select
statement on the database it causes Process A to get SQLITE_BUSY errors
when executing the insert statements and will continue to return busy
until Process B closes the connection to the database.



So my basic question is why is Process B locking the database when it is
opening the file for read-only access and not performing any writes on
the database being written to by Process A.



Is it possible that a select statement can lock a table?



Thanks,



Dave.



___
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] SQLite Locking Issue From Another Process

[David Levinson]

We have Process A which writes constantly to our SQLite database and we
have Process B which occasionally reads from this same database. When
Process B opens the database for read-only access and performs a select
statement on the database it causes Process A to get SQLITE_BUSY errors
when executing the insert statements and will continue to return busy
until Process B closes the connection to the database. 

 

So my basic question is why is Process B locking the database when it is
opening the file for read-only access and not performing any writes on
the database being written to by Process A. 

 

Is it possible that a select statement can lock a table?

 

Thanks,

 

Dave.

 

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

Re: [sqlite] Addition: Re: SQLite locking question

[Pavel Ivanov]

> My understanding of the shared cache model was that it just ist intended
> for solving our problem by relaxing the locking a little and that there
> should not be any mutexes at all when using the uncomitted read mode.
>
> Have I missed anything?

Yes, you are involved in a "magical" thinking. All that you said would
be true if any change that SQLite does in the database cache was
atomic. I.e. let's say SQLite needs to add a row, zing, and the row is
in there and all necessary cache pages are changed without other
thread ever noticing any inconsistent state during the process. Also
let's say SQLite needs to find some row in the table, zing, and it
already knows where that row is and other thread didn't change
anything during the search... All this cannot happen, thus mutex is
held.

BTW, if you just read data then locking cannot be an issue for you.
Turn off shared cache and you will be okay.


Pavel


On Fri, May 6, 2011 at 3:56 PM, Ole Reinhardt
 wrote:
> Hi Pavel,
>
>> >> Any other chance to speed this up (apart from the obvious "optimize the
>> >> query, do not use distinct on large tables)=
>> >
>> > Without seeing the query or database schema? Not really... Depending
>> > on the exact query an index on "xyz" might help.
>>
>> Another suggestion could be to turn off shared cache mode.
>
> Oh ok :)
>
> My understanding of the shared cache model was that it just ist intended
> for solving our problem by relaxing the locking a little and that there
> should not be any mutexes at all when using the uncomitted read mode.
>
> Have I missed anything?
>
> Bye,
>
> Ole
>
> --
>
> Thermotemp GmbH, Embedded-IT
>
> Embedded Hard-/ Software and Open Source Development,
> Integration and Consulting
>
> http://www.embedded-it.de
>
> Geschäftsstelle Siegen - Steinstraße 67 - D-57072 Siegen -
> tel +49 (0)271 5513597, +49 (0)271-73681 - fax +49 (0)271 736 97
>
> Hauptsitz - Hademarscher Weg 7 - 13503 Berlin
> Tel +49 (0)30 4315205 - Fax +49 (0)30 43665002
> Geschäftsführer: Jörg Friedrichs, Ole Reinhardt
> Handelsregister Berlin Charlottenburg HRB 45978 UstID DE 156329280
>
> ___
> 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] Addition: Re: SQLite locking question

[Ole Reinhardt]

Hi Pavel,

> >> Any other chance to speed this up (apart from the obvious "optimize the
> >> query, do not use distinct on large tables)=
> >
> > Without seeing the query or database schema? Not really... Depending
> > on the exact query an index on "xyz" might help.
> 
> Another suggestion could be to turn off shared cache mode.

Oh ok :)

My understanding of the shared cache model was that it just ist intended
for solving our problem by relaxing the locking a little and that there
should not be any mutexes at all when using the uncomitted read mode.

Have I missed anything?

Bye,

Ole

-- 

Thermotemp GmbH, Embedded-IT

Embedded Hard-/ Software and Open Source Development, 
Integration and Consulting

http://www.embedded-it.de

Geschäftsstelle Siegen - Steinstraße 67 - D-57072 Siegen - 
tel +49 (0)271 5513597, +49 (0)271-73681 - fax +49 (0)271 736 97

Hauptsitz - Hademarscher Weg 7 - 13503 Berlin
Tel +49 (0)30 4315205 - Fax +49 (0)30 43665002
Geschäftsführer: Jörg Friedrichs, Ole Reinhardt
Handelsregister Berlin Charlottenburg HRB 45978 UstID DE 156329280 

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

Re: [sqlite] Addition: Re: SQLite locking question

[Ole Reinhardt]

Hi Dan,

>[...]
> Mutexes are held for the duration of an sqlite3_step() call to make
> sure SQLite doesn't segfault when two threads try to access the same
> shared-cache object.

Ok, I see, so there is no chance to access the data realy in _parallel_?
(Just for reading).

> > Any other chance to speed this up (apart from the obvious "optimize the
> > query, do not use distinct on large tables)=
> 
> Without seeing the query or database schema? Not really... Depending
> on the exact query an index on "xyz" might help.

The database scheme looks like this:

static gchar sql_create_table_log[] =
"CREATE TABLE IF NOT EXISTS log ( "
"  keyINTEGER, "
"  timestamp_sINTEGER, "
"  timestamp_us   INTEGER, "
"  type   INTEGER, "
"  level  INTEGER, "
"  event  INTEGER, "
"  source TEXT,"
"  dest   TEXT,"
"  text   TEXT,"
"  radio_id   INTEGER, "
"  latitude   REAL,"
"  longitude  REAL,"
"  speed  REAL,"
"  direction  INTEGER, "
"  fixINTEGER, "
"  alert  INTEGER, "
"  state  INTEGER, "
"  cstate INTEGER, "
"  PRIMARY KEY(key)"
")";

static gchar sql_create_index_log[] = 
"CREATE INDEX IF NOT EXISTS timestamp ON log (timestamp_s,
timestamp_us)";


The SQL query that takes that much time for each single step is the
following:

"SELECT DISTINCT radio_id FROM log WHERE event IN (%d, %d) AND
timestamp_s >= %ld AND timestamp_s <= %ld"



The request that shall be done in parallel is the following:

SELECT timestamp_s, timestamp_us, type, level, event, source, dest,
text, radio_id, latitude, longitude, speed, direction, key FROM log
WHERE (timestamp_s * 1000 + timestamp_us / 1000) > %lld %s AND key >
((SELECT MAX(key) FROM log) -2000) ORDER BY KEY DESC


Regards!

Ole





-- 

Thermotemp GmbH, Embedded-IT

Embedded Hard-/ Software and Open Source Development, 
Integration and Consulting

http://www.embedded-it.de

Geschäftsstelle Siegen - Steinstraße 67 - D-57072 Siegen - 
tel +49 (0)271 5513597, +49 (0)271-73681 - fax +49 (0)271 736 97

Hauptsitz - Hademarscher Weg 7 - 13503 Berlin
Tel +49 (0)30 4315205 - Fax +49 (0)30 43665002
Geschäftsführer: Jörg Friedrichs, Ole Reinhardt
Handelsregister Berlin Charlottenburg HRB 45978 UstID DE 156329280 

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

Re: [sqlite] Addition: Re: SQLite locking question

[Pavel Ivanov]

>> Any other chance to speed this up (apart from the obvious "optimize the
>> query, do not use distinct on large tables)=
>
> Without seeing the query or database schema? Not really... Depending
> on the exact query an index on "xyz" might help.

Another suggestion could be to turn off shared cache mode.


Pavel


On Fri, May 6, 2011 at 7:15 AM, Dan Kennedy  wrote:
> On 05/06/2011 05:53 PM, Ole Reinhardt wrote:
>> Hi Dan,
>>
 I have to correct myself a little... the hanging sqlite3_open_v2() does
 not happe while the prepare of the first query but during the first
 sqlite3_step() of the first query...
>>>
>>> Each shared-cache has a mutex associated with it. When
>>> sqlite3_step is called it grabs the mutexes for all shared-caches
>>> it might use. The mutexes are not released until the call
>>> to sqlite3_step() returns.
>>>
>>> So if you have one query that spends a lot of time in sqlite3_step()
>>> you are going to block your other threads. Unfortunately, that is
>>> the nature of shared-cache mode.
>>
>> Thanks for the info. But is this even true when enabling read
>> uncommitted isolation mode using:
>>
>> sqlite3_exec(log_database_local, "PRAGMA read_uncommitted=True;", NULL,
>> 0,)
>>
>> ??
>>
>> I suspect it's the master table lock that is hold there, right?
>
> You are correct that your query will hold a shared-lock on the
> master table at the shared cache level, but it's the mutex that
> is causing you problems. Shared-cache locks are held for the
> duration of a transaction to ensure transactions are correctly
> isolated. In this case the master table is locked to make sure
> that the table your query is accessing is not dropped by another
> thread while you are using it.
>
> Mutexes are held for the duration of an sqlite3_step() call to make
> sure SQLite doesn't segfault when two threads try to access the same
> shared-cache object.
>
>> Any other chance to speed this up (apart from the obvious "optimize the
>> query, do not use distinct on large tables)=
>
> Without seeing the query or database schema? Not really... Depending
> on the exact query an index on "xyz" might help.
>
> 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] Addition: Re: SQLite locking question

[Ole Reinhardt]

Hi Dan,

> > I have to correct myself a little... the hanging sqlite3_open_v2() does
> > not happe while the prepare of the first query but during the first
> > sqlite3_step() of the first query...
> 
> Each shared-cache has a mutex associated with it. When
> sqlite3_step is called it grabs the mutexes for all shared-caches
> it might use. The mutexes are not released until the call
> to sqlite3_step() returns.
> 
> So if you have one query that spends a lot of time in sqlite3_step()
> you are going to block your other threads. Unfortunately, that is
> the nature of shared-cache mode.

Thanks for the info. But is this even true when enabling read
uncommitted isolation mode using:

sqlite3_exec(log_database_local, "PRAGMA read_uncommitted=True;", NULL,
0,)

??

I suspect it's the master table lock that is hold there, right?

Any other chance to speed this up (apart from the obvious "optimize the
query, do not use distinct on large tables)=

Bye,

Ole


-- 

Thermotemp GmbH, Embedded-IT

Embedded Hard-/ Software and Open Source Development, 
Integration and Consulting

http://www.embedded-it.de

Geschäftsstelle Siegen - Steinstraße 67 - D-57072 Siegen - 
tel +49 (0)271 5513597, +49 (0)271-73681 - fax +49 (0)271 736 97

Hauptsitz - Hademarscher Weg 7 - 13503 Berlin
Tel +49 (0)30 4315205 - Fax +49 (0)30 43665002
Geschäftsführer: Jörg Friedrichs, Ole Reinhardt
Handelsregister Berlin Charlottenburg HRB 45978 UstID DE 156329280 

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

Re: [sqlite] Addition: Re: SQLite locking question

[Dan Kennedy]

On 05/06/2011 05:53 PM, Ole Reinhardt wrote:
> Hi Dan,
>
>>> I have to correct myself a little... the hanging sqlite3_open_v2() does
>>> not happe while the prepare of the first query but during the first
>>> sqlite3_step() of the first query...
>>
>> Each shared-cache has a mutex associated with it. When
>> sqlite3_step is called it grabs the mutexes for all shared-caches
>> it might use. The mutexes are not released until the call
>> to sqlite3_step() returns.
>>
>> So if you have one query that spends a lot of time in sqlite3_step()
>> you are going to block your other threads. Unfortunately, that is
>> the nature of shared-cache mode.
>
> Thanks for the info. But is this even true when enabling read
> uncommitted isolation mode using:
>
> sqlite3_exec(log_database_local, "PRAGMA read_uncommitted=True;", NULL,
> 0,)
>
> ??
>
> I suspect it's the master table lock that is hold there, right?

You are correct that your query will hold a shared-lock on the
master table at the shared cache level, but it's the mutex that
is causing you problems. Shared-cache locks are held for the
duration of a transaction to ensure transactions are correctly
isolated. In this case the master table is locked to make sure
that the table your query is accessing is not dropped by another
thread while you are using it.

Mutexes are held for the duration of an sqlite3_step() call to make
sure SQLite doesn't segfault when two threads try to access the same
shared-cache object.

> Any other chance to speed this up (apart from the obvious "optimize the
> query, do not use distinct on large tables)=

Without seeing the query or database schema? Not really... Depending
on the exact query an index on "xyz" might help.

Dan.

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

Re: [sqlite] Addition: Re: SQLite locking question

[Dan Kennedy]

On 05/06/2011 05:17 PM, Ole Reinhardt wrote:
> Hi all,
>
> I have to correct myself a little... the hanging sqlite3_open_v2() does
> not happe while the prepare of the first query but during the first
> sqlite3_step() of the first query...

Each shared-cache has a mutex associated with it. When
sqlite3_step is called it grabs the mutexes for all shared-caches
it might use. The mutexes are not released until the call
to sqlite3_step() returns.

So if you have one query that spends a lot of time in sqlite3_step()
you are going to block your other threads. Unfortunately, that is
the nature of shared-cache mode.

Dan.



>
> So in pseudo code the database access looks like this:
>
> first thread:
>
> sqlite3_open_v2("log.sqlite", SQLITE_OPEN_READONLY |
> SQLITE_OPEN_NOMUTEX, NULL);
> sqlite3_exec(log_database_local, "PRAGMA read_uncommitted=True;", NULL,
> 0,)
> sqlite3_prepare_v2(log_database_local, "select distinct xyz from log
> where ...)
>
> while(sqlite3_step(sql_stmt) == SQLITE_ROW)...
>
> While every step takes _very_ long time as the log table has quite a lot
> entries (>  5.000.000)
>
>
>
> second thread:
>
> sqlite3_open_v2("log.sqlite", SQLITE_OPEN_READONLY |
> SQLITE_OPEN_NOMUTEX, NULL);
>
> --->  This sqlite3_open_v2 does not return until the prepare
> sqlite3_step() statement of the first thread has completed...
>
>
> again: Any help or short hint would be very appreciated!
>
> Thanks in advance,
>
> Ole Reinhardt
>

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

[sqlite] SQLite locking question

[Ole Reinhardt]

Hi all,

we have a little problem with the locking of sqlite connections in
combination with the shared cache model.

We are using sqlite 3.6.10 (on Ubuntu 9.04) or later. Same problem
occurs on the latest ubuntu version with sqlite 3.7.4.

Problem:

We have a multi threaded application that connects from different
threads to a common sqlite database that contains just one large table
(kind of log).

We have enabled shared cache globaly and open the database connections
for read/write from the main thread and read only / multi threading
enabled (SQLITE_OPEN_READONLY | SQLITE_OPEN_NOMUTEX) from the other
threads.

After opening the database connection "Read-Uncommitted Isolation Mode"
is directly enabled using 

if (sqlite3_exec(log_database_local, "PRAGMA read_uncommitted=True;",
NULL, 0, ) != SQLITE_OK) {...




If the log table is well filled (~ 5.000.000 entries) we do different
requests on this table from different threads. They are read-only right
now.

One request takes very long time to complete (select distinct xyz
from ... where ...).

While this request is processed, opening the same database from another
thread blocks until the sqlite3_prepare for the first thread is
completed, even if shared cache model is enabled.



Is there any chance to open the same database from different threads to
do read-only requests _without_ blocking the requests at any place?

We need to be able to read the contents from the table in parallel from
different threads even if one single database requests takes very long
time.


Any help or short hint would be very appreciated!

Thanks in advance,

Ole Reinhardt

-- 

Thermotemp GmbH, Embedded-IT

Embedded Hard-/ Software and Open Source Development, 
Integration and Consulting

http://www.embedded-it.de

Geschäftsstelle Siegen - Steinstraße 67 - D-57072 Siegen - 
tel +49 (0)271 5513597, +49 (0)271-73681 - fax +49 (0)271 736 97

Hauptsitz - Hademarscher Weg 7 - 13503 Berlin
Tel +49 (0)30 4315205 - Fax +49 (0)30 43665002
Geschäftsführer: Jörg Friedrichs, Ole Reinhardt
Handelsregister Berlin Charlottenburg HRB 45978 UstID DE 156329280 

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

Re: [sqlite] Locking issue on NFS filesystem

[Sylvain Pointeau]

> int sqlite3_open_v2(
>  const char *filename,   /* Database filename (UTF-8) */
>  sqlite3 **ppDb, /* OUT: SQLite db handle */
>  int flags,  /* Flags */
>  const char *zVfs/* Name of VFS module to use */
> );
>
> With the last argument "unix-dotfile".
>
>
Does it work?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking issue on NFS filesystem

[Prakash Reddy Bande]

Hi,

Do you mean I shall use 

int sqlite3_open_v2(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb, /* OUT: SQLite db handle */
  int flags,  /* Flags */
  const char *zVfs/* Name of VFS module to use */
);

With the last argument "unix-dotfile".

BTW I am using it just as one uses database and our app also has connectors to 
client/server RDBMS.

We use sqlite in a mode where app is used for in a mode where only one process 
uses it and moreover the process is single threaded too.

Regards,

Prakash


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Roger Binns [rog...@rogerbinns.com]
Sent: Monday, June 14, 2010 8:30 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Locking issue on NFS filesystem

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/14/2010 02:56 AM, Prakash Reddy Bande wrote:
> Our application is a simple desktop application with a simple install and run 
> setup. It is not possible to tell users to apply workaround of sharing the 
> drive via SMB etc.

BTW SMB won't be much better than NFS.  (A lot of things have to line up right.)

You haven't specified if you are using SQLite because it is a convenient
file format or because the transactions and durability matter.  If it is the
latter then you cannot use a networked filesystem.

> I was hoping we have a way to enable dot file locking.

It is available by default.  Use "unix-dotfile" as the VFS name in your open
call.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkwWIP8ACgkQmOOfHg372QT6YQCZAVqZLFg94FlljT7PkZz0jkxP
RH8AoI2daz7YpQ3K7aYNVkG4Qpojqhdf
=jh3O
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking issue on NFS filesystem

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/14/2010 02:56 AM, Prakash Reddy Bande wrote:
> Our application is a simple desktop application with a simple install and run 
> setup. It is not possible to tell users to apply workaround of sharing the 
> drive via SMB etc.

BTW SMB won't be much better than NFS.  (A lot of things have to line up right.)

You haven't specified if you are using SQLite because it is a convenient
file format or because the transactions and durability matter.  If it is the
latter then you cannot use a networked filesystem.

> I was hoping we have a way to enable dot file locking.

It is available by default.  Use "unix-dotfile" as the VFS name in your open
call.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkwWIP8ACgkQmOOfHg372QT6YQCZAVqZLFg94FlljT7PkZz0jkxP
RH8AoI2daz7YpQ3K7aYNVkG4Qpojqhdf
=jh3O
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking issue on NFS filesystem

[Prakash Reddy Bande]

Hi,

Our application is a simple desktop application with a simple install and run 
setup. It is not possible to tell users to apply workaround of sharing the 
drive via SMB etc.
I was hoping we have a way to enable dot file locking. For Mac OS X, sqlite 
allows SQLITE_ENABLE_LOCKING_STYLE compile option, why not leverage that for 
NFS issues too?
May be I am completely wrong, but I feel dot file locking may work as long as 
file read/write/execute permissions are available.

Switching SQLITE_ENABLE_LOCKING_STYLE on RHEL resulted in compile errors. There 
is no configure option but by setting CFLAGS, CCFLAGS I tried compile sqlite 
with this option.
  

Regards,


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Simon Slavin [slav...@bigfraud.org]
Sent: Sunday, June 13, 2010 10:38 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Locking issue on NFS filesystem

On 14 Jun 2010, at 3:16am, Prakash Reddy Bande wrote:

> I have stumbled upon the issue as described in http://sqlite.org/faq.html#q5 
> (But use caution: this locking mechanism might not work correctly if the 
> database file is kept on an NFS filesystem.)
>
> The question is, do we have a workaround. Our application has to store data 
> in user home directory (RHEL/SLES/CentOS) and the home directory might be on 
> a NFS device (as is my home directory).
> stat -f -c %X /users/prakash returns nfs.

Which version of NFS ?  Locking was introduced in version 4.  However, locking 
even under NFS sucks.  That warning in the SQLite FAQ isn't there because the 
writers of SQLite are bad programmers, it's there because locking under many 
NFS installations is not implemented properly.

You may have an alternative of accessing your NFS drive as a shared drive.  If 
you do this, then locking is implemented by the networking protocol, not by the 
driver of the space being shared.  So if you have a way of sharing your NFS 
drive via SMB or AFS, or some other common space-sharing system, you might be 
able to get around the NFS problems completely.

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

Re: [sqlite] Locking issue on NFS filesystem

[Simon Slavin]

On 14 Jun 2010, at 3:16am, Prakash Reddy Bande wrote:

> I have stumbled upon the issue as described in http://sqlite.org/faq.html#q5 
> (But use caution: this locking mechanism might not work correctly if the 
> database file is kept on an NFS filesystem.)
> 
> The question is, do we have a workaround. Our application has to store data 
> in user home directory (RHEL/SLES/CentOS) and the home directory might be on 
> a NFS device (as is my home directory). 
> stat -f -c %X /users/prakash returns nfs.

Which version of NFS ?  Locking was introduced in version 4.  However, locking 
even under NFS sucks.  That warning in the SQLite FAQ isn't there because the 
writers of SQLite are bad programmers, it's there because locking under many 
NFS installations is not implemented properly.

You may have an alternative of accessing your NFS drive as a shared drive.  If 
you do this, then locking is implemented by the networking protocol, not by the 
driver of the space being shared.  So if you have a way of sharing your NFS 
drive via SMB or AFS, or some other common space-sharing system, you might be 
able to get around the NFS problems completely.

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

[sqlite] Locking issue on NFS filesystem

[Prakash Reddy Bande]

Hi,

I have stumbled upon the issue as described in http://sqlite.org/faq.html#q5 
(But use caution: this locking mechanism might not work correctly if the 
database file is kept on an NFS filesystem.)

The question is, do we have a workaround. Our application has to store data in 
user home directory (RHEL/SLES/CentOS) and the home directory might be on a NFS 
device (as is my home directory). 
stat -f -c %X /users/prakash returns nfs.

I am vaguely imagining if we could (at compile or run time) have SQLite use dot 
file locking.

Let me describe the problem I am facing.
The database file does not exist and the call to sqlite3_open, does not return 
(or atleast does not return as long as I waited ~10 mins). I killed the process 
using kill / kill -9, but process went into zombie state.
Also I observed that the database file got created (may not be usable) and 
another file nfs00. was created and locked by my app.

I compiled the latest sqlite 3.6.23.1 and ran the following command
sqlite /users/prakash/test.db
I got the sqlite command prompt, however, the command .tables resulted in hang.

So, again, I am trying to figure out a solution/workaround. Concurrency is not 
the most major concern and hence if we can use a different locking mechanism, 
that will be nice.

Regards,

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

Re: [sqlite] Locking under various Windows versions

[Noah Hart]

Robert, 

Makes sense.  Some background ... I'm again looking into how to handle
locking under Silverlight, and was looking into the WINCE method for
ideas.

The isolatedstoragefilestream under NET.4 claims to support the Lock
method, (see
http://msdn.microsoft.com/en-us/library/system.io.isolatedstorage.isolat
edstoragefilestream_methods%28v=VS.100%29.aspx) but doesn't actually
seem to be there under VS2010 and Silverlight 4

Might need to go the mutex route

Noah


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Robert Simpson
Sent: Friday, April 16, 2010 3:57 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Locking under various Windows versions

WinCE has no lockfile support, so it's fudged on the device by means of
a
shared memory block to handle the locking.  If you open a SQLite
database on
a network share from a CE device, then it will not be able to use the
network share's locking mechanisms.  In short, don't do it.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Noah Hart
Sent: Friday, April 16, 2010 3:48 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Locking under various Windows versions

I am trying to determine if SQLite holds an exclusive lock on a 
database opened under Windows-CE, will that lock be honored 
by a connection opened under another version of Windows?

What about the opposite case?  When  the database is first 
opened under Windows 7, an exclusive lock is acquired, then
a connection to the database is made under WindowsCE.

I've read the os_win.c code and am not clear how it is handled in this
type of mixed mode


Regards

Noah Hart




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If
you
are not the addressee or authorized to receive this for the addressee,
you
must not use, copy, disclose, or take any action based on this message
or
any information herein. If you have received this message in error,
please
advise the sender immediately by reply e-mail and delete this message.
Thank
you for your cooperation.


___
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] Locking under various Windows versions

[Robert Simpson]

WinCE has no lockfile support, so it's fudged on the device by means of a
shared memory block to handle the locking.  If you open a SQLite database on
a network share from a CE device, then it will not be able to use the
network share's locking mechanisms.  In short, don't do it.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Noah Hart
Sent: Friday, April 16, 2010 3:48 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Locking under various Windows versions

I am trying to determine if SQLite holds an exclusive lock on a 
database opened under Windows-CE, will that lock be honored 
by a connection opened under another version of Windows?

What about the opposite case?  When  the database is first 
opened under Windows 7, an exclusive lock is acquired, then
a connection to the database is made under WindowsCE.

I've read the os_win.c code and am not clear how it is handled in this
type of mixed mode


Regards

Noah Hart




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you
are not the addressee or authorized to receive this for the addressee, you
must not use, copy, disclose, or take any action based on this message or
any information herein. If you have received this message in error, please
advise the sender immediately by reply e-mail and delete this message. Thank
you for your cooperation.


___
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] Locking under various Windows versions

[Noah Hart]

I am trying to determine if SQLite holds an exclusive lock on a 
database opened under Windows-CE, will that lock be honored 
by a connection opened under another version of Windows?

What about the opposite case?  When  the database is first 
opened under Windows 7, an exclusive lock is acquired, then
a connection to the database is made under WindowsCE.

I've read the os_win.c code and am not clear how it is handled in this
type of mixed mode


Regards

Noah Hart




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


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

Re: [sqlite] locking across the network...

[Pavel Ivanov]

 http://www.sqlite.org/whentouse.html

   "...file locking logic of many network filesystems implementation
   contains bugs (on both Unix and Windows). If file locking does
   not work like it should, it might be possible for two or more
   client programs to modify the same part of the same database at
   the same time, resulting in database corruption. Because this
   problem results from bugs in the underlying filesystem
   implementation, there is nothing SQLite can do to prevent it."


So you better access SQLite DB always via Apache Web Server.


Pavel


On Fri, Mar 26, 2010 at 9:34 AM, Sam Carleton
 wrote:
> I am currently using SQLite in my client/server application:
>
> Server: Apache Web Server -- the sole process that access the SQLite
> databases
> Client A: Admin Client to configure the server, there is only 1 instance of
> this client.
> Client B: Kiosk Client, there is 1 or more of these, some have over 50
>
> Right now both Clients go through the web server to access the SQLite
> database, and all works fine.  The Kiosk Clients are also getting images
> from the server, so they will ALWAYS access the SQLite DB via the Apache
> server.  If I am not mistaken, the Admin Client, being on the same machine
> as the Apache Web Server, could also access the SQLite database with little
> or no effect, since they are very small rights.
>
> A third client needs to be added to the system, a Sales Client.  For larger
> setups where there are more than 2 Sales Clients (10 kiosk clients), a
> traditional client/server database will be used.  What I am wondering is, if
> for the smaller setups where only 2 sales clients are needed, can these
> sales clients access the SQLite DB via a network share without a major
> preformance hit or am I better off having the sales clients go through the
> Apache Web Server, too?
>
> Sam
> ___
> 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] locking across the network...

[Sam Carleton]

I am currently using SQLite in my client/server application:

Server: Apache Web Server -- the sole process that access the SQLite
databases
Client A: Admin Client to configure the server, there is only 1 instance of
this client.
Client B: Kiosk Client, there is 1 or more of these, some have over 50

Right now both Clients go through the web server to access the SQLite
database, and all works fine.  The Kiosk Clients are also getting images
from the server, so they will ALWAYS access the SQLite DB via the Apache
server.  If I am not mistaken, the Admin Client, being on the same machine
as the Apache Web Server, could also access the SQLite database with little
or no effect, since they are very small rights.

A third client needs to be added to the system, a Sales Client.  For larger
setups where there are more than 2 Sales Clients (10 kiosk clients), a
traditional client/server database will be used.  What I am wondering is, if
for the smaller setups where only 2 sales clients are needed, can these
sales clients access the SQLite DB via a network share without a major
preformance hit or am I better off having the sales clients go through the
Apache Web Server, too?

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

Re: [sqlite] Locking bug?

[Nico Coesel]

I did some further testing. It turns out there are two solutions:
1) Replace 'BEGIN IMMEDIATE' from the transaction with 'BEGIN EXCLUSIVE'
2) Set sqlite3_busytimeout() instead of re-trying the query during a
given period (with sleep intervals). I'm not sure this really solves the
issue. It just might take longer for the problem to surface.

Though I'm still not convinced the locking problem isn't a bug. IMHO a
database should never allow itself to be left in a situation from which
it cannot recover after a query.

Nico Coesel

> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Nico Coesel
> Sent: Wednesday, November 04, 2009 4:19 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Locking bug?
> 
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Simon Slavin
> > Sent: woensdag 4 november 2009 16:09
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Locking bug?
> >
> >
> > On 4 Nov 2009, at 1:17pm, Nico Coesel wrote:
> >
> > > Two seperate processes (process A and process B) on a Linux system
> > > read
> > > and write to the same database. Process A performs a transaction
> > > every 5
> > > seconds. Every now and then the locks from process A are not
> released
> > > (judging from /proc/locks). It seems this situation occurs when
> > > process
> > > A and B both try to access the database at the same time. I have
not
> > > found a way to release the lock besides closing the database
handle.
> > > I'm
> > > using the sqlite3_exec function to execute a query; this function
> > > calls
> > > slite3_finalize at the end so this should release the locks.
> >
> > Are you checking how every sqlite3_ function call returns, in both
> > processes, to see whether it is reporting an error ?  Even a
function
> > like 'COMMIT' can correctly make the changes you want but return an
> > error message anyway.
> >
> 
> Yes, I check every return value. I also see errors from process B
saying
> the database is locked. In /proc/locks I can see process A still
locked
> the database. I also tried to use the command line tool to access the
> database using the vacuum command but it also says the database is
> locked.
> 
> Nico Coesel
> 
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of O'Neill, Owen
> Sent: woensdag 4 november 2009 17:33
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Locking bug?
> 
> 
> I could be wrong, but even if the statement failed, and you have
called
> finalize, I vaguely recollect reading somewhere that (if you called
> BEGIN) then you need to rollback.
> Can't find the manual / wiki link to back that up at the moment.
> 
> (oh and installing a busy handler should help things - just setting a
> timeout will do to start with, just watch out that if your system only
> has sleep and not usleep then the timeout needs to be >=2000ms to do
> anything.)
> 
> Owen.

 
 


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

Re: [sqlite] Locking bug?

[O'Neill, Owen]

I could be wrong, but even if the statement failed, and you have called
finalize, I vaguely recollect reading somewhere that (if you called
BEGIN) then you need to rollback.
Can't find the manual / wiki link to back that up at the moment.

(oh and installing a busy handler should help things - just setting a
timeout will do to start with, just watch out that if your system only
has sleep and not usleep then the timeout needs to be >=2000ms to do
anything.)

Owen.



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Nico Coesel
Sent: Wednesday, November 04, 2009 4:19 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Locking bug?

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: woensdag 4 november 2009 16:09
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Locking bug?
> 
> 
> On 4 Nov 2009, at 1:17pm, Nico Coesel wrote:
> 
> > Two seperate processes (process A and process B) on a Linux system
> > read
> > and write to the same database. Process A performs a transaction
> > every 5
> > seconds. Every now and then the locks from process A are not
released
> > (judging from /proc/locks). It seems this situation occurs when
> > process
> > A and B both try to access the database at the same time. I have not
> > found a way to release the lock besides closing the database handle.
> > I'm
> > using the sqlite3_exec function to execute a query; this function
> > calls
> > slite3_finalize at the end so this should release the locks.
> 
> Are you checking how every sqlite3_ function call returns, in both
> processes, to see whether it is reporting an error ?  Even a function
> like 'COMMIT' can correctly make the changes you want but return an
> error message anyway.
> 

Yes, I check every return value. I also see errors from process B saying
the database is locked. In /proc/locks I can see process A still locked
the database. I also tried to use the command line tool to access the
database using the vacuum command but it also says the database is
locked. 

Nico Coesel



___
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] Locking bug?

[Jay A. Kreibich]

On Wed, Nov 04, 2009 at 05:19:11PM +0100, Nico Coesel scratched on the wall:
> 
> > > I'm using the sqlite3_exec function to execute a query; this function
> > > calls slite3_finalize at the end so this should release the locks.
> 
> Yes, I check every return value. I also see errors from process B saying
> the database is locked. In /proc/locks I can see process A still locked
> the database. I also tried to use the command line tool to access the
> database using the vacuum command but it also says the database is
> locked. 

  There are situations when SQLite can dead-lock, where two processes
  will both sit and wait for the other to release its locks.  It isn't
  just a matter of checking the return values, but also of doing the
  right thing.  In specific, if you get any SQLITE_BUSY codes, you need
  to rollback the transaction and try again from the start.

  However, if you using sqlite3_exec() for all your SQL and you're NOT
  using explicit transactions, then that should take care of things for
  you.  If you are using explicit transactions (sending a "BEING" and
  "END" or "COMMIT") you need to handle this situation yourself.



  Is process A cleanly exiting when the lock is left behind?

  What OS and filesystem are you using?

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking bug?

[Nico Coesel]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: woensdag 4 november 2009 16:09
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Locking bug?
> 
> 
> On 4 Nov 2009, at 1:17pm, Nico Coesel wrote:
> 
> > Two seperate processes (process A and process B) on a Linux system
> > read
> > and write to the same database. Process A performs a transaction
> > every 5
> > seconds. Every now and then the locks from process A are not
released
> > (judging from /proc/locks). It seems this situation occurs when
> > process
> > A and B both try to access the database at the same time. I have not
> > found a way to release the lock besides closing the database handle.
> > I'm
> > using the sqlite3_exec function to execute a query; this function
> > calls
> > slite3_finalize at the end so this should release the locks.
> 
> Are you checking how every sqlite3_ function call returns, in both
> processes, to see whether it is reporting an error ?  Even a function
> like 'COMMIT' can correctly make the changes you want but return an
> error message anyway.
> 

Yes, I check every return value. I also see errors from process B saying
the database is locked. In /proc/locks I can see process A still locked
the database. I also tried to use the command line tool to access the
database using the vacuum command but it also says the database is
locked. 

Nico Coesel



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

Re: [sqlite] Locking bug?

[Simon Slavin]

On 4 Nov 2009, at 1:17pm, Nico Coesel wrote:

> Two seperate processes (process A and process B) on a Linux system  
> read
> and write to the same database. Process A performs a transaction  
> every 5
> seconds. Every now and then the locks from process A are not released
> (judging from /proc/locks). It seems this situation occurs when  
> process
> A and B both try to access the database at the same time. I have not
> found a way to release the lock besides closing the database handle.  
> I'm
> using the sqlite3_exec function to execute a query; this function  
> calls
> slite3_finalize at the end so this should release the locks.

Are you checking how every sqlite3_ function call returns, in both  
processes, to see whether it is reporting an error ?  Even a function  
like 'COMMIT' can correctly make the changes you want but return an  
error message anyway.

Simon.


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

[sqlite] Locking bug?

[Nico Coesel]

Hello,
I have the following situation:

Two seperate processes (process A and process B) on a Linux system read
and write to the same database. Process A performs a transaction every 5
seconds. Every now and then the locks from process A are not released
(judging from /proc/locks). It seems this situation occurs when process
A and B both try to access the database at the same time. I have not
found a way to release the lock besides closing the database handle. I'm
using the sqlite3_exec function to execute a query; this function calls
slite3_finalize at the end so this should release the locks.

I suspect there may be a path in the code which skips releasing the
locks.

Nico Coesel


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

Re: [sqlite] SQLite locking problem with mozStorage

[Simon Slavin]

On 16 Aug 2009, at 11:55am, Sebastian Arcus wrote:

> Just to thank you both for your suggestions and to let you know the
> problem is solved.

I'm glad you solved it, and thanks for your thanks.  In a week like  
this one it's nice to know I'm doing someone some good somewhere.

>  I've tried your suggestion Simon and everything
> worked fine through the SQLite command line directly. So I proceeded  
> to
> strip down all my code line by line and function by function until I
> found the problem.

It's often the use of the many SQLite interface APIs that causes  
problems like this, but it's hard to prove.  That's why I like the  
bare-bones command-line utility so much: if you can reproduce the  
problem in that, it's definitely SQLite's fault.  If you can't, it's  
not.

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

Re: [sqlite] SQLite locking problem with mozStorage

[Sebastian Arcus]

Hi Simon and Roger,

Simon Slavin wrote:
> On 15 Aug 2009, at 9:58am, Sebastian Arcus wrote:
>
>   
>> I've just noticed another interesting thing. A read operation after a
>> write on same table (like a SELECT statement after an INSERT) works
>> perfectly fine - instantly. Which is bizarre, as I would have  
>> expected a
>> write to keep a much longer lock on the table then a read. So it's  
>> only
>> writes after reads that causes the lock.
>> 
>
> Would it be possible to put the SQL commands into a text file, use the  
> command-line utility to execute the text file, and time how long it  
> takes ?
>
> Since the command-line utility uses the bare sqlite function library  
> and very little extra code, this would tell you whether the delay is  
> being caused by the SQLite library on down, or some level above the  
> SQLite library.
>
>   
Just to thank you both for your suggestions and to let you know the 
problem is solved. I've tried your suggestion Simon and everything 
worked fine through the SQLite command line directly. So I proceeded to 
strip down all my code line by line and function by function until I 
found the problem. It was all my doing. A dodgy function return was 
cutting off my code - and a statement object (container for SQL in the 
mozStorage implementation) which I was under the impression that was 
being finalized/reset (to be re-used in the second SQL query) was not. 
Actually the Mozilla people do warn that any statement object needs to 
be reset (or finalized, which includes a reset) before it can be 
re-used. The statement reset code was all there - it was just never 
executed. Thus it seems that it took about 10 seconds for the statement 
object to be reset by default - without specific instruction. Entirely 
my fault.

Thanks for all the input.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite locking problem with mozStorage

[Simon Slavin]

On 15 Aug 2009, at 9:58am, Sebastian Arcus wrote:

> I've just noticed another interesting thing. A read operation after a
> write on same table (like a SELECT statement after an INSERT) works
> perfectly fine - instantly. Which is bizarre, as I would have  
> expected a
> write to keep a much longer lock on the table then a read. So it's  
> only
> writes after reads that causes the lock.

Would it be possible to put the SQL commands into a text file, use the  
command-line utility to execute the text file, and time how long it  
takes ?

Since the command-line utility uses the bare sqlite function library  
and very little extra code, this would tell you whether the delay is  
being caused by the SQLite library on down, or some level above the  
SQLite library.

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

Re: [sqlite] SQLite locking problem with mozStorage

[Sebastian Arcus]

Roger Binns wrote:
>
> Sebastian Arcus wrote:
>   
>> Hi and thanks for the suggestion. I did as you advised and ran 'vmstat 
>> 1' in a terminal. Very little activity - maybe 20-40kb every 6-7 seconds 
>> on the bo - pretty much nothing on bi. Also, zeros all around on so/si. 
>> 
>
> That rules out disk activity as the cause.  Consequently I would pronounce
> your problem to be caused by whatever layers you have on top of SQLite.
> Another useful tool on Linux is ltrace which shows library calls.  Note
> however that it can't show calls made to libraries loaded via dlopen which
> is usually most of them.
>
>   
>> People seem to be talking about operations on tens to hundreds of 
>> thousands of records, and performance in some circumstances close to 
>> MySQL - it just seems all very far off from waiting 10 seconds on a 
>> single UPDATE or INSERT SQL statement. I must be doing something wrong.
>> 
>
> It will be the layer above SQLite causing the problem.  Disks are orders of
> magnitude slower than memory.  The performance choke point is having to do
> syncs to ensure data is on disk, but both SQLite and database servers have
> to do that.  SQLite then also benefits from running in the same process as
> whatever is using it, omitting network round trips.  In general the way
> servers win is by dedicating gigabytes of memory to caching but SQLite can
> also a lot if you increase various defaults.
>
>   
I've just noticed another interesting thing. A read operation after a 
write on same table (like a SELECT statement after an INSERT) works 
perfectly fine - instantly. Which is bizarre, as I would have expected a 
write to keep a much longer lock on the table then a read. So it's only 
writes after reads that causes the lock.

I just thought it was interesting to note.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite locking problem with mozStorage

[Sebastian Arcus]

Roger Binns wrote:
>
> Sebastian Arcus wrote:
>   
>> Hi and thanks for the suggestion. I did as you advised and ran 'vmstat 
>> 1' in a terminal. Very little activity - maybe 20-40kb every 6-7 seconds 
>> on the bo - pretty much nothing on bi. Also, zeros all around on so/si. 
>> 
>
> That rules out disk activity as the cause.  Consequently I would pronounce
> your problem to be caused by whatever layers you have on top of SQLite.
> Another useful tool on Linux is ltrace which shows library calls.  Note
> however that it can't show calls made to libraries loaded via dlopen which
> is usually most of them.
>
>   
>> People seem to be talking about operations on tens to hundreds of 
>> thousands of records, and performance in some circumstances close to 
>> MySQL - it just seems all very far off from waiting 10 seconds on a 
>> single UPDATE or INSERT SQL statement. I must be doing something wrong.
>> 
>
> It will be the layer above SQLite causing the problem.  Disks are orders of
> magnitude slower than memory.  The performance choke point is having to do
> syncs to ensure data is on disk, but both SQLite and database servers have
> to do that.  SQLite then also benefits from running in the same process as
> whatever is using it, omitting network round trips.  In general the way
> servers win is by dedicating gigabytes of memory to caching but SQLite can
> also a lot if you increase various defaults.
>
>   
Thanks Roger. I guess then I'm no further with this. I'm starting to 
wonder if the mozStorage library has such a small developer-user base 
(outside the actual Mozilla apps and Firefox/Thunderbird extensions) 
that I shouldn't expect to be able to get to the bottom of the thing. 
The strangest thing is that I remember few years back I've hit an almost 
identical road-block with a Microsoft Access database. After working 
fine for years - after some Windows update - it started taking 3-4 
seconds for one particular UPDATE operation. Never got to the bottom of 
that either :-) I had to include a progress bar on the form, and wait 4 
secs every time when attempting to save the record.

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

Re: [sqlite] SQLite locking problem with mozStorage

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Sebastian Arcus wrote:
> Hi and thanks for the suggestion. I did as you advised and ran 'vmstat 
> 1' in a terminal. Very little activity - maybe 20-40kb every 6-7 seconds 
> on the bo - pretty much nothing on bi. Also, zeros all around on so/si. 

That rules out disk activity as the cause.  Consequently I would pronounce
your problem to be caused by whatever layers you have on top of SQLite.
Another useful tool on Linux is ltrace which shows library calls.  Note
however that it can't show calls made to libraries loaded via dlopen which
is usually most of them.

> People seem to be talking about operations on tens to hundreds of 
> thousands of records, and performance in some circumstances close to 
> MySQL - it just seems all very far off from waiting 10 seconds on a 
> single UPDATE or INSERT SQL statement. I must be doing something wrong.

It will be the layer above SQLite causing the problem.  Disks are orders of
magnitude slower than memory.  The performance choke point is having to do
syncs to ensure data is on disk, but both SQLite and database servers have
to do that.  SQLite then also benefits from running in the same process as
whatever is using it, omitting network round trips.  In general the way
servers win is by dedicating gigabytes of memory to caching but SQLite can
also a lot if you increase various defaults.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkqGRHcACgkQmOOfHg372QSwbQCg4Nqct/BJQ8Sm8/Wdr8NH8Mvn
rIEAoN/yoHWFPTBHDxMR1p47qJNdewnn
=Eyrr
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite locking problem with mozStorage

[Sebastian Arcus]

Roger Binns wrote:
> Sebastian Arcus wrote:
>   
>> The SQLite documentation talks about entire database locks by operations 
>> of the order of milliseconds - 10 seconds seems a long way off. 
>> 
>
> There is a possible but unlikely cause for what you are seeing.  In order to
> ensure the database and/or journal are fully on disk, SQLite calls the fsync
> system call on the relevant file handle.
>
> Due to the internals of the way the ext3 filesystem is written, an fsync
> call actually turns into a sync call which means the fsync does not return
> until all outstanding data on that filesystem has been written to disk.  If
> you have a slow disk (your laptop most likely does) plus lots of outstanding
> data then 10 seconds is quite possible, especially if "laptop mode" is
> active (increases the periodic background filesystem sync to 5 minutes in
> some cases).  A fun way of showing this is to do a hefty compile as that
> will generate lots of big files quickly.
>
> I recommend you run "vmstat 1" in a terminal somewhere while doing your
> operations.  Look at the bo/bi columns which show the 1kb blocks being
> read/written from disk per second.  The so/si columns show if any swap
> activity is happening also using a 1kb reporting unit.
>   
Hi and thanks for the suggestion. I did as you advised and ran 'vmstat 
1' in a terminal. Very little activity - maybe 20-40kb every 6-7 seconds 
on the bo - pretty much nothing on bi. Also, zeros all around on so/si. 
I am running a very lightly loaded system - fluxbox as a desktop 
environment - in order to get quite snappy performance from what is 
otherwise not a very fast CPU.

People seem to be talking about operations on tens to hundreds of 
thousands of records, and performance in some circumstances close to 
MySQL - it just seems all very far off from waiting 10 seconds on a 
single UPDATE or INSERT SQL statement. I must be doing something wrong.

P.S. I don't know why my initial post has shown as a reply to another 
poster's topic - apologies - I don't understand why that happened - I've 
sent a new message to the list - not a reply.

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

Re: [sqlite] SQLite locking problem with mozStorage

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Sebastian Arcus wrote:
> The SQLite documentation talks about entire database locks by operations 
> of the order of milliseconds - 10 seconds seems a long way off. 

There is a possible but unlikely cause for what you are seeing.  In order to
ensure the database and/or journal are fully on disk, SQLite calls the fsync
system call on the relevant file handle.

Due to the internals of the way the ext3 filesystem is written, an fsync
call actually turns into a sync call which means the fsync does not return
until all outstanding data on that filesystem has been written to disk.  If
you have a slow disk (your laptop most likely does) plus lots of outstanding
data then 10 seconds is quite possible, especially if "laptop mode" is
active (increases the periodic background filesystem sync to 5 minutes in
some cases).  A fun way of showing this is to do a hefty compile as that
will generate lots of big files quickly.

I recommend you run "vmstat 1" in a terminal somewhere while doing your
operations.  Look at the bo/bi columns which show the 1kb blocks being
read/written from disk per second.  The so/si columns show if any swap
activity is happening also using a 1kb reporting unit.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkqF2+oACgkQmOOfHg372QQvCgCgr+WTz3tC4eOViSIfPwmHUV7Z
QXAAn2PeO5nLy+fXKeIowsbd+/jgc78m
=njqI
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite locking problem with mozStorage

[Sebastian Arcus]

Hi all,

I'm using the mozStorage implementation from Mozilla foundation, with 
Javascript and XUL (for the UI) to build a business-type app.

I'm having this problem where I seem to get a lock of about 10 seconds 
after a read operation on a table, before I can perform a write 
operation on the same table.

This is what I'm trying to do. When user clicks the 'OK' button on a 
particular form, I run a 'SELECT' query on the invoice table to check if 
the invoice number on the form is already in use. Then, if it is not in 
use, I attempt to save the input from the form in the invoices table. 
However, this silently fails. If I assign (for testing purposes) the 
first part of the code for checking the invoice number to a different 
button, I am able to perform that operation, and if I wait about 10 
seconds, I can press the other button and save the record. Any shorter 
period of time, it refuses to save the invoice data into the table.

The SQLite documentation talks about entire database locks by operations 
of the order of milliseconds - 10 seconds seems a long way off. Does 
anybody have any ideas on this? Has anybody used the mozStorage 
interface - and is it to blame for the delay? I have tried finalizing 
the statements, and closing the connection after each SQL statement, or 
leaving the connection to the database open - doesn't make any 
difference. I have tried using a transaction around the first operation, 
with each transaction type in turns, to try and get it out of the way 
quicker and release the lock - still no difference. The database is 
small - about 10 tables, with only a few testing records in each. It is 
not shared by any other app and it is on the local hard-disk. The 
computer is relatively fast and responsive - no other speed issues. 
Actually, aside from this, everything works very nice - even complex 
'SELECT' statements with aggregate functions pulling data from three 
different tables work instantly. Oh, and SQL update statements where 
there is no immediate prior operation on same table are fast.

The setup is:

OS: Slackware Linux 12.2
Kernel: 2.6.27.7
CPU: Intel Core 2 Duo ULV 2500 (1.2GHz)
RAM: 2GB
XULRunner: 1.9.0.7
SQLite: 3.6.6.2


Many thanks for any suggestions or ideas.


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

Re: [sqlite] Locking in sqlite

[Pavel Ivanov]

So if A, B and C are different files then they will not block despite
leaving inside the same connection, right? BTW, by "block" I mean
retrying after SQLITE_BUSY too.
That's very good and convenient thing.

Thank you,
Pavel

On Sat, May 2, 2009 at 10:05 PM, Nuno Lucas  wrote:
> On Sat, May 2, 2009 at 3:48 PM, Pavel Ivanov  wrote:
> [...]
>> update main.t set x = 1
>> update B.t set x = 1
>> update C.t set x = 1
>>
>> My question is will these statements be able to execute in connections
>> in parallel simultaneously because they use different databases and
>> should lock only those databases? Or they will lock each other and
>> will exhibit effectively serial execution because main database in all
>> connections is the same?
>
> The lock is per database file, so they will only block (or fail with
> SQLITE_BUSY) if done at the same time on the same database file.
>
> Regards,
> ~Nuno Lucas
>
>>
>> Pavel
> ___
> 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