date:20190812

> And now you have a file which can't be edited with old versions of the CLI.  
> However you cut it, you have compatibility problems.

One shouldn't do it at all. It's like trying to a edit a DOCX with Word95. It's 
not *backward* compatibility. It's not the case you mentioned before. And 
finally, when using the CLI there's no reason not to update the CLI.

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

[sqlite] DEF CON (wasL A license plate of NULL)

2019-08-12 Thread Keith Medcalf

On Monday, 12 August, 2019 11:09, Simon Slavin wrote:

>Some interesting things are emerging from this year's DEF CON. This
>one is related to an issue we've often discussed here. I hope you'll
>indulge this slightly off-charter post.
>
>https://www.iheart.com/content/2019-08-12-clever-vanity-license-plate-backfires-on-man-winds-up-with-tons-of-tickets/>

Perhaps more apropos is the following story from the Register, also originating
at DEF CON:

https://www.theregister.co.uk/2019/08/10/memory_corruption_sqlite/

Although I would point out that the root problem is that the attacker already
has access to the file in order to change it, and therefore already has
presence on the machine. This is really no different that saying that "if an
attacker has access the filesystem to replace the login program, that the login
program can be compromised". In other words, much ado about nothing. Solve
the root issue (the inappropriate granting of access by some other method) and
the issue is resolved.

This (seems to me) falls into the class of "if you have SYSTEM (root, for the
*nix crowd) authority" you can "exploit vulnerability X" to obtain SYSTEM
authority. Why on earth would you bother? Sure, you can exploit the
vulnerability but it gains you nothing that you do not already have. Perhaps I
am just lazy but I see no point in engaging in extra work for no advantage
(then again, maybe that is just the Control Systems background rearing its
head).

As a side note, if one ALREADY HAS access to a machine hosting a database, and
ALREADY HAS access to be able to make arbitrary changes to the database file,
then the same exploit can be carried out on just about ANY system running just
about ANY database imaginable .. it is trivial to create a view which replaces
a table and have that view "do things" that are other than what was intended by
the original designer, and have the fact that the table was replaced by a view
remain "hidden" from routine uses. And in any case, why bother with all the
rigamarole. You can already copy the contents of the database or make changes
directly, so why go to such great lengths to be able to achieve indirectly that
which you can already do directly? (Not to mention that there are already
capabilities to monitor for this sort of thing via the authorizer).

Granted, it is not usual to "ship around" SQLServer or DB2 databases or have
those host "application file formats" quite like it is with SQLite3 databases,
but then, files (no matter the type) originating from untrustworthy
third-parties should be, well, untrusted. The same applies to files which have
been accessed (and perhaps modified) by untrustworthy parties. The root
problem is the prior untrustworthy access -- fix that and the problem goes away.

Conversely there is a great trend these days to "execute" data -- thankfully
something which SQLite3 does not do. An application might, but that is an
application problem and not a data problem.

The only interesting thing is CVE-2015-7036, but I don't know if that was so
much an SQLite3 issue, as it was an issue in the use of the tokenizer by Apple.
In either case, Apple fixed their bugs and SQLite3 was hardened against some
inappropriate (unintended by the application developer) uses of the
fts3_tokenizer() function.

https://www.sqlite.org/releaselog/3_28_0.html
Item 10

--
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] Preserve file descriptors for journal files

2019-08-12 Thread krimskrams

Hi,


On Sun, 7 Jul 2019 05:38:30 -0400

> Have you tried
>
> PRAGMA journal_mode=WAL;
>
> Does your application have exclusive access to the database file?  If
> so, then consider:
>
> PRAGMA locking_mode=EXCLUSIVE;

both options seem to work although the second one is not an option for
us. Thanks a lot!


Kind regards,

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

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

On 12 Aug 2019, at 8:20pm, Thomas Kurz  wrote:

> c) To enable the new DATE interpreation, we do:
> CREATE TABLE TEST VERSION=3.34 --> will be stored the same way

And now you have a file which can't be edited with old versions of the CLI.  
However you cut it, you have compatibility problems.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

> A programmer uses a copy of the SQLite CLI to correct errors in a database 
> made and maintained by a production program.  This involves making a new 
> table, copying some data from the old data to the new table, deleting the old 
> table, then renaming the new table.  When the programmer is finished making 
> changes, they replace the old version of the database with the corrected 
> version.

But this ain't a problem because the default would be a compatible version.

Maybe I should explain my idea a bit more explicit, to avoid a 
misunderstanding. Let's assume preparations with 3.31 and the first real change 
(e.g. DATE) in 3.34. For simplicity, we create a table TEST without any 
declaration. Application itself uses 3.16.

a) Creating table with any version <3.31 results in: CREATE TABLE TEST

b) Creating table with 3.31+ without any explicit declaration: 
CREATE TABLE TEST --> will be stored as CREATE TABLE TEST VERSION=3.31
This should be no problem for any library below 3.31 as it should ignore the 
VERSION declaration.

Everything that is done with this table will always operate in 3.31 
compatibility mode, regardless of what library version is actually being used, 
be it an old one or a new one.

c) To enable the new DATE interpreation, we do:
CREATE TABLE TEST VERSION=3.34 --> will be stored the same way

Now we have to distinguish three cases:

i) Library versions 3.34+ can use the modified DATE datatype. Reasonably, it 
should internally be encoded as INT or FLOAT, depending on what timestamp is 
internally being used. This will be a compatibility-fallback for case iii. 
Alternatively, one could use BLOB to tell case iii not to handle this data at 
all.

