Re: [sqlite] 3.29.0 .recover command

[Keith Medcalf]

On Tuesday, 6 August, 2019 04:27, Olivier Mascia  inquired:

>On one database instance, a .dump command gives me (among many other
>lines) things like:
>
>INSERT INTO STATISTICS VALUES(11237795927160,11868);
>
>while the output of .recover command gives me things this way:
>
>INSERT INTO 'STATISTICS'('_rowid_', STATDATE, DISKUSED) VALUES(
>1, 11237795927160, 11868 );
>
>I'm wondering why these differences in the way to construct the
>instructions to rebuild a sound database instance. What are the
>(probably rightful) motivations?

>1) Why 'STATISTICS' (and not STATISTICS as in .dump command)? If
>escaping wanted, why not double quotes instead of single quotes?

This is a good question and the single quotes are bad syntax (or should be).  
Identifier quoting should be using double-quotes.  As to why identifier quotes 
are included when not required, this is a matter of taste.  Some people love 
quotes.  Some people use them only when necessary.  Others deliberately choose 
identifiers so quotes are never required.  I happen to fall into the latter 
camp.

>2) Why do the insert statement prefer to name and repeat, ad nausea,
>the column names on each insert when, apparently, the shortcut syntax
>capitalizing on the known column order in the schema might seem much
>less verbose?

In this particular case it is to preserve the _rowid_.  The .recover command is 
designed to "recover" the database, not merely ".dump" the data.  Therefore, if 
one wants to preserve the internal _rowid_ (because there is no explicitly 
named INTEGER PRIMARY KEY for a rowid table, for example), the only way to do 
so is to provide the list of attributes for which values are being provided.

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





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

Re: [sqlite] 3.29.0 .recover command

[Simon Slavin]

On 6 Aug 2019, at 7:51pm, Dan Kennedy  quoted:

>> INSERT INTO 'STATISTICS'('_rowid_', STATDATE, DISKUSED) VALUES( 1, 
>> 11237795927160, 11868 );

Quoting these entity names using apostrophes looks wrong to me.  It may work 
but someone might read it, know it's official output from a program written by 
the SQLite dev team, and write a ton of software which does likewise.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.29.0 .recover command

[Dan Kennedy]

On 6/8/62 17:26, Olivier Mascia wrote:

On one database instance, a .dump command gives me (among many other lines) 
things like:

 INSERT INTO STATISTICS VALUES(11237795927160,11868);

while the output of .recover command gives me things this way:

 INSERT INTO 'STATISTICS'('_rowid_', STATDATE, DISKUSED) VALUES( 1, 
11237795927160, 11868 );

I'm wondering why these differences in the way to construct the instructions to 
rebuild a sound database instance. What are the (probably rightful) motivations?

1) Why 'STATISTICS' (and not STATISTICS as in .dump command)? If escaping 
wanted, why not double quotes instead of single quotes?
2) Why do the insert statement prefer to name and repeat, ad nausea, the column 
names on each insert when, apparently, the shortcut syntax capitalizing on the 
known column order in the schema might seem much less verbose?

On the real DB I quickly tested .recover on (with no reason, I have nothing to 
recover, just testing the feature) I had an issue while rebuilding a new DB 
from the script made by .recover. I got foreign key constraint failures (which 
I have not yet traced exactly).

sqlite> .once system.sql
sqlite> .recover

sqlite3 recover.db
sqlite> .read system.sql
Error: near line 14658: FOREIGN KEY constraint failed
Error: near line 14659: FOREIGN KEY constraint failed
Error: near line 14660: FOREIGN KEY constraint failed


Thanks for the report. Looks like the .dump command adds "PRAGMA 
foreign_keys=OFF;" to the output to avoid this. .recover now does this too:


https://sqlite.org/src/info/bfc29e62eff0ed00

Dan.




sqlite> .q

While doing the same kind of work around .dump worked nicely:

sqlite> .once systemd.sql
sqlite> .dump

