Re: [sqlite] When do I need SQLITE_THREADSAFE?

[Jay A. Kreibich]

On Wed, Oct 06, 2010 at 08:07:00AM +0200, Michele Pradella scratched on the 
wall:
>  So let me know if I understand:
>
>   1. SQLITE_THREADSAFE=0: lock are disabled at all, and you can use
>  this if the application  is not multi thread, so if there's only
>  one thread that can use the SQLITE library
>   2. SQLITE_THREADSAFE=1: is the highest level of thread safety, so if
>  the slowest mode but you do not have to care about multiple access
>  to the SQLITE library from different thread. You need this in a
>  multi thread application to be sure to avoid damage to the DB
>   3. SQLITE_THREADSAFE=2: it's a compromise between thread safety and
>  speed. The library are thread safe but you have to avoid that two
>  thread can use at the same time the same DB connection. So if you
>  have for example 3 thread and every thread access the library with
>  it own connection there's no problem So you can use two different
>  DB connection from two thread at the same time without problem. Is
>  that right?

  As I understand it, yes, that's correct.

  Further, my understanding is that the performance difference between
  "multithread" (=2) and "serial" (=1) is very minimal on most systems,
  especially next to any physical I/O costs.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When do I need SQLITE_THREADSAFE?

[Dustin Sallings]

On Oct 5, 2010, at 23:50, Michele Pradella wrote:

>  I check in the sqlite3.c code for the SQLITE_THREADSAFE. I can't found 
> any difference between SQLITE_THREADSAFE=1 and SQLITE_THREADSAFE=2. I 
> found only differences if SQLITE_THREADSAFE=0 or SQLITE_THREADSAFE>0
> Have I miss something?


Look for bCoreMutex and bFullMutex.  bFullMutex is in use when 
SQLITE_THREADSAFE=1 and bCoreMutex is in use for 1 or 2.

-- 
Dustin Sallings

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

Re: [sqlite] When do I need SQLITE_THREADSAFE?

[Michele Pradella]

  I check in the sqlite3.c code for the SQLITE_THREADSAFE. I can't found 
any difference between SQLITE_THREADSAFE=1 and SQLITE_THREADSAFE=2. I 
found only differences if SQLITE_THREADSAFE=0 or SQLITE_THREADSAFE>0
Have I miss something?

Il 06/10/2010 8.07, Michele Pradella ha scritto:
>So let me know if I understand:
>
> 1. SQLITE_THREADSAFE=0: lock are disabled at all, and you can use
>this if the application  is not multi thread, so if there's only
>one thread that can use the SQLITE library
> 2. SQLITE_THREADSAFE=1: is the highest level of thread safety, so if
>the slowest mode but you do not have to care about multiple access
>to the SQLITE library from different thread. You need this in a
>multi thread application to be sure to avoid damage to the DB
> 3. SQLITE_THREADSAFE=2: it's a compromise between thread safety and
>speed. The library are thread safe but you have to avoid that two
>thread can use at the same time the same DB connection. So if you
>have for example 3 thread and every thread access the library with
>it own connection there's no problem So you can use two different
>DB connection from two thread at the same time without problem. Is
>that right?
>
>
> Il 05/10/2010 18.34, Jay A. Kreibich ha scritto:
>> On Tue, Oct 05, 2010 at 12:44:59PM +0200, Zaher Dirkey scratched on the wall:
>>> On Tue, Oct 5, 2010 at 4:55 AM, Jay A. Kreibich   wrote:
>>>
 On Mon, Oct 04, 2010 at 07:25:05PM -0700, Dustin Sallings scratched on the
 wall:


 The main difference between =1 and =2 is that =2 assumes you more or
less know what you're doing and will either lock a database handle as
you pass it between threads or you'll keep it private to a thread.

=1 is designed to be more or less idiot proof, and will simply not
let you do something stupid with a database handle, like have two
threads try to execute two different statements at the same time.

>>> What if two therad make insert or update then commit, what is happen to the
>>> second thread after the first made commit?.
>> The second thread will block if it attempts to use a database
>> connection that the first thread is already using.  Once the first
>> thread finishes (either with an explicit COMMIT/ROLLBACK, or because
>> all auto-commit transactions go out of scope) then the first thread
>> will release the database connection and the second thread will wake
>> up and allowed to proceed.  That's why the =1 mode is called "serial"...
>> it automatically serializes the database statements.
>>
>> At least, I'm pretty sure that's how it works.  I generally avoid
>> threaded code, and when I do use it, I tend to use thread-specific
>> resources that are carefully locked and guarded.
>>
>>  -j
>>
>


-- 
Selea s.r.l.