ii) Versions 3.31, 3.32 and 3.33 should reject modifying such kind of table as 
(when being honest) they do not know about the feature.

iii) Versions up to and including 3.30 should basically also reject 
modifications, but they cannot do it as they don't now about the VERSION flag. 
For this case, we have stored the data either as INT/FLOAT which can be 
handled, or as BLOB which wouldn't be touched.

The important point is that we would have introduced a mechanism which should 
be future-proof, but breaking compatibility as few as possible. And a 
programmer would always have to actively enable a "feature level". If he didn't 
do that, the library would operate in compatibility mode as it is the case 
right now. Modifying tables would not be a problem either.

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

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

On 12 Aug 2019, at 6:01pm, Thomas Kurz  wrote:

>> This would break backward compatibility.  It is necessary to be sure that 
>> database files made with current versions of SQLite can be opened with old 
>> versions back to 2013.
> 
> This is what I would call "forward compatibility": You expect an old 
> application to be able to read file formats of a future version. Do you have 
> an example where there is really required?

A programmer uses a copy of the SQLite CLI to correct errors in a database made 
and maintained by a production program.  This involves making a new table, 
copying some data from the old data to the new table, deleting the old table, 
then renaming the new table.  When the programmer is finished making changes, 
they replace the old version of the database with the corrected version.

The production program, written three years ago, uses an old version of the 
SQLite library.  The editing happens on the programmer's own computer which, 
naturally, has an up-to-date copy of the SQLite CLI, with an up-to-date copy of 
the SQLite library.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] A license plate of NULL

Some interesting things are emerging from this year's DEF CON.  This one is 
related to an issue we've often discussed here.  I hope you'll indulge this 
slightly off-charter post.



"  Droogie decided to buy a vanity California license plate that simply said 
"NULL," [...]

Apparently, when they didn't have the right data for a vehicle, a privately 
operated citation processing center used the word NULL in the license plate 
field for many tickets. Since that just happens to be Droogie's license plate, 
he got all of them. "

Most people reading this will notice the error in simplification.  But it still 
indicates that the DMV needs to review its programming.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

> This would break backward compatibility.  It is necessary to be sure that 
> database files made with current versions of SQLite can be opened with old 
> versions back to 2013.

This is what I would call "forward compatibility": You expect an old 
application to be able to read file formats of a future version. Do you have an 
example where there is really required? Apart from database browsers I cannot 
think of many situations where an application has to read an arbitrary database 
created by an arbitrary application. It wouldn't know how to interpret data 
anyhow?

> > Alternatively, one could introduce a pragma statement, say PRAGMA 
> > emulation=ver, that could default to 3.30 (or whatever) now and which 
> > doesn't change at all

> This would break backward compatibility.  It is necessary to be sure that 
> database files made with current versions of SQLite can be opened with old 
> versions back to 2013.

I don't think it would break compatibility. If there's no space to include 
version information in the database file directly, it could e.g. be added to 
the table definition: CREATE TABLE whatsoever VERSION=3.30, just like the 
ENGINE keyword of MariabDB. Reading tables without a VERSION keyword are 
automatically interpreted in the version where this feature was introduced 
first.

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

Re: [sqlite] [EXTERNAL] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

2019-08-12 Thread James K. Lowden

On Mon, 12 Aug 2019 12:02:33 +0200
Kira Backes  wrote:

> I would change the error description for SQLITE_BUSY_SNAPSHOT so that
> it no longer says "read transaction" but instead says "read
> transaction or ongoing select statement". 

While I agree the documentation could be clarified, that wouldn't be a
particularly good change.  A "read transaction" is, exactly, an
"ongoing select statement".  More precisely, the *execution* of a
SELECT statement is a read transaction, whether or not preceded by
BEGIN TRANSACTION. The person reading the documentation is expected to
know that.  It's defined by SQL, and has nothing to do with the SQLite
implementation.  

I think you actually got burned by multithreading.  Let's just say
you're not the first.  ;-)  

To the extent the documentation could be made clearer, I would suggest
it focus on the interface and not the implementation.  Knowledge of
locks, while interesting, should not be required to use transactions
correctly or to understand SQLITE_BUSY_SNAPSHOT.  

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

Re: [sqlite] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

2019-08-12 Thread Jens Alfke

> On Aug 12, 2019, at 12:34 AM, Kira Backes  wrote:
> 
> Our code base does not use
> transactions at all (we have a segmented code base protected by
> mutexes for a whole section, so reads/writes do not conflict ever).

This will really hurt performance of multiple writes, since each write 
statement will be followed by an implicit commit, which requires some expensive 
disk I/O and file system flushes.

You’re also losing opportunities for parallelism by using a single connection, 
as SQLite allows reads on one connection to run concurrently with both reads 
and writes on another connection.

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

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

2019-08-12 Thread Tim Streater

On 12 Aug 2019, at 14:30, J Decker  wrote:

> On Mon, Aug 12, 2019 at 5:42 AM Simon Slavin  wrote:
>
>> On 12 Aug 2019, at 1:27pm, Tim Streater  wrote:
>>
>> > I don't expect to do that with SQL. My "seconds since the epoch" is
>> based on converting any particular time to GMT and storing that. That
>> number is then converted to a date/time with TZ info for display.

> If the timezone is stored, then the time is all UTC and easily sortable.
>  A sub-order of timeone within a sepcific time sequence ends up happening
> *shrug*

Why are you storing the timezone? You display the TZ of the user who is, later, 
viewing the data. And that user could be anywhere.