sqlite3 dump.db
sqlite> .read systemd.sql
sqlite> .q

The source test db passes successfully those tests:

sqlite> pragma integrity_check;
integrity_check
ok
sqlite> pragma foreign_key_check;
sqlite> .dbconfig
enable_fkey on
 enable_trigger on
 fts3_tokenizer off
 load_extension on
   no_ckpt_on_close off
enable_qpsg off
trigger_eqp off
 reset_database off
  defensive off
writable_schema off
legacy_alter_table off
dqs_dml off
dqs_ddl off

Again, I have no recovery to attempt for now. I was just exercising the 
.recover feature for learning, using a db I'm not suspecting of anything.

—
Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten 
Grüßen,
Olivier Mascia


___
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] THREADSAFE (1, SERIALIZED) vs (2, MULTITHREAD)

[Keith Medcalf]

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


On Tuesday, 6 August, 2019 04:35, test user  
wrote:

>So in summary, there is no difference in the multi threaded
>performance that can be gained between SERIALIZED and MULTITHREADED 
>(aside from the mutex overhead)? The only difference is SERIALIZED 
>enforces correct usage at a small overhead cost?

That is my understanding, yes.  However, the cost of 
obtaining/checking/releasing a MUTEX may vary by OS implementation so the 
definition of "small" depends on the underlying implementation.

>So for example, if I had:

>- 8 cores
>- 8 threads
>- 8 db connections, 1 per thread
>- 1 database file
>- x amount of read requests per second

>If I were to load balance x requests over each of the 8 threads, all
>the reads would complete concurrently when in SERIALIZED mode, with WAL
>enabled?

Yes.  Within the abilities of the OS to concurrently perform any required I/O 
(including I/O from the cache).  That is that as far as the library is 
concerned they would all operate (compute) in parallel.  Whether or not 
something else (at the OS or hardware level) may impose overheads or 
serialization is not something that user code can guarantee.

>Assume other bottlenecks in the system are not an issue (like disk
>speed).

>Im just trying to confirm that SERIALIZED will not queue up requests
>for (1 file, multiple connections to that file, read only requests).

No, SERIALIZED serializes requests for CPU access to shared data located in 
memory to prevent concurrent access to that data by multiple threads.  Once the 
mutex is obtained any further serialization is an OS concurrency issue handled 
by the OS.

Be aware, however, that things like using SHARED_CACHE may have extra 
serialization between connections to the same shared cache to prevent 
simultaneous access to the cache data structures by different threads running 
on different connections, even though those connections do not have multiple 
simultaneous call contention on their own.

So basically, if you executed 8 SELECT statements, each on a separate 
connection, each on a separate core, with no I/O limitations (that is, the 
entire database was contained in the OS block cache), and no memory 
limitations, and each connection using its own cache per connection (no shared 
cache) you should expect the limiting factor to be CPU only.  You will note 
that in that scenario there is not really much difference between using one 
process with 8 threads and one connection per thread, one thread per core each 
executing one SELECT each, and 8 processes each having one thread executing the 
same select statements one per process where each process is being dispatched 
to each core.  Except any overhead that the OS might impose for thread/process 
handling, that is.





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

Re: [sqlite] THREADSAFE (1, SERIALIZED) vs (2, MULTITHREAD)

[Keith Medcalf]

On Tuesday, 6 August, 2019 04:35, test user  
wrote:

>When SQLITE_THREADSAFE=(1, SERIALIZED), how are the reads/writes
>queued? Is it per connection, file, or process?

>Quote: I don't know exactly what you mean by "how are read/writes
>queued".

>Quote: if you make two simultaneous calls on the same connection
>(from different threads, for example), one of them will get the mutex and
>proceed immediately, and the other one will have to wait until that mutex is
>released

>What I mean by "queue" is that if one request is waiting until a
>mutex is released, it is in an implicit queue for that resource.

>I wanted to know how this queue works:

>- Does the second request just take longer to return from the FFI
>call whilst waiting for the mutex?

