Re: [sqlite] How to get access to SQLite Test Harness #3(TH3)

[Kishore Reddy]

Hi  *Roger Binns*,
Thanks for the reply.Actually i wasnt checking mail-list for answer i
thought i will be getting mail to my gmail that is the reason i posted on
developers forum too.

*Richard Hipp*,
i dont see any reply from you in the mail-list.


*Roger Binns/*

*Richard Hipp,*
I am planning to use SQLite library in *Avionics Level B software*.For that
i need to prove that this software complies to *DO-178B standards*.For that
i need to prove that it follows some 64 objectives set by DO-178B .

Ex)Listed below are some of the 64 objectives

*Planning Documents:*
1)Plan for software aspects of certification
2)Software development plan
3)software verification plan
4)software configuration management plan
5)software quality assurance plan
6)software requirements standards
7)software design standards
8)software coding standards

*Verification Documents:*

1)Software verification cases & Procedures
2)software verification results
3)software configuration management records
4)software configuration index
5)bug reports
6)software quality assurance records
7)software conformity review
8)software accomplishment summary

So i want to know where to get all these documents or information for
*SQLite*





On Tue, Jun 24, 2014 at 11:39 AM, Kishore Reddy  wrote:

> Hi,
> a)I want to use sqlite library in my project software.In the website it is
> listed as TH3 achieves 100% branch test coverage.I want all the test
> results of the SQLite software which shows that the software has *100%
> statement+branch coverage*.Can any one tell me how to get these test
> results ?? or how to get this TH3 framework and run those test cases to get
> results?
> b)Do we have requirements for this SQLite software which provides
> *traceability* to the test cases provided by TH3 framework?
>
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Data visibility problem

[Igor Tandetnik]

On 6/25/2014 8:48 PM, João Ramos wrote:

Now that you mentioned the WAL, shouldn't this actually help prevent this
scenario?


Quite the opposite - WAL helps enable this scenario. With traditional 
rollback journal, the writer would be unable to write at all while a 
reader is active, so issues of visibility fail to arise. Thus, as long 
as at least one connection to the shared cache has an open statement, so 
does the cache's connection to the underlying file, and the writer will 
be locked out.



I've never looked at SQLite implementation, but if a transaction
starts after a successful commit (T1), the new data it tries to access
shouldn't be cached because it was just now written to the WAL, correct?


Yes. But remember - this is true for "real" connections to the 
underlying file. All "pseudo"-connections to the shared cache use the 
same underlying "real" connection. The transaction on the "real" 
connection starts when the number of "pseudo" transactions on "pseudo" 
connections goes from 0 up to 1, and ends when that number goes from 1 
down to 0.

--
Igor Tandetnik

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

Re: [sqlite] Data visibility problem

[Keith Medcalf]

>> "Externally, from the point of view of another process or thread, two
>> or more database connections using a shared-cache appear as a single
>> connection. The locking protocol used to arbitrate between multiple
>> shared-caches or regular database users is described elsewhere."

>Of course and I understand what your saying, and I'm not asking to
>document every single feature interaction. However, the documentation never
>mentions the possibility of data visibility changes caused by the shared-cache
>mode and, in my opinion, it's a big enough issue that should merit being
>referenced in the documentation.

>> Then there's documentation on WAL mode, explaining how it is possible
>> for a writer to co-exist with readers, via page versioning.

>Now that you mentioned the WAL, shouldn't this actually help prevent this
>scenario? I've never looked at SQLite implementation, but if a
>transaction starts after a successful commit (T1), the new data it tries 
>to access shouldn't be cached because it was just now written to the WAL, 
>correct?

Yes.  Except of course that the new transaction is started against a view of 
the database  "frozen" by WAL to a time before the data is committed.  By the 
operation of the first paragraph above, the view of the database is the 
"instant" of the first transaction started against the database.  Only when all 
transactions against the shared cache are committed can another transaction 
against the shared cache get the "current" database view ... in other words, as 
long as one of the connections using the "shared cache" is in a transaction, 
the view of the database stays where it was when the first of a series of 
overlapping transactions commenced.

BEGIN A
  BEGIN B
  COMMIT B  BEGIN W
  BEGIN C   COMMIT W
BEGIN D
  COMMIT C
COMMIT D
COMMIT A
BEGIN E
...

The write in transaction W will not be visible until the start of transaction E 
because the database view is being held at the view which existed at the time 
transaction A was commenced by the shared cache.  In other words, from the 
perspective of the shared cache, the commit of W only becomes visible after the 
commit of A and the before the beginning of E.  Even if you committed A 
immediately after the update W was complete, the change would still not be 
visible until transaction E was commenced.






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

Re: [sqlite] Support for concurrent transactions

[Simon Slavin]

After a little experimentation I can't find any better way of doing this than 
to have each transaction handled with a different connection to the database.  
In other words, for each BEGIN you need a sqlite3_open().  If you do this, 
different concurrent parts of your app can each run their own transaction.  It 
all works as expected.  Of course, you do get the increased time (it takes time 
to open and parse the database file) and memory overhead.

Looking back at the design of SQL (not just SQLite), the fact that BEGIN is not 
reentrant or recursive does remind us that SQL was designed back in the days 
before apps were as complicated as they are now.

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

Re: [sqlite] Data visibility problem

[João Ramos]

On Wed, Jun 25, 2014 at 8:04 PM, Sohail Somani 
wrote:

> On 22/06/2014 6:33 PM, João Ramos wrote:
>
>> The issue appeared with the following scenario: - using SQLite v3.8.4.3 -
>> the DB is in WAL mode and running with multi-thread mode - every thread
>> has
>> a read-only DB connection (using thread-local-storage) - a single DB
>> connection is shared between threads to write
>>
>
> I had a similar issue and while I haven't yet resolved it, the culprit is
> a SQL statement not being finalized somewhere, not sure where. Make sure
> you have no open SQL statements.


I'm certain that no other statements were open in that connection. That's
one of the first things I rechecked.


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

Re: [sqlite] Data visibility problem

[João Ramos]

On Wed, Jun 25, 2014 at 2:19 PM, Igor Tandetnik  wrote:

> On 6/25/2014 5:19 AM, João Ramos wrote:
>
>> And I understand the limitations a shared-cache mode may have, but if this
>> is to be the case, it should be documented. I can be wrong, but I didn't
>> find anywhere in the docs where this scenario is presented (or has some
>> sort of warning).
>>
>
> Well, one can't document every possible scenario, every possible
> interaction of various features. There's a documentation on shared cache,
> complete with a nice picture explaining how all connections to the cache
> share a single connection to the underlying database file:
>
> http://sqlite.org/sharedcache.html
> "Externally, from the point of view of another process or thread, two or
> more database connections using a shared-cache appear as a single
> connection. The locking protocol used to arbitrate between multiple
> shared-caches or regular database users is described elsewhere."
>

Of course and I understand what your saying, and I'm not asking to document
every single feature interaction. However, the documentation never mentions
the possibility of data visibility changes caused by the shared-cache mode
and, in my opinion, it's a big enough issue that should merit being
referenced in the documentation.


>
> Then there's documentation on WAL mode, explaining how it is possible for
> a writer to co-exist with readers, via page versioning.
>
> http://www.sqlite.org/wal.html


Now that you mentioned the WAL, shouldn't this actually help prevent this
scenario? I've never looked at SQLite implementation, but if a transaction
starts after a successful commit (T1), the new data it tries to access
shouldn't be cached because it was just now written to the WAL, correct?


>
>
> If you use both features, you kind of have to put two and two together to
> see how they would interact.


Well, the way I see it, it's more like a 10 variable equation: not that
obvious. But that could be just me.


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

Re: [sqlite] SQLite performance with NOR FLASH

[Simon Slavin]

On 25 Jun 2014, at 9:14pm, Vivek Ranjan  wrote:

> sqlite3_backup_init()
> 
> sqlite3_backup_step()
> 
> sqlite3_backup_finish()
> 
> 
> SQLite writes data in very small chunks i.e. 1, 4, bytes which causes the
> poor performance while writing to Flash

What parameter are you passing to _step() as a number of pages ?

Simon.

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

[sqlite] SQLite performance with NOR FLASH

[Vivek Ranjan]

Dear All,

I am unsing SQLite on NOR Flash with Sqlite version 3.8.5 with default
setting i.e. sector and page size. Writing to NOR Flash taken around 10
seconds ( SQLite file size of 400KB, which is somehow not acceptable). Upon
inserting additional debug prints, I found that since I use the  following
API

sqlite3_backup_init()

sqlite3_backup_step()

sqlite3_backup_finish()


SQLite writes data in very small chunks i.e. 1, 4, bytes which causes the
poor performance while writing to Flash

Please suggest if there are some experiences to fine tune the writes to NOR
Flash.

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

Re: [sqlite] Sequential numbers

[RSmith]

On 2014/06/25 21:38, Dave Wellman wrote:

Hi Petite,
Many thanks fo rthsuggestion, it works a treat!.

Hi Simon,
Thanks for the thoughts but in this particular processing that is not going
to happen (delete a few rows). In this processing we always empty the table
completely before re-populating it.

I've gone with the TRIGGEr solution, it works really well.



Hi Dave, as a side note - if you never remove rows, AND in stead of emptying the table (Truncation) you actually DROP and Re-CREATE 
it... then a standard AUTOINCREMENT rowid alias is almost guaranteed to actually produce you sequential numbering from 1 onwards. It 
really only starts doing funnies when you remove rows - but if you don't break the chain, the rowid won't either. I know the 
documentation does not "guarantee" it simply because the behaviour can go haywire after amendments to the table, but I have yet to 
see a rowid NOT start at 1 in a brand-new table, or indeed just randomly "miss a beat" as you add rows. never happens - until that 
first row is deleted.


Sometimes however one doesn't intend to delete things, but it happens, so I think the trigger solution by Mr. Bee is still safer - 
though this would equally mess up the sequence if a deletion happens - BUT, you can just manually go change the values in that 
column then until they are happy again and the trigger should perform business-as-usual from then on.


Just another thought,
Have a great day!


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

Re: [sqlite] Sequential numbers

[Dave Wellman]

Hi Petite,
Many thanks fo rthsuggestion, it works a treat!.

Hi Simon,
Thanks for the thoughts but in this particular processing that is not going
to happen (delete a few rows). In this processing we always empty the table
completely before re-populating it.

I've gone with the TRIGGEr solution, it works really well.

Many thanks to all.
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Petite Abeille
Sent: 24 June 2014 21:57
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Sequential numbers


On Jun 24, 2014, at 10:47 PM, Dave Wellman 
wrote:

> I need the values to be sequential.

Well. if your data set is as small as you mentioned (20 records or less).
you could roll your own numbering schema with the simple expedient of
attaching a trigger to your tables to auto -number them with 'select count(
* ) + 1 from table' or something.

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

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

Re: [sqlite] Data visibility problem

[Sohail Somani]

On 22/06/2014 6:33 PM, João Ramos wrote:

The issue appeared with the following scenario: - using SQLite v3.8.4.3 -
the DB is in WAL mode and running with multi-thread mode - every thread has
a read-only DB connection (using thread-local-storage) - a single DB
connection is shared between threads to write


I had a similar issue and while I haven't yet resolved it, the culprit 
is a SQL statement not being finalized somewhere, not sure where. Make 
sure you have no open SQL statements.


Sohail

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

Re: [sqlite] SQLite and BLOBs

[Drago, William @ MWG - NARDAEAST]

Whether to use internal or external blobs is application dependant. In my test 
& measurement application testing 1 widget generates 140 small (1.2kB to 14kB) 
blobs. Multiply that by hundreds of widgets and you can see that storing and 
managing these as individual files would be a big headache compared to the 
simplicity of putting them in the database. These blobs will never be modified, 
filtered, searched, etc.. It makes perfect sense to keep them in the database.

So, none of what is said below applies to my application, but it may apply to 
others. Also, read this:

http://www.sqlite.org/intern-v-extern-blob.html

-Bill

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Carlos Ferreira
> Sent: Monday, June 23, 2014 10:52 AM
> To: 'General Discussion of SQLite Database'
> Subject: [sqlite] SQLite and BLOBs
>
> Hello,
>
> I found this reference in this link:
>
> http://stackoverflow.com/questions/676524/how-to-update-piecewise-a-
> blob-in-sqlite
>
>
> "
> This is not directly an answer to your question, but I have some
> experience using random access for (big) blobs in SQLite, and I advise
> you against using it, if you can. Here's why:
>
> Blobs break the SQL query format entirely. If your blob data needs any
> kind of processing, it will certainly at some point need filtering.
> Whatever mechanism you have in place to deal with filtering in SQL will
> be useless in this regard.
>
> Dealing with binary blobs wrapped in databases opposed to binary data
> in raw files limits your options. You will not be able to randomly read
> and write to data at the same time from multiple processes, which is
> possible with files. You can't use any tool that deals with this data
> and provides only a file I/O interface. You can't truncate or resize
> the blob. Files are simply a lot more versatile.
>
> It may seem convenient to have everything contained within one file, as
> it simplifies backup and transfer, but the pain of working with blobs
> is simply not worth it.
>
> So as your attorney, I advise you to write your blobs as raw files to
> the file system, and merely store a reference to the filename in your
> database. If your blobs are rather small and guaranteed not to grow,
> forget my advice.
> "
>
> Do you agree with this information?
>
> Regards.
>
> Carlos
>
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Dan Kennedy
> Sent: segunda-feira, 23 de Junho de 2014 15:26
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Planner chooses incorrect index
>
> On 06/23/2014 05:48 AM, João Ramos wrote:
> > Here you go:
> >
> > sqlite_stat1 (before - good planning)
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 14992 44 5
> HistoryEntry
> > idx_HistoryEntry_historyOrder_historyTimestamp 14992 6 2 HistoryEntry
> > idx_HistoryEntry_sourceType_sourceId 14992 2999 2
> >
> > sqlite_stat1 (after - bad planning)
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 15492 45 5
> HistoryEntry
> > idx_HistoryEntry_historyOrder_historyTimestamp 15492 5 2 HistoryEntry
> > idx_HistoryEntry_sourceType_sourceId 15492 3099 2
>
> Unfortunately the last column of the sqlite_stat4 data is missing,
> likely because it contains embedded 0x00 bytes. And without the
> sqlite_stat4 data, it seems that SQLite picks the "good" query plan in
> either case.
>
> Can you dump the sqlite_stat1 and sqlite_stat4 tables using the shell
> ".dump" command instead?
>
> Thanks,
> Dan.
>
>
>
> >
> > sqlite_stat4 (before - good planning)
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 366 340 1 78 78 81 17
> > 17 81 HistoryEntry idx_HistoryEntry_uri_historyOrder 722 489 1 566
> 566
> > 661 38 96
> > 661
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 25 25 1 1650 1650 1665
> > 56
> > 352 1665
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 862 747 1 2569 2569
> > 2899 106
> > 447 2899
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 862 1 1 2569 3331 3331
> > 106
> > 462 3331
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 2026 1789 1 3825 3825
> > 4997 131
> > 660 4997
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 143 113 1 6178 6178
> > 6232 154
> > 931 6232
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 181 180 1 6412 6412
> > 6477 162
> > 974 6477
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 2 2 1 6663 6663 6663
> > 169 984
> > 6663
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 1251 988 1 6953 6953
> > 7488 186
> > 1062 7488
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 379 370 1 8260 8260
> > 8329 195
> > 1361 8329
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 135 92 1 8915 8915
> 8965
> > 218
> > 1439 8965
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 196 194 1 9128 9128
> > 9129 227
> > 1501 9129
> > HistoryEntry idx_HistoryEntry_uri_historyOrder 280 170 1 9622 9622
> > 9650 237
> > 1579 9650
> > HistoryEntry 

Re: [sqlite] Data visibility problem

[Igor Tandetnik]

On 6/25/2014 5:19 AM, João Ramos wrote:

And I understand the limitations a shared-cache mode may have, but if this
is to be the case, it should be documented. I can be wrong, but I didn't
find anywhere in the docs where this scenario is presented (or has some
sort of warning).


Well, one can't document every possible scenario, every possible 
interaction of various features. There's a documentation on shared 
cache, complete with a nice picture explaining how all connections to 
the cache share a single connection to the underlying database file:


http://sqlite.org/sharedcache.html
"Externally, from the point of view of another process or thread, two or 
more database connections using a shared-cache appear as a single 
connection. The locking protocol used to arbitrate between multiple 
shared-caches or regular database users is described elsewhere."


Then there's documentation on WAL mode, explaining how it is possible 
for a writer to co-exist with readers, via page versioning.


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

If you use both features, you kind of have to put two and two together 
to see how they would interact.

--
Igor Tandetnik

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

Re: [sqlite] Sequential numbers

[David Wellman]

HI,
That sounds a neat solution, I'll have a look at implementing one.
Many thanks.
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Petite Abeille
Sent: 24 June 2014 21:57
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Sequential numbers


On Jun 24, 2014, at 10:47 PM, Dave Wellman 
wrote:

> I need the values to be sequential.

Well. if your data set is as small as you mentioned (20 records or less).
you could roll your own numbering schema with the simple expedient of
attaching a trigger to your tables to auto -number them with 'select count(
* ) + 1 from table' or something.

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

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

Re: [sqlite] Planner chooses incorrect index

[João Ramos]

The sample column has some sensitive data that I can't share, so I'll have
to change it and then try to reproduce the problem (I removed it before
because I assumed that it wouldn't be important).

On Mon, Jun 23, 2014 at 3:26 PM, Dan Kennedy  wrote:

> On 06/23/2014 05:48 AM, João Ramos wrote:
>
>> Here you go:
>>
>> sqlite_stat1 (before - good planning)
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 14992 44 5
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 14992 6 2
>> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14992 2999 2
>>
>> sqlite_stat1 (after - bad planning)
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 15492 45 5
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 15492 5 2
>> HistoryEntry idx_HistoryEntry_sourceType_sourceId 15492 3099 2
>>
>
> Unfortunately the last column of the sqlite_stat4 data is missing, likely
> because it contains embedded 0x00 bytes. And without the sqlite_stat4 data,
> it seems that SQLite picks the "good" query plan in either case.
>
> Can you dump the sqlite_stat1 and sqlite_stat4 tables using the shell
> ".dump" command instead?
>
> Thanks,
> Dan.
>
>
>
>
>
>> sqlite_stat4 (before - good planning)
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 366 340 1 78 78 81 17 17
>> 81
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 722 489 1 566 566 661 38
>> 96
>> 661
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 25 25 1 1650 1650 1665 56
>> 352 1665
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 862 747 1 2569 2569 2899
>> 106
>> 447 2899
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 862 1 1 2569 3331 3331 106
>> 462 3331
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 2026 1789 1 3825 3825
>> 4997 131
>> 660 4997
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 143 113 1 6178 6178 6232
>> 154
>> 931 6232
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 181 180 1 6412 6412 6477
>> 162
>> 974 6477
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 2 2 1 6663 6663 6663 169
>> 984
>> 6663
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 1251 988 1 6953 6953 7488
>> 186
>> 1062 7488
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 379 370 1 8260 8260 8329
>> 195
>> 1361 8329
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 135 92 1 8915 8915 8965
>> 218
>> 1439 8965
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 196 194 1 9128 9128 9129
>> 227
>> 1501 9129
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 280 170 1 9622 9622 9650
>> 237
>> 1579 9650
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 1228 1045 1 9954 9954
>> 9995 244
>> 1709 9995
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 178 170 1 11192 11192
>> 11322 246
>> 1898 11322
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 312 299 1 11503 11503
>> 11661 252
>> 1963 11661
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 546 543 1 11921 11921
>> 12037 258
>> 1994 12037
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 169 113 1 12574 12574
>> 12586 261
>> 2013 12586
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 216 115 1 12862 12862
>> 12973 265
>> 2187 12973
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 388 379 1 13101 13101
>> 13327 270
>> 2291 13327
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 366 351 1 13722 13722
>> 13973 295
>> 2388 13973
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 200 1 1 14482 14530 14530
>> 331
>> 2749 14530
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 233 2 1 14684 14848 14849
>> 333
>> 3067 14849
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
>> 1665 1665 0 1663 1665
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
>> 3331 3331 0 3329 3331
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
>> 4997 4997 0 4995 4997
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
>> 6663 6663 0 6661 6663
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
>> 8329 8329 0 8327 8329
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
>> 9995 9995 0 9993 9995
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
>> 11661 11661 0 11659 11661
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 5 1 1 12392
>> 12392 12392 377 12390 12392
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 12557
>> 12558 12558 530 12556 12558
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 5 1 1 13042
>> 13044 13044 998 13042 13044
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 13049
>> 13049 13049 1000 13047 13049
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 13061
>> 13061 13061 1006 13059 13061
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 13210
>> 13212 13212 1150 13210 13212
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 1 1 1 13327
>> 13327 13327 1261 13325 13327
>> HistoryEntry 

Re: [sqlite] Data visibility problem

[João Ramos]

On Mon, Jun 23, 2014 at 3:31 PM, Simon Slavin  wrote:

>
> On 23 Jun 2014, at 11:17am, João Ramos  wrote:
>
> > On Mon, Jun 23, 2014 at 10:58 AM, Simon Slavin 
> wrote:
> >
> >> Are you finalizing all these SQL commands (or using _exec() which
> amounts
> >> to the same thing) ?
> >
> > Yes, I double checked and everything is being terminated properly (e.g.:
> > sqlite3_reset, etc.) and without any error codes.
>
> Excellent.
>
> >> What do you mean by 'releases' here ?  If you have other threads using
> the
> >> same connection, you're obviously not closing it.
> >
> > No, the SQLite connection isn't closed. By "release" I mean release back
> to
> > the thread pool.
>
> Okay.  I would temporarily, for testing purposes, turn off shared cache
> mode.  Another thing to try messing with is read_uncommitted:
>

Turning off shared cache mode solved this (read uncommitted has always been
off).


>
> 
>
> Which of these does or doesn't change how your app behaves might help you
> diagnose the problem.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Data visibility problem

[João Ramos]

On Mon, Jun 23, 2014 at 3:03 PM, Igor Tandetnik  wrote:

> On 6/23/2014 5:43 AM, João Ramos wrote:
>
>> If multiple connections in shared cache mode are treated as a single
>> "real"
>> connection, should I change the thread mode to Serialized?
>>
>
> What mode do you use now?
>
> In any case, I doubt the thread mode has any bearing on the problem. The
> scenario may occur even if no two threads ever run in parallel. It's only
> necessary that SELECTs on different connections to shared cache interleave:
> you call sqlite3_step on connection A, then later call sqlite3_step on
> connection B (while the statement in A has not been reset) - that second
> traversal would see the data as of the start of the first traversal.
>
>
I'm using multi-thread because DB connections aren't used simultaneously in
two or more threads. But since you said that in shared cache mode multiple
connections behave as a "single" connection, I was afraid that this mode
would somehow change the meaning of "simultaneously in two or more threads".

For example, imagine a scenario with two threads, each with its own
connection, both with shared cache and in multi-thread mode. According to
the documentation, I don't need the serialized thread mode because the
connections aren't being used simultaneously. However, internally, they
will simultaneously access the shared cache. I'm sure the shared cache is
protected against this, and I was just making sure that this is the case
(and not an omission in the docs, like for example: "Multi-thread. In this
mode, SQLite can be safely used by multiple threads provided that no single
database connection is used simultaneously in two or more threads *and* one
or more connections aren't in shared cache mode.")


>
>  I tough this
>> "single" connection was in terms of page cache
>>
>
> Well, yes. So you have a version of a page in this cache - that version
> must be as of the time of the oldest read transaction still outstanding. So
> all connections to that shared cache see the same version of that page, and
> cannot observe any changes made to it later.


Ok, I see what you mean.


>
>  and would not have any
>> implications on how the connections are accessed (or not) concurrently,
>> especially transaction control.
>>
>
> Concurrent access and transaction control are unrelated concepts. I'm not
> sure why you are bringing them up together. You can observe transaction
> isolation effects with a single thread interleaving access to two database
> connections. Start traversing a SELECT statement on one connection; between
> two calls to sqlite3_step, modify the data on the other (WAL journal mode
> would let you); continue traversal of the statement - it would not observe
> the changes the same thread has just made.


I never meant to imply that they are related somehow (see the first
response). Only asking if I could be stumbling against an improper use of
thread-mode + shared cache, and that could cause a connection not seeing
committed data.


>
>  Either way, the example you gave does not apply. What was happening with
>> my
>> code was that a transaction A started at T1, inserted a row and was
>> committed successfully at T2. Another transaction B started at T3 and
>> didn't see the new row.
>>
>
> Was there, by any chance, a seemingly unrelated transaction C using the
> same shared cache that started reading at T0, and has not completed by T3?


Yes, this could very possible be the case.


>
>
>  I can't see how the fact that transaction A was in
>> shared cache mode and B in private cache mode
>>
>
> Shouldn't it be the other way round? I thought you said all readers (B
> among them) use shared cache, while a writer (like A) uses a private
> connection.


Your're correct, sorry for my mistake.


>
>
>  could influence the new row
>> not being seen in transaction B, when its transaction started after
>> transaction A commit. Doesn't this break ACID?
>>
>
> ACID provides guarantees on when changes made in one transaction will
> *not* be seen by another. It says nothing about when changes in one
> transaction *will* be seen by another.
>
> Yes, connections sharing a cache are not quite as isolated from each other
> as independent private connections (it's even possible to enable
> read-uncommitted mode, whereby one such connection can see
> not-yet-committed changes made by another). As any other option, shared
> cache brings some benefits and some limitations (if it were all benefits,
> it wouldn't be an option).


Sorry, but I'm going to disagree (in part) with what you're saying. I agree
with what you say concerning "consistency" but the scenario you describe
completely violates "isolation". A transaction creates a well defined area
of isolation that always, and without question, terminates in the commit.
What you're saying is that its impossible to control when the transaction
isolation terminates. If the example above, if the transaction that started
at T0 lasted a few