> But then, I'm assuming the time would just be ISO8601; since SQLite
> datetime functions take that as an input already.

I'm a user; I don't want my times displayed as ISO8601. That's why we have 
date/time control panels so the user gets to choose how those are displayed.


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

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

2019-08-12 Thread J Decker

On Mon, Aug 12, 2019 at 5:42 AM Simon Slavin  wrote:

> On 12 Aug 2019, at 1:27pm, Tim Streater  wrote:
>
> > I don't expect to do that with SQL. My "seconds since the epoch" is
> based on converting any particular time to GMT and storing that. That
> number is then converted to a date/time with TZ info for display.
>
>
If the timezone is stored, then the time is all UTC and easily sortable.
 A sub-order of timeone within a sepcific time sequence ends up happening
*shrug*

But then, I'm assuming the time would just be ISO8601; since SQLite
datetime functions take that as an input already.



> I'm with Tim.  Storing the time zone with the timestamp is a different
> matter.  It leads to problems with sorting and searching.  We can discuss
> it, but it doesn't belong in this thread.
> ___
> 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] dates, times and R

2019-08-12 Thread Gabor Grothendieck

Thanks for the idea but the interface already handles that and does it
without special names. The last example in my last post shows that
d was correctly typed in the output because the interface noticed that
it had the same name as an input column.  Other problems are that it
would still not handle propagation through expressions and would require
that the user use special names different than the names in the
input.

I appreciate these ideas but these or equally effective alternatives are
already implemented and it is precisely these kludges that I was
trying to avoid.  With one R statement the user can switch back ends
so unless sqlite works as smoothly as the alternative backends
a user will choose one of those if they are doing a lot of date and
datetime processing.

For many applications the other advantages
of sqlite would take precedence.
The fact that sqlirte is included right in
the R driver package is very convenient as it means there is nothing
additional to install
beyond the R driver.  (H2 is also included in the R driver but in that
case java needs to be installed.) Also the new windowing functions, CTEs
and other features are great.
Unfortunately in the widely applicable case of dates and date times
the other databases just work and additional care needs to be taken
with sqlite.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

On 12 Aug 2019, at 1:27pm, Tim Streater  wrote:

> I don't expect to do that with SQL. My "seconds since the epoch" is based on 
> converting any particular time to GMT and storing that. That number is then 
> converted to a date/time with TZ info for display.

I'm with Tim.  Storing the time zone with the timestamp is a different matter.  
It leads to problems with sorting and searching.  We can discuss it, but it 
doesn't belong in this thread.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] dates, times and R

2019-08-12 Thread Graham Holden

Monday, August 12, 2019, 1:06:00 PM, Gabor Grothendieck 
 wrote:

> The whole point of this is to make it as easy as possible for the user.
> With other backends the database handles the types but with sqlite
> the user has to get involved.

> ...

>   sqldf("select d as d__Date, d + 1 as nextDay__Date from DF", method
> = "name__class")
>   ##dnextDay
>   ## 1 2000-01-01 2000-01-02

NOTE: I don't know R, so I may be talking rubbish...

I *think* the idea of whoever suggested it was not that the *user*
should have to do things like the above, but that the INTERFACE
between R and SQLite should do it. In my simplistic view the interface
"knows" the input is a date ("as.Date(..)") and so would tack the
"...__date" hint on to the column name when talking to SQLite. Coming
back out of SQLite, the interface would recognize the "...__date" and
turn the value into an R date.

Graham Holden

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

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

2019-08-12 Thread Tim Streater

On 12 Aug 2019, at 12:41, Thomas Kurz  wrote:

> The problem is not only about storing an arbitrary integer or float number. A
> date is much more, it has timezone information with it, and I would like to
> see a DATE column handle this in a proper and well-defined way, just as a
> calendar (CalDAV) does handle it, so it would allow me to convert between e.g.
> EST and CEST or calculating time differences (e.g. working time in a company).
> I know many of this is somehow possible now as well, but as far as I know, not
> really compliant with other RDMSs.

I don't expect to do that with SQL. My "seconds since the epoch" is based on 
converting any particular time to GMT and storing that. That number is then 
converted to a date/time with TZ info for display.


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

Re: [sqlite] dates, times and R

2019-08-12 Thread Gabor Grothendieck

The whole point of this is to make it as easy as possible for the user.
With other backends the database handles the types but with sqlite
the user has to get involved.

It is not a matter of storage.  It is a matter of maintaining the type
information
on the database side and passing the type information back to R.

Perhaps an example would help.  Consider this.  Here R passes the
type information to the H2 database backend and H2 passes it back to R
so that the output is correctly typed.  This works as desired.

  library(RH2) # load H2 driver and H2 database
  library(sqldf) # load package

  DF <- data.frame(d = as.Date("2000-01-01"))  # create data.frame
with one column d

  sqldf("select d, d + 1 as nextDay from DF")
  ##dnextDay
  ## 1 2000-01-01 2000-01-02