>- Or does the FFI return SQLITE_BUSY and expect the caller to call
>again in the future?

Being that this is an Operating System provided MUTEX I would expect that the 
subsequent entry merely waits in the OS queue until it is able to obtain the 
mutex.  There is a #define that you can make at compile time 
(SQLITE_ENABLE_MULTITHREADED_CHECKS) that will chuck an message to the log if 
you use MULTITHREADED and then violate the rules anyway.

Without looking though the code in detail the above is my expectation, however, 
I suppose you could get an SQLITE_BUSY / SQLITE_MISUSE return if you attempt 
something that you should not be doing, like trying to do something 
simultaneously from multiple threads on the same connection.

Only one of the devs familliar with the mutex system everywhere it is used 
would know the answer to this without either (a) experimenting or (b) searching 
widely through the code looking for how each particular instance of a mutex is 
handled.

However, since the default is SERIALIZED I would expect that the SERIALIZATION 
occurs without notification to the programmer that it has been done.

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




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

Re: [sqlite] THREADSAFE (1, SERIALIZED) vs (2, MULTITHREAD)

[test user]

Quote: Please don't try to parallelize a part of your program which exists
mostly to read or write to a SQLite database.  SQLite is not a
client/server system.  Access to the database file on disk is a
bottleneck.  If you have 8 threads which spend most of their time executing
SQLite calls, all they will do is constantly block one-another.


I think this is true of writes.

But I dont think it is true of reads.


Given an immutable SQLite file (read only), this can be safely shared among
different threads and processes.

I think distributing reads among cores is in the class of problem called
"embarrassingly parallel":

https://en.wikipedia.org/wiki/Embarrassingly_parallel





Quote: Access to the database file on disk is a bottleneck.

https://www.amazon.com/Samsung-970-EVO-1TB-MZ-V7E1T0BW/dp/B07BN217QG/

This NVMe SSD disk has a read speed of 3GB/s.

If I distribute readers across 8 CPU cores so they all compute concurrently
(without blocking each other), and they all use a tiny portion of that disk
read bandwidth, surely they would be 8x faster than using a single core?


On Tue, Aug 6, 2019 at 1:42 PM Simon Slavin  wrote:

> On 6 Aug 2019, at 11:34am, test user  wrote:
>
> > - Does the second request just take longer to return from the FFI call
> whilst waiting for the mutex?
>
> Yes.  You do not need to build backoff-and-retry code into your own
> software.  SQLite does it for you.  For every database connection you open,
> set a timeout using this:
>
> 
>
> A setting of perhaps 30 seconds would be appropriate.  The SQLite API will
> use the amount of time you set to decide how long to keep retrying access
> before it gives up, assumes that the database is permanently locked, and
> returns an error code of SQLITE_BUSY.
>
> > So for example, if I had:
> >
> > - 8 cores
> > - 8 threads
> > - 8 db connections, 1 per thread
> > - 1 database file
> > - x amount of read requests per second
> >
> > If I were to load balance x requests over each of the 8 threads, all the
> reads would complete concurrently when in SERIALIZED mode, with WAL enabled?
>
> Please don't try to parallelize a part of your program which exists mostly
> to read or write to a SQLite database.  SQLite is not a client/server
> system.  Access to the database file on disk is a bottleneck.  If you have
> 8 threads which spend most of their time executing SQLite calls, all they
> will do is constantly block one-another.  You will get a far simpler
> program, far simpler to debug, if you do all your access through one thread
> using one connection.
>
> What you /might/ want to do is have one thread which reads data from the
> database and keeps an in-memory buffer full of, say, the next thousand rows
> of data.  Then other simultaneous processing threads can read their data
> directly from memory.
>
> However, once again this may not be appropriate.  You may find that your
> software executes at acceptable speed without any parallelization at all.
> I suggest you try this before devoting your time to complicated programming.
> ___
> 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] THREADSAFE (1, SERIALIZED) vs (2, MULTITHREAD)

