Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

On 08/26/2016 01:20 PM, David Raymond wrote: [snip] > Many do not persist after closing your connection, including synchronous and > foreign_keys that you mentioned. In fact, I'd say that the number that do > persist is pretty small, and those that do usually require a vacuum or such > after

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

for subsequent connections. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Adam Jensen Sent: Friday, August 26, 2016 12:55 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Locking databases - Possibly (probably?) a dumb

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

On 08/25/2016 04:41 PM, Adam Jensen wrote: > On 08/20/2016 01:01 PM, Simon Slavin wrote: >> On 20 Aug 2016, at 5:56pm, Lev wrote: > [snip] >>> So this 'setting' is stored in the database file? Is it enough to do the >>> PRAGMA when the database is created? >> >> Yes and

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

On 08/20/2016 01:01 PM, Simon Slavin wrote: > On 20 Aug 2016, at 5:56pm, Lev wrote: [snip] >> So this 'setting' is stored in the database file? Is it enough to do the >> PRAGMA when the database is created? > > Yes and yes, but do it this way. > > 1) Create the database

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

One minor optional addition below On Sat, 20 Aug 2016 18:01:46 +0100, Simon Slavin wrote: > >On 20 Aug 2016, at 5:56pm, Lev wrote: [...] >> So this 'setting' is stored in the database file? Is it enough to do the >> PRAGMA when the database is

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

On Sat, 20 Aug 2016 18:01:46 +0100 Simon Slavin wrote: > Yes and yes, but do it this way. > > 1) Create the database file by opening it. > 2) Do something that makes the file non-blank, like creating a table. > 3) Issue "PRAGMA journal_mode=WAL"

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

On 20 Aug 2016, at 5:56pm, Lev wrote: > I read in the documentation: > > The WAL journaling mode uses a write-ahead log instead of a rollback journal > to implement transactions. The WAL journaling mode is persistent; after being > set it stays in effect across multiple

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

On Sat, 6 Aug 2016 17:03:30 -0400 Richard Hipp wrote: > Doing "PRAGMA journal_mode=WAL;" on your database (just once, perhaps > from a command-line shell) will fix this for you. I read in the documentation: The WAL journaling mode uses a write-ahead

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

Rob, At 18:27 07/08/2016, you wrote: Too little sleep and far too much coffee. I was in the same situation, multiplying by 2 instead of dividing, as Ryan pointed out. Nice to see that WAL fits your use case. I for one found it rock solid and very useful. -- Jean-Christophe

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

Jean-Christophe Thanks for the update on wal-mode. Your explanation is clear and makes sense to us. We can see what we would have a 224MB -wal file, we experimented with killing processes whilst updating and generally messing around and SQLite did what is was supposed to do. I wouldn’t say

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

Ryan, Thanks for the update. We have done a few more tests during the day and not had any issues to date. This is still on a test version but we are getting a warm, cuddly feeling about using WAL mode. The -wal file grows as you describe and you have explained it very well. We were groping

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

On 2016/08/07 8:55 AM, Rob Willett wrote: Richard, Ryan, Thanks for this. We were dimly aware of WAL but until now hadn’t needed to use it. We’ve done a quick check with it and it *seems* to work on a test database. We’ve all read the docs again and paid attention to

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

Rob, At 08:55 07/08/2016, you wrote: We think that using WAL mode works for us, indeed inspection seems to indicate it does, but the size of the -wal file appears to be far larger than would be expected. Is there a problem here? It doesn't appear to be a problem but would welcome any

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

Richard, Ryan, Thanks for this. We were dimly aware of WAL but until now hadn’t needed to use it. We’ve done a quick check with it and it *seems* to work on a test database. We’ve all read the docs again and paid attention to https://www.sqlite.org/wal.html#bigwal To test if it works we

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

On 2016/08/06 10:50 PM, Rob Willett wrote: Our understanding of this is that many processes can READ the database at the same time but NO process can INSERT/UPDATE if another is reading. We had thought that one process can write and multiple processes can read. Our reading (no pun

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

On 8/6/16, Rob Willett wrote: > > What we have now found is that when we are running the analytics query > in one Perl process, we can no longer UPDATE the main database through > another Perl process. We are getting “database is locked” errors. Doing "PRAGMA

[sqlite] Locking databases - Possibly (probably?) a dumb question

Hi, We’ve been using Sqlite though Perl for some time now and have started to get more adventurous. Our SQLite database is around 32GB in size, is created and manipulated by a single Perl process and is working well. What we now want to do is mine the database using a very long running