Re: [sqlite] Multi-thread mode question

[Dan Kennedy]

On 02/09/2015 02:18 PM, Hick Gunter wrote:

In serialized mode, SQLite will acquire the mutex when it detects you are "starting to 
use" the database handle (somewhere between entering sqlite3_prepare and the first 
sqlite3_step) and then HANG ON TO IT, NOT LETTING GO until the calling thread is 
"finished" (like when sqlite3_step returns SQLITE_DONE or the thread calls sqlite3_reset 
or sqlite3_finalize).


This is quite inaccurate. Here is the implementation of sqlite3_prepare():

  http://www.sqlite.org/src/artifact/173a5a4991384?ln=792

Notice that all it does is call sqlite3LockAndPrepare(). The 
implemenation of which is here:


  http://www.sqlite.org/src/artifact/173a5a4991384?ln=722-730

It grabs the database mutex, does its work, then releases the database 
mutex.


sqlite3_step() does exactly the same. It grabs the db mutex, does its 
work, releases the mutex.


SQLite does not hold the database mutex between non-nested API calls.

Dan.










In multithread mode, you are taking over this responsibility; if you take care, 
you may nest several selects from different threads into a single transaction, 
but need to be aware of the fact that they will all commit or rollback together.

-Ursprüngliche Nachricht-
Von: Neo Anderson [mailto:neo_in_mat...@msn.com]
Gesendet: Montag, 09. Februar 2015 06:34
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Multi-thread mode question


Does the application work if you configure SQLite to serialized mode?

Yes. But I am confused why serialized mode works while multi-thread mode always 
cause crashes because I also wrap calls around statement handle.


even if you wrap the sqlite3_ calls... you'll need to wrap the entire
lifetime of the statment...

Do I need to do this in serialized mode (suppose I use a single connection 
across multiple threads)?



Date: Sun, 8 Feb 2015 03:31:46 -0800
From: d3c...@gmail.com
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Multi-thread mode question

it's better to use a connection per thread... the connection resource
isn't very big...
even if you wrap the sqlite3_ calls... you'll need to wrap the entire
lifetime of the statment... if you do a execute and then start
stepping and getting values while another thread starts another
statement... that's 3 individual locks, but it doesn't lock the
context of the statement being used... it will lead to bizarre crashes
in the database; similar to double-releasing memory or delayed
reference of memory that has been released.

On Sun, Feb 8, 2015 at 3:00 AM, Dan Kennedy <danielk1...@gmail.com> wrote:


On 02/08/2015 04:30 PM, Neo Anderson wrote:


The doc says:

Multi-thread.
In this mode, SQLite can be safely used by multiple threads provided
that no single database connection is used simultaneously in two or
more threads.

I have a scenario that every sqlite3_calls around a single database
connection is protected by a recursive mutex, but I have very
strange runtime error in sqlite3.c and each time the error occurs at
a different place.

Does this mean the following statement is true:

In muti-thead mode, a single database connection cannot be shared
among threads even if any activity around the connection is protected by a 
mutex.


Not true.

The only difference between multi-threaded and serialized mode is
that, internally, every sqlite3_xxx() API call grabs a recursive
mutex to prevent two threads from simultaneously accessing the database handle 
structure.
i.e. the same thing your code is doing externally.

Note that calls on statement handles (i.e. sqlite3_step(),
sqlite3_column_text() etc.) count as calls on the database handle
that created them. So you need to protect them with the same mutex.

Does the application work if you configure SQLite to serialized mode?

Dan.



___
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

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


___
  Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the origina

Re: [sqlite] Multi-thread mode question

[Neo Anderson]

Thanks for your clarification.


