Concurrency Question

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

Concurrency Question

Mario M. Westphal-2
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?

_______________________________________________
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: Concurrency Question

Dan Kennedy-4

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

Re: Concurrency Question

Jens Alfke-2

> On Nov 23, 2019, at 7:17 AM, Dan Kennedy <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Concurrency Question

Mario M. Westphal-2
In reply to this post by Mario M. Westphal-2
 

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

Re: Concurrency Question

Dan Kennedy-4
In reply to this post by Jens Alfke-2

On 24/11/62 00:05, Jens Alfke wrote:
>> On Nov 23, 2019, at 7:17 AM, Dan Kennedy <[hidden email]> 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
> [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: Concurrency Question

Dominique Devienne
In reply to this post by Dan Kennedy-4
On Sat, Nov 23, 2019 at 4:17 PM Dan Kennedy <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users