Now if we switch from H2 to sqlite that would have not have worked.
Any of these workaournds
are currently implemented and work but are not optimal.

  library(sqldf)  # if no driver loaded it assumes sqlite

  # Alternative 1
  # method = "Date" means all returned numbers are regarded as Date

  sqldf("select d, d + 1 as nextDay from DF", method = "Date")
  ##dnextDay
  ## 1 2000-01-01 2000-01-02

 # Alternative 2
  # this is similar to what Simon suggested.
  # method = "name__class" means use the column name suffix
  # to determine the type

  sqldf("select d as d__Date, d + 1 as nextDay__Date from DF", method
= "name__class")
  ##dnextDay
  ## 1 2000-01-01 2000-01-02

 # Alternative 3
  # convert it manually.  It deduces that d is Date because an input
  # column of the same name exists as Date but that is not foolproof
  # and does not, for example, work for the nextDay column so we
  # convert it manually

  out <- sqldf("select d, d + 1 as nextDay from DF")
  out$nextDay <- as.Date(out$nextDay, origin = "1970-01-01")
  out
  ##dnextDay
  ## 1 2000-01-01 2000-01-02

On Sun, Aug 11, 2019 at 7:16 PM Keith Medcalf  wrote:
>
>
> On Sunday, 11 August, 2019 07:45, Gabor Grothendieck 
>  wrote:
>
> >R supports Date and POSIXct (date/time) classes which are represented
> >internally as days and seconds since the UNIX Epoch respectively;
> >however, due to the class it knows to display and manipulate them as
> >dates and datetimes rather than numbers.
>
> SQLite3 can store integers and doubles.  Seems to me this is quite sufficient 
> for storing a "number of seconds" since the Unix Epoch or "number of days" 
> since the Unix epoch.  What exactly is the problem?  An IEEE-754 double 
> precision floating point number is certainly capable of storing all offsets 
> from the Unix Epoch (either as a days or seconds offset) with far more 
> precision that a human is likely able to discern (or with more precision than 
> the accuracy of most atomic clocks, for that matter).
>
> >If sqldf sends a Date or POSIXct to SQLite then it is sent as a
> >number (days or seconds since the UNIX Epoch) but when it is sent
> >back it cannot know that that number is supposed to represent a
> >date or datetime.
>
> Why not?  The column type declaration in the table is merely an "arbitrary 
> string", and the returned column names from a select are merely "arbitrary 
> strings".  It seems like an "application deficiency" that it cannot set and 
> retrieve "arbitrary metadata" for its own internal use (such as providing 
> column type declarations in the CREATE TABLE or additional metadata in the 
> column name (in select statements).  Many other "applications" do so without 
> difficulty (for example the default sqlite3 wrapper in Python).  Are the R 
> programmers somehow particularly deficient in this regard?
>
> I quite often store "timestamps" using application dependent epochs and 
> offsets without difficulty (for example, the number of one-minute intervals 
> since the Unix epoch).  Given that just about every programming system and 
> language ever invented seems to store datetime data as some type of offset 
> from some epoch, and each of them different, does not seem to bother 
> interoperability in the least.  Once you know the Epoch and Offset interval, 
> conversion is rather simple arithmetic that most children learned in primary 
> school.
>
> --
> 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] Backward compatibility vs. new features (was: Re: dates, times and R)

> Since date/time is stored as an offset in some units from an epoch of some 
> type, the "datatype" declaration is nothing more than an annotation of an 
> already existing double or integer type -- and you can already annotate your 
> select column names and table attribute type declarations just fine.

The problem is not only about storing an arbitrary integer or float number. A 
date is much more, it has timezone information with it, and I would like to see 
a DATE column handle this in a proper and well-defined way, just as a calendar 
(CalDAV) does handle it, so it would allow me to convert between e.g. EST and 
CEST or calculating time differences (e.g. working time in a company). I know 
many of this is somehow possible now as well, but as far as I know, not really 
compliant with other RDMSs.

It's not only the date type itself, I miss some other useful aids as well. 
MariaDB/MySQL for example have the "DEFAULT CURRENT_TIMESTAMP ON UPDATE 
CURRENT_TIMESTAMP" feature, useful for having a "last modified" column to track 
changes. SQLite requires me to define a trigger which is quite complex as one 
has to declare each column except for the "last modified" one to avoid a 
recursive trigger.

Beyond data/time, a great improvement would be support for geodata. SQLite has 
become quite popular for GIS applications (e.g. QGIS, but also ArcGIS) using 
Spatialite and/or GeoPackage. Currently, there is no GEOMETRY data type so both 
store geometries in BLOBs ensuring proper functionality with a whole bunch of 
triggers. This leads to many problems, for example when renaming tables or 
columns. I often had inconsistencies and needed to manually adjust the 
"geometry_columns" table (which also would be obsolete then, leading to much 
cleaner database layout) to make things working again.

Please apologize: I do not want to offend anyone, it's just my opinion. I 
appreciate SQLite being a great thing and I don't know a better embedded 
database (one can forget about Firebird, etc.). But in my everyday work I 
always find some issues that could make a great thing perfect if they were 
implemented :-)

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

Re: [sqlite] SHARED lock vs READ transaction

I understand your semantic point but this helps no one. Coming from
other databases and SQL in general the term "transaction" has a very
specific meaning. So if the documentation talks about read
transactions in some places and shared locks in other places I think
these are different things.

Let's say I use a MySQL client, do not request a read transaction but
I see somewhere that the MySQL server will need an internal shared
lock to satisfy the SELECT query, what do I care? If what you say is
true then I think it would greatly help the documentation to replace
all occurrences of "read transaction" with "shared lock" and thereby
introduce ubiquitous language and reduce confusion.

mit freundlichen Grüßen,
Kira Backes

