WAL and pragma uncommitted

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: WAL and pragma uncommitted

Simon Slavin-3
On 19 Jan 2018, at 4:26pm, Hannah Massey <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> wrote:

> On 19 Jan 2018, at 4:26pm, Hannah Massey <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: WAL and pragma uncommitted

Deon Brewis
In reply to this post by Simon Slavin-3
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'12340000'

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:[hidden email]] On Behalf Of Simon Slavin
Sent: Friday, January 19, 2018 8:36 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] WAL and pragma uncommitted

On 19 Jan 2018, at 4:26pm, Hannah Massey <[hidden email]> 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
[hidden email]
https://nam01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7C%7C7304a7658ee04115fe6508d55f5abc8e%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636519765662668955&sdata=o6sR0H3YrZMdFUm9OwJ3CjyYi3J9s0MapK2%2BBeoiBuY%3D&reserved=0
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: WAL and pragma uncommitted

Clemens Ladisch
In reply to this post by Hannah Massey
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: WAL and pragma uncommitted

Clemens Ladisch
In reply to this post by Deon Brewis
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: WAL and pragma uncommitted

Simon Slavin-3
In reply to this post by Deon Brewis
On 19 Jan 2018, at 4:46pm, Deon Brewis <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: WAL and pragma uncommitted

Dan Kennedy-4
In reply to this post by Hannah Massey
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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 <[hidden email]> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: WAL and pragma uncommitted

Dan Kennedy-4
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 <[hidden email]> 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 <[hidden email]> 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
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users