Re: [sqlite] WAL and pragma uncommitted
Does anybody know why I would be getting SQLITE_BUSY when closing the database connection in each thread even though I have finalized all prepared statements and am not doing any outstanding commands on the database? I have tests that need to copy the database file once the application has finished and then delete it to clear up and they cannot delete the file because "it is in use by another process". Nothing else is accessing this database. Is there something else that must be done when working in WAL mode or when accessing from multiple threads as it has only started having this problem since I changed to having multiple reader threads and one write thread. Many Thanks in advance On 22 January 2018 at 09:37, Hannah Massey <hannah.bea...@gmail.com> wrote: > ok thanks. So looks like I'm going to try WAL mode with one connection to > the database per thread and accessing the database using > SQLITE_OPEN_NOMUTEX., no shared-cache mode, no pragma read_uncommitted. > Thanks for the advice. > > On 20 January 2018 at 19:49, Dan Kennedy <danielk1...@gmail.com> wrote: > >> On 01/19/2018 11:26 PM, Hannah Massey wrote: >> >>> Currently we access a single SQLite database in a single thread but I am >>> working on changing this as performance has become a real problem. We >>> will >>> be using WAL mode and there will be one thread for writes and multiple >>> threads for reads. For many cases, speed will be of a priority and it >>> will >>> not matter if the data returned from a read is slightly out of date so I >>> can considering using #pragma uncommitted in some of the reader threads. >>> Will #pragma uncommitted work in WAL mode and will it have the effect I'm >>> looking for (where the read will be faster because it can ignore the >>> recently written information in the WAL File) and simply use the database >>> file only? >>> >> >> Don't use "PRAGMA read_uncommitted". It is a no-op unless you turn on >> shared-cache mode. And using shared-cache mode reduces the concurrency >> provided by using wal mode. >> >> Dan. >> >> >> >> ___ >> 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] WAL and pragma uncommitted
ok thanks. So looks like I'm going to try WAL mode with one connection to the database per thread and accessing the database using SQLITE_OPEN_NOMUTEX., no shared-cache mode, no pragma read_uncommitted. Thanks for the advice. On 20 January 2018 at 19:49, Dan Kennedy <danielk1...@gmail.com> wrote: > On 01/19/2018 11:26 PM, Hannah Massey wrote: > >> Currently we access a single SQLite database in a single thread but I am >> working on changing this as performance has become a real problem. We will >> be using WAL mode and there will be one thread for writes and multiple >> threads for reads. For many cases, speed will be of a priority and it will >> not matter if the data returned from a read is slightly out of date so I >> can considering using #pragma uncommitted in some of the reader threads. >> Will #pragma uncommitted work in WAL mode and will it have the effect I'm >> looking for (where the read will be faster because it can ignore the >> recently written information in the WAL File) and simply use the database >> file only? >> > > Don't use "PRAGMA read_uncommitted". It is a no-op unless you turn on > shared-cache mode. And using shared-cache mode reduces the concurrency > provided by using wal mode. > > Dan. > > > > ___ > 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] WAL and pragma uncommitted
ok thanks for your advice. If I use separate connections for the reading threads then is there an advantage to using "shared cache" for those connections? On 19 January 2018 at 16:35, Simon Slavin <slav...@bigfraud.org> wrote: > On 19 Jan 2018, at 4:26pm, Hannah Massey <hannah.bea...@gmail.com> wrote: > > > Will #pragma uncommitted work in WAL mode and will it have the effect I'm > > looking for (where the read will be faster because it can ignore the > > recently written information in the WAL File) and simply use the database > > file only? > > The command you’re talking about is > > PRAGMA read_uncommitted = boolean > > You may find you don’t need it. Switch to WAL mode and try it without > that PRAGMA. It works more or less the way you described: one connection > can write while other read, and nothing will block anything else. > > Pay a lot of attention to how many connections you’re using. You might > want one connection for the writing, and another for all the reading > threads. Or you might want separate connections for the reading threads > too. Testing various approaches will let you find the solution which best > suits your programming and timing requirements. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WAL and pragma uncommitted
Currently we access a single SQLite database in a single thread but I am working on changing this as performance has become a real problem. We will be using WAL mode and there will be one thread for writes and multiple threads for reads. For many cases, speed will be of a priority and it will not matter if the data returned from a read is slightly out of date so I can considering using #pragma uncommitted in some of the reader threads. Will #pragma uncommitted work in WAL mode and will it have the effect I'm looking for (where the read will be faster because it can ignore the recently written information in the WAL File) and simply use the database file only? Thanks ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