[Olivier Mascia]

> Le 6 août 2019 à 15:34, David Raymond  a écrit :
> 
>> "But you do need WAL to achieve multiple readers concurrency..."
> 
> Nope, you can have concurrent readers with rollback journal mode. You just 
> can't have anyone writing while they're all reading.
> 
> (Or I may just be misunderstanding what you meant)

No David, you are perfectly right.  It just is that I was too concise in 
expressing my thoughts.  In my book, short of very specific specialized needs, 
the prospect of some process involving multiple concurrent readers, which tend 
to imply that there are very often one or many readers at any one time, without 
much place for a writer to intervene, if only on occasion, without disrupting 
the flow of readers, is not very welcome.  That's why SQLite makes sense for me 
(my needs), albeit in WAL mode only. I'm so bathed in WAL all day long, that I 
tend to forget the details when not using WAL.

You did well to correct my assertion which without context, is misleading, 
another word for wrong. Thanks. :)

—  
Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten 
Grüßen,
Olivier Mascia

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

Re: [sqlite] THREADSAFE (1, SERIALIZED) vs (2, MULTITHREAD)

[David Raymond]

>"But you do need WAL to achieve multiple readers concurrency..."

Nope, you can have concurrent readers with rollback journal mode. You just 
can't have anyone writing while they're all reading.

(Or I may just be misunderstanding what you meant)

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

Re: [sqlite] THREADSAFE (1, SERIALIZED) vs (2, MULTITHREAD)

[Simon Slavin]

On 6 Aug 2019, at 11:34am, test user  wrote:

> - Does the second request just take longer to return from the FFI call whilst 
> waiting for the mutex?

Yes.  You do not need to build backoff-and-retry code into your own software.  
SQLite does it for you.  For every database connection you open, set a timeout 
using this:



A setting of perhaps 30 seconds would be appropriate.  The SQLite API will use 
the amount of time you set to decide how long to keep retrying access before it 
gives up, assumes that the database is permanently locked, and returns an error 
code of SQLITE_BUSY.

> So for example, if I had:
> 
> - 8 cores
> - 8 threads
> - 8 db connections, 1 per thread
> - 1 database file
> - x amount of read requests per second
> 
> If I were to load balance x requests over each of the 8 threads, all the 
> reads would complete concurrently when in SERIALIZED mode, with WAL enabled?

Please don't try to parallelize a part of your program which exists mostly to 
read or write to a SQLite database.  SQLite is not a client/server system.  
Access to the database file on disk is a bottleneck.  If you have 8 threads 
which spend most of their time executing SQLite calls, all they will do is 
constantly block one-another.  You will get a far simpler program, far simpler 
to debug, if you do all your access through one thread using one connection.

What you /might/ want to do is have one thread which reads data from the 
database and keeps an in-memory buffer full of, say, the next thousand rows of 
data.  Then other simultaneous processing threads can read their data directly 
from memory.

However, once again this may not be appropriate.  You may find that your 
software executes at acceptable speed without any parallelization at all.  I 
suggest you try this before devoting your time to complicated programming.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.29.0 .recover command

[Olivier Mascia]

> Le 6 août 2019 à 14:18, Chris Locke  a écrit :
> 
>> I got foreign key constraint failures
> 
> I don't know why one would work and one would fail, but usually, this
> occurs when you insert a record which has foreign keys to another table,
> but that table hasn't been imported yet.  The workaround is usually to
> ensure all the 'lookup' tables are done first, so when the main record is
> inserted, the required record exists, or to turn off foreign key checks,
> and only put them into the database once all the imports have completed.

Of course :), but thanks too, Chris.  The real matter at hand is why the script 
produced by .recover (new feature of 3.29.0) differs from the one produced by 
.dump to the point that it triggers these 3 constraint failures on this 
particular db instance I happen to have tested.

