Re: [sqlite] WAL and pragma uncommitted

2018-02-02 Thread Dan Kennedy
On 02/02/2018 10:00 PM, Hannah Massey wrote: 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

Re: [sqlite] WAL and pragma uncommitted

2018-02-02 Thread Hannah Massey
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

Re: [sqlite] WAL and pragma uncommitted

2018-01-22 Thread Hannah Massey
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 wrote: > On 01/19/2018 11:26

Re: [sqlite] WAL and pragma uncommitted

2018-01-20 Thread Dan Kennedy
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

Re: [sqlite] WAL and pragma uncommitted

2018-01-19 Thread Simon Slavin
On 19 Jan 2018, at 4:46pm, Deon Brewis wrote: > If you start with: > INSERT INTO Woz(Foo, Bar) Values(1,1) > > And a (normal) writer thread updates the 2 columns: > UPDATE Woz SET Foo=2, Bar=2 > > Can a read_uncommitted thread read the value from the row as: > Foo=1, Bar=2 > ? No. The very l

Re: [sqlite] WAL and pragma uncommitted

2018-01-19 Thread Clemens Ladisch
Deon Brewis wrote: > What is the level of consistency (or rather inconsistency) for > read_uncommitted? In read_uncommited mode, read-only transactions to not take the database file lock. However, most sqlite3_xxx() function calls still lock the in-memory database object(s) (this is required for

Re: [sqlite] WAL and pragma uncommitted

2018-01-19 Thread Clemens Ladisch
Hannah Massey wrote: > If I use separate connections for the reading threads then is there an > advantage to using "shared cache" for those connections? The shared cache would be useful to reduce memory usage (which should not be a concern except in embedded systems), but concurrent accesses to th

Re: [sqlite] WAL and pragma uncommitted

2018-01-19 Thread Deon Brewis
w was deleted, can a read_uncommitted read cause a crash? - Deon -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Friday, January 19, 2018 8:36 AM To: SQLite mailing list Subject: Re: [sqlite] WAL and pragma uncommitted On 19

Re: [sqlite] WAL and pragma uncommitted

2018-01-19 Thread Hannah Massey
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 wrote: > On 19 Jan 2018, at 4:26pm, Hannah Massey wrote: > > > Will #pragma uncommitted work in WA

Re: [sqlite] WAL and pragma uncommitted

2018-01-19 Thread Simon Slavin
On 19 Jan 2018, at 4:26pm, Hannah Massey 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

[sqlite] WAL and pragma uncommitted

2018-01-19 Thread Hannah Massey
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 m