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 yo

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

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 
> Subject: Re: [sqlite] Locking semantics are broken?
> 
> 
> 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:
> 
> <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

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

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 
To: sqlite-users@sqlite.org 
Sent: Saturday, May 5, 2012 6:02 PM
Subject: Re: [sqlite] Locking/Concurrency
  
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
___
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 
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

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,&err)
>>
>> ??
>>
>> 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,&err)

??

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,&err)
>
> ??
>
> 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,&err)
> 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] Addition: Re: SQLite locking question

[Ole Reinhardt]

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...

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, &err)
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

-- 

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

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

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

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

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

Re: [sqlite] Locking in sqlite

[Nuno Lucas]

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

Re: [sqlite] Locking documentation enhancement.

[John Karp]

Speaking of documentation of locks... my one beef with the sqlite
documentation is that its not very explicit about implicit locking. To
find out about implicit locks, you have to read
http://www.sqlite.org/lang_transaction.html, even though implicit
locks aren't necessarily associated with transactions. The C API
functions that actually manage these locks just mention them in
passing.

To me, it makes sense to have a blurb like:
"For 'step' to succeed, the database must either be unlocked, or have
no more than a SHARED lock upon it. Upon successful completion of this
function, the database lock state will be SHARED. (For this lock to be
undone, all statements in the executed state must be either reset or
finalized.)"
at http://www.sqlite.org/c3ref/step.html, since its an important
side-effect of the function.
And perhaps have similar text in all other functions that manipulate
the implicit lock.

-John

On Mon, Dec 15, 2008 at 12:15 PM, Mike Mestnik  wrote:
> On Mon, Dec 15, 2008 at 11:40:45AM -0600, Jay A. Kreibich wrote:
>> On Mon, Dec 15, 2008 at 09:08:34AM -0600, Mike Mestnik scratched on the wall:
>> >   The locking is well documented, but it's not vary clear that the
>> > BEGIN command can be used to acquire locks.  The relation between the
>> > different locking stats and parameters to the BEGIN command could be
>> > spelled out.
>>
>>   You might have missed this.  Assuming you understand the locking
>>   system, it does a pretty good job at filling in the details on BEGIN.
>>
>>   http://www.sqlite.org/lang_transaction.html
>>
>> Transactions can be deferred, immediate, or exclusive. The default
>> transaction behavior is deferred. Deferred means that no locks are
>> acquired on the database until the database is first accessed. Thus
>> with a deferred transaction, the BEGIN statement itself does nothing.
>> Locks are not acquired until the first read or write operation. The
>> first read operation against a database creates a SHARED lock and the
>> first write operation creates a RESERVED lock. Because the acquisition
>> of locks is deferred until they are needed, it is possible that another
>> thread or process could create a separate transaction and write to the
>> database after the BEGIN on the current thread has executed. If the
>
> At this time I have more then enough information to complete my
> project, thank you so much for all the help.
>
>> transaction is immediate, then RESERVED locks are acquired on all
>> databases as soon as the BEGIN command is executed, without waiting for
>
> I think text like the above is missing for each of the BEGIN
> arguments.  To make things easy to find it might also be good to put
> some BEGIN examples into the page that explains how locking works.
>
> DEFERRED/default, then initially ??no?? locks.
> IMMEDIATE, then initially RESERVED lock.
> EXCLUSIVE, then initially ? lock.
>
> There are more locks then there are BEGIN arguments, correct?  Perhaps
> an explanation on why BEGIN has no support for creating this type of
> lock.
>
> I also wonder about nested begins?  For example when you want to
> explicitly change an EXCLUSIVE lock into a IMMEDIATE one, or the
> other way around.
>
>
> Perhaps my confusion simply comes from a lack of understanding basic
> SQL concepts.
>
>> the database to be used. After a BEGIN IMMEDIATE, you are guaranteed
>> that no other thread or process will be able to write to the database
>> or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can
>> continue to read from the database, however. An exclusive transaction
>> causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN
>> EXCLUSIVE, you are guaranteed that no other thread or process will be
>> able to read or write the database until the transaction is complete.
>>
>>
>>
>> --
>> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking documentation enhancement.

[Mike Mestnik]

On Mon, Dec 15, 2008 at 11:40:45AM -0600, Jay A. Kreibich wrote:
> On Mon, Dec 15, 2008 at 09:08:34AM -0600, Mike Mestnik scratched on the wall:
> >   The locking is well documented, but it's not vary clear that the
> > BEGIN command can be used to acquire locks.  The relation between the
> > different locking stats and parameters to the BEGIN command could be
> > spelled out.
> 
>   You might have missed this.  Assuming you understand the locking
>   system, it does a pretty good job at filling in the details on BEGIN.
> 
>   http://www.sqlite.org/lang_transaction.html
> 
> Transactions can be deferred, immediate, or exclusive. The default
> transaction behavior is deferred. Deferred means that no locks are
> acquired on the database until the database is first accessed. Thus
> with a deferred transaction, the BEGIN statement itself does nothing.
> Locks are not acquired until the first read or write operation. The
> first read operation against a database creates a SHARED lock and the
> first write operation creates a RESERVED lock. Because the acquisition
> of locks is deferred until they are needed, it is possible that another
> thread or process could create a separate transaction and write to the
> database after the BEGIN on the current thread has executed. If the

At this time I have more then enough information to complete my
project, thank you so much for all the help.

> transaction is immediate, then RESERVED locks are acquired on all
> databases as soon as the BEGIN command is executed, without waiting for

I think text like the above is missing for each of the BEGIN
arguments.  To make things easy to find it might also be good to put
some BEGIN examples into the page that explains how locking works.

DEFERRED/default, then initially ??no?? locks.
IMMEDIATE, then initially RESERVED lock.
EXCLUSIVE, then initially ? lock.

There are more locks then there are BEGIN arguments, correct?  Perhaps
an explanation on why BEGIN has no support for creating this type of
lock.

I also wonder about nested begins?  For example when you want to
explicitly change an EXCLUSIVE lock into a IMMEDIATE one, or the
other way around.


Perhaps my confusion simply comes from a lack of understanding basic
SQL concepts.

> the database to be used. After a BEGIN IMMEDIATE, you are guaranteed
> that no other thread or process will be able to write to the database
> or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can
> continue to read from the database, however. An exclusive transaction
> causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN
> EXCLUSIVE, you are guaranteed that no other thread or process will be
> able to read or write the database until the transaction is complete. 
> 
> 
> 
> -- 
> 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 documentation enhancement.

[Jay A. Kreibich]

On Mon, Dec 15, 2008 at 09:08:34AM -0600, Mike Mestnik scratched on the wall:
>   The locking is well documented, but it's not vary clear that the
> BEGIN command can be used to acquire locks.  The relation between the
> different locking stats and parameters to the BEGIN command could be
> spelled out.

  You might have missed this.  Assuming you understand the locking
  system, it does a pretty good job at filling in the details on BEGIN.

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

Transactions can be deferred, immediate, or exclusive. The default
transaction behavior is deferred. Deferred means that no locks are
acquired on the database until the database is first accessed. Thus
with a deferred transaction, the BEGIN statement itself does nothing.
Locks are not acquired until the first read or write operation. The
first read operation against a database creates a SHARED lock and the
first write operation creates a RESERVED lock. Because the acquisition
of locks is deferred until they are needed, it is possible that another
thread or process could create a separate transaction and write to the
database after the BEGIN on the current thread has executed. If the
transaction is immediate, then RESERVED locks are acquired on all
databases as soon as the BEGIN command is executed, without waiting for
the database to be used. After a BEGIN IMMEDIATE, you are guaranteed
that no other thread or process will be able to write to the database
or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can
continue to read from the database, however. An exclusive transaction
causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN
EXCLUSIVE, you are guaranteed that no other thread or process will be
able to read or write the database until the transaction is complete. 



-- 
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 causing exponentially degraded peformance with multiple processes

[Samuel Neff]

Yes, for write transactions, begin immediate will cause the second write
transaction to wait on the first.  However, the reads are done without an
explicity transaction so the reads should still proceed while the writes
have a reserved lock in place, right?

And even if there is no concurrency, at worst the 4 process test should take
just slightly more than 4 times longer than one process, or 2 seconds.  Not
exponentially longer, 10, 20, 45 seconds, which is what I'm seeing.  right?

Thanks,

Sam


On Fri, May 16, 2008 at 9:59 AM, John Stanton <[EMAIL PROTECTED]> wrote:

> By using BEGIN IMMEDIATE you lose any chance of concurrency.
>
> Samuel Neff wrote:
> > We're running into a lot of very slow queries and db locks when running
> with
> > multiple processes accessing the same database.  As a test we created a
> > small application that has only two threads and a small single table
> > database.
> >
> > RunRead reads all data from the one table (100 records).
> >
> > RunWrite updates all the records in the table (no add/delete, just
> update).
> >
> > When run by itself with just one process, each read/write concurrent
> > operation runs in 500ms.  It's synchronized to start both read/write at
> the
> > same time and then wait for each to finish before starting the next test,
> > and then loops.  It's pretty consistent around 500ms.
> >
> > When I run the same app multiple times (multiple concurrent processes),
> the
> > operations degrade very quickly.  It starts off taking 1-5 seconds for
> each
> > read/write concurrent operation but after a few minutes it often takes
> 20-30
> > seconds for a a single operation and sometimes 45 seconds.
> >
> > The transactions are all BEGIN IMMEDIATE and the noticable time taken is
> > during COMMIT.  The reads are not run within a transaction.
> >
> > I'm using sqlite 3.5.9 in SQLite.NET.  Full C# test code follows.
> >
> > Is there something I'm doing wrong that is causing this lock contention?
>  Is
> > there anything I can do to improve performance in a multi-process
> > application?
> >
> > Thanks,
> >
> > Sam
> >
> >
> >
> >
> > using System;
> > using System.Data;
> > using System.Data.SQLite;
> > using System.Diagnostics;
> > using System.IO;
> > using System.Threading;
> >
> > namespace test
> > {
> >   public class DbLockTest
> >   {
> > private static readonly Random _random = new Random();
> >
> > private readonly ManualResetEvent _start = new
> ManualResetEvent(false);
> > private readonly ManualResetEvent _readDone = new
> > ManualResetEvent(false);
> > private readonly ManualResetEvent _writeDone = new
> > ManualResetEvent(false);
> > private Stopwatch _timer;
> >
> > public static void Run()
> > {
> >   if (!File.Exists("DbLockTest.dat"))
> >   {
> > using (SQLiteConnection cnn = CreateConnection())
> > {
> >   using (SQLiteTransaction trans = cnn.BeginTransaction())
> >   {
> > using (SQLiteCommand cmd = cnn.CreateCommand())
> > {
> >   cmd.CommandText = "CREATE TABLE Data (id INTEGER PRIMARY
> KEY
> > AUTOINCREMENT, text TEXT);";
> >   cmd.ExecuteNonQuery();
> > }
> >
> > for (int i = 0; i < 100; i++)
> > {
> >   using (SQLiteCommand cmd = cnn.CreateCommand())
> >   {
> > cmd.CommandText = "INSERT INTO Data (text) VALUES
> (@text);";
> > cmd.Parameters.AddWithValue("@text", new string((char)(65
> +
> > i), i * 100));
> > cmd.ExecuteNonQuery();
> >   }
> > }
> > trans.Commit();
> >   }
> > }
> >   }
> >
> >   for (int i = 0; i < 50; i++)
> >   {
> > new DbLockTest().RunImpl();
> > Thread.Sleep(1);
> > Console.WriteLine();
> > Console.WriteLine();
> > Console.WriteLine();
> >   }
> >
> >   Console.WriteLine("Done.  Hit any key.");
> >   Console.ReadKey();
> > }
> >
> > public void RunImpl()
> > {
> >   _timer = Stopwatch.StartNew();
> >
> >   Console.WriteLine("{0:0,000} - MAIN  - Queuing threads",
> > _timer.ElapsedMilliseconds);
> >
> >   ThreadPool.QueueUserWorkItem(RunRead, _random.Next(15));
> >   ThreadPool.QueueUserWorkItem(RunWrite, _random.Next(15));
> >
> >   Thread.Sleep(100);
> >
> >   Console.WriteLine("{0:0,000} - MAIN  - Signaling threads",
> > _timer.ElapsedMilliseconds);
> >
> >   _start.Set();
> >
> >   _readDone.WaitOne();
> >   Console.WriteLine("{0:0,000} - MAIN  - Read done received",
> > _timer.ElapsedMilliseconds);
> >   _writeDone.WaitOne();
> >   Console.WriteLine("{0:0,000} - MAIN  - Write done received",
> > _timer.ElapsedMilliseconds);
> > }
> >
> > private void RunRead(object state)
> > {
> >   try
> >   {
> > Console.WriteLine("{0:0,000} - READ  - Waiting for signal",
> > _timer.ElapsedMilliseconds);
> >

Re: [sqlite] Locking causing exponentially degraded peformance with multiple processes

[John Stanton]

By using BEGIN IMMEDIATE you lose any chance of concurrency.

Samuel Neff wrote:
> We're running into a lot of very slow queries and db locks when running with
> multiple processes accessing the same database.  As a test we created a
> small application that has only two threads and a small single table
> database.
> 
> RunRead reads all data from the one table (100 records).
> 
> RunWrite updates all the records in the table (no add/delete, just update).
> 
> When run by itself with just one process, each read/write concurrent
> operation runs in 500ms.  It's synchronized to start both read/write at the
> same time and then wait for each to finish before starting the next test,
> and then loops.  It's pretty consistent around 500ms.
> 
> When I run the same app multiple times (multiple concurrent processes), the
> operations degrade very quickly.  It starts off taking 1-5 seconds for each
> read/write concurrent operation but after a few minutes it often takes 20-30
> seconds for a a single operation and sometimes 45 seconds.
> 
> The transactions are all BEGIN IMMEDIATE and the noticable time taken is
> during COMMIT.  The reads are not run within a transaction.
> 
> I'm using sqlite 3.5.9 in SQLite.NET.  Full C# test code follows.
> 
> Is there something I'm doing wrong that is causing this lock contention?  Is
> there anything I can do to improve performance in a multi-process
> application?
> 
> Thanks,
> 
> Sam
> 
> 
> 
> 
> using System;
> using System.Data;
> using System.Data.SQLite;
> using System.Diagnostics;
> using System.IO;
> using System.Threading;
> 
> namespace test
> {
>   public class DbLockTest
>   {
> private static readonly Random _random = new Random();
> 
> private readonly ManualResetEvent _start = new ManualResetEvent(false);
> private readonly ManualResetEvent _readDone = new
> ManualResetEvent(false);
> private readonly ManualResetEvent _writeDone = new
> ManualResetEvent(false);
> private Stopwatch _timer;
> 
> public static void Run()
> {
>   if (!File.Exists("DbLockTest.dat"))
>   {
> using (SQLiteConnection cnn = CreateConnection())
> {
>   using (SQLiteTransaction trans = cnn.BeginTransaction())
>   {
> using (SQLiteCommand cmd = cnn.CreateCommand())
> {
>   cmd.CommandText = "CREATE TABLE Data (id INTEGER PRIMARY KEY
> AUTOINCREMENT, text TEXT);";
>   cmd.ExecuteNonQuery();
> }
> 
> for (int i = 0; i < 100; i++)
> {
>   using (SQLiteCommand cmd = cnn.CreateCommand())
>   {
> cmd.CommandText = "INSERT INTO Data (text) VALUES (@text);";
> cmd.Parameters.AddWithValue("@text", new string((char)(65 +
> i), i * 100));
> cmd.ExecuteNonQuery();
>   }
> }
> trans.Commit();
>   }
> }
>   }
> 
>   for (int i = 0; i < 50; i++)
>   {
> new DbLockTest().RunImpl();
> Thread.Sleep(1);
> Console.WriteLine();
> Console.WriteLine();
> Console.WriteLine();
>   }
> 
>   Console.WriteLine("Done.  Hit any key.");
>   Console.ReadKey();
> }
> 
> public void RunImpl()
> {
>   _timer = Stopwatch.StartNew();
> 
>   Console.WriteLine("{0:0,000} - MAIN  - Queuing threads",
> _timer.ElapsedMilliseconds);
> 
>   ThreadPool.QueueUserWorkItem(RunRead, _random.Next(15));
>   ThreadPool.QueueUserWorkItem(RunWrite, _random.Next(15));
> 
>   Thread.Sleep(100);
> 
>   Console.WriteLine("{0:0,000} - MAIN  - Signaling threads",
> _timer.ElapsedMilliseconds);
> 
>   _start.Set();
> 
>   _readDone.WaitOne();
>   Console.WriteLine("{0:0,000} - MAIN  - Read done received",
> _timer.ElapsedMilliseconds);
>   _writeDone.WaitOne();
>   Console.WriteLine("{0:0,000} - MAIN  - Write done received",
> _timer.ElapsedMilliseconds);
> }
> 
> private void RunRead(object state)
> {
>   try
>   {
> Console.WriteLine("{0:0,000} - READ  - Waiting for signal",
> _timer.ElapsedMilliseconds);
> 
> _start.WaitOne();
> /*
> int wait = (int) state;
> Console.WriteLine("{0:0,000} - READ  - Sleeping {1} ms",
> _timer.ElapsedMilliseconds, wait);
> Thread.Sleep(wait);
> */
> IDataReader reader;
> 
> Console.WriteLine("{0:0,000} - READ  - Opening connection",
> _timer.ElapsedMilliseconds);
> 
> SQLiteConnection cnn = CreateConnection();
> using(SQLiteCommand cmd = cnn.CreateCommand())
> {
>   cmd.CommandText = "SELECT * FROM Data";
>   Console.WriteLine("{0:0,000} - READ  - Getting reader",
> _timer.ElapsedMilliseconds);
>   reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
> }
> 
> Console.WriteLine("{0:0,000} - READ  - Looping through data",
> _timer.ElapsedMilliseconds);
> int 

Re: [sqlite] locking problem

[John Stanton]

Do you have posix-locks enabled with your glusterfs?  If you have use 
regular Sqlite locking logic and hope that the locks work properly.

Lanyi Zoltan wrote:
> Hello!
> 
> I have shared disc system with glusterfs.
> When i read or write on this disc system then i get error: database is locked
> But i not get error if i'd like read or write another program. (php write to 
> text file, etc.)
> 
> I have sqlite3.
> 
> Somebody help me?
> 
> Thanks,
> Zoltan
> 
> 
> A szexjátékoktól a routergyorsításig – a legolvasottabb tartalmak!
> http://ad.adverticum.net/b/cl,1,6022,247493,293836/click.prm
> 
>  
> 
> ___
> 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 queries

[Trevor Talbot]

On 8/23/07, Pavan <[EMAIL PROTECTED]> wrote:

> As per my understanding I see that SQLite supports only database locking, as
> opposed to table/row locking. Does anyone know if you can read from a locked
> database ? (i.e. if one application has locked the database for writing,
> then can another application read/query it whilst its locked?

No, however the blocking time for readers is made as short as
possible.  See http://sqlite.org/lockingv3.html for the details.

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

Re: [sqlite] locking problem under windows

[Trevor Talbot]

On 12/11/06, "LEGRAND, Véronique" <[EMAIL PROTECTED]> wrote:


I found in the sqlite3 documentation that there were locking problems when
several processes running on different machines where trying to access an
sqlite3 database located on an NFS filesystem and that this problem could be
solved by running the "nfslockd" daemon.

As I encounter the same problem on windows NTFS filesystem, I was woundering
if there was an equivalent of "nfslockd" under windows?


This applies to NFS servers, to supply locking semantics across the
network.  NTFS is only relevant for the local machine only, for which
locking works fine.  If you're exporting things via CIFS/SMB shares
from a Windows server, locking is handled.  If you're doing it from
samba, locking may or may not work.

What is your setup?

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

Re: [sqlite] Locking problems

[Jay Sprenkle]

On 9/23/06, Martin Alfredsson <[EMAIL PROTECTED]> wrote:

 >
 > Windows XP, SQLite 3.3.4.
 >
 > /Martin
 > ma1999ATjmaDOTse


Martin, did anyone mention virus scanners can lock your database file?
Do you have any other processes that might be reading the database file?

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

Re: [sqlite] Locking problems

[He Shiming]

Hi !

I'm getting a lots of "database is locked" (code:5).

My app keeps giving up on one machine, it might be that
there is a network problem but I not 100% sure.

Anyway, when the app hangs all the other machines
including the machine where the database file is get
the "database is locked" (code:5) error.
To get rid of the error I have to close my app on all
machines (five total) and sometimes reboot the machine
with the database. As far as I can tell there are no
processes still running.

The database is as far as I know not corrupted and seems
ok after "restarting the network".

Why do I get "database is locked" (code:5) and what do I
have to do to avoid it ?
I can try to ensure that there is a try/catch block but
I'm unsure how to unlock the database, Is it enough to
do a sqlite3_close() or do I need to ROLLBACK TRANSACTION
and sqlite3_finalize() before I sqlite3_close() ?

Windows XP, SQLite 3.3.4.

/Martin
ma1999ATjmaDOTse



Usually, the SQLITE_LOCKED means that you tried to prepare or execute a new 
SQL statement without resetting or finalizing a previous one on a single 
connection. The statements should be prepared and executed this way:

sqlite3_prepare
sqlite3_bind_*
sqlite3_step
// more sqlite3_steps if there are more statements
sqlite3_finalize

You can't put another sqlite3_prepare in the middle. If you did, you'll get 
the database is locked error. And if any error encountered during the 
process, you should call sqlite3_reset to reset the statement.


I suggest that you check your code very carefully to see if any statements 
are not finalized or resetted.


Best regards,
He Shiming 



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

Re: [sqlite] Locking

[Dennis Jenkins]

RohitPatel wrote:
> FoxPro supports row level locking. Ofcourse FoxPro creates one file for each
> table. There must be some way to implement row level locking. Probable by
> locking region in a file or somehow.
>
> Just thinking curiously...how MS could have implemented row level locking in
> FoxPro.
>   

FoxPro (DBase-III file structures) is a piece of shit that corrupts
all the time.  NEVER run FoxPro on a novell network with Win95 clients
(ok, so this was last decade).  FoxPro does region locking and has NO
JOURNAL.  "Rollbacks" are done by the client by replacing the changed
rows.  If the client crashes with a table (or table set) partially
modified, then the table is left inconsistent.  FoxPro is not ACID.

At my previous employer I wrote some C code to repair broken foxpro
tables.  (I  myself did not write foxpro code).  Our main database at
that time was 5 large tables, each about 256M in size.  All sitting on a
single Novell share.  Every few days a win95 or win98 client (100
clients, all using IPX, novell v3 on HUBS (not switches)) would send a
junk packet to the server. (or it sent a good packet that arrived
mangled).  From my analysis of the corrupted database file, it seems
that the client intended to send a "seek (somewhere far down in the
file)" and then "write".  But what actually happened is that the Novell
server seeked to file offset 0 (btw, the metadata for the table) and
dutifully wrote out the data record.  So instead of the DBF (foxpro
table) file having the proper header that defines the record layout for
the table, it contained junk.  The FoxPro guys had no way to fix this
except to try to restore from a backup and re-run tens of thousands of
"transactions".  So one day this happened and the CIO was freaking out. 
I pulled up a file format spec for the DBF file and fixed it using a hex
editor.  Later that day I wrote a tool to repair the damaged header.  A
fun hack, but a horrible situation and horrible technology.

The moral of the story is that you should never trust a database
system that does region lock of flat files sitting on a server IF the
server will release the lock when the client dies AND the client does
not leave behind some sort of useful journal.  Maybe even this statement
is too liberal.  I'm sure that someone in this group can make a better
argument for never using region locking.

Moral #2 is that creative use of hex editors in front of the CIO can
help your career.  Unless he's a total spaz and doesn't understand what
you are doing.

Re: [sqlite] Locking

[RohitPatel9999]

FoxPro supports row level locking. Ofcourse FoxPro creates one file for each
table. There must be some way to implement row level locking. Probable by
locking region in a file or somehow.

Just thinking curiously...how MS could have implemented row level locking in
FoxPro.

Rohit

--
View this message in context: 
http://www.nabble.com/Locking-t799702.html#a4899211
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] Locking with auto-commit disabled

[Jay Sprenkle]

> Hello,
> I have a question regarding sqlite's behavior when auto commit is disabled.
>
> I have two conflicting transactions, A & B, where A is started first.
> B starts by executing some SELECT queries (which all succeed), then on
> the first UPDATE I get SQLITE_BUSY (as A is holding a conflicting
> lock). I was under the assumption that
> repeating the UPDATE until A releases its lock is enough, but what
> actually happens when I attempt this is that I get SQLITE_BUSY all the
> way through, even after A finishes.
>
> I came to the conclusion that I should restart the whole transaction
> instead of trying to repeat the failed UPDATE, but I would really like
> to avoid that (it's not feasible in the application). I'd rather use
> any means of declaring that a transaction requires an exclusive lock
> before it starts (perhaps by issuing an artificial UPDATE as the first
> query, although it's ugly...I wonder if sqlite provides a cleaner
> solution), so that I know if it's going to get blocked as early as
> possible.

try  BEGIN IMMEDIATE

http://sqlite.org/lang_transaction.html

RE: [sqlite] Locking

[Dinsmore, Jeff]

Dan,

One of the techniques I use is to maintain small tables for the writer
and then periocically flush them to reader tables. The readers only have
to contend with the writer during the flush. When the flush is wrapped
in a transaction, it goes pretty quick.

Good luck!

Jeff Dinsmore
Interfaces
Ridgeview Medical Center

-Original Message-
From: Dan Petitt [mailto:[EMAIL PROTECTED] 
Sent: Saturday, December 24, 2005 11:37 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Locking

> Isolation in SQLite is SERIALIZABLE.  Note that SERIALIZABLE implies 
> that locking can be no more fine-grained than table-level.
> You can obtain table-level locking in SQLite now.  Just put each table

> in a separate database file and ATTACH as many tables to your 
> connection as you require.
Yes, I did think of that, but it's a bit messy and things like
relationships no longer work. I have a question on that also, would
attaching databases make queries quite a bit slower? Does SQLite
maintain a cache of connections for each of these 'ATTACH'es, or on each
query, does it have to make a connection and retrieve info then close it
again? That would be quite an overhead would it not?

> Beginning with version 3.3.0, you will be able to configure SQLite so 
> that multiple connections running in the same thread will be able to 
> select READ UNCOMMITED isolation relative to one another.
This sounds really interesting, I think it would help some of our tasks
but we do have multiple threads accessing the database abstraction layer
so those areas wouldn't be able to use this which is a shame ... I would
interested if improvements in concurrency is an ongoing thing with more
and more support being added as versions get released?

Maybe we could assist development in that area possibly if required, but
as it may be a core area you would rather control this part of
development yourself. What are your thoughts?

Thanks for your answers and I must say thanks a lot for your hard work
in the development in SQLite ... I have done *a lot* of investigation in
databases for my testing and there is a lot of *rubbish* out there,
there is a lot of *expensive* solutions, and a lot of *slow* solutions,
SQLite is by far one of the quickest, easiest to use and integrate,
excellently documented with good user support (through these lists),
small/light, and its *free*!!

Well done and Merry Christmas to you.




Ridgeview Medical Center Confidentiality Notice: This email message, including 
any attachments, is for the sole use of the intended recipient(s) and may 
contain confidential and privileged information. Any unauthorized review, use, 
disclosure or distribution is prohibited. If you are not the intended 
recipient, please contact the sender by reply email and destroy all copies of 
the original message.

RE: [sqlite] Locking

[Dan Petitt]

> Isolation in SQLite is SERIALIZABLE.  Note that SERIALIZABLE
> implies that locking can be no more fine-grained than table-level.
> You can obtain table-level locking in SQLite now.  Just put each
> table in a separate database file and ATTACH as many tables to
> your connection as you require. 
Yes, I did think of that, but it's a bit messy and things like relationships
no longer work. I have a question on that also, would attaching databases
make queries quite a bit slower? Does SQLite maintain a cache of connections
for each of these 'ATTACH'es, or on each query, does it have to make a
connection and retrieve info then close it again? That would be quite an
overhead would it not?

> Beginning with version 3.3.0, you will be able to configure SQLite
> so that multiple connections running in the same thread will be
> able to select READ UNCOMMITED isolation relative to one another.
This sounds really interesting, I think it would help some of our tasks but
we do have multiple threads accessing the database abstraction layer so
those areas wouldn't be able to use this which is a shame ... I would
interested if improvements in concurrency is an ongoing thing with more and
more support being added as versions get released?

Maybe we could assist development in that area possibly if required, but as
it may be a core area you would rather control this part of development
yourself. What are your thoughts?

Thanks for your answers and I must say thanks a lot for your hard work in
the development in SQLite ... I have done *a lot* of investigation in
databases for my testing and there is a lot of *rubbish* out there, there is
a lot of *expensive* solutions, and a lot of *slow* solutions, SQLite is by
far one of the quickest, easiest to use and integrate, excellently
documented with good user support (through these lists), small/light, and
its *free*!!

Well done and Merry Christmas to you.

RE: [sqlite] Locking

[Dan Petitt]

> Does your flat file support ACID transactions? That´s the killer feature
fo
> my app. I want to store financial transactions and I don´t trust normal
flat
> files.
No it isnt acid, its not got critical information in it but it does need
very fast read access and write access that doesn’t block reads.

But this is one of the reasons why we are investigating SQLite, we want a
bit more resiliant data storage but also the flexibility that a quick query
engine will give us ... Searching for a specific word in all of the records
(300k) isnt very quick but with SQLite would be much quicker and more
flexible.

Re: [sqlite] Locking

[drh]

Joe Wilson <[EMAIL PROTECTED]> wrote:
> Does Sqlite support the ability to have several concurrent non-blocking 
> database read operations
> in a single thread? 
> 
> What about the more traditional many-threads-each-with-a-blocking-read model?
> 

Two or more connections can all read at the same time,
regardless of whether or not they are in the same thread
or even the same process.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Locking

[Joe Wilson]

Does Sqlite support the ability to have several concurrent non-blocking 
database read operations
in a single thread? 

What about the more traditional many-threads-each-with-a-blocking-read model?

--- [EMAIL PROTECTED] wrote:
> Beginning with version 3.3.0, you will be able to configure SQLite
> so that multiple connections running in the same thread will be
> able to select READ UNCOMMITED isolation relative to one another.
> Note that this will only be possible for connections in the same
> thread - it will not be possible for connections in different
> threads of the same process.  With READ UNCOMMITTED isolation,
> you can still only have a single connection writing to the database
> at a time, but writers will not block readers nor will readers block
> writers.  This allows a much higher level of concurrency.
> 
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 





__ 
Yahoo! for Good - Make a difference this year. 
http://brand.yahoo.com/cybergivingweek2005/

Re: [sqlite] Locking

[drh]

"Dan Petitt" <[EMAIL PROTECTED]> wrote:
> Are there any plans for sqlite to support row or table level locking, or
> possibly even Multiversion Concurrency Control, MVCC, its definition being:
>  

"Axel Mammes \(gmail\)" <[EMAIL PROTECTED]> wrote:
> Does your flat file support ACID transactions?

SQLite supports ACID transactions.  The database remains consistent
and transactions are atomic and durable even under the following 
conditions:

   *  Your program segfaults in the middle of an update
   *  Your operating system crashes in the middle of an update
   *  Your computer loses power in the middle of an update

SQLite does *not* assume that sector writes on a disk drive are
atomic.  Checksums are used to detect partial sector writes in
the event of a power loss.

In summary, SQLite is very much an ACID database engine.

Isolation in SQLite is SERIALIZABLE.  Note that SERIALIZABLE
implies that locking can be no more fine-grained than table-level.
You can obtain table-level locking in SQLite now.  Just put each
table in a separate database file and ATTACH as many tables to
your connection as you require.

Beginning with version 3.3.0, you will be able to configure SQLite
so that multiple connections running in the same thread will be
able to select READ UNCOMMITED isolation relative to one another.
Note that this will only be possible for connections in the same
thread - it will not be possible for connections in different
threads of the same process.  With READ UNCOMMITTED isolation,
you can still only have a single connection writing to the database
at a time, but writers will not block readers nor will readers block
writers.  This allows a much higher level of concurrency.

--
D. Richard Hipp <[EMAIL PROTECTED]>

RE: [sqlite] Locking

[Axel Mammes \(gmail\)]

Does your flat file support ACID transactions? That´s the killer feature fo
my app. I want to store financial transactions and I don´t trust normal flat
files.

-Original Message-
From: Dan Petitt [mailto:[EMAIL PROTECTED] 
Sent: Viernes, 23 de Diciembre de 2005 08:00 p.m.
To: sqlite-users@sqlite.org
Subject: [sqlite] Locking

Are there any plans for sqlite to support row or table level locking, or
possibly even Multiversion Concurrency Control, MVCC, its definition being:
 
~~
While querying a database each transaction sees a snapshot of data (a
database version) as it was some time ago, regardless of the current state
of the underlying data. This protects the transaction from viewing
inconsistent data that could be caused by (other) concurrent transaction
updates on the same data rows, providing transaction isolation for each
database session. 
 
The main advantage to using the MVCC model of concurrency control rather
than locking is that in MVCC locks acquired for querying (reading) data do
not conflict with locks acquired for writing data, and so reading never
blocks writing and writing never blocks reading
~~
 
The reason being is that we are inserting large amounts of data into the
database but we need to read it at the same time, and we need to do this
quickly.
 
Some background info:
 
Currently our indexed flat file system is working at speeds in excess of
sqlite (or any DB we have found) but sqlite is very close, but the locking
issue effectively makes the gui stall whilst the inserts are occuring.
 
Yes they are wrapped up in transactions and we only have a couple of indexes
(there are only 5 fields anyway).
 
Also CPU seems to be very high whilst this is going on.
 
We want to use sqlite (if possible) for its flexibility in producing better
querying and results than we currently are able to.
 
Thanks for your time.
 
Dan Petitt

Re: [sqlite] locking problem (on insert inside callback)

[Rachel Willmer]

Nah, ignore that, I was talking garbage...

Using the view is a neater way of doing that select statement, but you
still need to create the temp table to avoid the locking issues...

Rachel

On 03/12/05, Rachel Willmer <[EMAIL PROTECTED]> wrote:
> (Replying to my own email so I can find this answer again via google
> in the future...)
>
> I have an even better solution...
>
> CREATE VIEW NewView AS SELECT * FROM table1 LEFT JOIN table2 on
> table1.field=table2.field WHERE table2.field is NULL;
>
> Works just fine...
>
> Cheers
> Rachel
>
> Back In Oct, I asked:
> Rachel Willmer <[EMAIL PROTECTED]> wrote:
> > I want to search two tables which should contain the same records and
> > add any that are missing from the second into the first.
> >
> > So I do
> >
> > SELECT * FROM table1 LEFT JOIN table2 on table1.field=table2.field
> > WHERE table2.field is NULL
> >
> > So far, so good, I get the records I want. Then in the callback, I try
> >
> > INSERT INTO table1 etc...
> >
> > This fails with a "database table is locked" error.
> >
> > I'm assuming that this is because I'm still in the middle of doing the
> > SELECT statement.
> >
> > So my question is this, do I have to use the callback to copy the
> > records into a temp table, and then only add them after the
> > sqlite3_exec() which calls the SELECT has returned? or is there a more
> > elegant/obvious solution?
>
> On 14/10/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> >
> > Solution 1 is to use a TEMP table:
> >
> >   CREATE TEMP TABLE diffs AS
> >  SELECT * FROM table1 LEFT JOIN table2 ;
> >   SELECT * FROM diffs; -- Insert into table1 in the callback;
> >   DROP TABLE diffs;
> >
> > Solution 2 is a dirty trick.  It works now and in all historical versions
> > of SQLite and there are no plans to change it, but there are also no
> > promises not to change it.  In solution 2, add
> >
> >   ORDER BY +table1.rowid
> >
> > to the end of your SELECT statement.  The "+" sign in front of the
> > "table1.rowid" is *essential* if this is trick is to work.
> >
> >   --
> > D. Richard Hipp <[EMAIL PROTECTED]>
>

Re: [sqlite] locking problem (on insert inside callback)

[Rachel Willmer]

(Replying to my own email so I can find this answer again via google
in the future...)

I have an even better solution...

CREATE VIEW NewView AS SELECT * FROM table1 LEFT JOIN table2 on
table1.field=table2.field WHERE table2.field is NULL;

Works just fine...

Cheers
Rachel

Back In Oct, I asked:
Rachel Willmer <[EMAIL PROTECTED]> wrote:
> I want to search two tables which should contain the same records and
> add any that are missing from the second into the first.
>
> So I do
>
> SELECT * FROM table1 LEFT JOIN table2 on table1.field=table2.field
> WHERE table2.field is NULL
>
> So far, so good, I get the records I want. Then in the callback, I try
>
> INSERT INTO table1 etc...
>
> This fails with a "database table is locked" error.
>
> I'm assuming that this is because I'm still in the middle of doing the
> SELECT statement.
>
> So my question is this, do I have to use the callback to copy the
> records into a temp table, and then only add them after the
> sqlite3_exec() which calls the SELECT has returned? or is there a more
> elegant/obvious solution?

On 14/10/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
> Solution 1 is to use a TEMP table:
>
>   CREATE TEMP TABLE diffs AS
>  SELECT * FROM table1 LEFT JOIN table2 ;
>   SELECT * FROM diffs; -- Insert into table1 in the callback;
>   DROP TABLE diffs;
>
> Solution 2 is a dirty trick.  It works now and in all historical versions
> of SQLite and there are no plans to change it, but there are also no
> promises not to change it.  In solution 2, add
>
>   ORDER BY +table1.rowid
>
> to the end of your SELECT statement.  The "+" sign in front of the
> "table1.rowid" is *essential* if this is trick is to work.
>
>   --
> D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] locking problem (on insert inside callback)

[Rachel Willmer]

> Solution 1 is to use a TEMP table:
>
>   CREATE TEMP TABLE diffs AS
>  SELECT * FROM table1 LEFT JOIN table2 ;
>   SELECT * FROM diffs; -- Insert into table1 in the callback;
>   DROP TABLE diffs;

that sounds like the answer for me
thanks
Rachel

Re: [sqlite] locking problem (on insert inside callback)

[Rachel Willmer]

> I'm not sure I understand your logic. Your left join
> indicates that there are records missing from table2,
> so I would expect that you want to insert the missing
> records into table2. Assuming that's what you meant,
>
>   insert into table2
>   select * from table1
>   where table1.field not in (select field from table2)
>
> or
>
>   insert into table2
>   select * from table1
>   where not exists
> (select field from table2 where table2.field=table1.field)
>
> The subquery in the first form is static (executed only
> once). The subquery in the second form is correlated
> (executed many times), but the second form can be
> faster in some circumstances.

I missed out a bit of the logic. I want to be able to ask the user if
they want to approve the addition of the new record, so I need to be
able to do this as two separate operations, rather than combine them
into a single sql statement.

Rachel

Re: [sqlite] locking problem (on insert inside callback)

[Kurt Welgehausen]

> SELECT * FROM table1 LEFT JOIN table2 on table1.field=table2.field
> WHERE table2.field is NULL
>
> So far, so good, I get the records I want. Then in the callback, I try
>
> INSERT INTO table1 etc...

I'm not sure I understand your logic. Your left join
indicates that there are records missing from table2,
so I would expect that you want to insert the missing
records into table2. Assuming that's what you meant,

  insert into table2
  select * from table1
  where table1.field not in (select field from table2)

or

  insert into table2
  select * from table1
  where not exists
(select field from table2 where table2.field=table1.field)

The subquery in the first form is static (executed only
once). The subquery in the second form is correlated
(executed many times), but the second form can be
faster in some circumstances.

Regards

Re: [sqlite] locking problem (on insert inside callback)

[Jay Sprenkle]

Why do it in the call back? Why not just do it in sql:

CREATE TEMP TABLE diffs AS
SELECT * FROM table1 LEFT JOIN table2 ;

insert into table1
SELECT * FROM diffs; -- Insert into table1

DROP TABLE diffs;



On 10/14/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
> Rachel Willmer <[EMAIL PROTECTED]> wrote:
> > Hi, apologies if this is a trivial question, but I'm a newbie to
> > sqlite3. (very impressed so far)
> >
> > I want to search two tables which should contain the same records and
> > add any that are missing from the second into the first.
> >
> > So I do
> >
> > SELECT * FROM table1 LEFT JOIN table2 on table1.field=table2.field
> > WHERE table2.field is NULL
> >
> > So far, so good, I get the records I want. Then in the callback, I try
> >
> > INSERT INTO table1 etc...
> >
> > This fails with a "database table is locked" error.
> >
> > I'm assuming that this is because I'm still in the middle of doing the
> > SELECT statement.
> >
> > So my question is this, do I have to use the callback to copy the
> > records into a temp table, and then only add them after the
> > sqlite3_exec() which calls the SELECT has returned? or is there a more
> > elegant/obvious solution?
> >
>
> Solution 1 is to use a TEMP table:
>
> CREATE TEMP TABLE diffs AS
> SELECT * FROM table1 LEFT JOIN table2 ;
> SELECT * FROM diffs; -- Insert into table1 in the callback;
> DROP TABLE diffs;
>
> Solution 2 is a dirty trick. It works now and in all historical versions
> of SQLite and there are no plans to change it, but there are also no
> promises not to change it. In solution 2, add
>
> ORDER BY +table1.rowid
>
> to the end of your SELECT statement. The "+" sign in front of the
> "table1.rowid" is *essential* if this is trick is to work.
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>


--
---
The Castles of Dereth Calendar: a tour of the art and architecture of
Asheron's Call
http://www.lulu.com/content/77264

Re: [sqlite] locking problem (on insert inside callback)

[drh]

Rachel Willmer <[EMAIL PROTECTED]> wrote:
> Hi, apologies if this is a trivial question, but I'm a newbie to
> sqlite3. (very impressed so far)
> 
> I want to search two tables which should contain the same records and
> add any that are missing from the second into the first.
> 
> So I do
> 
> SELECT * FROM table1 LEFT JOIN table2 on table1.field=table2.field
> WHERE table2.field is NULL
> 
> So far, so good, I get the records I want. Then in the callback, I try
> 
> INSERT INTO table1 etc...
> 
> This fails with a "database table is locked" error.
> 
> I'm assuming that this is because I'm still in the middle of doing the
> SELECT statement.
> 
> So my question is this, do I have to use the callback to copy the
> records into a temp table, and then only add them after the
> sqlite3_exec() which calls the SELECT has returned? or is there a more
> elegant/obvious solution?
> 

Solution 1 is to use a TEMP table:

  CREATE TEMP TABLE diffs AS
 SELECT * FROM table1 LEFT JOIN table2 ;
  SELECT * FROM diffs; -- Insert into table1 in the callback;
  DROP TABLE diffs;

Solution 2 is a dirty trick.  It works now and in all historical versions
of SQLite and there are no plans to change it, but there are also no
promises not to change it.  In solution 2, add

  ORDER BY +table1.rowid

to the end of your SELECT statement.  The "+" sign in front of the 
"table1.rowid" is *essential* if this is trick is to work. 

  --
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] locking and retries in a multi-reader multi-writer environment

[fsg]

Jay writes:
> I did this for my writer operations:
> 
> begin immediate;
> if busy keep retrying for a reasonable period, if still no lock then error 
> out.
> // lock is now established
> // the following should never fail because I have an exclusive lock

But you don't have an exclusive lock yet:

"After a BEGIN IMMEDIATE, you are guaranteed that no other thread or
process will be able to write to the database or do a BEGIN IMMEDIATE or
BEGIN EXCLUSIVE. Other processes can continue to read from the database,
however."

So a reader process could still BUSY out your updates, if your
memory cache fills up and has to spill to disk, but can't due to
an extant SHARED lock.  At least, according to the documentation and
my limited understanding.

I like your idea of avoiding the entire retry hassle in the
writer process by upping the transaction level to a point where
nobody could possibly conflict.  Given the large number of retries
necessary otherwise, that seems like the best option.

Felix

Re: [sqlite] locking and retries in a multi-reader multi-writer environment

[Jay Sprenkle]

On 4/28/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> Hi!  SQLite 3.2.1 on win32.
> 
> I'm having trouble wrapping my mind around where I have to be
> paranoid about locking issues.  In particular, the behavior when
> readers and writers overlap, or two writers overlap, is not 100%
> clear to me from an obsessive reading and rereading of the locking
> documentation, google, the wiki and the mailing list archives.
> So here is what I believe; if anything I say is inaccurate, I
> would have undying appreciation for a correction.

Any number of simultaneous readers at the same time.
Writing totally excludes all other operations.
I.E.You may not read using a multi-row select and update while the
select is still open.

> 1. the 'reader' operation (happens asynchronously, often)
> open db
> prepare the following sql statement
> select e.name, d.name from employee e, department d, emp_dep ed
> where e.id = ed.emp_id
> and d.id = ed.dep_id
> and e.name = '?'
> for each employee I'm interested in (1..many)
> execute
> scroll through results
> close the query
> end for
> close sql prepare
> close db

looks fine. close query (sqlite_finalize()?) should be outside the loop.

> 
> 2. the 'writer' operation (happens asynchronously, more rarely)
> open db
> prepare the following sql statements
> A: insert into employee(name, phone) values (?, ?)
> B: insert into department(name) values (?, ?)
> C: insert into emp_dep(emp_id, dep_id) values (?, ?)
> begin deferred transaction
> for each employee I'm interested in (1..many)
> execute A
> save last autoincrement
> execute B
> save last autoincrement
> execute C (using the two autoincrements)
> end for
> commit
> close sql prepares
> close db


> 
> Here is the reader operation again, this time marked up.  ALL CAPS
> means that something else could have a lock preventing the operation
> from successfully completing.
> 
> OPEN DB -- a writer could be in the middle of writing
> prepare the following sql statement -- only unsafe if schema changes
> for each employee I'm interested in (1..many)
> EXECUTE -- a writer could have jumped in
> scroll (slowly) through the results -- uninterruptible
> close the query
> end for
> close sql prepare
> close db

Your writer operation might cause BUSY to the reader operations
(if they try to read while a write is in progress)..
They should have a loop that will retry them if they get a busy return
code.

> 
> Here is the writer operation, marked up with ALL CAPS for ops
> that could fail due to some other writer or reader.
> 
> OPEN DB -- a writer could be operating
> prepare the following sql statements -- only unsafe if schema changes
> BEGIN DEFERRED TRANSACTION -- a writer or reader could cause BUSY
> for each employee I'm interested in (1..many)
> EXECUTE A -- the transaction could spill into EXCLUSIVE & BUSY
> -- thus conflicting with an extant SHARED
> SAVE LAST AUTOINCREMENT -- as above
> EXECUTE B -- as above
> SAVE LAST AUTOINCREMENT -- as above
> EXECUTE C -- as above
> end for
> COMMIT -- writer or reader could cause busy
> close sql prepares
> close db
> 
> In the comment for 'execute A' above, it seems to me that the pager could
> conceivably try to force the transaction into EXCLUSIVE mode at any time
> (dependent on memory settings etc. outside the control of the API).
> So every call to execute a SQL statement could get the BUSY treatment,
> and thus must be explicitly wrapped in retry handling code.

You might get busy in the writer operation if another reader or writer
are using the database. It should also wait on busy.

> 
> This further suggests that a naive approach to executing sql -- putting
> multiple sql statements in one string and passing it to exec -- is
> unsafe at any speed; consider
> 
> begin deferred transaction;
> update...;
> update...;
> update...;
> commit;

I think that's correct. Exec() is also deprecated.

I did this for my writer operations:

begin immediate;
if busy keep retrying for a reasonable period, if still no lock then error out.
// lock is now established
// the following should never fail because I have an exclusive lock
// unless the disk fills or something catastrophic happens!
do updates.
commit

I have very small and quick operations so delays will be minimal.
This is very much cooperative multitasking. 



-- 
---
You a Gamer? If you're near Kansas City:
Conquest 36
https://events.reddawn.net

The Castles of Dereth Calend

Re: [sqlite] locking and retries in a multi-reader multi-writer environment

[Tiago Dionizio]

On 4/28/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> Hi!  SQLite 3.2.1 on win32.
> 
> I'm having trouble wrapping my mind around where I have to be
> paranoid about locking issues.  In particular, the behavior when
> readers and writers overlap, or two writers overlap, is not 100%
> clear to me from an obsessive reading and rereading of the locking
> documentation, google, the wiki and the mailing list archives.
> So here is what I believe; if anything I say is inaccurate, I
> would have undying appreciation for a correction.

It seems you missunderstood the purpose of a DEFERRED transaction. You
should use DEFERRED transactions when you plan to READ only from the
database, or you will eventually get BUSY errors during the
transaction (which may or may not call the busy handler).

>From what i could understand (and seems to work for me) you should use
IMMEDIATE transactions when you plan to write to a database.

When there is an IMMEDIATE transaction active, you can still read from
the database from other processes (and database handles if im correct
- sqlite3_open), but if you try to start another IMMEDIATE transaction
(from other process) it will return BUSY error and will not start the
transaction. While you have control over an IMMEDIATE transaction you
can update the database at will and allow other processes to read from
the database. When you commit from an IMMEDIATE transaction it will
return BUSY untill all other active transactions are open (readers)
and other readers that are trying to start a transaction will also
return BUSY (the price to pay for concurrency).

When BUSY is returned, it will call the busy callback if it was set.

If you find this confusing or incorrect just let me know.


Tiago

Re: [sqlite] Locking Methods

[Jay Sprenkle]

> 1) You have to loop through the resultset twice,
> once to put it in memory, the other to process
> it in your application.

Yes.

> 
> 2) The sqlite3_prepare() and sqlite3_finalize()
> both should be in a loop as well right?  Since
> they can throw SQLITE_BUSY and SQLITE_SCHEMA
> themselves.  So that should be a nested loop.

I don't believe that's right. I might be wrong here but SQLITE_SCHEMA is only
thrown if people change the database schema while you're using it. I wouldn't do
that under any circumstances. BUSY just tells you someone is using it and you
should wait. I didn't believe you had to prepare the sql again unless the schema
was changed.


> So we now have about 25-30 lines of code
> including a nested loop so to step through a
> *relatively* simple resultset.

I think it should work for any result set.

> 
> PS.  What if sqlite *optionally* placed the
> resultset in memory for the user.  Then they
> would they have to worry about BUSY or SCHEMA
> errors whilst stepping through it?

I think this is over simplification. If you write an application with the busy
and schema events "hidden" what happens if the database goes busy?
Does your app just hang? or Terminate? Shouldn't you be proactive about
handling errors?

I did this by writing a c++ wrapper that loads the result into a
standard container
list. It's a perfect place to use try/catch error trapping. If the
database goes busy
I retry for a resonable period. If it stays busy I throw() an error
and give the user
an apology and an explanation.

RE: [sqlite] Locking Methods

[Thomas Briggs]

   What did your code look like before you started storing results in
memory? 

> -Original Message-
> From: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, April 20, 2005 10:06 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Locking Methods
> 
> I prepared the statement and executed it.
> Preparing doesn't use callbacks and prevents sql injection attacks.
> I used a C++ class to store the query results in an STL container.
> 
> brief C/C++ pseudocode:
> 
> string sql = "select * from blah";
> sqlite3_open();
> sqlite3_prepare( sql );
> bool Loop = true;
> int retry = 0;
> while ( loop && retry < 10 )
>   switch ( sqlite3_step() )
> {
>   // if database busy wait for a short time
>   // to see if it becomes available
>   case SQLITE_BUSY:
>   case SQLITE_LOCKED:
> retry++;
> break;
>   case SQLITE_ROW:
> // get results
> retry = 0;
> p = sqlite3_column_text();
> in_memory_list.push_back( p );
> break;
>   case SQLITE_DONE:
> Loop = false;
> break;
>   default:
> string str = "Cannot execute sql: " + sql + ", Error: " +
> sqlite3_errmsg(db);
> throw str.c_str();
> break;
> }
> // clean up when finished
> sqlite3_finalize();
> 
> //process your list here
> 
> sqlite3_close( db );
> 
> 
> On 4/20/05, Thomas Briggs <[EMAIL PROTECTED]> wrote:
> > 
> >What APIs are you guys using to retrieve results and execute the
> > subsequent updates?  Are you using prepare/step or 
> sqlite3_exec with a
> > callback to retrieve the results?  Would it be possible for 
> you to post
> > more detailed pseudo-code?  Conceptually I think we're all 
> on the same
> > page; I think what we're unclear about is exactly how 
> you're trying to
> > accomplish this.
> > 
> >Thanks
> >-Tom
> > 
> > > -Original Message-
> > > From: Ben Clewett [mailto:[EMAIL PROTECTED]
> > > Sent: Wednesday, April 20, 2005 3:32 AM
> > > To: sqlite-users@sqlite.org
> > > Subject: Re: [sqlite] Locking Methods
> > >
> > > This is exactly my problem.  My version is 3.1.6.  The error is
> > > SQLITE_LOCKED.
> > >
> > > Ben
> > >
> > > Jay Sprenkle wrote:
> > > > I had the same trouble he did. Here's what I did that 
> doesn't work:
> > > >
> > > >   select * from a into result;
> > > >foreach row in result
> > > >   ' this fails:
> > > >   update b set col = a.value;
> > > >next
> > > >
> > > > But based on what I read here it's supposed to do this.
> > > >
> > > > On 4/19/05, Gerry Blanchette <[EMAIL PROTECTED]> wrote:
> > > >
> > > >>Could you please elaborate your scenario?
> > > >>I tried a test myself but am afraid I may not have
> > > interpreted your test
> > > >>case properly.
> > > >>
> > > >>I have 2 tables, fred and bob, each with 1 rows. I
> > > select a column
> > > >>from fred and bind the value obtained from 
> sqlite3_column_int to an
> > > >>update statement that operates on bob. I loop over fred via
> > > >>sqlite3_step, where each iteration successfully updates the
> > > row in bob.
> > > >>Both tables exist in the same DB, accessed via the same 
> sqlite3 *.
> > > >>
> > > >>Have I misinterpreted your scenario somehow, as this 
> works for me?
> > > >>
> > > >>Thank you for helping clear this up for me.
> > > >>
> > > >>-- Gerry Blanchette
> > > >>
> > > >>-Original Message-
> > > >>From: Ben Clewett [mailto:[EMAIL PROTECTED]
> > > >>Sent: Monday, April 18, 2005 4:50 AM
> > > >>To: sqlite-users@sqlite.org
> > > >>Subject: [sqlite] Locking Methods
> > > >>
> > > >>
> > > >>I am experiencing problems with the locking.  Because 
> SQLite uses
> > > >>database locking this forces two major problems:
> > > >>
> > > >>- I can't read through a record set and use the data to
> > > execute updates.
> > > >>
> > > >>  For instance, some parsing exercise which cannot be
> > > completed using a
> > > >>single SQL command.  I have to store all the data locally,
> > > get to the
> > > >>end of the query, then execute and update statements.
> > > >>
> > > >>Ben Clewett.
> > > >>
> > > >>
> > > >
> > > >
> > >
> > >
> > 
> 
> 
> -- 
> ---
> You a Gamer? If you're near Kansas City:
> Conquest 36
> https://events.reddawn.net
> 
> The Castles of Dereth Calendar: a tour of the art and architecture of
> Asheron's Call
> http://www.lulu.com/content/77264
> 

Re: [sqlite] Locking Methods

[Kervin L. Pierre]

That's part of the problem I had been having...
1) You have to loop through the resultset twice,
once to put it in memory, the other to process
it in your application.
2) The sqlite3_prepare() and sqlite3_finalize()
both should be in a loop as well right?  Since
they can throw SQLITE_BUSY and SQLITE_SCHEMA
themselves.  So that should be a nested loop.
So we now have about 25-30 lines of code
including a nested loop so to step through a
*relatively* simple resultset.
I'm not complaining, just wanted to share my
observation :)
PS.  What if sqlite *optionally* placed the
resultset in memory for the user.  Then they
would they have to worry about BUSY or SCHEMA
errors whilst stepping through it?
-
Kervin
Jay Sprenkle wrote:
I prepared the statement and executed it.
Preparing doesn't use callbacks and prevents sql injection attacks.
I used a C++ class to store the query results in an STL container.
brief C/C++ pseudocode:
string sql = "select * from blah";
sqlite3_open();
sqlite3_prepare( sql );
bool Loop = true;
int retry = 0;
while ( loop && retry < 10 )
  switch ( sqlite3_step() )
{
  // if database busy wait for a short time
  // to see if it becomes available
  case SQLITE_BUSY:
  case SQLITE_LOCKED:
retry++;
break;
  case SQLITE_ROW:
// get results
retry = 0;
p = sqlite3_column_text();
in_memory_list.push_back( p );
break;
  case SQLITE_DONE:
Loop = false;
break;
  default:
string str = "Cannot execute sql: " + sql + ", Error: " +
sqlite3_errmsg(db);
throw str.c_str();
break;
}
// clean up when finished
sqlite3_finalize();
//process your list here
sqlite3_close( db );
On 4/20/05, Thomas Briggs <[EMAIL PROTECTED]> wrote:
  What APIs are you guys using to retrieve results and execute the
subsequent updates?  Are you using prepare/step or sqlite3_exec with a
callback to retrieve the results?  Would it be possible for you to post
more detailed pseudo-code?  Conceptually I think we're all on the same
page; I think what we're unclear about is exactly how you're trying to
accomplish this.
  Thanks
  -Tom

-Original Message-
From: Ben Clewett [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 20, 2005 3:32 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Locking Methods
This is exactly my problem.  My version is 3.1.6.  The error is
SQLITE_LOCKED.
Ben
Jay Sprenkle wrote:
I had the same trouble he did. Here's what I did that doesn't work:
 select * from a into result;
  foreach row in result
 ' this fails:
 update b set col = a.value;
  next
But based on what I read here it's supposed to do this.
On 4/19/05, Gerry Blanchette <[EMAIL PROTECTED]> wrote:

Could you please elaborate your scenario?
I tried a test myself but am afraid I may not have
interpreted your test
case properly.
I have 2 tables, fred and bob, each with 1 rows. I
select a column
from fred and bind the value obtained from sqlite3_column_int to an

update statement that operates on bob. I loop over fred via
sqlite3_step, where each iteration successfully updates the
row in bob.
Both tables exist in the same DB, accessed via the same sqlite3 *.
Have I misinterpreted your scenario somehow, as this works for me?
Thank you for helping clear this up for me.
-- Gerry Blanchette
-Original Message-
From: Ben Clewett [mailto:[EMAIL PROTECTED]
Sent: Monday, April 18, 2005 4:50 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Locking Methods

I am experiencing problems with the locking.  Because SQLite uses
database locking this forces two major problems:
- I can't read through a record set and use the data to
execute updates.
For instance, some parsing exercise which cannot be
completed using a
single SQL command.  I have to store all the data locally,
get to the
end of the query, then execute and update statements.
Ben Clewett.

Re: [sqlite] Locking Methods

[Jay Sprenkle]

I prepared the statement and executed it.
Preparing doesn't use callbacks and prevents sql injection attacks.
I used a C++ class to store the query results in an STL container.

brief C/C++ pseudocode:

string sql = "select * from blah";
sqlite3_open();
sqlite3_prepare( sql );
bool Loop = true;
int retry = 0;
while ( loop && retry < 10 )
  switch ( sqlite3_step() )
{
  // if database busy wait for a short time
  // to see if it becomes available
  case SQLITE_BUSY:
  case SQLITE_LOCKED:
retry++;
break;
  case SQLITE_ROW:
// get results
retry = 0;
p = sqlite3_column_text();
in_memory_list.push_back( p );
break;
  case SQLITE_DONE:
Loop = false;
break;
  default:
string str = "Cannot execute sql: " + sql + ", Error: " +
sqlite3_errmsg(db);
throw str.c_str();
break;
}
// clean up when finished
sqlite3_finalize();

//process your list here

sqlite3_close( db );


On 4/20/05, Thomas Briggs <[EMAIL PROTECTED]> wrote:
> 
>What APIs are you guys using to retrieve results and execute the
> subsequent updates?  Are you using prepare/step or sqlite3_exec with a
> callback to retrieve the results?  Would it be possible for you to post
> more detailed pseudo-code?  Conceptually I think we're all on the same
> page; I think what we're unclear about is exactly how you're trying to
> accomplish this.
> 
>Thanks
>-Tom
> 
> > -Original Message-
> > From: Ben Clewett [mailto:[EMAIL PROTECTED]
> > Sent: Wednesday, April 20, 2005 3:32 AM
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] Locking Methods
> >
> > This is exactly my problem.  My version is 3.1.6.  The error is
> > SQLITE_LOCKED.
> >
> > Ben
> >
> > Jay Sprenkle wrote:
> > > I had the same trouble he did. Here's what I did that doesn't work:
> > >
> > >   select * from a into result;
> > >foreach row in result
> > >   ' this fails:
> > >   update b set col = a.value;
> > >next
> > >
> > > But based on what I read here it's supposed to do this.
> > >
> > > On 4/19/05, Gerry Blanchette <[EMAIL PROTECTED]> wrote:
> > >
> > >>Could you please elaborate your scenario?
> > >>I tried a test myself but am afraid I may not have
> > interpreted your test
> > >>case properly.
> > >>
> > >>I have 2 tables, fred and bob, each with 1 rows. I
> > select a column
> > >>from fred and bind the value obtained from sqlite3_column_int to an
> > >>update statement that operates on bob. I loop over fred via
> > >>sqlite3_step, where each iteration successfully updates the
> > row in bob.
> > >>Both tables exist in the same DB, accessed via the same sqlite3 *.
> > >>
> > >>Have I misinterpreted your scenario somehow, as this works for me?
> > >>
> > >>Thank you for helping clear this up for me.
> > >>
> > >>-- Gerry Blanchette
> > >>
> > >>-Original Message-
> > >>From: Ben Clewett [mailto:[EMAIL PROTECTED]
> > >>Sent: Monday, April 18, 2005 4:50 AM
> > >>To: sqlite-users@sqlite.org
> > >>Subject: [sqlite] Locking Methods
> > >>
> > >>
> > >>I am experiencing problems with the locking.  Because SQLite uses
> > >>database locking this forces two major problems:
> > >>
> > >>- I can't read through a record set and use the data to
> > execute updates.
> > >>
> > >>  For instance, some parsing exercise which cannot be
> > completed using a
> > >>single SQL command.  I have to store all the data locally,
> > get to the
> > >>end of the query, then execute and update statements.
> > >>
> > >>Ben Clewett.
> > >>
> > >>
> > >
> > >
> >
> >
> 


-- 
---
You a Gamer? If you're near Kansas City:
Conquest 36
https://events.reddawn.net

The Castles of Dereth Calendar: a tour of the art and architecture of
Asheron's Call
http://www.lulu.com/content/77264

RE: [sqlite] Locking Methods

[Thomas Briggs]

   What APIs are you guys using to retrieve results and execute the
subsequent updates?  Are you using prepare/step or sqlite3_exec with a
callback to retrieve the results?  Would it be possible for you to post
more detailed pseudo-code?  Conceptually I think we're all on the same
page; I think what we're unclear about is exactly how you're trying to
accomplish this.

   Thanks
   -Tom

> -Original Message-
> From: Ben Clewett [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, April 20, 2005 3:32 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Locking Methods
> 
> This is exactly my problem.  My version is 3.1.6.  The error is 
> SQLITE_LOCKED.
> 
> Ben
> 
> Jay Sprenkle wrote:
> > I had the same trouble he did. Here's what I did that doesn't work:
> > 
> >   select * from a into result;
> >foreach row in result
> >   ' this fails:
> >   update b set col = a.value;
> >next
> > 
> > But based on what I read here it's supposed to do this.
> > 
> > On 4/19/05, Gerry Blanchette <[EMAIL PROTECTED]> wrote:
> > 
> >>Could you please elaborate your scenario?
> >>I tried a test myself but am afraid I may not have 
> interpreted your test
> >>case properly.
> >>
> >>I have 2 tables, fred and bob, each with 1 rows. I 
> select a column
> >>from fred and bind the value obtained from sqlite3_column_int to an
> >>update statement that operates on bob. I loop over fred via
> >>sqlite3_step, where each iteration successfully updates the 
> row in bob.
> >>Both tables exist in the same DB, accessed via the same sqlite3 *.
> >>
> >>Have I misinterpreted your scenario somehow, as this works for me?
> >>
> >>Thank you for helping clear this up for me.
> >>
> >>-- Gerry Blanchette
> >>
> >>-Original Message-
> >>From: Ben Clewett [mailto:[EMAIL PROTECTED]
> >>Sent: Monday, April 18, 2005 4:50 AM
> >>To: sqlite-users@sqlite.org
> >>Subject: [sqlite] Locking Methods
> >>
> >>
> >>I am experiencing problems with the locking.  Because SQLite uses
> >>database locking this forces two major problems:
> >>
> >>- I can't read through a record set and use the data to 
> execute updates.
> >>
> >>  For instance, some parsing exercise which cannot be 
> completed using a
> >>single SQL command.  I have to store all the data locally, 
> get to the
> >>end of the query, then execute and update statements.
> >>
> >>Ben Clewett.
> >>
> >>
> > 
> > 
> 
> 

Re: [sqlite] Locking Methods

[Jay Sprenkle]

With the current design you have to save your query results to memory
then do your
updates from that list.

On 4/20/05, Ben Clewett <[EMAIL PROTECTED]> wrote:
> This is exactly my problem.  My version is 3.1.6.  The error is
> SQLITE_LOCKED.
> 
> Ben
> 
> Jay Sprenkle wrote:
> > I had the same trouble he did. Here's what I did that doesn't work:
> >
> >   select * from a into result;
> >foreach row in result
> >   ' this fails:
> >   update b set col = a.value;
> >next
> >
> > But based on what I read here it's supposed to do this.
> >
> > On 4/19/05, Gerry Blanchette <[EMAIL PROTECTED]> wrote:
> >
> >>Could you please elaborate your scenario?
> >>I tried a test myself but am afraid I may not have interpreted your test
> >>case properly.
> >>
> >>I have 2 tables, fred and bob, each with 1 rows. I select a column
> >>from fred and bind the value obtained from sqlite3_column_int to an
> >>update statement that operates on bob. I loop over fred via
> >>sqlite3_step, where each iteration successfully updates the row in bob.
> >>Both tables exist in the same DB, accessed via the same sqlite3 *.
> >>
> >>Have I misinterpreted your scenario somehow, as this works for me?
> >>
> >>Thank you for helping clear this up for me.
> >>
> >>-- Gerry Blanchette
> >>
> >>-Original Message-
> >>From: Ben Clewett [mailto:[EMAIL PROTECTED]
> >>Sent: Monday, April 18, 2005 4:50 AM
> >>To: sqlite-users@sqlite.org
> >>Subject: [sqlite] Locking Methods
> >>
> >>
> >>I am experiencing problems with the locking.  Because SQLite uses
> >>database locking this forces two major problems:
> >>
> >>- I can't read through a record set and use the data to execute updates.
> >>
> >>  For instance, some parsing exercise which cannot be completed using a
> >>single SQL command.  I have to store all the data locally, get to the
> >>end of the query, then execute and update statements.
> >>
> >>Ben Clewett.
> >>
> >>
> >
> >
> 
> 


-- 
---
You a Gamer? If you're near Kansas City:
Conquest 36
https://events.reddawn.net

The Castles of Dereth Calendar: a tour of the art and architecture of
Asheron's Call
http://www.lulu.com/content/77264