Michele Pradella R


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.prade...@selea.com* 
*http://www.selea.com*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When do I need SQLITE_THREADSAFE?

[Michele Pradella]

  So let me know if I understand:

   1. SQLITE_THREADSAFE=0: lock are disabled at all, and you can use
  this if the application  is not multi thread, so if there's only
  one thread that can use the SQLITE library
   2. SQLITE_THREADSAFE=1: is the highest level of thread safety, so if
  the slowest mode but you do not have to care about multiple access
  to the SQLITE library from different thread. You need this in a
  multi thread application to be sure to avoid damage to the DB
   3. SQLITE_THREADSAFE=2: it's a compromise between thread safety and
  speed. The library are thread safe but you have to avoid that two
  thread can use at the same time the same DB connection. So if you
  have for example 3 thread and every thread access the library with
  it own connection there's no problem So you can use two different
  DB connection from two thread at the same time without problem. Is
  that right?


Il 05/10/2010 18.34, Jay A. Kreibich ha scritto:
> On Tue, Oct 05, 2010 at 12:44:59PM +0200, Zaher Dirkey scratched on the wall:
>> On Tue, Oct 5, 2010 at 4:55 AM, Jay A. Kreibich  wrote:
>>
>>> On Mon, Oct 04, 2010 at 07:25:05PM -0700, Dustin Sallings scratched on the
>>> wall:
>>>
>>>
>>>The main difference between =1 and =2 is that =2 assumes you more or
>>>   less know what you're doing and will either lock a database handle as
>>>   you pass it between threads or you'll keep it private to a thread.
>>>
>>>   =1 is designed to be more or less idiot proof, and will simply not
>>>   let you do something stupid with a database handle, like have two
>>>   threads try to execute two different statements at the same time.
>>>
>> What if two therad make insert or update then commit, what is happen to the
>> second thread after the first made commit?.
>The second thread will block if it attempts to use a database
>connection that the first thread is already using.  Once the first
>thread finishes (either with an explicit COMMIT/ROLLBACK, or because
>all auto-commit transactions go out of scope) then the first thread
>will release the database connection and the second thread will wake
>up and allowed to proceed.  That's why the =1 mode is called "serial"...
>it automatically serializes the database statements.
>
>At least, I'm pretty sure that's how it works.  I generally avoid
>threaded code, and when I do use it, I tend to use thread-specific
>resources that are carefully locked and guarded.
>
> -j
>


-- 
Selea s.r.l.


Michele Pradella R


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.prade...@selea.com* 
*http://www.selea.com*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When do I need SQLITE_THREADSAFE?

[Jay A. Kreibich]

On Tue, Oct 05, 2010 at 12:44:59PM +0200, Zaher Dirkey scratched on the wall:
> On Tue, Oct 5, 2010 at 4:55 AM, Jay A. Kreibich  wrote:
> 
> > On Mon, Oct 04, 2010 at 07:25:05PM -0700, Dustin Sallings scratched on the
> > wall:
> >
> >
> >   The main difference between =1 and =2 is that =2 assumes you more or
> >  less know what you're doing and will either lock a database handle as
> >  you pass it between threads or you'll keep it private to a thread.
> >
> >  =1 is designed to be more or less idiot proof, and will simply not
> >  let you do something stupid with a database handle, like have two
> >  threads try to execute two different statements at the same time.
> >
>
> What if two therad make insert or update then commit, what is happen to the
> second thread after the first made commit?.

  The second thread will block if it attempts to use a database
  connection that the first thread is already using.  Once the first
  thread finishes (either with an explicit COMMIT/ROLLBACK, or because
  all auto-commit transactions go out of scope) then the first thread
  will release the database connection and the second thread will wake
  up and allowed to proceed.  That's why the =1 mode is called "serial"...
  it automatically serializes the database statements.

  At least, I'm pretty sure that's how it works.  I generally avoid
  threaded code, and when I do use it, I tend to use thread-specific
  resources that are carefully locked and guarded.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When do I need SQLITE_THREADSAFE?

[Zaher Dirkey]

On Tue, Oct 5, 2010 at 4:55 AM, Jay A. Kreibich  wrote:

> On Mon, Oct 04, 2010 at 07:25:05PM -0700, Dustin Sallings scratched on the
> wall:
>
>
>   The main difference between =1 and =2 is that =2 assumes you more or
>  less know what you're doing and will either lock a database handle as
>  you pass it between threads or you'll keep it private to a thread.
>
>  =1 is designed to be more or less idiot proof, and will simply not
>  let you do something stupid with a database handle, like have two
>  threads try to execute two different statements at the same time.
>
>
What if two therad make insert or update then commit, what is happen to the
second thread after the first made commit?.