> From: h...@scigames.at
> To: sqlite-users@sqlite.org
> Date: Mon, 9 Feb 2015 07:18:29 +
> Subject: Re: [sqlite] Multi-thread mode question
>
> In serialized mode, SQLite will acquire the mutex when it detects you are 
> "starting to use" the database handle (somewhere between entering 
> sqlite3_prepare and the first sqlite3_step) and then HANG ON TO IT, NOT 
> LETTING GO until the calling thread is "finished" (like when sqlite3_step 
> returns SQLITE_DONE or the thread calls sqlite3_reset or sqlite3_finalize).
>
> In multithread mode, you are taking over this responsibility; if you take 
> care, you may nest several selects from different threads into a single 
> transaction, but need to be aware of the fact that they will all commit or 
> rollback together.
>
> -Ursprüngliche Nachricht-
> Von: Neo Anderson [mailto:neo_in_mat...@msn.com]
> Gesendet: Montag, 09. Februar 2015 06:34
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] Multi-thread mode question
>
>> Does the application work if you configure SQLite to serialized mode?
> Yes. But I am confused why serialized mode works while multi-thread mode 
> always cause crashes because I also wrap calls around statement handle.
>
>> even if you wrap the sqlite3_ calls... you'll need to wrap the entire
>> lifetime of the statment...
> Do I need to do this in serialized mode (suppose I use a single connection 
> across multiple threads)?
>
> --------
>> Date: Sun, 8 Feb 2015 03:31:46 -0800
>> From: d3c...@gmail.com
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] Multi-thread mode question
>>
>> it's better to use a connection per thread... the connection resource
>> isn't very big...
>> even if you wrap the sqlite3_ calls... you'll need to wrap the entire
>> lifetime of the statment... if you do a execute and then start
>> stepping and getting values while another thread starts another
>> statement... that's 3 individual locks, but it doesn't lock the
>> context of the statement being used... it will lead to bizarre crashes
>> in the database; similar to double-releasing memory or delayed
>> reference of memory that has been released.
>>
>> On Sun, Feb 8, 2015 at 3:00 AM, Dan Kennedy <danielk1...@gmail.com> wrote:
>>
>>> On 02/08/2015 04:30 PM, Neo Anderson wrote:
>>>
>>>> The doc says:
>>>>
>>>> Multi-thread.
>>>> In this mode, SQLite can be safely used by multiple threads provided
>>>> that no single database connection is used simultaneously in two or
>>>> more threads.
>>>>
>>>> I have a scenario that every sqlite3_calls around a single database
>>>> connection is protected by a recursive mutex, but I have very
>>>> strange runtime error in sqlite3.c and each time the error occurs at
>>>> a different place.
>>>>
>>>> Does this mean the following statement is true:
>>>>
>>>> In muti-thead mode, a single database connection cannot be shared
>>>> among threads even if any activity around the connection is protected by a 
>>>> mutex.
>>>>
>>>
>>> Not true.
>>>
>>> The only difference between multi-threaded and serialized mode is
>>> that, internally, every sqlite3_xxx() API call grabs a recursive
>>> mutex to prevent two threads from simultaneously accessing the database 
>>> handle structure.
>>> i.e. the same thing your code is doing externally.
>>>
>>> Note that calls on statement handles (i.e. sqlite3_step(),
>>> sqlite3_column_text() etc.) count as calls on the database handle
>>> that created them. So you need to protect them with the same mutex.
>>>
>>> Does the application work if you configure SQLite to serialized mode?
>>>
>>> Dan.
>>>
>>>
>>>
>>> ___
>>> 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
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

Re: [sqlite] Multi-thread mode question

[Hick Gunter]

In serialized mode, SQLite will acquire the mutex when it detects you are 
"starting to use" the database handle (somewhere between entering 
sqlite3_prepare and the first sqlite3_step) and then HANG ON TO IT, NOT LETTING 
GO until the calling thread is "finished" (like when sqlite3_step returns 
SQLITE_DONE or the thread calls sqlite3_reset or sqlite3_finalize).

In multithread mode, you are taking over this responsibility; if you take care, 
you may nest several selects from different threads into a single transaction, 
but need to be aware of the fact that they will all commit or rollback together.

-Ursprüngliche Nachricht-
Von: Neo Anderson [mailto:neo_in_mat...@msn.com]
Gesendet: Montag, 09. Februar 2015 06:34
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Multi-thread mode question

> Does the application work if you configure SQLite to serialized mode?
Yes. But I am confused why serialized mode works while multi-thread mode always 
cause crashes because I also wrap calls around statement handle.

> even if you wrap the sqlite3_ calls... you'll need to wrap the entire
> lifetime of the statment...
Do I need to do this in serialized mode (suppose I use a single connection 
across multiple threads)?


> Date: Sun, 8 Feb 2015 03:31:46 -0800
> From: d3c...@gmail.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Multi-thread mode question
>
> it's better to use a connection per thread... the connection resource
> isn't very big...
> even if you wrap the sqlite3_ calls... you'll need to wrap the entire
> lifetime of the statment... if you do a execute and then start
> stepping and getting values while another thread starts another
> statement... that's 3 individual locks, but it doesn't lock the
> context of the statement being used... it will lead to bizarre crashes
> in the database; similar to double-releasing memory or delayed
> reference of memory that has been released.
>
> On Sun, Feb 8, 2015 at 3:00 AM, Dan Kennedy <danielk1...@gmail.com> wrote:
>
>> On 02/08/2015 04:30 PM, Neo Anderson wrote:
>>
>>> The doc says:
>>>
>>> Multi-thread.
>>> In this mode, SQLite can be safely used by multiple threads provided
>>> that no single database connection is used simultaneously in two or
>>> more threads.
>>>
>>> I have a scenario that every sqlite3_calls around a single database
>>> connection is protected by a recursive mutex, but I have very
>>> strange runtime error in sqlite3.c and each time the error occurs at
>>> a different place.
>>>
>>> Does this mean the following statement is true:
>>>
>>> In muti-thead mode, a single database connection cannot be shared
>>> among threads even if any activity around the connection is protected by a 
>>> mutex.
>>>
>>
>> Not true.
>>
>> The only difference between multi-threaded and serialized mode is
>> that, internally, every sqlite3_xxx() API call grabs a recursive
>> mutex to prevent two threads from simultaneously accessing the database 
>> handle structure.
>> i.e. the same thing your code is doing externally.
>>
>> Note that calls on statement handles (i.e. sqlite3_step(),
>> sqlite3_column_text() etc.) count as calls on the database handle
>> that created them. So you need to protect them with the same mutex.
>>
>> Does the application work if you configure SQLite to serialized mode?
>>
>> Dan.
>>
>>
>>
>> ___
>> 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

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] Multi-thread mode question