On Mon, 12 Aug 2019 at 13:30, Olivier Mascia  wrote:
>
> Could you please understand that this is only a matter of language?
>
> There is no hard thing as a read transaction. But it is commonly intuitive to 
> name a transaction as « read » as long as it did not started with write 
> intent and self-restraint itself from doing writes.
>
> --
> Best regards, Meilleures salutations, Met vriendelijke groeten,
> Olivier Mascia (from mobile device)
>
> Le 12 août 2019 à 13:19, Kira Backes  a écrit :
>
> >> There is no such thing as a "READ transaction".
> >
> > Could you please open the following google query:
> >
> > https://www.google.com/search?q=%22read+transaction%22+site%3Asqlite.org
> >
> > There are 300 mentions of "read transaction" in the documentation and 
> > commits
> ___
> 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] SHARED lock vs READ transaction

2019-08-12 Thread Olivier Mascia

Could you please understand that this is only a matter of language?

There is no hard thing as a read transaction. But it is commonly intuitive to 
name a transaction as « read » as long as it did not started with write intent 
and self-restraint itself from doing writes.

-- 
Best regards, Meilleures salutations, Met vriendelijke groeten,  
Olivier Mascia (from mobile device)

Le 12 août 2019 à 13:19, Kira Backes  a écrit :

>> There is no such thing as a "READ transaction".
> 
> Could you please open the following google query:
> 
> https://www.google.com/search?q=%22read+transaction%22+site%3Asqlite.org
> 
> There are 300 mentions of "read transaction" in the documentation and commits
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SHARED lock vs READ transaction

PS: thank you for your long answer!

It's an interesting read and I think I will learn things.

But if "read transaction" is used dozens of times through the
documentation you shouldn't just say there is no such thing as a read
transaction if the documentation claims otherwise at so many places.
If there really is no such thing as a read transaction then the
documentation should be completely cleaned of that term to reduce
confusion.

kind regards,
Kira Backes

On Mon, 12 Aug 2019 at 13:11, Olivier Mascia  wrote:
>
> > Le 12 août 2019 à 12:11, Kira Backes  a écrit :
> >
> > I have one question which popped up in my other thread: What are the
> > differences between a SHARED lock and a READ transaction? Are there
> > any differences at all? If so, are there also differences for WAL
> > databases?
>
> There is no such thing as a "READ transaction".
>
> There are transactions (DEFERRED, IMMEDIATE or EXCLUSIVE) which you can 
> control and there are database locks (NONE, SHARED, RESERVED, EXCLUSIVE) 
> which you don't control (directly).
>
> Transactions are either explicitly controlled by you (BEGIN, COMMIT, 
> ROLLBACK) or implicitly wrapping isolated statements when there is no 
> explicit transaction (which is also referred to as auto-commit mode).
>
> The big picture (without the numerous details) looks like this:
>
> - upon reading, a SHARED lock will be requested ;
> - upon writing, a RESERVED lock will be requested (or a SHARED one upgraded 
> to RESERVED) ;
>
> I'm intentionally leaving out the details (behaviours when not being able to 
> acquire one of these locks).
>
> A BEGIN DEFERRED enters a transaction, but does not yet request any lock. It 
> will happen on the first read or write.
> A BEGIN IMMEDIATE enters a transaction, and does request a RESERVED lock 
> immediately, showing your intent to write.
> A BEGIN EXCLUSIVE enters a transaction, and does request an EXCLUSIVE lock 
> immediately.
> A COMMIT will either abandon the SHARED lock (if no writes occurred during 
> the transaction) or request an EXCLUSIVE lock. It will release locks when 
> done.
> A ROLLBACK will abandon locks.
>
> What looks the most as a "READ transaction" is a transaction started with 
> BEGIN DEFERRED which then takes care of not executing any statement writing 
> to the DB. It will then seek a SHARED lock, and simply abandon it on COMMIT 
> or ROLLBACK.
>
> Non-WAL:
> The existence of a SHARED lock will block a writer (which has got a RESERVED 
> lock) to upgrade to EXCLUSIVE when attempting COMMIT. SQLITE_BUSY might then 
> get returned from the attempt to execute COMMIT. The transaction state is not 
> lost. And assuming the SHARED locks from readers disappear, COMMIT can be 
> retried and succeeds.
>
> WAL:
> The existence of SHARED locks won't block a writer attempting COMMIT. This is 
> because the readers won't see the changes made by the writer until they 
> COMMIT/ROLLBACK. WAL brings stable, long-standing view of the DB to 
> connections which are only reading, for the duration of their transaction. 
> This won't stop another connection to write and commit. Albeit the WAL file 
> might grow quite indefinitely if there are always readers within 
> long-standing transactions.
>
> This is quite an over-simplified view at the subject, but it should get you 
> the big picture. The documentation has all the details.
>
> —
> 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] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

Thank you very much, I hope my example which probably many users tap
into unknowingly might help :-)

mit freundlichen Grüßen,
Kira Backes

On Mon, 12 Aug 2019 at 12:58, Richard Hipp  wrote:
>
> The documentation on transactions at
> https://www.sqlite.org/lang_transaction.html was written long, long
> ago, apparently long before WAL mode was available, and is in serious
> need of updating and improvement.  I'm working on that now
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SHARED lock vs READ transaction

> There is no such thing as a "READ transaction".

Could you please open the following google query:

https://www.google.com/search?q=%22read+transaction%22+site%3Asqlite.org

There are 300 mentions of "read transaction" in the documentation and commits


mit freundlichen Grüßen,
Kira Backes
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SHARED lock vs READ transaction

2019-08-12 Thread Olivier Mascia