I have both those scripts and will spend some time looking up where exactly it 
goes wrong and why. But I wanted to report the strange findings first.  Just in 
case this is well-known and expected in this initial implementation of the 
.recover command.  Indeed, I'm seeing multiple significant checkins about 
.recover after the 3.29.0 release, so I might first have to rebuild using head 
of trunk.

—  
Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten 
Grüßen,
Olivier Mascia


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

Re: [sqlite] THREADSAFE (1, SERIALIZED) vs (2, MULTITHREAD)

[Olivier Mascia]

> Le 6 août 2019 à 12:34, test user  a écrit :
> 
> So for example, if I had:
> 
> - 8 cores
> - 8 threads
> - 8 db connections, 1 per thread
> - 1 database file
> - x amount of read requests per second
> 
> If I were to load balance x requests over each of the 8 threads, all the
> reads would complete concurrently when in SERIALIZED mode, with WAL enabled?

Yes.

Also, if your software can guarantee, by its own design, that a db connection 
(and its dependencies, aka statements) will never ever be shared by multiple 
threads, (or other techniques like fibers in Windows for instance), you don't 
need the per connection mutex that SERIALIZED brings to you, and you can use 
MULTITHREADED.  But you can't run in SINGLETHREAD mode from a multi-threaded 
process.

> Im just trying to confirm that SERIALIZED will not queue up requests for (1
> file, multiple connections to that file, read only requests).

The SERIALIZATION in question here is a per connection (- data structures) 
matter. It isn't related to the concurrency of accesses to the db through 
distinct connections. But you do need WAL to achieve multiple readers 
concurrency, each in a BEGIN [DEFERRED] [TRANSACTION] which you will take care 
not to upgrade inadvertantly to a writer (by executing any statement which 
would imply writing to the database).  Each of the readers will see a stable 
view of the database content, as of their first read statement after BEGIN. And 
this will last until they COMMIT (or ROLLBACK as they are anyway supposed to be 
readers). If not using explicit transactions, each statement will run 
independently in an auto BEGIN [DEFERRED] / auto COMMIT transaction.

—  
Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten 
Grüßen,
Olivier Mascia


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

Re: [sqlite] 3.29.0 .recover command

[Chris Locke]

>  I got foreign key constraint failures

I don't know why one would work and one would fail, but usually, this
occurs when you insert a record which has foreign keys to another table,
but that table hasn't been imported yet.  The workaround is usually to
ensure all the 'lookup' tables are done first, so when the main record is
inserted, the required record exists, or to turn off foreign key checks,
and only put them into the database once all the imports have completed.


On Tue, Aug 6, 2019 at 11:27 AM Olivier Mascia  wrote:

> On one database instance, a .dump command gives me (among many other
> lines) things like:
>
> INSERT INTO STATISTICS VALUES(11237795927160,11868);
>
> while the output of .recover command gives me things this way:
>
> INSERT INTO 'STATISTICS'('_rowid_', STATDATE, DISKUSED) VALUES( 1,
> 11237795927160, 11868 );
>
> I'm wondering why these differences in the way to construct the
> instructions to rebuild a sound database instance. What are the (probably
> rightful) motivations?
>
> 1) Why 'STATISTICS' (and not STATISTICS as in .dump command)? If escaping
> wanted, why not double quotes instead of single quotes?
> 2) Why do the insert statement prefer to name and repeat, ad nausea, the
> column names on each insert when, apparently, the shortcut syntax
> capitalizing on the known column order in the schema might seem much less
> verbose?
>
> On the real DB I quickly tested .recover on (with no reason, I have
> nothing to recover, just testing the feature) I had an issue while
> rebuilding a new DB from the script made by .recover. I got foreign key
> constraint failures (which I have not yet traced exactly).
>
> sqlite> .once system.sql
> sqlite> .recover
>
> sqlite3 recover.db
> sqlite> .read system.sql
> Error: near line 14658: FOREIGN KEY constraint failed
> Error: near line 14659: FOREIGN KEY constraint failed
> Error: near line 14660: FOREIGN KEY constraint failed
> sqlite> .q
>
> While doing the same kind of work around .dump worked nicely:
>
> sqlite> .once systemd.sql
> sqlite> .dump
>
> sqlite3 dump.db
> sqlite> .read systemd.sql
> sqlite> .q
>
> The source test db passes successfully those tests:
>
> sqlite> pragma integrity_check;
> integrity_check
> ok
> sqlite> pragma foreign_key_check;
> sqlite> .dbconfig
>enable_fkey on
> enable_trigger on
> fts3_tokenizer off
> load_extension on
>   no_ckpt_on_close off
>enable_qpsg off
>trigger_eqp off
> reset_database off
>  defensive off
>writable_schema off
> legacy_alter_table off
>dqs_dml off
>dqs_ddl off
>
> Again, I have no recovery to attempt for now. I was just exercising the
> .recover feature for learning, using a db I'm not suspecting of anything.
>
> —
> Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten
> Grüßen,
> Olivier Mascia
>
>
> ___
> 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] Probable bug with SQLITE_DQS=0 regarding shell .schema command?