[Keith Medcalf]

And those mutexes around statement usage apply the mutex based on the 
underlying connection, not the statement (which is irrelevant)?

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.


>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Neo Anderson
>Sent: Sunday, 8 February, 2015 22:34
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] Multi-thread mode question
>
>> Does the application work if you configure SQLite to serialized mode?
>Yes. But I am confused why serialized mode works while multi-thread mode
>always cause crashes because I also wrap calls around statement handle.
>
>> even if you wrap the sqlite3_ calls... you'll need to wrap the entire
>> lifetime of the statment...
>Do I need to do this in serialized mode (suppose I use a single
>connection across multiple threads)?
>
>
>> Date: Sun, 8 Feb 2015 03:31:46 -0800
>> From: d3c...@gmail.com
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] Multi-thread mode question
>>
>> it's better to use a connection per thread... the connection resource
>isn't
>> very big...
>> even if you wrap the sqlite3_ calls... you'll need to wrap the entire
>> lifetime of the statment... if you do a execute and then start stepping
>and
>> getting values while another thread starts another statement... that's
>3
>> individual locks, but it doesn't lock the context of the statement
>being
>> used... it will lead to bizarre crashes in the database; similar to
>> double-releasing memory or delayed reference of memory that has been
>> released.
>>
>> On Sun, Feb 8, 2015 at 3:00 AM, Dan Kennedy <danielk1...@gmail.com>
>wrote:
>>
>>> On 02/08/2015 04:30 PM, Neo Anderson wrote:
>>>
>>>> The doc says:
>>>>
>>>> Multi-thread.
>>>> In this mode, SQLite can be safely used by multiple threads provided
>that
>>>> no single database connection is used simultaneously in two or more
>>>> threads.
>>>>
>>>> I have a scenario that every sqlite3_calls around a single database
>>>> connection is protected by a recursive mutex, but I have very strange
>>>> runtime error in sqlite3.c and each time the error occurs at a
>different
>>>> place.
>>>>
>>>> Does this mean the following statement is true:
>>>>
>>>> In muti-thead mode, a single database connection cannot be shared
>among
>>>> threads even if any activity around the connection is protected by a
>mutex.
>>>>
>>>
>>> Not true.
>>>
>>> The only difference between multi-threaded and serialized mode is
>that,
>>> internally, every sqlite3_xxx() API call grabs a recursive mutex to
>prevent
>>> two threads from simultaneously accessing the database handle
>structure.
>>> i.e. the same thing your code is doing externally.
>>>
>>> Note that calls on statement handles (i.e. sqlite3_step(),
>>> sqlite3_column_text() etc.) count as calls on the database handle that
>>> created them. So you need to protect them with the same mutex.
>>>
>>> Does the application work if you configure SQLite to serialized mode?
>>>
>>> Dan.
>>>
>>>
>>>
>>> ___
>>> 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
>
>___
>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

Re: [sqlite] Multi-thread mode question

[Neo Anderson]

> Does the application work if you configure SQLite to serialized mode?
Yes. But I am confused why serialized mode works while multi-thread mode always 
cause crashes because I also wrap calls around statement handle.

> even if you wrap the sqlite3_ calls... you'll need to wrap the entire
> lifetime of the statment...
Do I need to do this in serialized mode (suppose I use a single connection 
across multiple threads)?