> Le 12 août 2019 à 12:11, Kira Backes  a écrit :
> 
> I have one question which popped up in my other thread: What are the
> differences between a SHARED lock and a READ transaction? Are there
> any differences at all? If so, are there also differences for WAL
> databases?

There is no such thing as a "READ transaction".

There are transactions (DEFERRED, IMMEDIATE or EXCLUSIVE) which you can control 
and there are database locks (NONE, SHARED, RESERVED, EXCLUSIVE) which you 
don't control (directly).

Transactions are either explicitly controlled by you (BEGIN, COMMIT, ROLLBACK) 
or implicitly wrapping isolated statements when there is no explicit 
transaction (which is also referred to as auto-commit mode).

The big picture (without the numerous details) looks like this:

- upon reading, a SHARED lock will be requested ;
- upon writing, a RESERVED lock will be requested (or a SHARED one upgraded to 
RESERVED) ;

I'm intentionally leaving out the details (behaviours when not being able to 
acquire one of these locks).

A BEGIN DEFERRED enters a transaction, but does not yet request any lock. It 
will happen on the first read or write.
A BEGIN IMMEDIATE enters a transaction, and does request a RESERVED lock 
immediately, showing your intent to write.
A BEGIN EXCLUSIVE enters a transaction, and does request an EXCLUSIVE lock 
immediately.
A COMMIT will either abandon the SHARED lock (if no writes occurred during the 
transaction) or request an EXCLUSIVE lock. It will release locks when done.
A ROLLBACK will abandon locks.

What looks the most as a "READ transaction" is a transaction started with BEGIN 
DEFERRED which then takes care of not executing any statement writing to the 
DB. It will then seek a SHARED lock, and simply abandon it on COMMIT or 
ROLLBACK.

Non-WAL:
The existence of a SHARED lock will block a writer (which has got a RESERVED 
lock) to upgrade to EXCLUSIVE when attempting COMMIT. SQLITE_BUSY might then 
get returned from the attempt to execute COMMIT. The transaction state is not 
lost. And assuming the SHARED locks from readers disappear, COMMIT can be 
retried and succeeds.

WAL:
The existence of SHARED locks won't block a writer attempting COMMIT. This is 
because the readers won't see the changes made by the writer until they 
COMMIT/ROLLBACK. WAL brings stable, long-standing view of the DB to connections 
which are only reading, for the duration of their transaction. This won't stop 
another connection to write and commit. Albeit the WAL file might grow quite 
indefinitely if there are always readers within long-standing transactions.

This is quite an over-simplified view at the subject, but it should get you the 
big picture. The documentation has all the details.

—  
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] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

2019-08-12 Thread Richard Hipp

The documentation on transactions at
https://www.sqlite.org/lang_transaction.html was written long, long
ago, apparently long before WAL mode was available, and is in serious
need of updating and improvement.  I'm working on that now

-- 
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] [EXTERNAL] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

2019-08-12 Thread test user

You can also use “BEGIN EXCLUSIVE” before any writes; this gets you a write
transaction immediately. If this returns OK all following read/writes will
not return BUSY.

A transaction can upgrade from a read only to a write (when you do a select
followed by an insert).

I think you get the snapshot error when the previous reads in the
transaction no longer reference the HEAD database version because another
connection has written to it.

On Mon, 12 Aug 2019 at 11:03, Kira Backes  wrote:

> > So how do you propose to have consistency and isolation if SELECT does
> not create an automatic transaction if no explicit transaction exists?
>
> I think this is a misunderstanding which might explain your first
> email reply. I do not propose any change for sqlite, but I wasted 2
> days debugging and reading every page in sqlite documentation (locks,
> transactions, isolation, error description) did not help me. So I just
> want the documentation to be improved. I would change the error
> description for SQLITE_BUSY_SNAPSHOT so that it no longer says "read
> transaction" but instead says "read transaction or ongoing select
> statement". And the first sentence in "transactions" should be changed
> to indicated that select statement also create implicit read
> transactions.
> ___
> 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

[sqlite] SHARED lock vs READ transaction

Dear mailing list,

I have one question which popped up in my other thread: What are the
differences between a SHARED lock and a READ transaction? Are there
any differences at all? If so, are there also differences for WAL
databases?

Because from Rowan's reply it seems like it's the same. Is it really?
If so, could we document this? :)

kind regards, Kira Backes
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

> So how do you propose to have consistency and isolation if SELECT does not 
> create an automatic transaction if no explicit transaction exists?

I think this is a misunderstanding which might explain your first
email reply. I do not propose any change for sqlite, but I wasted 2
days debugging and reading every page in sqlite documentation (locks,
transactions, isolation, error description) did not help me. So I just
want the documentation to be improved. I would change the error
description for SQLITE_BUSY_SNAPSHOT so that it no longer says "read
transaction" but instead says "read transaction or ongoing select
statement". And the first sentence in "transactions" should be changed
to indicated that select statement also create implicit read
transactions.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

2019-08-12 Thread Hick Gunter

So how do you propose to have consistency and isolation if SELECT does not 
create an automatic transaction if no explicit transaction exists?

Consider:

SELECT  FROM ;
BEGIN;
UPDATE  SET field =  +1;
COMMIT;

If the SELECT and UPDATE statements are not part of the same transaction, there 
is no guarantee that  is still valid by the time you use it in the 
UPDATE.

Or even better:

SELECT  FROM ;
SELECT  FROM ;

How do you know that the expression  +  ever had that specific 
value (either one, or both, could have been changed between the two read 
operations)?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Kira Backes
Gesendet: Montag, 12. August 2019 11:01
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a 
simple SELECT query -- Documentation needed!