[Olivier Mascia]

> Le 6 août 2019 à 12:45, Richard Hipp  a écrit :
> 
> On 8/6/19, Olivier Mascia  wrote:
>> Using 3.29.0 with SQLITE_DQS.
> 
> Is this the problem that was fixed here:
> https://www.sqlite.org/src/timeline?c=fcd937d9786a82ef

Indeed.
—  
Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten 
Grüßen,
Olivier Mascia
https://www.integral.be


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

Re: [sqlite] Probable bug with SQLITE_DQS=0 regarding shell .schema command?

[Richard Hipp]

On 8/6/19, Olivier Mascia  wrote:
> Using 3.29.0 with SQLITE_DQS.

Is this the problem that was fixed here:
https://www.sqlite.org/src/timeline?c=fcd937d9786a82ef


-- 
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] THREADSAFE (1, SERIALIZED) vs (2, MULTITHREAD)

[test user]

Thanks,

When SQLITE_THREADSAFE=(1, SERIALIZED), how are the reads/writes
queued? Is it per connection, file, or process?


Quote: I don't know exactly what you mean by "how are read/writes queued".

Quote: if you make two simultaneous calls on the same connection (from
different threads, for example), one of them will get the mutex and proceed
immediately, and the other one will have to wait until that mutex is
released



What I mean by "queue" is that if one request is waiting until a mutex is
released, it is in an implicit queue for that resource.

I wanted to know how this queue works:

- Does the second request just take longer to return from the FFI call
whilst waiting for the mutex?

- Or does the FFI return SQLITE_BUSY and expect the caller to call again in
the future?


In other words: How should I handle this in my library that uses the FFI?



So in summary, there is no difference in the multi threaded performance
that can be gained between SERIALIZED and MULTITHREADED (aside from the
mutex overhead)? The only difference is SERIALIZED enforces correct usage
at a small overhead cost?


So for example, if I had:

- 8 cores
- 8 threads
- 8 db connections, 1 per thread
- 1 database file
- x amount of read requests per second

If I were to load balance x requests over each of the 8 threads, all the
reads would complete concurrently when in SERIALIZED mode, with WAL enabled?

Assume other bottlenecks in the system are not an issue (like disk speed).

Im just trying to confirm that SERIALIZED will not queue up requests for (1
file, multiple connections to that file, read only requests).

On Tue, Aug 6, 2019 at 1:55 AM Keith Medcalf  wrote:

>
> On Monday, 5 August, 2019 17:23, test user 
> wrote:
>
> >Whats the difference between these two options for the
> >SQLITE_THREADSAFE compile time flag?
>
> >From the docs:
>
> >(1, SERIALIZED)
> >(2, MULTITHREAD)
>
> The SQLite3 library code is not multiply re-entrant, but is only
> singly-entrant on each connection.  (It is, however, multiply entrant
> provided that those entrances each are on a different connection)  This is
> because the SQLite3 connection pointer is a pointer to a structure that
> contains information pertaining to that connection, and a statement (or
> blob handle) is a sub-construct of the parent connection from which it was
> made.  Therefore, you may only make ONE CALL at a time into the SQLite3
> library per connection (or sub-construct thereof), because those calls will
> mutate data associated with the connection.  It does not matter from whence
> that call originated (as in thread) merely that there can only be one
> active at a time per connection.  Period.
>
> The difference between SINGLETHREAD, SERIALIZED and MULTITHREAD is in how
> this is guaranteed.
>
> When the THREADSAFE parameter is set to SEARALIZED this means that the
> SQLite3 library itself will protect the connection data with a mutex to
> make sure that you do not violate this constraint by "serializing"
> simultaneous calls into the library on a single connection.  This means
> that if you make two simultaneous calls on the same connection (from
> different threads, for example), one of them will get the mutex and proceed
> immediately, and the other one will have to wait until that mutex is
> released, thus ensuring that the single-entrance requirement is met.
>
> When the THREADSAFE parameter is set to MULTITHREAD this means that the
> SQLite3 library WILL NOT protect the connection data with a mutex to
> protect you from violating this constraint, and that it is entirely and
> completely your responsibility to ensure that you do not violate the
> single-entrance (per connection) requirement.  If you do violate the single
> entrance requirement, you may corrupt the database, the library, the
> computer, the world, and cause the end of the universe.
>
> If and only if you are absolutely sure that you are complying with the
> single-entrance requirement THEN you can change the THREADSAFE mode from
> SERIALIZED to MULTITHREAD, which will save you a few nanoseconds per call
> into the library because the mutexes will no longer be checked.
>
> When the THREADSAFE is set to either SERIALIZED or MULTITHREADED the code
> to handle these mutexes is compiled into the library.  You can switch
> between these two modes at runtime.
>
> If you are only using one thread then you can set the THREADSAFE parameter
> to SINGLETHREAD which will cause the mutex code to be omitted entirely,
> saving you another nanosecond per call since you will not even need to
> "jump around" the mutex checking code.
>
> When you compile the library with THREADSAFE set to SINGLETHREAD then the
> mutex protection code IS NOT compiled into the library and therefore you
> cannot turn it on or off at runtime, since it does not exist.
>
> You are free to use as many threads as you like to call into the SQLite3
> library no matter what THREADSAFE mode is set at compile or runtime if you
> are absolutely 

[sqlite] 3.29.0 .recover command

[Olivier Mascia]

On one database instance, a .dump command gives me (among many other lines) 
things like:

INSERT INTO STATISTICS VALUES(11237795927160,11868);

while the output of .recover command gives me things this way:

INSERT INTO 'STATISTICS'('_rowid_', STATDATE, DISKUSED) VALUES( 1, 
11237795927160, 11868 );

I'm wondering why these differences in the way to construct the instructions to 
rebuild a sound database instance. What are the (probably rightful) motivations?

1) Why 'STATISTICS' (and not STATISTICS as in .dump command)? If escaping 
wanted, why not double quotes instead of single quotes?
2) Why do the insert statement prefer to name and repeat, ad nausea, the column 
names on each insert when, apparently, the shortcut syntax capitalizing on the 
known column order in the schema might seem much less verbose?

On the real DB I quickly tested .recover on (with no reason, I have nothing to 
recover, just testing the feature) I had an issue while rebuilding a new DB 
from the script made by .recover. I got foreign key constraint failures (which 
I have not yet traced exactly).

sqlite> .once system.sql
sqlite> .recover

sqlite3 recover.db
sqlite> .read system.sql
Error: near line 14658: FOREIGN KEY constraint failed
Error: near line 14659: FOREIGN KEY constraint failed
Error: near line 14660: FOREIGN KEY constraint failed
sqlite> .q

While doing the same kind of work around .dump worked nicely:

sqlite> .once systemd.sql
sqlite> .dump

sqlite3 dump.db
sqlite> .read systemd.sql
sqlite> .q

