Is it safe to use same sqlite connection sequentially between threads ?

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

Is it safe to use same sqlite connection sequentially between threads ?

sanhua.zh
Here is a sample.
As for a specific sqlite connection,
1. sql is executed in thread A
2. sql is prepared in thread B
3. Prepared statement in 2. is stepped and finalized in thread C


All 1. 2. 3. steps are run sequentially, which means that the step 2 runs after step 1 finished and step 3 runs after step 2 finished theoretically .
Also, I can make sure the memory order between threads.


Then, is it a safe way to use sqlite connection ? If not, what make it impossible?
_______________________________________________
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: Is it safe to use same sqlite connection sequentially between threads ?

Clemens Ladisch
sanhua.zh wrote:
> All 1. 2. 3. steps are run sequentially, which means that the step 2
> runs after step 1 finished and step 3 runs after step 2 finished
> theoretically .
> Also, I can make sure the memory order between threads.
>
> Then, is it a safe way to use sqlite connection ?

Yes.

Multi-threading problems are caused by multiple threads accessing the
same data at the same time.  If code in multiple threads is serialized,
it is, for practical purpose, identical to single-threaded code.


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: Is it safe to use same sqlite connection sequentially between threads ?

Gwendal Roué-2

> Le 15 août 2017 à 08:44, Clemens Ladisch <[hidden email]> a écrit :
>
> sanhua.zh wrote:
>> All 1. 2. 3. steps are run sequentially, which means that the step 2
>> runs after step 1 finished and step 3 runs after step 2 finished
>> theoretically .
>> Also, I can make sure the memory order between threads.
>>
>> Then, is it a safe way to use sqlite connection ?
>
> Yes.
>
> Multi-threading problems are caused by multiple threads accessing the
> same data at the same time.  If code in multiple threads is serialized,
> it is, for practical purpose, identical to single-threaded code.

Serialized accesses from multiple threads is OK when the connection is in the "Multi-thread" or "Serialized" threading modes, but not in the "Single-thread" threading mode.

Have a look at https://www.sqlite.org/threadsafe.html for detailed information.

Gwendal Roué

_______________________________________________
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: Is it safe to use same sqlite connection sequentially between threads ?

Clemens Ladisch
Gwendal Roué wrote:
> Serialized accesses from multiple threads is OK when the connection is
> in the "Multi-thread" or "Serialized" threading modes, but not in the
> "Single-thread" threading mode.

<http://www.sqlite.org/threadsafe.html> says:
| 1. *Single-thread*. In this mode, all mutexes are disabled and SQLite
|    is unsafe to use in more than a single thread at once.

When the accesses are serialized, they are not _at once_.


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: Is it safe to use same sqlite connection sequentially between threads ?

Gwendal Roué-2

> Le 16 août 2017 à 08:38, Clemens Ladisch <[hidden email]> a écrit :
>
> Gwendal Roué wrote:
>> Serialized accesses from multiple threads is OK when the connection is
>> in the "Multi-thread" or "Serialized" threading modes, but not in the
>> "Single-thread" threading mode.
>
> <http://www.sqlite.org/threadsafe.html> says:
> | 1. *Single-thread*. In this mode, all mutexes are disabled and SQLite
> |    is unsafe to use in more than a single thread at once.
>
> When the accesses are serialized, they are not _at once_.

According to your interpretation, "Single-thread" and "Multi-thread" modes are equivalent:

> 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.

Assuming there *is* a difference, I thus believe you are mistaken. Now documentation is ambiguous, I agree. My cautious interpretation says that only "Multi-thread" and "Serialized" modes are safe for serialized accesses from multiple threads (where "Multi-thread" is enough, and "Serialized" does too much).

Finally, we don't know which threading mode the OP is using. We don't even know if OP knows about threading modes. This topic may interest him.

Gwendal

_______________________________________________
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: Is it safe to use same sqlite connection sequentially between threads ?

Clemens Ladisch
Gwendal Roué wrote:

>> Le 16 août 2017 à 08:38, Clemens Ladisch <[hidden email]> a écrit :
>> Gwendal Roué wrote:
>>> Serialized accesses from multiple threads is OK when the connection is
>>> in the "Multi-thread" or "Serialized" threading modes, but not in the
>>> "Single-thread" threading mode.
>>
>> <http://www.sqlite.org/threadsafe.html> says:
>> | 1. *Single-thread*. In this mode, all mutexes are disabled and SQLite
>> |    is unsafe to use in more than a single thread at once.
>>
>> When the accesses are serialized, they are not _at once_.
>
> According to your interpretation, "Single-thread" and "Multi-thread" modes are equivalent:
>
>> 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.

"Multi-thread" mode allows concurrent accesses to different connections
(which might end up accessing the same internal, global variables).

When all accesses are serialized, there are not concurrent accesses at all.

> Finally, we don't know which threading mode the OP is using.

