Write-ahead logging and database locking

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Write-ahead logging and database locking

David Barrett-4
I'm reading up on the new write-ahead logging feature, and I'm unclear
on one point: does WAL only help alleviate multi-threaded locking (by
allowing other threads to continue reading while one is writing), or
does WAL also help between multiple processes?

My understanding could be completely wrong, but I think the following
statements are true -- can you help fact check me?  (I've read
http://www.sqlite.org/lockingv3.html but it doesn't seem to have been
updated for WAL -- assuming any updates are even needed.)

1) Two processes can read from the same database at the same time.

2) However, one process cannot read from the database while another is
writing -- WAL is irrelevant here.

3) One process can have two threads that read from the database from the
same time.

4) And thanks to WAL, now one process can have one thread writing and
any number of threads reading at the same time.

5) However, WAL does not allow multiple threads to write at the same time.

Is this right?  Or am I totally off?  Are there any good pages outlining
the intersection between multi-process database locking and WAL?  Thanks!

-david
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Write-ahead logging and database locking

Dan Kennedy-4

On Aug 9, 2010, at 11:30 AM, David Barrett wrote:

> I'm reading up on the new write-ahead logging feature, and I'm unclear
> on one point: does WAL only help alleviate multi-threaded locking (by
> allowing other threads to continue reading while one is writing), or
> does WAL also help between multiple processes?
>
> My understanding could be completely wrong, but I think the following
> statements are true -- can you help fact check me?  (I've read
> http://www.sqlite.org/lockingv3.html but it doesn't seem to have been
> updated for WAL -- assuming any updates are even needed.)
>
> 1) Two processes can read from the same database at the same time.
>
> 2) However, one process cannot read from the database while another is
> writing -- WAL is irrelevant here.
>
> 3) One process can have two threads that read from the database from  
> the
> same time.
>
> 4) And thanks to WAL, now one process can have one thread writing and
> any number of threads reading at the same time.
>
> 5) However, WAL does not allow multiple threads to write at the same  
> time.
>
> Is this right?  Or am I totally off?  Are there any good pages  
> outlining
> the intersection between multi-process database locking and WAL?  
> Thanks!

Unless shared-cache mode is turned on, multiple threads each using
their own sqlite3* connection should behave in the same way as
multiple processes do wrt to sqlite locking.

If you do enable shared-cache mode, the rules here apply:

   http://www.sqlite.org/sharedcache.html

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Write-ahead logging and database locking

Dan Kennedy-4

On Aug 9, 2010, at 12:07 PM, Dan Kennedy wrote:

>
> On Aug 9, 2010, at 11:30 AM, David Barrett wrote:
>
>> I'm reading up on the new write-ahead logging feature, and I'm  
>> unclear
>> on one point: does WAL only help alleviate multi-threaded locking (by
>> allowing other threads to continue reading while one is writing), or
>> does WAL also help between multiple processes?
>>
>> My understanding could be completely wrong, but I think the following
>> statements are true -- can you help fact check me?  (I've read
>> http://www.sqlite.org/lockingv3.html but it doesn't seem to have been
>> updated for WAL -- assuming any updates are even needed.)
>>
>> 1) Two processes can read from the same database at the same time.
>>
>> 2) However, one process cannot read from the database while another  
>> is
>> writing -- WAL is irrelevant here.
>>
>> 3) One process can have two threads that read from the database from
>> the
>> same time.
>>
>> 4) And thanks to WAL, now one process can have one thread writing and
>> any number of threads reading at the same time.
>>
>> 5) However, WAL does not allow multiple threads to write at the same
>> time.
>>
>> Is this right?  Or am I totally off?  Are there any good pages
>> outlining
>> the intersection between multi-process database locking and WAL?
>> Thanks!
>
> Unless shared-cache mode is turned on, multiple threads each using
> their own sqlite3* connection should behave in the same way as
> multiple processes do wrt to sqlite locking.

I should be clearer: The above was meant to imply that (2)
is not a true statement. The others are all correct.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Write-ahead logging and database locking

David Barrett-4
On 08/08/2010 10:09 PM, Dan Kennedy wrote:
>>> 2) However, one process cannot read from the database while another
>>> is writing -- WAL is irrelevant here.
>>
>> Unless shared-cache mode is turned on, multiple threads each using
>> their own sqlite3* connection should behave in the same way as
>> multiple processes do wrt to sqlite locking.
>
> I should be clearer: The above was meant to imply that (2)
> is not a true statement. The others are all correct.

Very interesting!  To confirm I understand, if shared-cache mode is
enabled, then one process can read while another process is writing.

Also, I see in the documentation that when shared-cache mode is enabled,
SQLite uses table-level locking (instead of the default file-locking).
Taken all together, it suggests that you can get table-level locking
*and* write-ahead logging *and* atomic multi-table commits -- all within
a single file -- simply by enabling shared cache mode.

Am I reading this correctly, or does shared-cache table-level locking
still require that each table be put in different files as described here:

        http://www.sqlite.org/version3.html

(If so, then it means the only way to get table-locking with WAL is to
put the tables in different database files, but then WAL "disadvantage
#3" says you lose atomicity.)

Thanks Dan, I really appreciate your help!

-david
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Write-ahead logging and database locking

Pavel Ivanov-2
> Also, I see in the documentation that when shared-cache mode is enabled,
> SQLite uses table-level locking (instead of the default file-locking).
> Taken all together, it suggests that you can get table-level locking
> *and* write-ahead logging *and* atomic multi-table commits -- all within
> a single file -- simply by enabling shared cache mode.

Beware that shared cache mode turns on table-level locking only inside
one process between several connections. Inter-process locking is
still file-level. Also beware that in shared cache mode all
connections to the same database share one mutex on the cache, so all
calls to sqlite3_step on all connections to the same database will be
effectively serialized.


Pavel

On Tue, Aug 10, 2010 at 2:45 PM, David Barrett <[hidden email]> wrote:

> On 08/08/2010 10:09 PM, Dan Kennedy wrote:
>>>> 2) However, one process cannot read from the database while another
>>>> is writing -- WAL is irrelevant here.
>>>
>>> Unless shared-cache mode is turned on, multiple threads each using
>>> their own sqlite3* connection should behave in the same way as
>>> multiple processes do wrt to sqlite locking.
>>
>> I should be clearer: The above was meant to imply that (2)
>> is not a true statement. The others are all correct.
>
> Very interesting!  To confirm I understand, if shared-cache mode is
> enabled, then one process can read while another process is writing.
>
> Also, I see in the documentation that when shared-cache mode is enabled,
> SQLite uses table-level locking (instead of the default file-locking).
> Taken all together, it suggests that you can get table-level locking
> *and* write-ahead logging *and* atomic multi-table commits -- all within
> a single file -- simply by enabling shared cache mode.
>
> Am I reading this correctly, or does shared-cache table-level locking
> still require that each table be put in different files as described here:
>
>        http://www.sqlite.org/version3.html
>
> (If so, then it means the only way to get table-locking with WAL is to
> put the tables in different database files, but then WAL "disadvantage
> #3" says you lose atomicity.)
>
> Thanks Dan, I really appreciate your help!
>
> -david
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Write-ahead logging and database locking

LincolnBurrows
This post has NOT been accepted by the mailing list yet.
In reply to this post by David Barrett-4
If the file belongs to the writing process but does not belong to the read-only process, the read-only process is blocked from opening read-only. read more: http://sqliteviewer.com/blog/write-ahead-logging-in-sqlite.html