The source test db passes successfully those tests:

sqlite> pragma integrity_check;
integrity_check
ok
sqlite> pragma foreign_key_check;
sqlite> .dbconfig
   enable_fkey on
enable_trigger on
fts3_tokenizer off
load_extension on
  no_ckpt_on_close off
   enable_qpsg off
   trigger_eqp off
reset_database off
 defensive off
   writable_schema off
legacy_alter_table off
   dqs_dml off
   dqs_ddl off

Again, I have no recovery to attempt for now. I was just exercising the 
.recover feature for learning, using a db I'm not suspecting of anything.

—  
Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten 
Grüßen,
Olivier Mascia


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

Re: [sqlite] Probable bug with SQLITE_DQS=0 regarding shell .schema command?

[Olivier Mascia]

> Le 6 août 2019 à 10:27, Olivier Mascia  a écrit :
> 
> Using 3.29.0 with SQLITE_DQS.
> 
> sqlite3 test1.db "create table A(I integer);"
> sqlite3 test2.db "create table B(J integer);"
> 
> sqlite3 test1.db
> sqlite> .schema
> CREATE TABLE A(I integer);
> 
> sqlite> attach 'test2.db' as cloud;
> sqlite> .schema
> Error: no such column: cloud
> sqlite> detach cloud;
> 
> sqlite> attach 'test2.db' as 'cloud';
> sqlite> .schema
> Error: no such column: cloud
> sqlite> detach cloud;
> 
> sqlite> attach 'test2.db' as "cloud";
> sqlite> .schema
> Error: no such column: cloud
> sqlite> detach cloud;

I can confirm that this checkin (after 3.29.0 release):
https://www.sqlite.org/src/info/fcd937d9786a82ef
already fixed this issue.

—  
Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten 
Grüßen,
Olivier Mascia



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

[sqlite] Probable bug with SQLITE_DQS=0 regarding shell .schema command?

[Olivier Mascia]

Using 3.29.0 with SQLITE_DQS.

sqlite3 test1.db "create table A(I integer);"
sqlite3 test2.db "create table B(J integer);"

sqlite3 test1.db
sqlite> .schema
CREATE TABLE A(I integer);

sqlite> attach 'test2.db' as cloud;
sqlite> .schema
Error: no such column: cloud
sqlite> detach cloud;

sqlite> attach 'test2.db' as 'cloud';
sqlite> .schema
Error: no such column: cloud
sqlite> detach cloud;

sqlite> attach 'test2.db' as "cloud";
sqlite> .schema
Error: no such column: cloud
sqlite> detach cloud;

sqlite> .schema
CREATE TABLE A(I integer);
sqlite>

?

I haven't bisected but as a quick check "SQLite version 3.24.0 2018-06-04 
14:10:15" on my macOS (which obviously has not been compiled with SQLITE_DQS) 
works OK:

sqlite> .schema
CREATE TABLE A(I integer);

sqlite> attach 'test2.db' as cloud;
sqlite> .schema
CREATE TABLE A(I integer);
CREATE TABLE cloud.X(I integer primary key);
CREATE TABLE cloud.B(J integer);
sqlite> detach cloud;

sqlite> attach 'test2.db' as 'cloud';
sqlite> .schema
CREATE TABLE A(I integer);
CREATE TABLE cloud.X(I integer primary key);
CREATE TABLE cloud.B(J integer);
sqlite> detach cloud;

sqlite> attach 'test2.db' as "cloud";
sqlite> .schema
CREATE TABLE A(I integer);
CREATE TABLE cloud.X(I integer primary key);
CREATE TABLE cloud.B(J integer);
sqlite> detach cloud;

sqlite> 

I don't know yet if anything else than shell .schema command is affected. At 
least, I can run statements referencing cloud.B without issue.

BTW, just removed my -DSQLITE_DQS=0 and things back to normal with 3.29.

—  
Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten 
Grüßen,
Olivier Mascia
https://www.integral.be


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