>  Either way, any multi-threaded program requires something other than =0.
>
>  In your case, it sounds like =2 would be sufficient, but if you're
>  not looking for every millisecond of performance you can find, the
>  default =1 might be easier and safer.
>

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

Re: [sqlite] When do I need SQLITE_THREADSAFE?

[Jay A. Kreibich]

On Mon, Oct 04, 2010 at 07:25:05PM -0700, Dustin Sallings scratched on the wall:
> 
> On Oct 4, 2010, at 14:46, Jay A. Kreibich wrote:
> 
> >  If you're treating the threads independently, each with their own
> >  database connections, you should be safe with =2 ("multithread"). 
> >  That provides less protection than =1 ("serialized"), but it is also
> >  faster.  Continued from above:
> > 
> >  When compiled with SQLITE_THREADSAFE=2, SQLite can be used in a
> >  multithreaded program so long as no two threads attempt to use
> >  the same database connection at the same time.
> 
> 
> I did read that, but I didn't quite understand what the global state
> is that will be accessed between otherwise independent threads. 
> Reading the code makes that a bit more clear.

  The main difference between =1 and =2 is that =2 assumes you more or
  less know what you're doing and will either lock a database handle as
  you pass it between threads or you'll keep it private to a thread.  

  =1 is designed to be more or less idiot proof, and will simply not
  let you do something stupid with a database handle, like have two
  threads try to execute two different statements at the same time.

  Either way, any multi-threaded program requires something other than =0.

  In your case, it sounds like =2 would be sufficient, but if you're
  not looking for every millisecond of performance you can find, the
  default =1 might be easier and safer.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When do I need SQLITE_THREADSAFE?

[Drake Wilson]

Quoth Dustin Sallings , on 2010-10-04 19:25:05 -0700:
> I did read that, but I didn't quite understand what the global state
> is that will be accessed between otherwise independent
> threads.  Reading the code makes that a bit more clear.

Consider things like how POSIX file locks are per (process, inode)
tuple and get destroyed on any relevant fd close, and therefore SQLite
has a considerable amount of shared-state logic to keep fds for the
same inode open so long as any of them needs to be locked.  (The fact
that your particular fds won't have this property (if you're on a
relevant platform) doesn't mean the shared state isn't necessary to
check.)

That's an example of why thread-awareness would be necessary even for
accessing completely separate databases in separate threads.  Multiple
threads should not access a database handle concurrently regardless of
any of this.

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

Re: [sqlite] When do I need SQLITE_THREADSAFE?

[Dustin Sallings]

On Oct 4, 2010, at 14:46, Jay A. Kreibich wrote:

>  If you're treating the threads independently, each with their own
>  database connections, you should be safe with =2 ("multithread"). 
>  That provides less protection than =1 ("serialized"), but it is also
>  faster.  Continued from above:
> 
>  When compiled with SQLITE_THREADSAFE=2, SQLite can be used in a
>  multithreaded program so long as no two threads attempt to use
>  the same database connection at the same time.


I did read that, but I didn't quite understand what the global state is 
that will be accessed between otherwise independent threads.  Reading the code 
makes that a bit more clear.

I was mainly wondering if there was a difference between having two 
entirely independent threads accessing two entirely independent databases, or 
if =2 was for concurrent access to a single database only.

It sounds like the answer is ``just do it anyway.''

Thanks.

-- 
Dustin Sallings

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

Re: [sqlite] When do I need SQLITE_THREADSAFE?

[Jay A. Kreibich]

On Mon, Oct 04, 2010 at 02:17:06PM -0700, Dustin Sallings scratched on the wall:
> 
>   I've read the documentation, but one thing that was unclear:
> 
>   Do I need SQLITE_THREADSAFE=2 (or 1) when I am using sqlite
>from two different threads entirely independently in a
>single-threaded manner?

  Yes.  This statement seems fairly clear:

  When compiled with SQLITE_THREADSAFE=0 all mutexing code is
  omitted and it is unsafe to use SQLite in a multithreaded
  program.  


  If you're treating the threads independently, each with their own
  database connections, you should be safe with =2 ("multithread"). 
  That provides less protection than =1 ("serialized"), but it is also
  faster.  Continued from above:

  When compiled with SQLITE_THREADSAFE=2, SQLite can be used in a
  multithreaded program so long as no two threads attempt to use
  the same database connection at the same time.


   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] When do I need SQLITE_THREADSAFE?

[Dustin Sallings]

I've read the documentation, but one thing that was unclear:

Do I need SQLITE_THREADSAFE=2 (or 1) when I am using sqlite from two 
different threads entirely independently in a single-threaded manner?

That is, no information sharing between them from my application.  
Specifically, is there any global state that will conflict?

-- 
Dustin Sallings

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