PS:

> Another example: X starts a read transaction using BEGIN and SELECT, then Y 
> makes a changes to the database using UPDATE.


Same goes for this example! We did not start a read transaction with BEGIN. 
This part says that you need to start a read transaction with BEGIN, which we 
did not do, and the other part says that SELECTs do
*NOT* start an automatic transaction.

So is this maybe even a bug in sqlite? Or the documentation is incorrect?

kind regards,
Kira Backes
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

Thank you Rowan,

in the context of *locks* it is kinda documented, but not in the
context of transactions :(

Just have a look at the error description:

https://www.sqlite.org/rescode.html#busy_snapshot

> The SQLITE_BUSY_SNAPSHOT error code is an extended error code for SQLITE_BUSY 
> that occurs on WAL mode databases when a database connection tries to promote 
> a read transaction into a write transaction but finds that another database 
> connection has already written to the database and thus invalidated prior 
> reads.
>
> The following scenario illustrates how an SQLITE_BUSY_SNAPSHOT error might 
> arise:
>
> Process A starts a read transaction on the database and does one or more 
> SELECT statement. Process A keeps the transaction open.
> Process B updates the database, changing values previous read by process A.
> Process A now tries to write to the database. But process A's view of the 
> database content is now obsolete because process B has modified the database 
> file after process A read from it. Hence process A gets an 
> SQLITE_BUSY_SNAPSHOT error.


in my opinion and from what I've read I've never started a READ
transaction. And if I open the transaction documentation it explicitly
says that SELECT statements to not start a read transaction. This is a
different concept from a SHARED lock. If it isn't than it should be
clearly explained and documented that every SHARED lock = READ
transaction.



mit freundlichen Grüßen,
Kira Backes
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

2019-08-12 Thread Rowan Worth

On Mon, 12 Aug 2019 at 16:55, Kira Backes  wrote:

> > When you do not use explicit transactions, SQLite will automatically
> create implicit transactions.
>
> But the documentation only says that an implicit transaction is
> created for data-changing queries like INSERT:
>
> https://www.sqlite.org/lang_transaction.html
>
> > Any command that changes the database (basically, any SQL command other
> than SELECT) will automatically start a transaction if one is not already
> in effect
>

Yeah I see what you mean... That sentence should not be taken in isolation,
but I agree it's misleading! It's clarified a few paragraphs down (after
noting that a "deferred" transaction is the default mode of operation):

Thus with a deferred transaction, the BEGIN statement itself does nothing
> to the filesystem. 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.
>

I think the initial statement should read:

"Any command that changes or reads the database will automatically start a
transaction if one is not already in effect"

Because the actual SQL command is irrelevant -- you can still run
INSERT/CREATE or other queries which represent write operations on a
database which is EXCLUSIVELY locked by another process, as long as the
query only involves temporary tables (which is kind of a cop-out because
such queries don't have to touch the database, but it just further
highlights the fact that DB access/modification is the crucial component
and not the SQL command).

Btw your original comment said "as far as I can tell this is not documented
anywhere," but the behaviour is unsurprising after understanding sqlite's
locking model, which is documented here:

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

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

Re: [sqlite] [EXTERNAL] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

PS:

> Another example: X starts a read transaction using BEGIN and SELECT, then Y 
> makes a changes to the database using UPDATE.


Same goes for this example! We did not start a read transaction with
BEGIN. This part says that you need to start a read transaction with
BEGIN, which we did not do, and the other part says that SELECTs do
*NOT* start an automatic transaction.

So is this maybe even a bug in sqlite? Or the documentation is incorrect?

kind regards,
Kira Backes
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

> Your first thread is creating an implicit transaction on connection FIRST by 
> reading from table FIRST.

But what you're saying is different to the documentation:

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

> Any command that changes the database (basically, any SQL command other than 
> SELECT) will automatically start a transaction if one is not already in effect

I don't understand why you are so defensive against improving the documentation?

mit freundlichen Grüßen,
Kira Backes
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

> When you do not use explicit transactions, SQLite will automatically create 
> implicit transactions.

But the documentation only says that an implicit transaction is
created for data-changing queries like INSERT:

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

> Any command that changes the database (basically, any SQL command other than 
> SELECT) will automatically start a transaction if one is not already in effect

it more or less explicitly says that a SELECT does not need a
transaction. So it should be documented that a SELECT is also an
implicit transaction or that a SELECT statement is promoted to a READ
transaction when a concurrent INSERT statement happens.

mit freundlichen Grüßen,
Kira Backes
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

2019-08-12 Thread Hick Gunter

Works as intended.

"our code base does not use transactions at all" does NOT mean that there are 
no transactions, just that SQLite uses *implicit* transactions, i.e. every 
statement is in it's own transaction.

"we can share a connection between threads as long as we don't read/write into 
the same table at the same time" is NOT a correct assumption. The assertion you 
need to prove for SQLite running in multi-threaded mode is "we can share a 
connection between threads as long as the connection is not used simultaneously 
in two or more threads".

What you program is doing is guaranteed to provoke SQLITE_BUSY_SNAPSHOT errors.

Your first thread is creating an implicit transaction on connection FIRST by 
reading from table FIRST.
Your second thread is creating an implicit transaction on connection SECOND by 
writing into table SECOND. The implied COMMIT "invalidates" the snapshot on 
connection FIRST.
Your third thread now attempts to insert into table THIRD on connection FIRST. 
But connection FIRST is within an READ transaction started by your first 
thread, so it has to escalate it's transaction to a WRITE transaction. But 
because it is in an "invalid" snapshot, it cannot do so.

This is well documented behaviour.

https://sqlite.org/isolation.html

"Another example: X starts a read transaction using BEGIN and SELECT, then Y 
makes a changes to the database using UPDATE. Then X tries to make a change to 
the database using UPDATE. The attempt by X to escalate its transaction from a 
read transaction to a write transaction fails with an SQLITE_BUSY_SNAPSHOT 
error because the snapshot of the database being viewed by X is no longer the 
latest version of the database. If X were allowed to write, it would fork the 
history of the database file, which is something SQLite does not support. In 
order for X to write to the database, it must first release its snapshot (using 
ROLLBACK for example) then start a new transaction with a subsequent BEGIN. "

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Kira Backes
Gesendet: Montag, 12. August 2019 09:33
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple 
SELECT query -- Documentation needed!

Dear sqlite mailing list,

I had to spend 2 days debugging a SQLITE_BUSY_SNAPSHOT, and reading the 
documentation did not help me. Our code base does not use transactions at all 
(we have a segmented code base protected by mutexes for a whole section, so 
reads/writes do not conflict ever). We neither had a crashing sqlite connection 
nor a corrupted database file. Even reading everything in isolation did not 
help me, since we're supposed to be able to share a connection between threads 
as long as we do not read/write into the same table at the same time, which we 
were able to ensure due to the section mutexes.

After thinking a very long time about this I found the reason: You absolutely 
can not share a WAL connection between threads or risk SQLITE_BUSY events. Yes, 
you heard right. If connection A runs a SELECT query in table A, then 
connection B inserts something into table B, and then you try in a concurrent 
thread to INSERT into table C using connection A you will get 
SQLITE_BUSY_SNAPSHOT errors until all SELECT queries are finished on connection 
A (in my case this took about a minute because I was reading some cache tables 
with several threads, so there was never a second where all queries were 
finished...). So I had SQLITE_BUSY_SNAPSHOT errors for a full minutes even 
though I never used transactions anywhere... Just because I had some long 
running SELECT statements in unrelated tables it made the connection completely 
unusable.

This is not a theoretical case, this can happen *VERY* easily and as far as I 
can tell this is not documented anywhere (and believe me, I've ready nearly 
every single page of the sqlite3 documentation). So you should really really 
document this very easy to trigger case.

And if you don't believe me: since I'm a nice girl I've written a unit test 
(C++, catch2) for this which reliably reproduces this behavior:


TEST_CASE("Test SQLITE_BUSY_SNAPSHOT", "[sqlite3]") {


  std::string db_name{"test_sqlite3_busy_snapshot.sqlite3"};
  if (std::filesystem::exists(db_name)) {
REQUIRE(std::filesystem::remove(db_name));
  }



  std::mt19937_64 engine{std::random_device{}()};
  std::uniform_int_distribution u(INT64_MIN, INT64_MAX);

  auto open_db_fn = [&]{
sqlite3* handle;

REQUIRE(sqlite3_open_v2(db_name.c_str(), , SQLITE_OPEN_READWRITE | 
SQLITE_OPEN_CREATE, nullptr) == SQLITE_OK);
REQUIRE(sqlite3_exec(handle, "PRAGMA journal_mode=WAL; PRAGMA 
synchronous=NORMAL; PRAGMA secure_delete=FAST;", nullptr, nullptr,
nullptr) == SQLITE_OK);
REQUIRE(sqlite3_busy_timeout(handle, 600'000) == SQLITE_OK);

return handle;
  };



  std::string insert_into_FIRST = "INSERT INTO

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

2019-08-12 Thread Tim Streater

On 12 Aug 2019, at 00:43, Keith Medcalf  wrote:

> On Sunday, 11 August, 2019 16:02, Richard Damon 
> wrote:
>
>>On 8/11/19 4:21 PM, Thomas Kurz wrote:
>
 I do understand the value of having date/time types in SQLite, but
 it is not easy to do while retaining backward compatibility.  
>
> I do not see any value in having a date/time type is SQLite.

Neither do I. The correct way is to store, as an integer, seconds (or whatever 
time unit you want) since some epoch. The display of what that stored value 
means is a *presentation* issue and should be handled as such by whatever 
language is presenting to the user. That also allows the program to either 
allow the user to presentation format to suit themselves, or for the program to 
follow the way system prefs are set.

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

Re: [sqlite] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

2019-08-12 Thread Clemens Ladisch

Kira Backes wrote:
> Our code base does not use transactions at all

When you do not use explicit transactions, SQLite will automatically
create implicit transactions.

 says:
| An implicit transaction (a transaction that is started automatically,
| not a transaction started by BEGIN) is committed automatically when
| the last active statement finishes. A statement finishes when its
| prepared statement is reset or finalized.

> we're supposed to be able to share a connection between threads as
> long as we do not read/write into the same table at the same time

One connection implies one transaction.  So if two statements happen
to be active at the same time in two threads, they will share
a transaction, and might keep the transaction active longer than the
other thread expects.

See .

> After thinking a very long time about this I found the reason: You
> absolutely can not share a WAL connection between threads or risk
> SQLITE_BUSY events.

This is not really related to threads; the same can happen when
a single thread tries to write in a formerly read-only transaction.

> This is not a theoretical case, this can happen *VERY* easily and as
> far as I can tell this is not documented anywhere

It is strongly recommended to use a separate connection per thread.

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

[sqlite] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!