Re: [sqlite] Concurrency Question

[Dominique Devienne]

On Sat, Nov 23, 2019 at 4:17 PM Dan Kennedy  wrote:

> > [...] Why is thread_B blocked when doing a read just because SQLite is
> writing to another table? [...]
> > Is this the expected behavior or am I doing something stupid in my code.
> And if so, what to check?
>
> This should only happen if you are using shared-cache mode. Don't use
> shared-cache mode.
>

But I'm forced to use shared-cache for multiple connections to an in-memory
database [1].

This is an important use-case IMHO, and the fact in-memory DBs can't use
WAL-mode,
and benefit from the added concurrency in the face of updates, is a real
bummer IMHO.

[1] https://www.sqlite.org/sharedcache.html#inmemsharedcache
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrency Question

[Dan Kennedy]

On 24/11/62 00:05, Jens Alfke wrote:

On Nov 23, 2019, at 7:17 AM, Dan Kennedy  wrote:

This should only happen if you are using shared-cache mode. Don't use 
shared-cache mode.

Shared-cache mode also breaks Isolation between connections — during a 
transaction, other connections will see the writer’s intermediate state. (IIRC. 
It’s been a few years.)



Only if you explicitly set "PRAGMA read_uncommitted" I think.

Dan.




In my experience, it’s only useful if all connections are read-only, or if 
you’re willing to use your own mutexes to keep writers from screwing up readers 
(in which case you might as well just share a single connection, right?)

—Jens
___
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] Concurrency Question

[Mario M. Westphal]

 

That's very helpful, thank you very much, Daniel. Also to Jens.

 

I've re-read the SQLite documentation for shared cache now. I guess I had
enabled it in the past to increase the performance (many threads in my
application).
Apparently, this had the opposite effect :-/

 

After disabling the shared cache mode, the locks no longer happen. As I had
initially anticipated, the update and the read now run happily in parallel.

 

Thanks again. On a weekend, even!

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrency Question

[Jens Alfke]

> On Nov 23, 2019, at 7:17 AM, Dan Kennedy  wrote:
> 
> This should only happen if you are using shared-cache mode. Don't use 
> shared-cache mode.

Shared-cache mode also breaks Isolation between connections — during a 
transaction, other connections will see the writer’s intermediate state. (IIRC. 
It’s been a few years.)

In my experience, it’s only useful if all connections are read-only, or if 
you’re willing to use your own mutexes to keep writers from screwing up readers 
(in which case you might as well just share a single connection, right?)

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrency Question

[Dan Kennedy]

On 23/11/62 17:02, Mario M. Westphal wrote:

I have an issue with concurrency. This may be the intended behavior of
SQLite. Or I'm doing something wrong.

If somebody could shed a light, I would be thankful.

  


I compile and use SQLite on Windows with VStudio.

I compile it with SQLITE_THREADSAFE=1

At runtime, I use sqlite3_open_v2 () and set the flag SQLITE_OPEN_NOMUTEX.

I use SQLite in WAL mode.

My application uses several threads. Each thread opens its own database
connection.

  


Two threads run in parallel.

Thread A does a lengthy UPDATE to table_A (prepared statement). This update
takes, say, 5 seconds.

Thread B uses a prepared statement to SELECT data from an unrelated table_B.

Thread_B is blocked seconds in sqlite3Step because lockBtreeMutex() blocks
in a mutex.

  


I did not expect this.

Why is thread_B blocked when doing a read just because SQLite is writing to
another table?

  


Is this the expected behavior or am I doing something stupid in my code. And
if so, what to check?


This should only happen if you are using shared-cache mode. Don't use 
shared-cache mode.


Dan.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrency Question

[Pavel Ivanov]

> If I have 2 separate processes, 1 of which will attempt to Read and Write a
> Database (Process A)
> and the 2nd which will only Read the database (Process B), then if Process A
> is in the middle of a Write when Process B tries to read what will happen?

If Process A is in the middle of writing transaction but has not
actually written anything to disk yet (all changes are in cache) then
Process B will proceed reading unlocked. If Process A is in the middle
of committing transaction or it has written some changes to disk
(there're so many changes that they don't fit into cache) then API in
Process B will return SQLITE_BUSY error. But only unless you've called
sqlite3_busy_timeout() with non-zero value. In the latter case Process
B will return SQLITE_BUSY only after mentioned timeout has gone and
Process A does not finish transaction yet. And when SQLITE_BUSY is
returned then indeed it's up to you to try again later.

> And Vice versa, What happens if Process B is reading while A tries to write?

Process A will continue unblocked until it will have to actually write
to disk. At this point it will wait for Process B to finish its
reading and after that it will actually write everything it needs.


Pavel

On Fri, Aug 7, 2009 at 11:50 AM, JimmyKryptonite wrote:
>
> I'm looking to start a project using SQLite to handle some database
> transactions.  I have question about how SQLite handles Concurrency.  I read
> up on SQLite.org about the file locking and concurrency but I didn't really
> get the info I wanted (at least I didn't walk away with an answer in the
> terms I wanted it).
>
> My question is the following:
>
> If I have 2 separate processes, 1 of which will attempt to Read and Write a
> Database (Process A)
> and the 2nd which will only Read the database (Process B), then if Process A
> is in the middle of a Write when Process B tries to read what will happen?
> Will the Read request be blocked and Process B will wait or will SQLite
> return some kind of Busy Error Code to process B and it is up to Process B
> to try again later?
>
> And Vice versa, What happens if Process B is reading while A tries to write?
> Same answer as above?
>
> I'm very much a newbie to SQLite so patience is requested.
>
> Thanks in advance
> --
> View this message in context: 
> http://www.nabble.com/Concurrency-Question-tp24867278p24867278.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users