[sqlite] Howto set lower pager page size when using zipvfs extension
On 03/13/2015 09:30 PM, Alexandre Mainville wrote: > Hi, > > When using the zipvfs extension, one can change the size for the upper > pager using pragma page_size but how does one change the page size for the > lower level pager. The zipvfs_block_size pragma does not seem to support > setting the value. Enable URI filenames: https://www.sqlite.org/uri.html and specify an option of the form "block_size=N". The lower level pager should use a page-size of N bytes to access the database file. e.g. file:test.db?block_size=4096 The setting is not persistent - each client should specify the block_size=N option separately. Dan. > > Thanks > > Alex > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] System.Data.SQLite and spellfix under VB.net
Hello, thank you very much! The zip does the trick! The speed of spellfix is simply mindblowing! Cheers sonypsx -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Hick Gunter Gesendet: Donnerstag, 12. M?rz 2015 19:17 An: 'General Discussion of SQLite Database' Betreff: Re: [sqlite] System.Data.SQLite and spellfix under VB.net The zip should contain 4 files shell.c sqlite3.c sqlite3.h sqlite3ext.h sqlite-amalgamation-3080803.zip -Urspr?ngliche Nachricht- Von: sonypsx [mailto:sonypsx at gmx.net] Gesendet: Donnerstag, 12. M?rz 2015 18:56 An: 'General Discussion of SQLite Database' Betreff: Re: [sqlite] System.Data.SQLite and spellfix under VB.net Hello, may i ask again ... Can somebody help to successfully compile spellfix.c for windows? Errors see below! Cheers sonypsx -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von sonypsx Gesendet: Mittwoch, 04. M?rz 2015 19:54 An: 'General Discussion of SQLite Database' Betreff: Re: [sqlite] System.Data.SQLite and spellfix under VB.net Hello Joe, ok i'll tried: gcc -s -O4 -I /path/to/sqlite/headers/ -shared -o spellfix.dll spellfix.c and got this error: c:\MinGW\bin>gcc -s -O4 -I c:\Sqlite\src\ -shared -o spellfix.dll c:\Sqlite\ext \misc\spellfix.c In file included from c:\Sqlite\ext\misc\spellfix.c:17:0: c:\Sqlite\src/sqlite3ext.h:20:21: fatal error: sqlite3.h: No such file or direct ory #include "sqlite3.h" ^ compilation terminated. c:\MinGW\bin> If I look into sqlite3ext.h i see the reference to the sqlite3.h file which does not exist in the whole sqlite source (zip) which I downloaded from http://www.sqlite.org/src/info/e693e11d1b926597 Can you help me please? Best regards sonypsx -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Joe Mistachkin Gesendet: Montag, 02. M?rz 2015 22:37 An: 'General Discussion of SQLite Database' Betreff: Re: [sqlite] System.Data.SQLite and spellfix under VB.net sonypsx wrote: > > could some please post a sample how to use the spellfix module with > System.Data.SQLite under VB.net? > The first step would be to compile the spellfix extension as a loadable module, as documented here: https://www.sqlite.org/loadext.html Next, you can load it using the SQLiteConnection.LoadExtension method. Finally, you should be able to follow along with the normal spellfix docs, here: https://www.sqlite.org/spellfix1.html -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Memory leak?
Hi, Matt I also have an iPhone app which directly interacts with sqlite using its C language API. So, a few questions for you, to get a better idea of what?s happening: Are you interacting directly with sqlite? Or are you using a 3rd party API such as FMDB? I take it you are not using Core Data (which optionally can use a sqlite database as a datastore.) Are you using ARC? For the uninitiated, ARC stands for Automatic Reference Counting. It is an option which can be enabled on a per project basis. Files within the project can opt out of using it. ARC, reduces the amount of memory management code the developer has to write, but it does not license the developer to design unwisely. For instance, it is still the developer?s responsibility to design in such a way that such things as retain cycles are avoided. Have you implemented the -(void)dealloc method for all your Objective-C (or Swift?) objects? I have had some success getting memory released by implementing -(void)dealloc and within that implementation setting specific objects to nil. Note that under ARC you are not allowed to call [super dealloc] directly but you still can implement the -(void)dealloc method. Are you willing to share some of your application code where it interacts with the sqlite database? > On Mar 13, 2015, at 5:13 AM, Matthias Schmitt wrote: > > Hello, > > thank you for your fast response. > >> On 12 Mar 2015, at 17:35, Richard Hipp wrote: >> >> Let's start with the basics: How do you know that the memory was in >> fact leaked and is not instead simply being held for reuse? > > The Xcode development environment comes with a debugging tool named > ?Instruments? which is able to detect memory leaks quite reliable. It traces > all references to code segments. If a reference to a code segment is > overwritten with a new value, but the previously addressed memory segment was > not released, then the software detects this as a leak. The same thing is > true when a variable created inside a code block references allocated memory. > When the program leaves the code block without releasing the previously > allocated memory, the reference get inaccessible and the allocated memory > cannot be released any more. > >> On 12 Mar 2015, at 17:31, Simon Slavin wrote: >> Is it, by any chance, always the first SQL command executed after doing >> sqlite3_open() ? > > No, there are multiple memory leaks which add over time. > >> Is there any chance you can look at this memory and see what appears there ? > > Here is an example. But different leaked memory segments look different. > > <0x7fc2938b5600> > > 5600: 0072 2b9d271d > 5608: c9b753a9 bfbc7557 > 5610: dadb57e5 cffoeefb > 5618: db68dd48 09aa68da > 5620: aad489da b44f4329 > 5628: dd3ba2ff 00f0ff00 > 5630: 3a653236 a9b72d6a > 5638: 9d8ceaae 7571942b > 5640: 51f251fe ed6bcc73 > 5648: 3a2d224a 72d43bbd > 5650: e9fb96a8 c496a4a8 > 5658: 57a53a82 96aac489 > 5660: da9ebd2a 1fbb5657 > 5668: 6ff1536e e6713d4f > 5670: e0ff00c2 ed73e2ff > 5678: 008f74df 046883ca > >> Do the extra 4344 bytes get included in the number shown by >> "sqlite3_memory_used()" ? You could try using that function after >> sqlite_close() and see what it shows. > > I am closing and reopening the database now after every transaction. > sqlite3_memory_used() shows always 0. I guess that sqlite is telling me by it > that it is not aware of any lost memory, correct? > >> Is the memory released at any later time ? When you use _close() on the >> connection, for example ? Or, if you can track it, when SQLite calls >> "sqlite3_db_release_memory()" internally ? > > > The closing and re-opening the database does not change anything in the > display of ?Instruments?. The program still reports memory leaks. In another > attempt I tried to compile the code with SQLITE_ENABLE_MEMORY_MANAGEMENT > compile-time option. Then I tried to call sqlite3_release_memory() after > every database command. Same result. > > Best regards > > Matthias Schmitt > > magic moving pixel s.a. > 23, Avenue Grande-Duchesse Charlotte > L-3441 Dudelange > Luxembourg > Phone: +352 54 75 75 > http://www.mmp.lu > > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Gregory Moore thewatchfulone at gmail.com
[sqlite] Optimization Opportunity?
Op 13 mrt 2015, om 00:03 heeft Wolfgang Enzinger het volgende geschreven: > Am Sun, 8 Mar 2015 14:06:51 +0100 schrieb E.Pasma: > >> Actually query one appears slightly faster, >> Searching the PK index is faster as that is always a COVERING index. > > I was under the impression that the opposite is true, but I wasn't > sure > about that. > >> From the secunsary indexes only a part oh the key is used. >> Note there is not much use on adding PK as second column in the >> additional indexes. It is there anyway a a pointer to the row. > > You're right, that index doesn't make much sense; in my real > application it > looks different, what I was showing here was just an example (one > that was > not very well thought of, obviously). > >> I agree that it is strange that the execution plan for the two >> queries >> is different, After EXISTS the optimizer might ignore the expression >> in the select part of the sub-query. And Query one looks better as it >> soes not mention any column names. Personally I'd write SELECT NULL >> instead of SELECT *. > > I prefer "SELECT 1 ...", like in Gunter's post. But that's a matter of > taste, of course. > > Well, my actual point was that the query planner seems to > unnecessarily > visit the table row in order to read a column value that will be > discarded > lateron anyway, and that this could probably be optimized out > automatically. But my point is obsolete of course when the way it is > right > now is the faster one. Then again, it's not quite clear why this very > strategy is *not* chosen when "SELECT 1 ..." or similar is being > used. Not > a big deal indeed, just curious. > >> If speed matters instead of EXIST you can use IN and a list sub- >> query. >> This is superfast now: >> >> SELECT a1 FROM a WHERE a1 in (SELECT b.a1 FROM b INNER JOIN c >> USING(b1) WHERE c.c1=222); >> >> 0|0|0|SEARCH TABLE a USING INTEGER PRIMARY KEY (rowid=?) >> 0|0|0|EXECUTE LIST SUBQUERY 1 >> 1|0|1|SEARCH TABLE c USING INTEGER PRIMARY KEY (rowid=?) >> 1|1|0|SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?) > > I avoided IN for a long time, but that must originate from the time > when I > mostly used Jet (Access) file databases ... with SQLite, it's really > fast > indeed. > > Wolfgang Excuses Wolfgang, my impression that the PK is automatically covering was wrong. I like to make an other correction: >> After EXISTS the optimizer might ignore the expression >> in the select part of the sub-query. This is not true if the expression is an aggrehate function. It remains true that there seems to be a optimization opportunity for query 2.
[sqlite] PRAGMA Synchronous safety
>So would it be possible to run that command each time you open the config >database and after any change to it ? That would give us a perfect way to >find out which commands were causing your problems.< Not really possible. The average update rare is low, but there are times when hundreds of settings are written, depending on which changes the user makes in preferences etc. Users can update settings from custom scripts, which may mean one update per session or hundreds per minute. Running a 5 second integrity check after each write would bring down performance badly. I now also run an integrity_check when closing the settings database during application shut-down and will seek to find a way to notify the user to retain the log file - in the hope that it contains more info. My users are no IT folks, just average users, moms & pops. Displaying scary error messages about damaged databases and asking to send log files will cause a lot of additional support and probably bad reviews in social media. Database damage is a very sensitive area.
[sqlite] PRAGMA Synchronous safety
> >So would it be possible to run that command each time you open the config > database and after any change to it ? That would give us a perfect way to > find out which commands were causing your problems.< > > Not really possible. The average update rare is low, but there are times when > hundreds of settings are written, depending on which changes the user > makes in preferences etc. Users can update settings from custom scripts, > which may mean one update per session or hundreds per minute. Running a > 5 second integrity check after each write would bring down performance > badly. > I had got the impression (and probably Simon had too) that the preferences database was a lot smaller, so the integrity check would be a lot quicker. > I now also run an integrity_check when closing the settings database during > application shut-down and will seek to find a way to notify the user to retain > the log file - in the hope that it contains more info. My users are no IT > folks, > just average users, moms & pops. Displaying scary error messages about > damaged databases and asking to send log files will cause a lot of additional > support and probably bad reviews in social media. Database damage is a very > sensitive area. > I wonder whether you could provide those that have suffered a corrupt database with "special" code with extra logging and checks. You could warn them about scary messages and longer delays. Explaining this is part of your investigations. These people already know there is a problem, so are unlikely to spread bad reviews. They may also be more likely to suffer another corruption if it relates to a particular workflow. As a user of your application I would be happy to help, unfortunately (not for me :^) I've never had a corrupted database. Regards Andy Ling
[sqlite] sqlite3_release_memory when SQLITE_THREADSAFE=0
Hello, I'm using SQLite extensively (thanks for the great tool!) in an application where it's almost exclusively accessed on a single thread. I have found that the SQLITE_THREADSAFE=0 compile-time option gives us a 3% improvement in overall performance by removing mutex overhead. However: there are 2 places where I very rarely access sqlite from a separate thread. One of these is a call to sqlite3_interrupt() which, on inspection of the source, seems like it is probably safe to access from multiple threads. However I also call sqlite3_release_memory() on receiving a memory warning from the system. That one looks much less safe to call off-thread. It seems like a shame to incur a 3% penalty across the board when I'm only very rarely accessing off another thread. Are there any other options here? Regards, Xavier Snelgrove -- Xavier Snelgrove Cofounder & CTO, Whirlscape Inc. http://whirlscape.com xavier at whirlscape.com
[sqlite] Memory leak?
Okay. The hex you showed us doesn't represent ASCII characters, so the bug is not obviously leaking memory which contains the sort of data you'd be storing. Apart from a few sequences of 'ff00' I see no obvious patterns. How are you getting your SQLite library ? Are you calling a library built into the development environment or are you including your own copies of sqlite.c and sqlite.h ? > I am closing and reopening the database now after every transaction. > sqlite3_memory_used() shows always 0. I guess that sqlite is telling me by it > that it is not aware of any lost memory, correct? Yes, I believe so. > Then I tried to call sqlite3_release_memory() after every database command. > Same result. This indicates the same thing: the memory leak is not part of SQLite's own memory management strategy, but something which is happening outside of it. I'm out of ideas but I hope someone else can help. Simon.
[sqlite] Memory leak?
Hello, thank you for your fast response. > On 12 Mar 2015, at 17:35, Richard Hipp wrote: > > Let's start with the basics: How do you know that the memory was in > fact leaked and is not instead simply being held for reuse? The Xcode development environment comes with a debugging tool named ?Instruments? which is able to detect memory leaks quite reliable. It traces all references to code segments. If a reference to a code segment is overwritten with a new value, but the previously addressed memory segment was not released, then the software detects this as a leak. The same thing is true when a variable created inside a code block references allocated memory. When the program leaves the code block without releasing the previously allocated memory, the reference get inaccessible and the allocated memory cannot be released any more. > On 12 Mar 2015, at 17:31, Simon Slavin wrote: > Is it, by any chance, always the first SQL command executed after doing > sqlite3_open() ? No, there are multiple memory leaks which add over time. > Is there any chance you can look at this memory and see what appears there ? Here is an example. But different leaked memory segments look different. <0x7fc2938b5600> 5600: 0072 2b9d271d 5608: c9b753a9 bfbc7557 5610: dadb57e5 cffoeefb 5618: db68dd48 09aa68da 5620: aad489da b44f4329 5628: dd3ba2ff 00f0ff00 5630: 3a653236 a9b72d6a 5638: 9d8ceaae 7571942b 5640: 51f251fe ed6bcc73 5648: 3a2d224a 72d43bbd 5650: e9fb96a8 c496a4a8 5658: 57a53a82 96aac489 5660: da9ebd2a 1fbb5657 5668: 6ff1536e e6713d4f 5670: e0ff00c2 ed73e2ff 5678: 008f74df 046883ca > Do the extra 4344 bytes get included in the number shown by > "sqlite3_memory_used()" ? You could try using that function after > sqlite_close() and see what it shows. I am closing and reopening the database now after every transaction. sqlite3_memory_used() shows always 0. I guess that sqlite is telling me by it that it is not aware of any lost memory, correct? > Is the memory released at any later time ? When you use _close() on the > connection, for example ? Or, if you can track it, when SQLite calls > "sqlite3_db_release_memory()" internally ? The closing and re-opening the database does not change anything in the display of ?Instruments?. The program still reports memory leaks. In another attempt I tried to compile the code with SQLITE_ENABLE_MEMORY_MANAGEMENT compile-time option. Then I tried to call sqlite3_release_memory() after every database command. Same result. Best regards Matthias Schmitt magic moving pixel s.a. 23, Avenue Grande-Duchesse Charlotte L-3441 Dudelange Luxembourg Phone: +352 54 75 75 http://www.mmp.lu
[sqlite] Howto set lower pager page size when using zipvfs extension
Hi, When using the zipvfs extension, one can change the size for the upper pager using pragma page_size but how does one change the page size for the lower level pager. The zipvfs_block_size pragma does not seem to support setting the value. Thanks Alex
[sqlite] Releasing a read (SHARED) lock
Hmmm, I am guilty of not always stepping until I get SQLITE_DONE (I am
usually selecting by the primary key, and therefore know I will only get a
single result), but I make sure to always call sqlite_reset, so I don't
think that should be the issue.
On further reflection, I suspect it might be the 'obscure side-effect'
mentioned by Dinu. I do have functions that look like:
//bind all parameters
//...
//begin querying statement1:
//Note: stmt1 is only queried once (and hence is still 'open' for now)
sqlite3_step(stmt1);
//Use the results from statement1 to bind statement2
//...
//begin querying statement2:
while(SQLITE3_OK == sqlite3_step(stmt2)) {
//Do things
}
//reset both statements
sqlite3_reset(stmt2);
sqlite3_reset(stmt1);
So, in this situation I do in fact have two SELECT statements open
simultaneously, which should trigger the unreleasable lock. I will try
change my code and post results here.
On 13 March 2015 at 09:02, Dinu Marina wrote:
> You should also check, as R.Smith and Slavin pointed, that you don't
> accidentally have a BEGIN [DEFERRED] somewhere, as that would trigger
> exactly this behavior: it will lock on the first select (the first step()
> actually)
>
>
> On 13.03.2015 02:44, Simon Slavin wrote:
>
>> On 13 Mar 2015, at 12:17am, Barry wrote:
>>
>> On 13 March 2015 at 01:21, Dinu Marina wrote:
>>>
>>> You should be also aware of a more common pitfall: unclosed result sets.
Any lock is held until you read PAST the last row or you call
stmt_finalize
(or the equivalent abstraction in your DBAL). Always close select
statements.
>>> Hi Dinu,
>>>
>>> Am I correct in my understanding then that it is not enough to call
>>> sqlite3_reset, I must call sqlite3_finalize after I am done with a
>>> statement?
>>>
>> In order to dispose of a statement you can do either sqlite3_reset() or
>> sqlite3_finalize(). The common mistake is to do something like this:
>>
>> SELECT * FROM myTable WHERE rowid = 36
>>
>> and then not do either _reset() or _finalize() because you know you have
>> asked for only one row so you expect SQLite to have done a _finalize() for
>> you.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
[sqlite] Memory leak?
On Fri, Mar 13, 2015 at 3:13 AM, Matthias Schmitt wrote: >> On 12 Mar 2015, at 17:35, Richard Hipp wrote: >> >> Let's start with the basics: How do you know that the memory was in >> fact leaked and is not instead simply being held for reuse? > > The Xcode development environment comes with a debugging tool named > ?Instruments? which is able to detect memory leaks quite reliable. It traces > all references to code segments. If a reference to a code segment is > overwritten with a new value, but the previously addressed memory segment was > not released, then the software detects this as a leak. The same thing is > true when a variable created inside a code block references allocated memory. > When the program leaves the code block without releasing the previously > allocated memory, the reference get inaccessible and the allocated memory > cannot be released any more. By default SQLite stores database pages by allocating space for the page plus space for metadata after the page, and then passes around references to that metadata. This can mislead tools of this sort, because the reference is to a point well within the allocation rather than to the actual allocation. You might experiment with SQLITE_PCACHE_SEPARATE_HEADER to see if that changes the results. -scott
[sqlite] Releasing a read (SHARED) lock
On 13 March 2015 at 01:21, Dinu Marina wrote: > You should be also aware of a more common pitfall: unclosed result sets. > Any lock is held until you read PAST the last row or you call stmt_finalize > (or the equivalent abstraction in your DBAL). Always close select > statements. Hi Dinu, Am I correct in my understanding then that it is not enough to call sqlite3_reset, I must call sqlite3_finalize after I am done with a statement? Cheers, - Barry > On Mar 12, 2015 11:40 AM, "R.Smith" wrote: > > > > > > > On 2015-03-12 04:38 AM, Barry wrote: > > > >> Hello everybody, > >> > >> I have a situation where two processes are accessing the same SQLite > >> database. One process only reads from the database, one process reads > and > >> writes. > >> > >> These processes keep a single database connection open for the lifetime > of > >> the process. > >> > >> It seems to me that once the reader process accesses the database (after > >> it > >> performs its first SELECT statement), it maintains a lock on the > database > >> until the connection is closed (when the program is exited). This > prevents > >> the writer process from updating the database. > >> > > > > Hi Barry, > > > > This is the usual situation when one of your transactions in the "reading > > only" database does not finalize. i.e. you started a transaction there > and > > did not end it with either "END TRANSACTION" or "COMMIT" or "ROLLBACK". > > > > In the non-WAL DB it will simply hold the lock preventing changes. In the > > WAL DB it will hold the lock for its own view of the data but let the > other > > writer write.. however it won't see the changes for itself. > > > > This is very common actually. Just find every transaction you start > > (explicitly or implicitly) and make sure you end it and when you end it, > > see what the return value is from SQLite and whether it reports any > error. > > > > HTH! > > Ryan > > > > > > > >> I tried changing to WAL. This made the writer process able to commit its > >> changes, but now the reader does not see any modifications made to the > >> database until it is restarted (It seems to see a snapshot of the DB at > >> the > >> time of its first read). > >> > >> I am using prepared statements: On opening the DB, I create all my > >> prepared > >> statements. When I need to execute a statement, I bind to the statement, > >> call sqlite3_step (possibly multiple times), then call sqlite3_reset. I > do > >> not finalise the statements until the program closes. > >> > >> In order to simulate 'save' behaviour, the writer process always holds a > >> transaction open. When the user chooses 'save', the current transaction > is > >> committed and a new transaction is begun. (I understand that the reader > >> will not see any changes in the uncommitted transaction, but is not > seeing > >> any committed transactions either). > >> > >> I have checked quite thoroughly through my code and cannot find any > >> instances of statements executed without a sqlite3_reset quickly > >> following. > >> > >> Is this intended behaviour - that once a connection has performed a > read, > >> it maintains its lock on the database for its lifetime? Is SQLite smart > >> enough to know that the pages it holds in cache of the reader are > invalid > >> after the writer has made changes to the DB on disk? > >> > >> If this is not the intended behaviour - is there a way I can find out > >> which > >> statements are causing the lock to be held open? Or can I force SQLite > to > >> discard its cache? > >> > >> Any help would be appreciated. > >> > >> Regards, > >> > >> Barry Smith > >> ___ > >> sqlite-users mailing list > >> sqlite-users at mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> > > > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Releasing a read (SHARED) lock
You should also check, as R.Smith and Slavin pointed, that you don't accidentally have a BEGIN [DEFERRED] somewhere, as that would trigger exactly this behavior: it will lock on the first select (the first step() actually) On 13.03.2015 02:44, Simon Slavin wrote: > On 13 Mar 2015, at 12:17am, Barry wrote: > >> On 13 March 2015 at 01:21, Dinu Marina wrote: >> >>> You should be also aware of a more common pitfall: unclosed result sets. >>> Any lock is held until you read PAST the last row or you call stmt_finalize >>> (or the equivalent abstraction in your DBAL). Always close select >>> statements. >> Hi Dinu, >> >> Am I correct in my understanding then that it is not enough to call >> sqlite3_reset, I must call sqlite3_finalize after I am done with a >> statement? > In order to dispose of a statement you can do either sqlite3_reset() or > sqlite3_finalize(). The common mistake is to do something like this: > > SELECT * FROM myTable WHERE rowid = 36 > > and then not do either _reset() or _finalize() because you know you have > asked for only one row so you expect SQLite to have done a _finalize() for > you. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Releasing a read (SHARED) lock
Na, it should be the same, everywhere I said finalize you can replace with reset; I had this problem come over and over from people forgetting to finalize (there was no intention to reuse the statement). But reset is the same. My point is just that if you don't have explicit transactions, a read lock is acquired by the first step() and not released until free() or reset() or step() returning SQLITE_MISUSE due to one too many calls and you should check one-rowers (COUNT is the usual suspect for me) first to make sure they are finalized (reset) properly because they are the easiest to miss, since they don't have an ugly loop following. Other than that, there is no reason a lock should be held in autocommit mode. You could debug the reader client this way: create a second connection and try to create a write lock (BEGIN EXCLUSIVE) at various points. It will return SQLITE_BUSY when you have a leaked lock. On 13.03.2015 02:17, Barry wrote: > On 13 March 2015 at 01:21, Dinu Marina wrote: > >> You should be also aware of a more common pitfall: unclosed result sets. >> Any lock is held until you read PAST the last row or you call stmt_finalize >> (or the equivalent abstraction in your DBAL). Always close select >> statements. > > Hi Dinu, > > Am I correct in my understanding then that it is not enough to call > sqlite3_reset, I must call sqlite3_finalize after I am done with a > statement? > > Cheers, > > - Barry > > >> On Mar 12, 2015 11:40 AM, "R.Smith" wrote: >> >>> >>> On 2015-03-12 04:38 AM, Barry wrote: >>> Hello everybody, I have a situation where two processes are accessing the same SQLite database. One process only reads from the database, one process reads >> and writes. These processes keep a single database connection open for the lifetime >> of the process. It seems to me that once the reader process accesses the database (after it performs its first SELECT statement), it maintains a lock on the >> database until the connection is closed (when the program is exited). This >> prevents the writer process from updating the database. >>> Hi Barry, >>> >>> This is the usual situation when one of your transactions in the "reading >>> only" database does not finalize. i.e. you started a transaction there >> and >>> did not end it with either "END TRANSACTION" or "COMMIT" or "ROLLBACK". >>> >>> In the non-WAL DB it will simply hold the lock preventing changes. In the >>> WAL DB it will hold the lock for its own view of the data but let the >> other >>> writer write.. however it won't see the changes for itself. >>> >>> This is very common actually. Just find every transaction you start >>> (explicitly or implicitly) and make sure you end it and when you end it, >>> see what the return value is from SQLite and whether it reports any >> error. >>> HTH! >>> Ryan >>> >>> >>> I tried changing to WAL. This made the writer process able to commit its changes, but now the reader does not see any modifications made to the database until it is restarted (It seems to see a snapshot of the DB at the time of its first read). I am using prepared statements: On opening the DB, I create all my prepared statements. When I need to execute a statement, I bind to the statement, call sqlite3_step (possibly multiple times), then call sqlite3_reset. I >> do not finalise the statements until the program closes. In order to simulate 'save' behaviour, the writer process always holds a transaction open. When the user chooses 'save', the current transaction >> is committed and a new transaction is begun. (I understand that the reader will not see any changes in the uncommitted transaction, but is not >> seeing any committed transactions either). I have checked quite thoroughly through my code and cannot find any instances of statements executed without a sqlite3_reset quickly following. Is this intended behaviour - that once a connection has performed a >> read, it maintains its lock on the database for its lifetime? Is SQLite smart enough to know that the pages it holds in cache of the reader are >> invalid after the writer has made changes to the DB on disk? If this is not the intended behaviour - is there a way I can find out which statements are causing the lock to be held open? Or can I force SQLite >> to discard its cache? Any help would be appreciated. Regards, Barry Smith ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> ___ >>> sqlite-users mailing list >>> sqlite-users at mailinglists.sqlite.org >>>
[sqlite] Releasing a read (SHARED) lock
On Fri, 13 Mar 2015 08:17:26 +0800, Barry wrote: >On 13 March 2015 at 01:21, Dinu Marina wrote: > >> You should be also aware of a more common pitfall: unclosed result sets. >> Any lock is held until you read PAST the last row or you call stmt_finalize >> (or the equivalent abstraction in your DBAL). Always close select >> statements. > > >Hi Dinu, > >Am I correct in my understanding then that it is not enough to call >sqlite3_reset, I must call sqlite3_finalize after I am done with a >statement? sqlite3_reset() is enough to release the state/context of a statement. Aditionally, any BEGIN TRANSACTION should be paired with a COMMIT or ROLLBACK. >Cheers, > > - Barry -- Regards, Cordialement, Groet, Kees Nuyt
[sqlite] Releasing a read (SHARED) lock
On 13 Mar 2015, at 12:17am, Barry wrote: > On 13 March 2015 at 01:21, Dinu Marina wrote: > >> You should be also aware of a more common pitfall: unclosed result sets. >> Any lock is held until you read PAST the last row or you call stmt_finalize >> (or the equivalent abstraction in your DBAL). Always close select >> statements. > > Hi Dinu, > > Am I correct in my understanding then that it is not enough to call > sqlite3_reset, I must call sqlite3_finalize after I am done with a > statement? In order to dispose of a statement you can do either sqlite3_reset() or sqlite3_finalize(). The common mistake is to do something like this: SELECT * FROM myTable WHERE rowid = 36 and then not do either _reset() or _finalize() because you know you have asked for only one row so you expect SQLite to have done a _finalize() for you. Simon.
[sqlite] Optimization Opportunity?
Am Sun, 8 Mar 2015 14:06:51 +0100 schrieb E.Pasma: > Actually query one appears slightly faster, > Searching the PK index is faster as that is always a COVERING index. I was under the impression that the opposite is true, but I wasn't sure about that. > From the secunsary indexes only a part oh the key is used. > Note there is not much use on adding PK as second column in the > additional indexes. It is there anyway a a pointer to the row. You're right, that index doesn't make much sense; in my real application it looks different, what I was showing here was just an example (one that was not very well thought of, obviously). > I agree that it is strange that the execution plan for the two queries > is different, After EXISTS the optimizer might ignore the expression > in the select part of the sub-query. And Query one looks better as it > soes not mention any column names. Personally I'd write SELECT NULL > instead of SELECT *. I prefer "SELECT 1 ...", like in Gunter's post. But that's a matter of taste, of course. Well, my actual point was that the query planner seems to unnecessarily visit the table row in order to read a column value that will be discarded lateron anyway, and that this could probably be optimized out automatically. But my point is obsolete of course when the way it is right now is the faster one. Then again, it's not quite clear why this very strategy is *not* chosen when "SELECT 1 ..." or similar is being used. Not a big deal indeed, just curious. > If speed matters instead of EXIST you can use IN and a list sub-query. > This is superfast now: > > SELECT a1 FROM a WHERE a1 in (SELECT b.a1 FROM b INNER JOIN c > USING(b1) WHERE c.c1=222); > > 0|0|0|SEARCH TABLE a USING INTEGER PRIMARY KEY (rowid=?) > 0|0|0|EXECUTE LIST SUBQUERY 1 > 1|0|1|SEARCH TABLE c USING INTEGER PRIMARY KEY (rowid=?) > 1|1|0|SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?) I avoided IN for a long time, but that must originate from the time when I mostly used Jet (Access) file databases ... with SQLite, it's really fast indeed. Wolfgang
