Re: [sqlite] WAL and pragma uncommitted

[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 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.



Nothing else to do. If sqlite3_close() returns SQLITE_BUSY, then SQLite 
thinks there are one or more unfinalized statements or ongoing backup 
operations (open sqlite3_backup* handles). For debugging purposes, use 
sqlite3_next_stmt() to find the unfinalized statements after close() 
returns SQLITE_BUSY, and perhaps sqlite3_sql() to identify where in the 
app they come from.


  http://sqlite.org/c3ref/next_stmt.html
  http://sqlite.org/c3ref/expanded_sql.html

Dan.






Many Thanks in advance

On 22 January 2018 at 09:37, Hannah Massey  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  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



___
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

[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 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  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  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

[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 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

[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, 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

Re: [sqlite] WAL and pragma uncommitted

[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 least you can get is statement-level consistency.  If that PRAGMA 
is on you might get (!,1) or (2,2), you just can’t predict which you’ll get.

Simon.
___
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

[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 any multi-threaded
accesses), so other threads can see only the complete changes made
while a lock was held.  In particular, sqlite3_step() takes a single
lock around all VDBE statements it executes, so what you end up with is
statement-level atomicy.


Regards,
Clemens
___
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

[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
the same data structure need locking.  This locking is done inside most
sqlite3_xxx() function calls (see sqlite3_db_mutex()), and independent
from the transaction locking.

So for maximum performance, use one connection per thread without
shared-cache mode.  This allows you to run all reading threads in
parallel, even when they are accessing the same database, and to use
SQLITE_OPEN_NOMUTEX to avoid the locking overhead.  (If the lock is
not actually contested, the locking overhead would probably be too
small to notice.)


Regards,
Clemens
___
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

[Deon Brewis]

What is the level of consistency (or rather inconsistency) for read_uncommitted?

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 
?


And if so, what about something like:
UPDATE Woz SET Foo=2, Bar=x'12345789'

Can a read_uncommitted thread read:
Foo=x'1234'

i.e. A partially updated column?


I would assume that for a transactional update across 2 rows all bets will be 
off.

What if a row 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 <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] WAL and pragma uncommitted

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
https://nam01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users=02%7C01%7C%7C7304a7658ee04115fe6508d55f5abc8e%7C84df9e7fe9f640afb435%7C1%7C0%7C636519765662668955=o6sR0H3YrZMdFUm9OwJ3CjyYi3J9s0MapK2%2BBeoiBuY%3D=0
___
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

[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 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

Re: [sqlite] WAL and pragma uncommitted

[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 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] WAL and pragma uncommitted

[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 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