Serializing everything, as the OP is doing, is safe in any mode.


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: Is it safe to use same sqlite connection sequentially between threads ?

Keith Medcalf
In reply to this post by Gwendal Roué-2

On Wednesday, 16 August, 2017 00:11, Gwendal Roué <[hidden email]> wrote:
>> Le 15 août 2017 à 08:44, Clemens Ladisch <[hidden email]> a écrit :

>> sanhua.zh wrote:

>>> All 1. 2. 3. steps are run sequentially, which means that the step 2
>>> runs after step 1 finished and step 3 runs after step 2 finished
>>> theoretically .
>>> Also, I can make sure the memory order between threads.
>>>
>>> Then, is it a safe way to use sqlite connection ?

>> Yes.

>> Multi-threading problems are caused by multiple threads accessing the
>> same data at the same time.  If code in multiple threads is serialized,
>> it is, for practical purpose, identical to single-threaded code.

>Serialized accesses from multiple threads is OK when the connection
>is in the "Multi-thread" or "Serialized" threading modes, but not in
>the "Single-thread" threading mode.

>Have a look at https://www.sqlite.org/threadsafe.html for detailed
>information.

Basically, the restriction is on simultaneous entry from multiple threads to objects on the same connection (that is, simultaneous calls to prepare/bind/step/colun/reset/finalize from multiple threads at the same time).  The default is to SERIALIZE any such accesses to ensure that the single-entrance requirement for each connection is enforced through built-in mutexes.

Setting "multithreaded" mode disables these checks in the SQLite3 library and it is up to the application level code to ensure the single-entrance per connection is enforced at the application level.  If the application makes a "mistake" and violates the rules then corruption and not nice consequences will ensue.  However, since the SQLite3 core is no longer passing through a mutex on each entry, the code runs slightly faster.

Setting "single-threaded" mode disables all mutexes in the library and is intended for use when the code is entirely single threaded.  Since there is no protection at all (even for the VFS layer) if you perform any kind of multiple-entrance nasty consequences may ensue.

Generally speaking if you leave the default "serialized" mode, then you are protected against accidental re-entrancy application errors on multiple threads at a price penalty for the extra safety.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
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: Is it safe to use same sqlite connection sequentially between threads ?

Keith Medcalf
In reply to this post by Gwendal Roué-2

>According to your interpretation, "Single-thread" and "Multi-thread"
>modes are equivalent:

>> 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.

>Assuming there *is* a difference, I thus believe you are mistaken.
>Now documentation is ambiguous, I agree. My cautious interpretation
>says that only "Multi-thread" and "Serialized" modes are safe for
>serialized accesses from multiple threads (where "Multi-thread" is
>enough, and "Serialized" does too much).

I believe there is a difference and that has to do with cache management, internal b-tree page cache management, and the VFS layer.  When set to multi-threaded, there are still some mutexes used in the VFS and cache management code as well as an expectation of multiple simultaneous access to the cache.  When in single-threaded mode, these protections and management is unnecessary.  So single-threaded is not entirely equivalent to multi-threaded.

Whether globally single-entrant serialized multithreaded access at the application level will not cause problems if the SQLite engine is in single-threaded mode is something that can only be answered by Richard of one of the other developers.

>Finally, we don't know which threading mode the OP is using. We don't
>even know if OP knows about threading modes. This topic may interest
>him.

Generally speaking I would leave the access mode as the default serialized unless there is a specific performance related reason to change that.  It provides the most safety.




_______________________________________________
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: Is it safe to use same sqlite connection sequentially between threads ?

Dominique Devienne
In reply to this post by Keith Medcalf
On Wed, Aug 16, 2017 at 4:15 PM, Keith Medcalf <[hidden email]> wrote:

> Setting "multithreaded" mode disables these checks in the SQLite3 library
> and it is up to the application level code to ensure the single-entrance
> per connection is enforced at the application level.  If the application
> makes a "mistake" and violates the rules then corruption and not nice
> consequences will ensue.  However, since the SQLite3 core is no longer
> passing through a mutex on each entry, the code runs slightly faster.
>
> Setting "single-threaded" mode disables all mutexes in the library and is
> intended for use when the code is entirely single threaded.  Since there is
> no protection at all (even for the VFS layer) if you perform any kind of
> multiple-entrance nasty consequences may ensue.
>
> Generally speaking if you leave the default "serialized" mode, then you
> are protected against accidental re-entrancy application errors on multiple
> threads at a price penalty for the extra safety.
>

+1 Keith. Very useful, thanks! I actually find this clearer than the doc on
this subject, which is a bit too terse IMHO.

I understand one doesn't want to explain implementation details too much in
doc,
but Keith's "annotated" version made it "click" in my head better, when the
doc didn't.

The doc is not wrong of course, just too terse for my liking (as a
developer). My $0.02. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users