> Date: Sun, 8 Feb 2015 03:31:46 -0800
> From: d3c...@gmail.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Multi-thread mode question
>
> it's better to use a connection per thread... the connection resource isn't
> very big...
> even if you wrap the sqlite3_ calls... you'll need to wrap the entire
> lifetime of the statment... if you do a execute and then start stepping and
> getting values while another thread starts another statement... that's 3
> individual locks, but it doesn't lock the context of the statement being
> used... it will lead to bizarre crashes in the database; similar to
> double-releasing memory or delayed reference of memory that has been
> released.
>
> On Sun, Feb 8, 2015 at 3:00 AM, Dan Kennedy <danielk1...@gmail.com> wrote:
>
>> On 02/08/2015 04:30 PM, Neo Anderson wrote:
>>
>>> The doc says:
>>>
>>> Multi-thread.
>>> In this mode, SQLite can be safely used by multiple threads provided that
>>> no single database connection is used simultaneously in two or more
>>> threads.
>>>
>>> I have a scenario that every sqlite3_calls around a single database
>>> connection is protected by a recursive mutex, but I have very strange
>>> runtime error in sqlite3.c and each time the error occurs at a different
>>> place.
>>>
>>> Does this mean the following statement is true:
>>>
>>> In muti-thead mode, a single database connection cannot be shared among
>>> threads even if any activity around the connection is protected by a mutex.
>>>
>>
>> Not true.
>>
>> The only difference between multi-threaded and serialized mode is that,
>> internally, every sqlite3_xxx() API call grabs a recursive mutex to prevent
>> two threads from simultaneously accessing the database handle structure.
>> i.e. the same thing your code is doing externally.
>>
>> Note that calls on statement handles (i.e. sqlite3_step(),
>> sqlite3_column_text() etc.) count as calls on the database handle that
>> created them. So you need to protect them with the same mutex.
>>
>> Does the application work if you configure SQLite to serialized mode?
>>
>> Dan.
>>
>>
>>
>> ___
>> 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
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-thread mode question

[J Decker]

it's better to use a connection per thread... the connection resource isn't
very big...
even if you wrap the sqlite3_ calls... you'll need to wrap the entire
lifetime of the statment... if you do a execute and then start stepping and
getting values while another thread starts another statement... that's 3
individual locks, but it doesn't lock the context of the statement being
used... it will lead to bizarre crashes in the database; similar to
double-releasing memory or delayed reference of memory that has been
released.

On Sun, Feb 8, 2015 at 3:00 AM, Dan Kennedy  wrote:

> On 02/08/2015 04:30 PM, Neo Anderson wrote:
>
>> The doc says:
>>
>> Multi-thread.
>> In this mode, SQLite can be safely used by multiple threads provided that
>> no single database connection is used simultaneously in two or more
>> threads.
>>
>> I have a scenario that every sqlite3_calls around a single database
>> connection is protected by a recursive mutex, but I have very strange
>> runtime error in sqlite3.c and each time the error occurs at a different
>> place.
>>
>> Does this mean the following statement is true:
>>
>> In muti-thead mode, a single database connection cannot be shared among
>> threads even if any activity around the connection is protected by a mutex.
>>
>
> Not true.
>
> The only difference between multi-threaded and serialized mode is that,
> internally, every sqlite3_xxx() API call grabs a recursive mutex to prevent
> two threads from simultaneously accessing the database handle structure.
> i.e. the same thing your code is doing externally.
>
> Note that calls on statement handles (i.e. sqlite3_step(),
> sqlite3_column_text() etc.) count as calls on the database handle that
> created them. So you need to protect them with the same mutex.
>
> Does the application work if you configure SQLite to serialized mode?
>
> Dan.
>
>
>
> ___
> 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

Re: [sqlite] Multi-thread mode question

[Dan Kennedy]

On 02/08/2015 04:30 PM, Neo Anderson wrote:

The doc says:

Multi-thread.
In this mode, SQLite can be safely used by multiple threads provided that
no single database connection is used simultaneously in two or more threads.

I have a scenario that every sqlite3_calls around a single database connection 
is protected by a recursive mutex, but I have very strange runtime error in 
sqlite3.c and each time the error occurs at a different place.

Does this mean the following statement is true:

In muti-thead mode, a single database connection cannot be shared among threads 
even if any activity around the connection is protected by a mutex.


Not true.

The only difference between multi-threaded and serialized mode is that, 
internally, every sqlite3_xxx() API call grabs a recursive mutex to 
prevent two threads from simultaneously accessing the database handle 
structure. i.e. the same thing your code is doing externally.


Note that calls on statement handles (i.e. sqlite3_step(), 
sqlite3_column_text() etc.) count as calls on the database handle that 
created them. So you need to protect them with the same mutex.


Does the application work if you configure SQLite to serialized mode?

Dan.


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

[sqlite] Multi-thread mode question

[Neo Anderson]

The doc says:

Multi-thread.
In this mode, SQLite can be safely used by multiple threads provided that
no single database connection is used simultaneously in two or more threads.

I have a scenario that every sqlite3_calls around a single database connection 
is protected by a recursive mutex, but I have very strange runtime error in 
sqlite3.c and each time the error occurs at a different place.

Does this mean the following statement is true:

In muti-thead mode, a single database connection cannot be shared among threads 
even if any activity around the connection is protected by a mutex.

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